by Richard Harshaw
It’s that time of year—time to do your sales and budget forecast. (Actually, you should have already done it, as I recommend you do your forecasts early in the 4th quarter, but I know you’re busy trying to dodge the sub-surface mines in this crazy economic sea!)
Let’s walk through a simple—real simple—example. Let’s do a forecast for Fester’s Best Heating Company, a mid-sized outfit located in the suburbs of a large metro area.
Fester has 8 employees—he oversees the 4 installers; his wife oversees the 2 service techs. He has sales and expense records for the last 9 years by month.
Fester should begin by taking the average of his monthly sales over the last three years. There are two ways he can do this. He can simply add the three Januaries together and divide by three; repeat for the three Februaries, and so on. Or, to be more accurate, he should do a weighted average—take last January times 3, the January before that times 2, and the third January back times 1. Add the results and divide that total by 6 (the sum of the weights). This gives half the vote to the most recent year and better reflects current economic trends. (After all, 2007 was the year before the economic crisis developed.)
He should then figure what each month is as a percentage of the total for the year. Here are Fester’s numbers:
January 4.4% February 1.3% March 4.3%
April 4.7% May 11.1% June 14.5%
July 14.0% August 12.7% September 7.2%
October 9.3% November 8.1% December 8.5%
Last year, Fester’s sales totaled $980,000. He expects inflation to be flat and he wants to grow sales by 15%. What should he see for sales by month next year?
Simple! Just take $980,000 times 1 (for no inflation) and take that times 1.15 for $1,127,000. (If he wanted to assume inflation, he would multiply last year’s sales by 1.x where x is the assumed inflation rate.)
January’s sales would then be $1,127,000 x 0.044 = $49,588 (which we can round off to $49,600). February would be $1,127,000 x 0.013 = $14,700 (approximately), and so on. I’ll leave it to Fester to finish this task.
Next, he has to estimate his direct costs. Using last year’s data, he sees that for every dollar he sold, he spent $0.40 at the suppliers for equipment and material. That lets Fester estimate his monthly material costs by simply multiplying the monthly sales by 0.40 (assuming no change in supplier prices… yeah, right). If suppliers raise their prices, he would need to add a factor for the price increases.
For labor, he sees that on average in labor came to 22% of sales. Likewise, he can ballpark monthly labor costs by multiplying the monthly sales by 0.22.
Finally, all the other direct costs (such as freight, subs, warranty and so on) came to 5% of his sales, so he can multiply each month’s sales by 0.05 to get a ballpark figure for the rest of the costs. He can then add all of these up to get the monthly direct costs forecast. (In this case, it would come to $1,127,000 x 0.67 or $755,090, giving him a gross margin of $371,910 (33%).
For the overhead, he needs to figure it two ways—a monthly estimate tied to the sales for the variable overhead (such as fuel, postage, utilities, advertising and the like) and a fixed monthly amount for the fixed overhead (like rent, office salaries, dues, license, and the like). Fester needs to look at his overhead from last year and ask himself for each account what he expects to see in the upcoming year—more or less, and adjust his forecast accordingly. For the fixed overhead, he then divides the total of all the fixed overhead expenses by 12 and puts that in each month’s budget column. For the variable overhead, he would take the total variable overhead and multiply it by the sales percentages by month to get a good idea of what those expenses will do.
Finally, if Fester expects any expense to change by a factor other than these assumptions, he must allow for that. For instance, if he expects health care costs to rise more than the 15% sales goal, he needs to bump up his insurance costs accordingly. Likewise, if he plans on a wage increase for his employees, he may need to increase labor by more than the 15% volume increase, unless the sales increase will come with the same amount of labor as last year.
The entire process can be done by hand on a few sheets of paper in an afternoon. It can be done on a spreadsheet in a few minutes (after you design the sheet, which may take several days).
Once done, Fester should do one final thing—figure two more forecasts off the one he just finished. He needs to run a forecast called “Holy Cow, what a Year!” (for a year much better than he thought) and another for “Oh, No, The Defecation is Hitting the Air Circulator!” (for a terrible year). Then he should watch sales and expenses month by month and see which track he seems to be on, and then adjust his plans accordingly.
Have a happy and safe holiday, and may next year find you rising out of the ashes to a strong future!
