<img height="1" width="1" style="display:none;" alt="" src="https://dc.ads.linkedin.com/collect/?pid=1005900&amp;fmt=gif">

Insights

Forecasting: Basic Regression in Excel

In my earlier posts on forecasting with Time Series Decomposition (see Forecasting: Further Time Series Decomposition in Excel) we looked at producing a forecast based on historical behaviour of the data. The forecast we had at the end, including upper bound forecast error, looked like this:

time series decomposition with MAPE

Now this looks great, it gives me a fairly low error and if everything continues in that way it will be fairly accurate. If it does. But what if it doesn’t? I can have all the historical data in the world and build the most complex model of it, but if that assumption is incorrect my forecast is going to be wrong.

Change

The truth of the matter is that things do change. Businesses do expand (or shrink) suddenly. Promotions increase user numbers. New functionality changes user behaviour.

As mentioned before, maybe the actual CPU on our favourite server will look like this:

actual server cpu

How could we have forecast this from the available data? The answer is we couldn’t. What if we had 10 years’ worth of data going back to 2002? Unless this is a regular uplift that happens e.g. every March, we still couldn’t have predicted it.

But almost certainly, someone knew that change was going to happen. Maybe the business who have been planning that promotion/acquisition/enrolment for months if not years. Maybe the dev team who have finally decided to remove caching to fix that niggling functional bug! This is where we need to be able to put known information into our forecasts so that they are based on more than just historical data.

On the other hand though, the business doesn’t know what effect their decisions will have on our CPU. They know that, due to a new marketing channel, sales are expected to double from March onwards; but it’s our job to work out what impact that will have on the servers. And this is where regression modelling in Excel comes in.

Finding relationships

Let’s assume that someone can give you a data set of historical sales volumes and a forecast for how many sales will be made per day over the next few months. Now there may not be an exact forecast in which case you’ll have to build it from the information available, but that’s another topic.

First let’s graph the sales and CPU together and see if they look similar. As they are different scales we’ll put them on separate axes.

sales vs cpu

Those look like they follow a similar pattern. To confirm we can do a scatter plot of the relationship between these variables.

scatter plot sales cpu

We can see there is a relationship here, though with an R2 of just 0.63 perhaps not as strong as we might have hoped. There are other things we could try at this stage such as fitting other reasonable non-linear models, eliminating outliers with known causes, looking at other factors etc. For simplicity though we’ll just use the linear relationship we have identified. The question ‘is this relationship strong enough’ very much comes down to the error tolerance of your forecasts. Some real scenarios will have a much stronger linear relationship particularly when volumes are very large, which means we can have much more confidence in the forecasting results. A good case study of a strong linear regression we’ve seen is with Domino’s pizza orders against CPU load – you can find a graph of this on slide 16 of this presentation which one of our consultants gave recently.

Building the forecast

Here is the forecast we have been given for Sales:

sales forecast

Now we can simply use whatever relationship we found to translate this into CPU. 

CPU Forecast

This is done using the same techniques we used before for linear regression, except here the driver is the sales rather than the date. 

excel regression

Here the slope is defined as =SLOPE(B5:B66,C5:C66), the intercept as =INTERCEPT(B5:B66,C5:C66)

The forecast CPU is then Sales * Slope + Intercept, e.g. =C5*D$1+D$2

And again we are using an MAPE error figure to identify the upper bounds of the forecast. The error figure of 31% is quite a bit higher than the 15% that we had on the Time Series Decomposition forecast. However what we have sacrificed in fit to historical data we have gained in understanding of future behaviour, which is after all what the forecast is for! If we return to our forecast to validate it with the Actual figures given in the second chart above, we now have an error of 27% on the TSD forecast, but only 12% on the Regression one. 

forecast validation

This is because, even though the relationship is not as strong as it might be, we have captured some knowledge of future behaviour into our forecast, and that makes all the difference.

Maybe some more on how to get the business forecasts (and in the right form) another time!