• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Forecast Sheet

You are here: Home / Excel Charts / Excel Forecast Sheet
Excel Forecast Sheet
February 5, 2018 by Mynda Treacy

The Excel Forecast Sheet and charts are a new feature available in Excel 2016. In just a few clicks you can get a forecast done for you, including a chart like the one below:

Excel Forecast Sheet

This new forecasting tool creates a copy of your data and inserts it on a new sheet in your file, along with the forecast formulas to predict the future values, and all presented in a chart.

Watch the Video

Subscribe YouTube

Download Workbook and Take a Look

Note, this workbook contains functions only available in Excel 2016 or later. If you don’t have Excel 2016 then you can’t edit the FORECAST.ETS… formulas.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Data Required for Excel Forecast Sheet

All you need is some historical time-based data in a tabular format. Excel needs a column containing the dates/times at consistent intervals, and a column containing the values.

For example, below is the data I’m using, which is visitor numbers to Hawaii:

number of Hawaii visitors

Data source: HTA and DBEDT

Note: Your date/time data can be missing up to 30% of the data points or have several numbers with the same time stamp. Excel will average the data with the same time stamp. However, summarizing data before you create the forecast will produce more accurate forecast results.

Inserting Excel Forecast Sheets

To insert a Forecast chart, start by selecting your data, or if it’s a contiguous range just select a cell in the table > Data tab > Forecast Sheet:

inserting Excel forecast sheet

This opens the Create Forecast Worksheet dialog box, shown below, where you can choose the chart type (1) and when to end the forecast (2):

Create Forecast sheet

Clicking on ‘Options’, below the ‘Forecast End’ field, reveals more settings. These are explained as follows (numbers below correspond to image above):

 
Forecast Options Description
Forecast Start (3) This is the date the forecast begins. By default, it’s the end of your data, but if you want to exclude data from the forecast then you can choose a date before the end of the historical data. This will only include data prior to the ‘start date’ in the forecast (this is sometimes referred to as "hindcasting").

Tips:

  • Starting your forecast before the last historical point allows you to get a feel for the prediction accuracy as you can compare the forecasted series to the actual data. However, if you start the forecast too early, the forecast generated won't necessarily represent the forecast you'll get using all the historical data. Using all your historical data gives you a more accurate prediction.
  • For seasonal data it is recommended to start the forecast before the last historical point.
Confidence Interval (4)

The confidence interval is the range surrounding each predicted value, in which 95% of future points are expected to fall, based on the forecast (with normal distribution). These are the two finer lines either side of the forecast line (or if you choose the column chart option they are the error bar values).

The confidence interval can help you get a feel for the accuracy of the forecast. A smaller interval implies more confidence for the specific point.

Change the default level using the up or down arrows, or turn confidence intervals off by unchecking the box.

Seasonality (5)

Seasonality is a number for the length (number of points) of the seasonal pattern and is automatically detected. For example, in a yearly sales cycle, with each point representing a month, the seasonality is 12. You can override the automatic detection by choosing Set Manually and then entering a number.

When the seasonality is not significant enough for the algorithm to detect, the prediction will revert to a linear trend.

The default value of 1 means Excel detects seasonality automatically for the forecast and uses positive, whole numbers for the length of the seasonal pattern. 0 indicates no seasonality, meaning the prediction will be linear.

Note: When setting seasonality manually, avoid a value for less than 2 cycles of historical data. With less than 2 cycles, Excel cannot identify the seasonal components.

Include Forecast Statistics (6)

Excel can automatically generate a table of statistics using the FORECASE.ETS.STAT function, including measures such as the smoothing coefficients (Alpha, Beta, Gamma), and error metrics (MASE, SMAPE, MAE, RMSE).

Check this box if you want this additional statistical information on the forecast included in a new worksheet.

Timeline Range (7) By default, the timeline range includes all dates in your table, but you can change it here. Note; the range must match the Values Range.
Values Range (8) By default, the Values range includes all values in your table, but you can change it here. Note; the range must match the Timeline Range.
Fill Missing Points Using (9) Excel uses interpolation to handle missing points, which means missing point will be completed as the weighted average of its neighboring points, where fewer than 30% of the points are missing. If you prefer, you can treat the missing points as zeros by selecting Zeros in the list.
Duplicate Aggregates Using (10) Excel will average values that contain the same date/time. You can choose another calculation method, such as Median, from the list.
 

