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.

techqa-2


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.

techqa-3


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.

techqa-4


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.

techqa6

 

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.

SPONSORED REPORT

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.