Chester Tugwell 42, views. The raw data is in Cells A2-B This post may contain affiliate links. By this, I mean something that can be constructed simply such that if anyone follows the same process they will get the same figures. The optimal value of alpha can be found either by manually changing alpha until the minimum RMSE is found, or else you can use the "Solver" to perform an exact minimization. By looking at deseasonalized data, we easily can see that sales fell in and inand increased slightly in the other periods. The formula in H26 is:. Now the scene is set for having a simple, mechanical approach for budgeting. Excel to Excel 1, views. The chart shows 2 years of sales revenue.

Enter the following. It is clear that there is a seasonal variation, with revenue peaking in In this circumstance, we cannot use the FORECAST function by itself.

## Using the FORECAST function with seasonality Excel Off The Grid

A seasonal index is a way of measuring the seasonal variation -- that is, to measure the change that is due to Calculating the seasonal index allows a business.

That's probably a good idea. As you start to work with your seasonal index, you'll probably have a few questions. Exponential triple smoothing ETS sounds like a dairy process, but it actually uses the weighted mean of past values for forecasting. Now the scene is set for having a simple, mechanical approach for budgeting.

Linear regression works where there is a relationship between the X and Y values i.

Video: Seasonal variation formula in excel Moving Averages, Trend Line and Seasonal Variation

function, using the same cell references, to calculate the average period. This Excel chart shows the typical saw-tooth pattern of seasonal sales. To find the CMA for August, we simply copy the Excel formula for July's CMA to the next.

This needs to be a mechanical, objective process. Skip navigation.

### How to Use Microsoft Excel to Calculate Seasonal Indexes

Hi- Thank you for very through explanation and great example to practice with. The most useful technique was to report, analyze, and forecast deseasonalized sales.

Nimesh says: Hi- Thank you for very through explanation and great example to practice with. Look at the chart below: The chart shows 2 years of sales revenue.

It is usually a good idea to see what happens to the long-term trend projection when alpha is varied, because the value that is best for short-term forecasting will not necessarily be the best value for predicting the more distant future. This chart also clearly illustrates how the model with a smaller value of alpha is slower to respond to "turning points" in the data and therefore tends to make an error of the same sign for many periods in a row.

With this particular value of alpha, which is optimal for one-period-ahead predictions, the projected trend is slightly upward, reflecting the local trend that was observed over the last 2 years or so.

A combination of two offset averages rather than a single average is needed for centering purposes when the number of seasons is even.

And we do the same for each month thereafter. Related Resources.

Label column A "Month. Furthermore, operational managers can review the trend and state where future numbers are wrong, and all they need to do is explain the variation, ie, undertake incremental budgeting.