Columns B and C show actual sales by month for the fictional company.
#Forecast functions in excel 2016 how to#
What are those Excel formulas? Well, let’s see… How to Calculate Centered Moving Averages and the Sales RatiosĬolumn A in this figure shows the sequence number for each row of data. And we do the same for each month thereafter. To find the CMA for August, we simply copy the Excel formula for July’s CMA to the next cell. To get around this problem, we also find the average for February through January of the next year, and then average these two averages, as shown here: (And no, we can’t cheat by arbitrarily assigning the average to one of the two months.) This is a problem, because the average of the sales for January through December doesn’t apply to either June or July. So when we try to calculate the CMA for the twelve months of January through December, we have this problem: This is because seven is an odd number of days, which therefore has a center day.īut a year has an even number of months. And the CMA for Saturday is the average of the sales for Wednesday through Tuesday, as shown here:įinding the CMA for seven days is easy. To find the CMA for Friday, you average the sales for Tuesday through Monday. If you average the sales for Monday through Sunday, you find the centered moving average for Thursday, because Thursday is at the mid-point of those seven days, as shown in this figure: To find a monthly CMA within a year, the averaging period is 12 months…well, sort of. To find the daily CMA within a week, the averaging period is seven days. Specifically, you need to calculate the “centered moving average” (CMA) for each period in your historical data. The most important step is to calculate the correct value for average sales. To calculate the seasonality for days within a week, we create a seasonal index for the days of the week. Then to deseasonalize sales, we divide the sales for each month by its Seasonal Index. This value tells us, on average, how much each month’s sales vary from average sales for the year. For each month of the year we calculate a Seasonal Index (column C in this figure). The idea behind deseasonalized sales is easy to understand. Let’s see how that’s done… How to Deseasonalize Your Sales To forecast sales, we estimate the general direction of deseasonalized sales, and then seasonalize that trend to produce specific sales estimates. That is, the dark line shows deseasonalized sales.īy looking at deseasonalized data, we easily can see that sales fell in 2010 and the first part of 2011, fell slightly in 2012, and generally increased in the remaining periods. The dark gray line in this Excel chart shows sales over the past four and a half years, with the effects of seasonality removed from the data shown by the blue line. The most useful technique was to report, analyze, and forecast deseasonalized sales. To deal with my analytical challenges, I worked out some spreadsheet techniques that really helped. Years ago, I faced these issues when I was the CFO for a small lawn-and-garden company whose monthly sales varied from a low in Winter months of about $50,000 to a high in the Spring of about $1 million per month. It’s hard to picture any method that could generate a reliable sales forecast that has a pattern like the one shown here. In good periods, it’s hard to know whether good sales are better than usual in bad periods, it’s hard to know if bad sales are worse than usual.įorecasting seasonal sales can be just as difficult. Seasonal sales typically are difficult to analyze. In some specific months or days of the week, sales are always above average and in other specific periods, sales are below average. Seasonal sales have about the same pattern every year, every week, or both. The Excel chart below shows the typical saw-tooth pattern of seasonal sales.