How to do your own modelling in Excel with Forecast

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Excel can use past data to project possible future results with the Forecast feature.  It’s a very simple version of statistical ‘modelling’. 

The Forecast function will let you predict future values and data trends, such as consumer trends, future sales and or inventory needs, based on seasonality. 

Here’s a simple guide on how to use the Forecast feature. Although the data below looks fairly complicated, Excel makes it pretty easy to create in just a few clicks.  

How to use Forecast Sheet 

First, simply gather your historically time-based data in Excel, in this example we’ve put together a few random values and dates. 

Next, use the mouse to select the data, then go to Data | Forecast | Forecast Sheet 

This will bring up a preview of your chart. 

From here, you can select between a line or column chart, and adjust the Forecast End date.  

Customise Options 

By selecting the arrow next to Options, there are extra features you can use or adjust to customise your forecast. 

Forecast Start 

Choose the start date of the prediction. If the start date is before the end of historical data, the forecast uses the data only prior to that date. 

Confidence Interval 

This refers to a range of values so defined that there is a specified probability that the value of a parameter lies within it. It helps you figure out the accuracy of the prediction. The default is set to 95%, but you can adjust the percentage or remove the Confidence interval from the graph completely. 

Seasonality 

Seasonality is a measurement of how long (how many points) the seasonal pattern lasts. A yearly sales cycle, for example, with one point representing a month, has 12 points of seasonality. Setting Manually allows you to choose a number overriding the automatic detection. 

It’s important to note when setting seasonality manually, to avoid a value less than 2. Excel won’t be able to identify the seasonal components when the seasonality isn’t high enough for the algorithm to detect… so it’ll revert to a linear trend.  

Timeline Range 

Here you can adjust your timeline range. You need to match this range with the Values Range. 

Values Range 

Here you can change your value range. You need to match this range with the Timeline Range. 

Fill Missing Points Using 

In order to fill in missing data points, Excel uses Interpolation, as long as less than 30 percent of the data points are missing, the missing point will be filled in by the weighted average of its neighbouring points. To treat the missing points as zeros instead, click Zeros in the list. 

Aggregate Duplicates Using 

Excel will average the values if your data has several entries with the same timestamp. Select the calculation you wish from the list to use another method, such as a median or count. 

Include Forecast Statistics 

You may check this box if you want additional forecast statistics to be included in a new worksheet. By doing so, a table of statistical information will appear based on the FORECAST.ETS.STAT function and includes smoothing coefficients measures (Alpha, Beta, Gamma), and error metrics (MASE, SMAPE, MAE, RMSE). 

We like adding the Forecast Statistics because it gives you the chance to edit the modelling to account for events Excel can’t predict. 

Formulas used in Forecast Sheet 

If you choose to include Statistics, once you’ve created the Forecast Sheet, a table will appear including the following columns: 

Date/Timeline: The historical time-based data series. 

Historical Values Column: The list of corresponding value series  

Forecast Values Column: Forecasted value series calculated using the formula =FORECAST.ETS 

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

Confidence Interval Columns: If selected, two columns will represent the confidence interval. This is calculated using the formula =FORECAST.ETS.CONFINT 

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

Adjusting the Forecast 

Once Excel has made its forecast, feel free to adjust it for things Excel can’t possibly know. For example, change future sales figures for known events (open/close stores, special sales etc). Manually tweak the values in the Forecast column.  The confidence bound, stats and chart will all adjust accordingly, like this. 

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address