4 Forecasting functions in Microsoft Excel
Quantitative forecasts are as in-demand as ever. This post provides four solid forecasting options in Microsoft Excel that can be used to predict sales, operating costs, performance and more. The forecasting methods explained include: moving average, the Excel FORECAST function, trendline and regression using the Analysis ToolPak.
A moving average predicts the future value of a time period through averaging past time period values. That forecast can then be used in further forecasting down the line, averaging it with other time period values. The risk in Moving Average forecasting is that it can lag behind a trend.
- Select the Data tab, then Data Analysis command button. In the Data Analysis dialog box, select the Moving Average item from the list and then click OK.
- Identify the data you want to use to calculate the moving average. Click in the Input Range text box of the Moving Average dialog box. Then identify the input range, either by typing a worksheet range address or by selecting the worksheet range. Your range reference should use absolute cell addresses ($A$1:$A$5 as opposed to A1:A5).
- Indicate how many values are to be included in the moving average calculation in the Interval text box. By default, Excel uses the most recent three values to calculate the moving average (i.e 3 month moving average, 3 year moving average, etc.). To specify that another number of values are to be used to calculate the moving average, enter that value into the Interval text box.
- Use the Output Range text box to establish the worksheet range into which you want to place the moving average data.
The FORECAST Function
The FORECAST Function predicts a future value using existing values. The predicted value is a y value for a given x value. The known values are existing x values and y values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends.
FORECAST (x, known_y’s, known_x’s)
X is the data point for which you want to predict a value.
Known Ys are the dependent range of data.
Known Xs are the independent range of data.
A Trendline is simple way to analyse the trend within a collection of data points within a graph, smoothing out the data oscillations in the process. The trend within the data is then used to forecast future performance.
- Create a graph with your existing data.
- Right click on any of the data oints within the graph and select Add Trendline.
- In the Format Trendline window select whether you would like to forecast/analyse using: exponential, linear, logarithmic, polynomial, power or moving average. Linear is most common. If the data appears to trend towards compounding, try exponential
- Click on close.
Regression Using the Analysis Toolpak (ATP)
- Ensure Analysis Toolpack is installed (Tools tab, select Excel Add-ins, select Analysis Toolpak)
- Select the Tools tab
- Select Data Analysis
- In the Data Analysis window select regression.
- The Input Y range represents what you want to estimate (likely sales)
- The Input X range represents the data that can explain your Y (likely unit cost or price)
- Click OK
What does all this mean?In Regression Statistics
Multiple R represents to correlation coefficient between Y and X
R Square represents the percentage of Y you can explain from X. A number closer to 1 indicates low variability and a number closer to 0 indicates a random correlation between your X and Y.
Regression represents the number of independent variables
Risidual represents Total – Regression
Total represents the number of values – 1 (minus 1) (likely the number of active rows – 1)
SS represents Sum of Square
MS represents Mean Sum of Square
Lower % and Upper % means that 95% of the time, your coefficient will be between the lower value and the upper value
P-Value – The lower the P-Value, the less variability you have. The result of 1 – P-Value provides you with the percentage that the intercept will be correct
Cover photo credit: Finance Monthly