Excel: This forecast was bound to happen
Q. Our company creates seasonally adjusted revenue forecasts in Excel 2016 based on historical data using regression analysis. I would like to also chart the seasonally adjusted upper- and lower-bounded forecast ranges, based on a confidence level of 95%. Can you tell me how this might be done?
A. Excel 2016 provides a new tool called Forecast Sheet that automatically calculates and plots your upper and lower forecast boundaries based on your desired level of confidence, and this new tool is smart enough to also calculate and incorporate seasonality into your upper- and lower-boundary forecasts. To use this tool, highlight your historical data, including dates, as pictured in columns B and C in the example screenshot below.
From the Data tab, select Forecast Sheet, expand the tool’s Options if necessary, adjust the desired level of Confidence Interval (95% in this example) if necessary, adjust the Seasonality setting as desired, and then click Create to produce the new forecast (on a new worksheet), an example of which is pictured below.
As you can see, the chart depicts the forecast revenue line (the bolded orange line), as well as upper and lower forecast revenue lines depicting the upper and lower boundaries of the forecast based on the specified level of confidence (the nonbolded orange lines). You can also see that historically (shown as the blue line) the company’s revenue has spiked in the June—August time frame each year; accordingly, the Forecast Sheet tool has automatically factored this seasonality into both the revenue and boundary forecasts.
It is interesting to note that the Forecast Sheet tool produces the forecast results using the two new Excel 2016 functions FORECAST.ETS and FORECAST.ETS.CONFINT, as depicted in the chart’s underlying data table pictured below.
These new functions predict future values based on historical time-based data using the AAA version of the exponential smoothing (ETS) algorithm with the weights assigned to data variances over time in proportion to the terms of their geometric progression based on the following exponential scale {1, (1 − α), (1 − α)2, (1 − α)3, …}. In lay words, this approach weights the data’s seasonal variations by exponentially increasing amounts over time; hence, in this example, the revenue’s seasonality in 2015 has a greater impact on the forecast than the seasonality of the data in 2014, and 2014’s seasonality impacts the forecast more so than 2013’s seasonality, and so on.
If you are using an older edition of Excel, and the Forecast Sheet tool and functions are not available to you, you can still adjust your forecast and boundary calculations for seasonality manually. For example, the worksheet pictured below includes actual revenue for 2015 on row 2, linear-based forecasts calculations on row 6, and simplified boundary calculations on rows 7 and 8. These calculated data are then seasonalized on rows 12, 13, and 14 (highlighted in orange) based on the actual seasonality achieved in 2015 by dividing each calculation (highlighted in green) by the total amount of revenue for 2015, and then multiplying the product by the actual revenue amounts for each respective month in 2015.
Download this example workbook at carltoncollins.com/forecast.xlsx.
About the author
J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a CPE instructor, and a JofA contributing editor.
Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
