Revenue and Cost Data in Excel Sales Forecasting - dummies

Revenue and Cost Data in Excel Sales Forecasting

By Conrad Carlberg

If your ecological niche is in your company’s sales management chain, it’s likely that you keep a record in Excel of the company’s sales results, or you have someone keep it for you. If your products are doing well, you like to fire up Excel, look at those results, and sigh contentedly.

If your products aren’t performing as they should, you reflexively fire up Excel, look at those results, and wonder how to fix what’s gone wrong. Either way, the data is probably in an Excel workbook already, and that makes doing your forecasting easy.

Building revenue baselines is pretty straightforward. You decide on your forecast period (the length of time that’s represented by each observation in your baseline) and if necessary use Excel to figure the total revenue for each period in your baseline. Now you’re ready to forecast how much you’ll bring in during the upcoming period.

Figuring costs, particularly cost of sales, is trickier. Should you include the cost of goods sold? Of course, you’ll include the costs of sales reps’ salaries and commissions, of product promotions, of leave-behinds, of trash-and-trinkets, of travel and entertainment. What about indirect costs?

One way to proceed is to subtract those costs, for each period in your baseline, from the revenues for the same period to get a profit estimate for the period. This creates a new, profit baseline that you can use to forecast the next period’s profit.

But what about opportunity costs? When you spend money to support the sales of one product line, you’re diverting resources from another product line. That’s an opportunity cost: You had the opportunity to spend that money in support of Gidgets rather than Widgets, and you might have created more revenue and more profit if you’d used the money to help sell Widgets.

No rule of accounting tells you whether to include opportunity costs in your profit calculations. Here, you’re not doing accounting, you’re using it to help lay your plans.

The figure illustrates how forecasting can help you plan how to support product lines.

The TREND worksheet function is based on linear regression — here, using the historical relationship between costs and revenues.

The data on Widgets and Gidgets is pretty straightforward. The figure shows the actual revenue and the actual direct costs of supporting each product line during each month from January 2014 through July 2016, in row 3 through row 33. The worksheet gets the profit figures simply by subtracting the costs from the revenues.

Row 35 shows forecasts for August 2016. Here’s how it gets them:

  1. It forecasts the costs for August 2016 using exponential smoothing; see Chapter 15 for more information, but for those of you who are playing along at home, the smoothing constant has not yet been optimized by minimizing the mean square error.
    The cost forecasts are shown in cells C35 and G35.
  2. For each product, it forecasts the revenues for August 2016 by using the regression approach (where you use a dependable relationship between sales revenues and one or more predictor variables to make your forecast), in the guise of the TREND worksheet function.
    Using information about the historic relationship between the costs and revenues for each product, it forecasts in cells B35 and F35 what the revenues would be, given the cost forecasts.
  3. It forecasts the profits for August 2016 by subtracting the forecast cost from the forecast revenue.
    Adding the forecast profits for both product lines results in a total profit for August 2016 of $11,330.

Now, what if you took the opportunity costs of supporting Widgets into account, and instead poured them into Gidgets? In the next figure, you can see the effect of abandoning Widgets and putting its costs — the resources your company spends supporting Widgets — into supporting Gidgets only.

What happens when you abandon Widgets and put its costs into supporting Gidgets.

Here, columns A through H are identical to those in the earlier figure. Columns J through K show the effect of taking the resources away from Widgets and using them to support Gidgets. The following steps show how to get those projections:

  1. In cell K3, enter =C3+G3 and copy and paste the formula into cells K4 through K33, and into cell K35.
    Column K now has the sum of the actual costs for the two product lines from January 2014 through July 2016, plus the sum of the forecast costs in K35.
  2. In cell J3, enter =(F3/G3)*K3 and copy and paste the formula into cells J4 through J33.
    This formula gets the ratio of revenue to cost for Gidgets in January 2014, and multiplies it by the total costs shown in cell K3. The effect is to apply one measure of gross margin to a higher measure of costs, and estimate what the revenue for Gidgets would be in that case.
  3. In cell J35, enter this formula =TREND(J3:J33,K3:K33,K35).
    This forecasts the revenues for Gidgets in August 2016, given the relationship between the projections of revenues and costs in J3 through K33, if you decided to support Gidgets only.
  4. To get a forecast of profit for Gidgets only in August 2016, enter =J35-K35 in cell L35.

Notice that the sum of the profit in August 2016 for Widgets and Gidgets is $4,128 + $7,201 = $11,330. But if you committed your Widget resources to Gidgets, your profit for August 2016 would be $14,004 — $2,674 more. In raw dollars, that doesn’t seem like much, but it’s a 24 percent increase. Generations of European casino owners have grown wealthy on much smaller advantages.

The reason, of course, is that the gross margin on Gidgets is larger than that on Widgets, even though your revenue on Widgets is almost 30 percent greater than on Gidgets. To summarize:

  • In column K, you act as though you had committed all your resources to Gidgets only, from January 2014 through July 2016. The effect is to remove all support from Widgets and add it to the support given to Gidgets.
  • In column J, you estimate the revenues you’d earn if you supported Gidgets only, using the historical margin for Gidgets.
  • Using the TREND function, you regress the revenue estimates in J3 through J33 onto the costs in K3 through K33, and apply the result to the estimated cost in K35. Subtract K35 from J35 to get a forecast of profit if you recognized your opportunity costs and supported Gidgets only.

Using two different scenarios — Widgets with Gidgets, and Gidgets alone — makes this example a little more difficult to follow. But it’s a realistic illustration of how you can use the basic forecasting function TREND to help make an informed decision about resource allocation.

Of course, other considerations would factor into a decision to shift resources from one product line to another — an analysis of the nature of the errors in the forecasts (often termed the residuals), sunk costs, possible retooling to support added manufacturing capacity in a product line, the necessity of ongoing support for customers who have invested in Widgets, and so on. But one of the criteria is almost always financial estimates, and if you can forecast the financials with confidence, you’re ahead of the game.