Excel Forecast Sheet

The new Forecast sheet contains a Table and Chart. The bottom of the Table contains a series of FORECAST formulas (see colored boxes in image below):

Excel forecast formulas

This is all created for you automatically.

Forecast Statistics

If you choose to include the Forecast Statistics in the Options when creating the forecast sheet, Excel will include a table of different statistics, shown below, beside the forecast table.

They all use the FORECAST.ETS.STAT function as you can see in column I below, which displays the formula from column H. Notice that the 3rd argument is what specifies the statistic type:

forecast statistics

Sharing Excel Forecast Sheets

You can share Excel workbooks containing forecast sheets and charts with users who have earlier versions of Excel. They will see the table and chart, but they can’t edit the formulas.

Modifying the Excel Forecast Sheet

So, you’ve created your forecast sheet and then you realise you want to change something.

Firstly, there is no link back to the original source data, so if you make changes to your source data you need to recreate the forecast sheet, or also edit the data in the forecast sheet table.

Also, there’s no way to re-open the ‘Create Forecast Worksheet’ dialog box and make changes to the Options and settings of the forecast. However, you can modify the FORECAST formulas directly in the Table.

For example, if you want to change the seasonality, you’d edit the ‘seasonality’ argument in the forecast formulas. Taking the example below, we’d alter the 12:

forecast sheet example 1

You can also edit the ranges being referenced to tweak the forecast. For example, the data below is on a consistent upward trend, but you can see it then takes a dip in the first forecast period:

forecast sheet example 2

Maybe you’d prefer it continued the upward trajectory, in which case you could alter the timeline and values ranges to forecast based on more recent data:

forecast sheet example 3

Don’t forget to also alter the confidence bound formulas in columns D and E.

Forecast Accuracy

You can get a feel for how accurate the forecast is by starting it prior to the end of your actual data. In the example below I've copied the forecast formula back to row 18. You can see the yellow forecast line in the chart veers off track from May to July.

forecast sheet example 3

Functions Used in Forecast Sheet

The Forecast Sheet uses some of the new forecast functions available in Excel 2016, as follows:

Forecasted Values - FORECAST.ETS Function:

=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

Confidence Interval – FORECAST.ETS.CONFINT Function:

=FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])

Forecast Stats – FORECAST.ETS.STAT Function:

=FORECAST.ETS.STAT(values, timeline, statistic_type, [seasonality], [data_completion], [aggregation])

They all contain similar arguments, which are described below:

Target_date - The next data point you want to forecast. This can be date/time or numeric

Values – The range of cells containing the historical values you’re basing your forecast on. This range must be the same size as the Timeline argument’s range.

Timeline - The range of cells containing the dates that relate to the historical ‘values’. The timeline values must have a consistent step between them and can’t be zero. The ranges of the timeline and values must be the same size, or Excel will return the #N/A error.

The timeline doesn’t need to be sorted, as Excel will sort it implicitly for calculations. If a constant step can't be identified in the timeline range, Excel will return the #NUM! error. If timeline contains duplicate values, Excel will return the #VALUE! error.

[Seasonality] – An optional numeric value. The default value of 1 means Excel detects seasonality automatically for the forecast and uses positive, whole numbers for the length of the seasonal pattern. 0 indicates no seasonality, meaning the prediction will be linear. Positive whole numbers will indicate to the algorithm to use patterns of this length as the seasonality. For any other value, Excel will return the #NUM! error.

Maximum supported seasonality is 8,760 (number of hours in a year). Any seasonality above that number will result in the #NUM! error.

[Confidence_level] - An optional numerical value greater than 0 and less than 1, indicating a confidence level for the calculated confidence interval. For example, for a 95% confidence interval, a 95% confidence level will be computed (95% of future points are to fall within this radius from prediction). The default value is 95%. If your confidence interval value is zero or less, or 1 or more, Excel will return the #NUM! error.

[Data completion] – This is an optional argument and although the timeline requires a constant step between data points, Excel supports up to 30% missing data, and will automatically adjust for it. 0 will indicate the algorithm to account for missing points as zeros. The default value of 1 will account for missing points by completing them to be the average of the neighboring points.

