Excel: This forecast was bound to happen

By J. Carlton Collins, CPA

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.


Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.