This is the sixth in a series of articles that will hopefully educate readers and illuminate the process of doing business in a simple to understand, nuts-and-bolts way.
To read all the articles in this series, click on any of the following:
In the previous articles, we have covered motivation, a Business Plan, the Organization Chart, Job Descriptions, as well as developing a rational Chart of Accounts.
In this article, I will begin to show how to use that rational Chart of Accounts, mainly in the Profit & Loss section, to build a history of your business to analyze past sales trends. This is very important. We need to understand what has come before so we can plan for the future in a realistic way. This comes back to that Business Plan we developed, so the financial portion of the Business Plan reflects reality and can be used to build reasonable plans for the future.
A Brief History of Sales – Understanding the past…
If you have been following along, by now it should have become evident that we are building a picture of the business, not only from an organizational way or even in a financial way, but in an integrated way. The Business Plan is connected to the Organization Chart, and those are connected to the Job Descriptions, which in turn need to be informed by the Org Chart and Business Plan, which contains the Financial Plan.
So, to produce a sales history, we need… well… history. If you don’t have any sales history because you haven’t started a business yet, don’t despair. Some of the process is the same, and in the end, you will just have to do some research and engage in best guesses for now. Remember, though, that by having a rational Chart of Accounts and tracking sales streams and associated Cost of Goods Sold (COGS) once you start a business, you are building a history that will be very valuable down the road for management and analysis purposes.
If you already have a business, it’s time to gather all the information you can before you start. At this point, we are just interested in monthly Sales figures, broken down into sales/income streams, if possible. For example, you may have to go through all your old customer receipts or paid invoices. Or, you have to fire up the old computer and run a report in your bookkeeping software. Maybe you’ll have to go through all your checkbook statements for the time you’ve been in business to see what deposits were made. You need to do whatever it takes. At the very least, your tax filings may have to do.
Once you have gathered all your records, sort them into years, and then months in each year you’ve been in business. The last 3 years are the most important, but 5 years is better. There is no need to go back farther than that, no matter how long you’ve been in business.
Add up each month’s sales in each year and list them on a piece of paper by year and month. If you have multiple lines of sales, segregate them within each month. Your list should look something like this:
and so on…
Using the above example, we are going to use the information in a couple of different ways, but for now, on to…
In this step, we are going to use just the monthly totals for each year. Go ahead and create another chart like the one above just using the monthly totals for all sales streams, listed by year. So much for 2008 in January, February, and so forth. So much for 2009 in January, etc.
Next to the most recent year you have complete data, mark it “times 5” (x5). The next most recent year is marked (x3), and every preceeding year, mark those (x1). These are multipliers. What we are trying to do is to create a “Weighted Total” and a “Weighted Average” for total sales. The concept is that the most recent information is much more important than any other year. The next most recent year is somewhat more important, and anything 3 years or earlier are much less important.
Now, create a Weighted Total by using the multipliers, adding the most recent year times 5, to the next most recent year times 3, to the third year back with no multiplier, and so forth. List that below the monthly totals.
In Excel, the formula might look like this: =D3+(D4*3)+(D5*5) where D3 is three years ago, D4 is two years ago, and D5 is last year.
On pencil and paper it might look like this:
2008 – $15,000 x 1 = $15,000
2009 – $14,910 x 3 = $44,730
2010 – (no data) x 5 = $0
Total = $59,730
Now comes the tricky part… Take each Weighted Total and DIVIDE by the number of multipliers used to get an average which we will call the Weighted Average. Be careful! If you have no data, you have no multiplier. That’s why we only use data from years with complete information.
To get a Weighted Average, the Excel formula might look like this: =D7/4 where D7 is the Weighted Total, and we can only use data from 2008 and 2009 because we have no data yet from 2010. The number 4 is the sum of the multipliers we DID use.
On pencil and paper, it might look like this:
Weighted Average = $59,730 / 4 = $14,933
The whole thing looks like this:
I know what many are thinking at this point. “Why do we need to go through all this?” The answer lies in statistical analysis. And while I will not go into all that, I would simply like to point out that isn’t the above method exactly how we think and use information? Last year means something. It is recent history. The farther back we go from today, the less important information becomes. What happened in your business three years ago means a whole lot less than last years’ sales figures! Lots could have changed. All I can say is that if this is done carefully, I have yet to be more than 5% off of any projections based on this method, unless forces external to the business caused a major and unexpected problem, or the nature of the business is totally hit-and-miss, as in a casual business on the side.
What all this means, and why do I care?
Building this little database and doing the calculations will tell you a lot. It begins to tell you how periodic your sales are, in real numbers. Is the spring your best part of the year? And if so, by how much? Are the sales in Sales1 carrying the business over a lean time in Sales2? And if it is, again, by how much, really? What can I expect to see as total sales at the end of the current year if I use the Weighted Averages each month as goals? These are important questions. Clients of mine have been stunned when this is presented to them. They usually do these projections and answer these sort of questions by guesswork and feel. I cannot count the times I’ve heard “I have a feel for my business” and then, when shown this breakdown, are amazed at the actual numbers.
Please contact me offline at [email protected] with anycomments, suggestions or ideas for future articles that you may not want to share here.
- What goes into a business plan? (e27.sg)
- Accounting Forecasting Tips for Seasonal Firms (brighthub.com)
- Business Plan or Roadmap – What’s the difference and does it matter? [Sonia Gavira] (ecademy.com)
- Ten Top Investor Turnoffs Around Business Plans (blogs.forbes.com)
- Marty Zwilling: 10 Business Plan Follies That Make Investors Cringe (huffingtonpost.com)
- Let Your ‘Story’ Frame Your Business Plan (entrepreneur.com)
- Business Plan Outline (imbrandon.com)
- Four Key Elements in an Abbreviated Business Plan (blogs.sitepoint.com)
- Want to survive? Then its time for an annual small business plan (usatoday.com)