Microsoft Excel - How to use Forecast Sheet

Another way of analysing data in Excel is the Forecast Sheet. Here is how

Excel is a powerful tool for analysing data and making predictions based on historical trends. One of the features that Excel offers is the Forecast Sheet, which allows you to create a visual forecast worksheet based on your data series.

Let's take a look at how to create a Forecast Sheet.

First you need to prepare your data. By this I mean you need to have two data series that correspond to each other:

A series with date or time entries for the timeline
A series with corresponding values that you want to forecast

For example, suppose you have a monthly sales data for the first quarter of the year as shown below:

Please keep in mind that the timeline requires consistent intervals between its data points, such as monthly, yearly, or numerical intervals. It's fine if your timeline series lacks up to 30% of the data points or has multiple numbers with the same time stamp. However, summarising data prior to forecasting will yield more accurate results.

Next: Create a forecast sheet

Click inside your data and then click on the Data Ribbon. Then click on Forecast Sheet in the Forecast Group.

In the Create Forecast Worksheet box, pick either a line chart or a column chart for the visual representation of the forecast. This is a tiny icon top right.

In the Forecast End box, pick an end date for your forecast.

Then click Create.

Excel generates a new worksheet with a table of historical and predicted values as well as a chart expressing this data. The new worksheet will be to the left ("in front of") of your original worksheet.

Step 3: Customise your forecast

If you want to change any advanced settings for your forecast, click Options in the Create Forecast Worksheet box. You'll find information about each option in this table:

Forecast Options Description

Forecast Start - Pick a date for your forecast to begin. When you pick a date before the end of your historical data, only data prior to that date are used in your prediction (this is sometimes referred to as "hindcasting")

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 your forecast (with normal distribution)

Seasonality - Specify whether your data has seasonal patterns and how long they are. You can choose Automatic (default), Manual or None. Automatic detects seasonality based on your data; Manual lets you enter an integer value for season length; None assumes no seasonality.

Fill Missing Points Using - Choose how Excel should handle missing points in your timeline series. You can choose Interpolation (default), Zeroes or Not Fill.

Aggregate Duplicates Using - Choose how Excel should handle duplicate values with same time stamp in your timeline series. You can choose Average (default), Sum Count Median Min Max First Last.

Once you have made your changes to the options, click the Create button and the Forecast Sheet will be created.

You can continue to create Forecast Sheets in the Workbook to show different end points or options.

It really is easy to create a Forecast Sheet with the right set up of your data.

Excel is simple once you understand it. Let me show you how.

To take YOUR Microsoft Excel skills to new heights - invest in your own learning with my course Essential Excel for The Savvy Assistant. Dip in and out at your leisure. Most lessons are less than 10 minutes long, invest just 10 minutes a day, your Excel knowledge will soar.

Categories: : Blog, Microsoft Excel