Issue #41 - Forecasting with Excel Part 4:

Is Your Model Any Good?

This Week’s Tutorial

Forecasting is about predicting the future. But you don't have a time machine. So, how can you trust that your forecasts are accurate?

In this week's tutorial, you will learn how to test your models by simulating the future.

Unfortunately, most professionals (e.g., Finance pros) are not taught how to do this. This is your opportunity to stand out.

If you would like to follow along with today's tutorial (highly recommended), you will need to download the SalesTimeSeries.xlsx file from the newsletter's GitHub repository.

​

Simulating the Future

To build better forecasts, you must use your data to simulate the future. You do this by splitting your data into two sets:

1. The oldest data becomes what you use to build your forecasts.

2. The newest data becomes what you use to test forecast accuracy.

Here's why this strategy is critical to your forecasting success.

First, by using only the oldest data to build your forecasts, you are hoping to discover the underlying patterns that stand the test of time.

Imagine that you're forecasting sales and have 6 years of monthly data. You use the first 4 years to build and the last 2 years to test.
​

If your forecasts are accurate, you've found these patterns. But that's not all.

Second, consider a forecasting model that received the first 4 years of sales data.

From the model's perspective, the future starts with the first month of the test data (i.e., January of year five). This is how you simulate the future.

Is this simulation perfect?

Absolutely not. But it's the best you can do.

And, as I mentioned above, most professionals haven't been taught how to do it.

​

Splitting the Data

The Sales table of the tutorial Excel workbook has 5 years of monthly sales data.

While there is no magical equation that tells you how much data should be split between building your forecasting model (i.e., the training dataset) vs. the test dataset, here are some guidelines:

• Each dataset set should include, at a minimum, one complete cycle of seasonality.

• More data is better than less data.

As covered in Part 2, the Sales data exhibits yearly seasonality. Therefore, here are two reasonable approaches to splitting the data:

1. Use 3 years of data for training and 2 years for testing.

2. Use 4 years of data for training and 1 year for testing.

I will cover option 2 in this tutorial series. Try out option 1 and compare the results.

​

A Simple Moving Average Model

In Part 3 of this tutorial series, I introduced the use of a simple 6-month moving average as a forecasting model. I will continue to use this MA(6) model as a baseline in this tutorial.

Setting up the MA(6) model in Microsoft Excel is straightforward: