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.

First Step

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.

Second Step

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…

Third Step

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:

img 2

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.


Previous articleThe Dark Times are Here
Next articleTenderfoot No More
I am a small town boy from northern Wisconsin, who grew up in the quintessential American family. Dad was a carpenter, mom stayed home, two brothers and a sister. Our politics, and we were political, was always Democrat. My dad always said all you had to do was to look at what each side was fighting for, and it was easy to see, even as a kid, that Republicans were all about big money and rich businessmen, whereas the Democrats were more about social solutions. I spent 6 years in the US Army in the VietNam era as an electronic instructor for NSA, worked as a Field Engineer for a computer firm based in Massachusetts, spent another few years building paper mills around the world for a firm from Washington state, drove long haul truck for a while, did 10 years of servitude in NYC for a large multinational market research company as the Business Manager, spent some more time on the road as a Business Consultant, and the last bit as the Business Manager for a manufacturing firm here north of Houston. I am trying to start up my own consulting firm using all my experience to help small and medium sized businesses stay out of trouble versus waiting until they get into trouble. No one teaches people how to properly run a business. Business schools and MBA programs really don't. There are very basic nuts and bolts that are either assumed or are ignored, and like the house built on sand, businessmen and entrepreneurs ignore these solid foundations at their peril. (Now retired and doing some substitute teaching at a couple of small K-12 schools here in northern Wisconsin. Living in a small hamlet of 340 people, quiet, peaceful, serene.)

Leave a Comment

Please Login to comment
3 Comment threads
3 Thread replies
Most reacted comment
Hottest comment thread
4 Comment authors
Plutocrats really suckfaux mccoyPocketWatchBig Al Recent comment authors
newest oldest most voted
Notify of
Plutocrats really suck
Plutocrats really suck

PW, if you sat and posed for me, I could make you an immortal. I would sculpt your face, cast it in bronze, and you will live forever in effigy(at least for a while). That is a talent given to me by providence or luck, I have no idea. However, the math is stuff I have a hard time wrapping my brain around. As you have said in a past article though, my business is at the point of stagnation. What you have posted has prompted me to question the way I am doing things, so I thank you.

faux mccoy

i have a gut feeling about this business – that being May is an excellent time to plan for a vacation 😉

thanks, as always!

Big Al
Big Al

Thank you for sharing your very interesting background and story with us, PW. This is good stuff.