Try the Excel Course for Free!

Forecast Sheets in Excel – Instructions

/ / Excel 2016, Excel 2019, Excel for Office 365, Latest, Microsoft, Office 2016, Office 2019, Office 365

Forecast Sheets in Excel: Video Lesson

            This video lesson, titled “Forecast Sheets,” shows you how to create forecast sheets in Excel. This video lesson on creating forecast sheets in Excel is from our complete Excel tutorial, titled “Mastering Excel Made Easy v.2019 and 365.”

Forecast Sheets in Excel: Overview

            You can insert forecast sheets in Excel into a workbook to predict future trends from existing time-based data. To create forecast sheets in Excel, you must have a series of date or time entries for the timeline. You must also have corresponding values for those time or date entries. From these existing date or time values, Excel can then project future trends and the associated values for future dates.

            Before you create forecast sheets in Excel, you should ensure your timeline’s values have consistent intervals between its data points. For example, you could have consistent daily, monthly, or yearly values for which to create a forecast. The timeline can have up to thirty percent of its data points missing or have duplicate time stamps on different values and still produce an accurate forecast. However, you may want to summarize and organize the data before creating forecast sheets in Excel to produce the most accurate forecast.

            To create the forecast sheet, either select both data series to forecast or click into a cell within one of the data series to forecast. Then click the “Data” tab in the Ribbon. Then click the “Forecast Sheet” button in the “Forecast” button group to open the “Create Forecast Worksheet” dialog box. Choose either the “Create a line chart” or the “Create a column chart” button in the upper-right corner of the dialog box. Doing this then displays the forecast as either a line chart or column chart. Below the chart, use the “Forecast End” drop-downs or spinner box to set the end date/time or ending value and set how far out to project the forecast.

Forecast Sheets in Excel - Instructions: A picture of the “Create Forecast Sheet” dialog box in Excel.

Forecast Sheets in Excel – Instructions: A picture of the “Create Forecast Sheet” dialog box in Excel.

            To set additional, advanced forecast options, click the “Options” arrow or link at the bottom of the dialog box to expand that section, if needed. You can select a different forecast start date by using the “Forecast Start” drop-downs or spinner box to set the start date/time or starting value for the forecast. If you pick a date before the end of the historical data, only data prior to the start date is used in the forecast. For seasonal data, Excel recommends starting the forecast before the last historical point.

            You can check or uncheck the “Confidence Interval” checkbox to show or hide the confidence interval in the forecast. If shown, the default confidence level of “95%” can be changed by using the adjacent spinner box. Alternatively, you can also type a new percentage to use into the box.

            To choose how to determine seasonal intervals, select a “Seasonality” option button. The default option is “Detect Automatically.” To manually set seasonality intervals, select the “Set Manually” option button. Then enter the seasonal value into the adjacent field. For example, in a yearly sales cycle where each data point plotted represents one month, “12” is the seasonality value. When manually setting seasonality, avoid using a value less than “2” to prevent the forecast from reverting to a linear trend.

            In the “Timeline Range” field, the range of timeline values appears. These are the time values that correspond to the data values in the “Values Range” field. The “Values Range” field contains the data values for the timeline values.

Forecast Sheets in Excel - Instructions: A picture of a forecast sheet created in Excel.

Forecast Sheets in Excel – Instructions: A picture of a forecast sheet created in Excel.

            You can use the “Fill Missing Points Using” drop-down to select “Zeros” to fill-in missing data points with zeros. The default value of “Interpolation” uses the weighted average of neighboring points to fill-in the value of missing points.

            If your data series contains multiple values with the same date/time stamp, Excel averages the values with the same date/time stamp. To use a different function, like “MEDIAN,” select the function to use from the “Aggregate Duplicates Using” drop-down.

            To include forecast statistics on a new sheet, then check the “Include forecast statistics” checkbox. This includes a table of statistics generated by the FORECAST.ETS.STAT function. It includes measures, like the smoothing coefficients and error metrics.

            To create the forecast sheet, then click the “Create” button in the lower-right corner to add the new forecast worksheet to your workbook. This sheet contains the selected data series, the forecasted values, and the upper and lower confidence bounds, if selected, in a table on the sheet. These values appear within a chart on the same sheet. You can then edit the table’s values or formulas to change the values in the forecast chart, if desired.

Forecast Sheets in Excel: Instructions

  1. Before creating forecast sheets in Excel, you must have a series of date or time entries for the timeline.
  2. You must also have corresponding values for those time or date entries.
  3. To create a forecast sheet, select both data series to forecast.
  4. Alternatively, click into a cell within one of the data series to forecast.
  5. Then click the “Data” tab in the Ribbon.
  6. Then click the “Forecast Sheet” button in the “Forecast” button group to open the “Create Forecast Worksheet” dialog box.
  7. To display the forecast as either a line chart or column chart, then choose either the “Create a line chart” or the “Create a column chart” button in the upper-right corner of the dialog box.
  8. To set the end date/time or ending value and set how far out the forecast should be projected, use the “Forecast End” drop-downs or spinner box below the chart.
  9. If you want to set additional, advanced forecast options, then click the “Options” arrow or link at the bottom of the dialog box to expand that section, if needed.
  10. To select a different forecast start date, then use the “Forecast Start” drop-downs or spinner box to set the start date/time or starting value for the forecast.
  11. To show or hide the confidence interval in the forecast, then check or uncheck the “Confidence Interval” checkbox.
  12. If shown, the default confidence level of “95%” can be changed by using the adjacent spinner box.
  13. Alternatively, you can also type a new percentage to use into the box.
  14. To choose how to determine seasonal intervals, then select an option button under “Seasonality.” The default option is “Detect Automatically.”
  15. To manually set seasonality intervals, select the “Set Manually” option button.
  16. Then enter the seasonal value into the adjacent field.
  17. In the “Timeline Range” field, the range of timeline values appears. These are the time values that correspond to the data values shown in the “Values Range” field.
  18. The “Values Range” field contains the data values for the timeline values.
  19. To fill-in missing data points with zeros, use the “Fill Missing Points Using” drop-down to select “Zeros.”
  20. To fill-in the value of missing points with the weighted average of neighboring points, use the “Fill Missing Points Using” drop-down to select the default value of “Interpolation.”
  21. If you want to use a function other than AVERAGE on values with the same date/time stamp if your data series contains multiple values with the same date/time stamp, then select the function to use from the “Aggregate Duplicates Using” drop-down.
  22. To include forecast statistics on a new sheet, then check the “Include forecast statistics” checkbox.
  23. To create the forecast sheet, click the “Create” button in the lower-right corner of the “Create Forecast Worksheet” dialog box.
  24. The forecast sheet contains the selected data series, the forecasted values, and the upper and lower confidence bounds, if selected, in a table on the sheet. These values appear within a chart on the same sheet.
  25. To change the values shown in the forecast chart, if desired, then edit the table’s values or formulas.
TOP