How to use the Excel forecast function
Yankee Hall of Fame catcher Yogi Berra said, “Forecasting is hard, especially about the future.” Many of your jobs require you to forecast future revenue, unit sales or profits. For example, in April, you may need to forecast sales for the remainder of the year. Microsoft Excel’s Forecast Sheet tool (introduced in Excel 2016) can often make forecasting easy. This article will introduce the forecasting tool in Excel.
Forecasting in Excel
Today’s work will be in the Forecasting with Excel spreadsheet. Within this spreadsheet, we’ll use monthly U.S. vehicle sales in thousands for the years 2014-2018 to forecast monthly sales for the years 2019-2020. Please note: your data must be in a .xlsx (not .xls) file to use the Forecast Sheet tool.
Here are step-by-step instructions.
- To begin, select the dates and sales that we’ll use to generate forecasts. For 2015-2018 we select the cell range A5:B64.
- Then, from the Forecast Group on the Data Tab we selected Forecast Sheet. Since we want to forecast monthly sales for 2019 and 2020, we change the Forecast End Date to 12/1/2020 (see Figure 1).
Figure 1 Monthly Auto Sales
In Figure 1, 2015-2018 monthly sales are in blue and monthly forecasts for 2019-2020 are in orange. Excel provides forecasted monthly sales in 2019 and 2020 with 95% probability. After clicking Create, you find the forecasts and lower and upper values for the 95% confidence interval in a new worksheet (see Figure 2). For example, cells D52 and E52 tell us that we are 95% sure that January 2019 auto sales will be between 1084.7 and 1323.5.
We copied the actual 2019 and 2020 sales into this worksheet. For example, for January 2019, actual sales were 1172.04 and our forecast was 1204.11. Thus, our absolute percentage error (APE) (see cell E52) is the absolute value of (1172-1204)/1172 = 2.7% For 2019, our Mean Absolute Percentage Error (MAPE) is a respectable 4.8%
Figure 2 Monthly Auto Sales Forecasts
The Forecast Sheet creates forecasts by using the trend and seasonal characteristics of the given data (2015-2018). In other words, the Forecast Sheet forecasts the future by assuming the future will behave in a fashion like the past data that was used to create the forecast. Note that for the period March-June 2020 our forecasts are much larger than the actual sales. This is because there was no way Excel could have foreseen the occurrence of COVID lockdowns, which greatly reduced auto sales.
You can test whether the Forecast Sheet tool provides acceptable forecasts for your organization. To do this, forecast several time periods using the previous 5 years of data. If the Forecast Sheet yields a smaller MAPE than your current forecasting method, the Forecast Sheet may be an easy method to create useful forecasts.
Learn more Excel functions that are useful in the accounting world with the Becker Microsoft Excel Fundamentals and Data Analytics Certificate.