Create the forecast for how much the overall sales target should be for next quarter.
The business they are in is cyclical, meaning the amount of sales goes up and down during the year. In order to forecast, you need to take this into account. By looking at the numbers in 3rd quarter from the prior year, you can see that it dropped from 152K to 144K. This tells us that our forecast for this year’s third quarter should probably drop from the second quarter.
The easiest way to do a simple forecast is to take a moving average of the year over year growth rate and apply that to the same quarter from last year. While 2 years seems like a long time for this database system to be in place, its really just beginning to be useful as it now contains a few quarters we can use to compare yearly data. We will use a 2 quarter moving average of the year over year growth. Forecasting remains an art, and the forecast generated here would simply be a starting point to have fact based discussions. To provide context, we generally include what the forecast would have been for the prior periods using the same method. The results show that the forecast would have been about 13% high in the first quarter, and 6% low in the second quarter.
The result should look like this:
To compute the total for each quarter, use the sum function and copy/paste across the columns. To compute the year over year change for the 7/1/95 period (cell F13) take the total for that quarter less the total for the same quarter in the prior year and divide by the total from the same quarter last year e.g. =(this year-last year)/last year
This means (144.5-56.8)/56.8 or 87.7/56.8. If sales grew by 87K from a starting point of 56K, that represents a 154% increase. You want to get to a point where you can glance at numbers like these and do the rough math in your head, e.g. 90 is roughly 1.5 times as big as 60.
Copy/paste the formula for F13 across to I13.
To compute a 2 quarter moving average, you use the average function on the prior two quarters, e.g. the formula for H14 should take an average of F13 through G13
Copy/paste that formula across to J14.
To compute the forecast, multiply the moving average growth rate by the sales from the same quarter last year, e.g. the formula for H15 should be D12 * (one plus H14) . The 1 is added to the growth rate because you are not simply calculating how much it grows over last year, but the growth plus what it was last year to get at what you think it will be this year. If your salary is going to grow 50% this year and you made 20K last year, you can’t simply multiply 20K by 50% – that just tells you your salary is going increase 10K this year. To know how much the salary is going to be we need to add that to what it was last year, e.g. 20+10=30K. That’s a lot more math in the formulas, so we simply add 1 to the growth rate which accomplishes the same result 20*1.5 = 30.
Copy/paste that formula across to J15, and we see the forecasted sales for next quarter to be 254K.
The expert performance time for this task is 2 minute 30 seconds. This assumes you already have the quarterly sales data in place in excel.