About this lesson
Generating forecast charts in Excel is only a few clicks away, resulting in a chart that you can customize and update later.
Download this lesson’s related exercise files.Forecast Sheets (Excel 2016).xlsx
94.6 KB Forecast Sheets (Excel 2016) - Completed.xlsx
Forecast Sheets (Excel 2016)
Building dynamic forecasts in Excel 2016.
When to use
When you want to create a dynamic forecast chart that shows confidence levels.
- Ensure that your data is stored in a two column tabular format with a header row
- Ideally, keep your dates in the first column and values in the second
Creating the Forecast sheet and chart
- Select any cell in the data
- Go to Data à Forecast Sheet
- Set the Forecast End date
- Can be modified by expanding the Options arrow during the creation phase
- Adjust the Forecast Start date
- Hide or adjust confidence levels (higher confidence yields a wider confidence spread)
- Adjust seasonality to reflect your data set
- Include additional forecast statistics
Hints & tips
- If your source data was just hard coded values, you can delete that worksheet as the values are copied into the forecast worksheet
- If your source data is dynamic, you can link it to the Forecast sheet to make that dynamic as well
- To create dynamic Month End dates, select cell A3, change the formula to =EOMONTH(A2,1) and copy down the table
Lesson notes are only available for subscribers.