How to Make An Excel Forecast Sheet in 3 Steps – The Best Guide
This Excel Forecast Sheet tutorial is suitable for users of Excel 2016/2019 and Microsoft 365.
Objective
Create an Excel forecast sheet to analyze and predict future sales. Display the historical and predicted values in a line chart using the Excel forecast sheet.
This Guide Covers:
Excel Forecast Sheet Explained
Imagine this, you have been notified at the last minute about a very important office meeting scheduled for tomorrow. You are frantically searching all over the internet for the easiest way to forecast your sales data. No worries, you have come to the right place.
Here in this guide, we are going to elucidate how to easily implement forecast sheets in Excel. Go ahead & read on to rock all of your future presentations. Let this be your go-to guide for all things Excel.
Related:
Excel Templates For More Efficient Project Management
Sumif With Multiple Columns – The Sumifs Function In Excel
If you use Excel 2016, 2019, or Excel for Office 365, you will have access to one of the newer features of Excel: The Excel Forecast Sheet. If you have historical time-based data, you can use it to create a forecast and predict future values. A forecast can help you predict trends, future sales, future requirements, and needs so you can make better business decisions.
When you create a forecast, Excel creates a new worksheet that contains a table that includes the historical and predicted values and a chart that shows the data. All with the click of one button.
Video Tutorial – Excel Forecast Sheet
How to Create an Excel Forecast Sheet to Predict Future Values?
In this example, I am using a time-based series of data. I have the date listed in column A and some sales figures in column B. I log the sales figures in the spreadsheet every day. My aim here is to forecast what the sales will look like from Jan 19th – Jan 24th, 2019.
This will comprise of three simple steps:
I can do this very simply using the Forecast Sheet option in Excel.
Insert an Excel Forecast Sheet
To create a forecast sheet in Excel, you need to make sure you enter two data series that correspond to each other, in this case, date and sales. These values predict future sales.
NOTE: The timeline requires consistent intervals between its data points. In this example, I am analysing sales every day of every month. You may choose to create a forecast for sales on the 1st of every month. It’s ok if up to 30% of data points are missing or the same date is listed multiple times, the forecast will still be accurate.
- In the worksheet, enter two sets of data series that correspond to each other.
- Select both data series.
- From the Data tab, in the Forecast group, select Forecast Sheet.
- In the Create Forecast Worksheet dialog box, select if you want to display the forecast as a line or a column chart.
- Ensure that the Forecast End date is correct and if not change the date using the calendar picker.
6. Click Create.
Excel will create a data table that shows the Forecast Sales, Lower Confidence Bound, and Upper Confidence Bound. I also have a line chart showing the results of the forecast.
Here is the same data displayed as a column chart.
Customize a Forecast
To customize your forecast, expand the Options button prior to clicking the Create button.
See the table below for information on each of the options available on the Excel forecast sheet.
Also read:
How to Protect Cells in Excel Workbooks-The Easiest Way
5 Steps to Easily Use Excel 3D Maps (Bonus Video Included)
How to Use Excel COUNTIFS: The Best Guide
Forecast Options in the Excel Forecast sheet
| FORECAST OPTIONS | DESCRIPTION |
| FORECAST START | Pick the date for the forecast to begin. When you pick a date before the end of the historical data, only data prior to the start date are used in the prediction. Starting your forecast before the last historical point gives you a sense of 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 will not necessarily represent the forecast you will get using all the historical data. If your data is seasonal, then starting a forecast before the last historical point is recommended. |
| CONFIDENCE INTERVAL | Check or uncheck Confidence Interval to show or hide it. 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). Confidence interval helps in figuring out the accuracy of the prediction. A smaller interval implies more confidence in the prediction for the specific point. The default level of 95% confidence can be changed using the up or down arrows. |
| SEASONALITY | 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 picking a number. 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. And when the seasonality is not significant enough for the algorithm to detect, the prediction will revert to a linear trend. |
| TIMELINE RANGE | Change the range used for your timeline here. This range needs to match the Values Range. |
| VALUES RANGE | Change the range used for your value series here. This range needs to be identical to the Timeline Range. |
| FILL MISSING POINTS USING | To handle missing points, Excel uses interpolation, meaning that a missing point will be completed as the weighted average of its neighboring points if fewer than 30% of the points are missing. To treat the missing points as zeros instead, click Zeros in the list. |
| AGGREGATE DUPLICATES USING | When your data contains multiple values with the same timestamp, Excel will average the values. To use another calculation method, such as Median or Count, pick the calculation you want from the list. |
| INCLUDE FORECAST STATISTICS | Check this box if you want additional statistical information on the forecast included in a new worksheet. Doing this adds a table of statistics generated using the FORECAST.ETS.STAT function and includes measures, such as the smoothing coefficients (Alpha, Beta, Gamma), and error metrics (MASE, SMAPE, MAE, RMSE). |
That’s it! We are at the end of this guide to implement forecast sheets in Excel. To learn more about forecast sheets & other advanced Excel features, do check out our dedicated courses on Excel.
FAQs
Does Excel 2013 have a forecast sheet feature?
No Excel 2013 & earlier versions don’t have this feature directly in them. Still, you can manually create your own forecast sheet using the FORECAST function
How accurate is the Excel forecast sheet?
Excel’s default confidence interval for forecast sheets is 95%. This means that Excel is 95% sure that the forecast value will lie between these predicted lower & upper bounds.
Like what you see? Check out these links for more examples of creating Excel Forecast Sheets.
Spreadsheet Web – Forecasting in Excel
K2 Enterprises – Building Better Forecasts with Excel’s Forecast Sheet Feature
For more Free Excel tutorials from Simon Sez IT. Take a look at our Excel Resource Center.
Other Excel classes you might like:
- Protecting WorkBooks in Excel
- Logical Functions in Excel
- Designing Better Spreadsheets in Excel
- Introduction to Power Pivot & Power Query in Excel
To learn Excel with Simon Sez IT. Take a look at the Excel courses we have available.
Deborah Ashby
Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom.She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP.She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.