[Aggregation] - Optional argument. Although the timeline requires a constant step between data points, Excel will aggregate multiple points which have the same time stamp. The aggregation parameter is a numeric value indicating which method will be used to aggregate several values with the same time stamp. The default value of 0 will use AVERAGE, while other options are SUM, COUNT, COUNTA, MIN, MAX, MEDIAN.

Statistic_type – This is numeric value between 1 and 8, indicating which statistic will be returned for the calculated forecast. See table below for descriptions:

 
statistic_type Description
1 Alpha parameter of ETS algorithm - Returns the base value parameter—a higher value gives more weight to recent data points.
2 Beta parameter of ETS algorithm - Returns the trend value parameter—a higher value gives more weight to the recent trend.
3 Gamma parameter of ETS algorithm - Returns the seasonality value parameter—a higher value gives more weight to the recent seasonal period.
4 MASE metric - Returns the mean absolute scaled error metric—a measure of the accuracy of forecasts.
5 SMAPE metric - Returns the symmetric mean absolute percentage error metric—an accuracy measure based on percentage errors.
6 MAE metric - Measures the average magnitude of the errors in a set of predictions, without considering their direction.
7 RMSE metric - Returns the root mean squared error metric—a measure of the differences between predicted and observed values.
8 Step size detected - Returns the step size detected in the historical timeline.
 

Tip: These new Forecast functions can also be used independently of the forecast sheet, just like regular functions.

Forecasting Pre Excel 2016

If you’re using Excel 2013 or earlier, you can create your own Forecast sheet manually using the FORECAST function (with some limitations).

For example, the chart below shows the result of the FORECAST function in the dashed line. You’ll notice it doesn’t take seasonality into account, this is the first limitation, as the FORECAST function uses linear regression and doesn’t have the ability to factor in seasonality.

forecast chart

The other limitation is that there’s no equivalent function to calculate the confidence intervals using exponential triple smoothing (ETS) that the new FORECAST.ETS.CONFINT function uses. The closest you’ll get is to use the CONFIDENCE.NORM function.

Please Share

If you liked this please click the buttons below to share.

email icon twittericon fb icon LI icon
Excel Forecast Sheet

More Excel Charts Posts

excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.
stacked bar waffle chart

Stacked Bar Excel Waffle Charts

Stacked Bar Excel Waffle Charts are an alternate to using conditional formatting to build waffle charts, and some say they're easier.
Category: Excel Charts
Previous Post:Labelling Events in Excel Charts
Next Post:Excel Infographicsexcel infographic

Reader Interactions

Comments

  1. Kendra

    February 8, 2018 at 3:51 am

    I have daily overtime hours (M-F, but I’ve added Sat. & Sun. to make it spaced 1 day intervals) for a quarter and want to use the forecast sheet to project the next quarter. When I try to use the forecast sheet with this data I get the error message: “A forecast can’t be created because the timeline isn’t evenly spaced.” What am I doing wrong?

    Reply
    • Mynda Treacy

      February 8, 2018 at 9:50 am

      Hi Kendra,

      Excel can handle up to 30% missing dates so not having Saturday and Sunday in your data set should be fine.

      Are your dates actual dates or are they text ‘M’, ‘T’, ‘W’ etc.? Perhaps you could post your question and sample Excel file on our Forum so we can see the data you’re working with.

      Mynda

      Reply
  2. Fabio

    February 8, 2018 at 1:56 am

    Hi Mynda, thanks for the clear and complete explanation.
    I noticed that the SMAPE metric and the MAE metric share the same description… is this correct?

    Reply
    • Mynda Treacy

      February 8, 2018 at 10:04 am

      Hi Fabio,

      Well spotted. This definition is from Microsoft’s documentation on the FORECAST.ETS.STAT page, but I think you’re absolutely right, they should be different, albeit slightly.

      MAE should read something more like: “Returns the symmetric mean absolute percentage error metric—an accuracy measure based on percentage errors.”

      I’ll edit the post above and let Microsoft know.

      Cheers,

      Mynda

      Reply
  3. Ted Kowalski

    February 6, 2018 at 6:53 am

    How do you enter the custom format to wrap the date?

    Reply
    • Mynda Treacy

      February 6, 2018 at 8:08 am

      Hi Ted,

      In the Format Cells dialog box use CTRL+J to force the format to wrap. You also need to format the cells with Wrap Text.

      Thanks to Jon Peltier for that tip.

      Mynda

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x