Introduction
Welcome! In this course we provide a basic introduction to forecasting using Microsoft Excel. The
course is practical in nature, with only a small amount of mathematical theory being introduced.
Forecasting is everywhere! In pretty much every situation where planning is required, forecasting (in
some form) is needed: to predict, and prepare for, changes in sales, profits, losses; set up next year’s
budget; scheduling staff in a call centre; building new power stations and other public utilities.
Forecasts may be short, or long term. Stock traders may need forecasts of only a few minutes ahead,
whereas city planners may need a ten year population forecast in order to give them enough time to
plan and implement infrastructure initiatives.
In this course we are going to focus on time series i.e., data that arrives at regular intervals (quarters,
months, days, minutes etc). The periods over which data is measured might change, but the
underlying ideas and approaches remain pretty much the same.
In order to forecast we need historical data. Our forecasting models (i.e., Excel) only have the
information contained in the historical data to help make their forecasts (an exception is Excel’s
What-If analysis which we explore later). Successful forecasting allows us to determine underlying
trends/patterns within that given historical data and use that information to make forecasts/predictions
for future time periods. This requires that we can distinguish between genuine trends/patterns and
random/noisy events within the data. This is not always easy.
A good question to ask yourself:
Is the period for which you are going to forecast, likely to be “typical” compared to your
historical data? For example, if the historical stock price has been rising consistently, then it
is extremely unlikely that any forecast model is going to be able to predict a massive drop
tomorrow. Typically forecast models can only predict versions of what they have “seen” in
the data. Forecasting in uncharted territory is dangerous!
Also, not everything can be forecast! Having the last five years winning lottery numbers will not help
you predict next week’s numbers. Some events are just random. Other events can be forecast with
great accuracy. Scientists can forecast the next solar eclipse with great precision. For the most part,
we will be concerned with data and events that fall between these two extremes.
I mentioned above that our forecasts rely purely on historical data. In certain circumstances you may
have extra information about the future (a new transformative product being released, increase in
marketing, staff layoffs etc). Excel’s What-If analysis tool allows forecasts to be adjusted to
incorporate this extra information.
The main focus points for the course are as follows:
- Forecasting models
- Forecast accuracy
- How to optimize forecasts using Solver
- What-If scenario analysis
Let’s get started!
Chapter 1 Forecasting with Smoothing Models
Simple Moving Average
- The idea here is to use the average of the last few observations as our prediction for the next
value. Mathematically we have that the forecast for next period is simply the average of some
previous q observations i.e.
where
(i) q equals the number of periods used in the moving average
(ii)
equals observation at period i.
- The example below presents a forecast using the previous two month’s observations. To
forecast April we take the average of sales in March and February.
Remark: MA works best when there is no trend or seasonality in the data.
How to choose “q” the number of observations to average over?
- Small values of q lead to light smoothing: forecast series closely tracks the original.
- Large values of q lead to heavy smoothing: forecast series tracks basic movements only. If
the series fluctuations contain genuine patterns, they may be smoothed out and result can be
misleading.
- We choose “q” that minimizes one of the error metrics discussed in Chapter 2.
We can also use the Data Analysis add in to perform the moving average modelling for us. The steps
are as follows:
1. Click on the Data tab at the top of the screen
2. Click on the Data Analysis Tools button (far right)
3. Select “Moving Average” and then click on “OK”
4. Select the input data we are going to base our forecast on. In our example this would be cells
E2:E14. Note that we are including the column header here. This means that we also need to
tick the “Labels in first row” box. We are averaging over the previous two periods, so set the
“intervals” box equal to 2. Next, select a cell for the output. Here this would be cell F4.
Finally, so that we can get a visual on how well we forecast, tick the “Chart Output” box.
Once everything has been entered, hit “OK”.
5. The results of the regression are then automatically fill the cells from F4 downwards. We get
a “#NA” in cell F4, but this is expected since we need two previous data points to average
over in order to forecast. The output results, included a chart are given below. Notice they
are the same as we obtained when entering the formulas ourselves.
Click OK
Select Moving Average
Weighted Moving Average
Weighted moving average (WMA) is a generalization of the moving average model. Say we are
using the average of the three previous data points to forecast the next point. We are implicitly
assuming that each of the three previous points is equally important in our forecast. With WMA we
are allowing that (usually) more recent points are more important for our forecast and hence should be
weighted more heavily. For example, we might decide that 70% of our forecast should be based on
the previous data point, 20% on the data point before that and finally 10% on the third data point.
Note that our weights sum to one (70% + 20% + 10% = 100%)! This should always be the case.
The mathematical formula for WMA is
where:
-
is the forecast for time .
-
is the actual value at time
-
is the weight assigned to previous observation value.
- is the number of previous observations we are using for our forecast.
So, in the 70/20/10 weighting example mentioned above, the formula would become
To implement WMA in Excel we can make use of the SUMPRODUCT function:
We can compare the three-month MA and WMA forecast results as follows:
Exponential Smoothing
The exponential smoothing formula is
where:
-
is the forecast value at time
- is the smoothing constant taking values between zero and one.
-
is the actual value for time
-
is the forecast value for time
So exponential smoothing is a weighted combination of the last actual data point and the last
forecasted point. We demonstrate forecasting using exponential smoothing below. We set
The Data Analysis toolpak can also be used to perform exponential smoothing. The steps are similar
to those we demonstrated previously for moving average models. They are as follows:
1) Click on the Data tab at the top of the screen
2) Click on the Data Analysis Tools button (far right)
3) Select “Exponential Smoothing” and click on “OK”.
4) Select the input data we are going to base our forecast on. In our example this would be cells
D3:D15. Note that we are including the column header here. This means that we also need to
tick the “Labels in first row” box. Next, enter a value of into the
Click OK
Select Exponential Smoothing
Damping factor box, and then select a cell for the output. Here this would be cell E4.
Finally click on OK.
Linear Regression
As the name suggests, linear regression is a method best suited for data that is trending upwards, or
downwards in a linear fashion. In this section we will give some mathematical formalities and then
introduce some useful Excel functions: FORECAST.LINEAR, TREND, SLOPE and INTERCEPT.
First, let’s get the mathematics out of the way. If we are given data
for , then
linear regression calculates a new y-value using the straight- line equation
,
where the y-axis intercept
,
and the slope/gradient is given by
Here and are the averages of the data and data respectively. Since we are dealing primarily
with time series the ’s will represent time (period).
The Excel function FORECAST.LINEAR is useful here. The steps to use it are as follows:
1) First create a data range containing known data and their corresponding periods
2) Click in the cell where you want to place the function.
3) Click on the Formulas tab.
4) Click on the Insert Function button.
5) In the Insert Function dialogue box, choose Statistical from the Or select a category: drop
down menu. Click on FORECAST.LINEAR and then finally click OK.
6) In the Function Arguments dialogue box:
a. In the X box, enter the cell reference for the period you wish to estimate for.
b. In the Known_ys box, select the cells containing the known values.
c. Make the cell references absolute (press F4 or manually enter $ signs).
d. In the Known_xs box, select the cells containing the known periods.
e. Make the cell references absolute (press F4 or manually enter $ signs).
f. Click OK.
7) Finally, copy down the FORECAST.LINEAR function to obtain forecasts for both known
and unknown periods. The result is as follows:
Click fx
Select FORECAST.LINEAR
Click Statistical
Click OK
1.4.1 TREND Function
Another way to perform linear regression is to make use of the TREND function. This differs from
the usual Excel functions and is an example of an array function. A “normal” Excel function is
entered into a single cell (and outputs to a single cell), whereas for an array function a range of cells is
selected and the function is entered using ctrl-shift-enter (rather than just enter). The result is that
the formula is applied to the entire range of cells at the same time. It’s probably easiest to understand
via an example.
The TREND function can be used to generate a trend line on given data and also can be used for
forecasting. We will demonstrate both of these below. We will take the same data as above and show
that TREND gives the same results as FORECAST.LINEAR.
The TREND function has the following syntax:
TREND( known_y's, [known_x's], [new_x's], [const])
where
known_y’s (required) – a set of dependent y-values that we already know.
known_x’s (optional) – one or more sets of independent variables (e.g. the periods that the y’s are
observed over.
new_x’s (optional) – one or more sets of new x-values for which we wish to calculate the trend.
const (optional) – a Boolean (True/False) variable. If False, then the equation is
estimated with intercept set equal to zero. If True (or omitted) then excel calculates as described
at the beginning of this section.
The steps to use the TREND function to determine the trend are as follows:
1) Create a data range containing know data and corresponding periods.
2) Select the range where you want the result of the function to go. In our example that would
be F3:F14.
3) Click on the Formulas tab.
4) Click on the Insert Function button.
Click fx
5) In the Insert Function dialogue box, choose Statistical from the Or select a category: drop
down menu. Click on TREND and then finally click OK.
6) In the Function Arguments dialogue box:
a. In the Known_ys box, select the cells containing the known values.
b. In the Known_xs box, select the cells containing the known periods.
c. Hold down ctrl + shift and Click OK.
7) The results should now fill the selected region, as shown above.
Once you are familiar with a function, it is simpler to skip explicitly using the Formulas tab and just
type directly into the spreadsheet. For the above example the process would be as follows:
1) Create a data range containing known data and corresponding periods.
2) Select the range where you want the result of the function to go. In our example that would
be F3:F14.
Click Statistical
Select TREND
Click OK
3) Next, start typing “= Trend(“. Hit tab and the TREND function will autocomplete as follows:
4) Then select known_ys (range E3:E14), type “,”, select the known_xs (range B3:B14) and hit
ctrl-shift-enter.
The above process will work for all functions, not just array functions! Going forward we are going
to use the short method, but the Formulas tab approach will always work, so the reader should use
whichever method they are most comfortable with.
Next, we can use TREND to forecast future values (sales). To do this we need to include a set of
new_xs in the TREND formula.
Using our running example, to forecast sales for January and February 2021 we proceed as follows:
1) Select the range for the forecast outputs. Here this would be cells E15:E16.
2) Next, start typing “= Trend(“. Hit tab and the TREND function will autocomplete as follows:
3) Then
a. select known_ys (range E3:E14), type “,”,
b. select the known_xs (range B3:B14), type “,”
c. select the new_xs (range B15:B16)
d. hit ctrl-shift-enter.
1.4.2 SLOPE and INTERCEPT functions
On some occasions you may need to know the actual slope (gradient) or intercept of a regression line.
These would be and respectively from the regression line equation . For this
purpose, Excel provides the aptly named SLOPE and INTERCEPT functions. Once we have the
slope and intercept then we could use them to construct the trend/regression line explicitly.
The SLOPE and INTERCEPT functions have the following inputs:
SLOPE( Known_ys, Known_xs)
INTERCEPT( Known_ys, Known_xs)
where Known_ys and Known_xs are the usual input values for actual known y and x (period) values.
The steps for using SLOPE and INTERCEPT to explicitly calculate a trend line are as follows:
1) Create a data range containing known data and corresponding periods.
2) In cell C18 type “= slope(“ and then select range E3:E14 for Known_ys and range B3:B14
for the Known_xs. Finally hit Enter.
3) In cell C18 type “= intercept(“ and then select range E3:E14 for Known_ys and range
B3:B14 for the Known_xs. Finally hit Enter.
Now we can use the SLOPE and INTERCEPT function results to explicitly calculate our trend line:
4) In cell F3 type “= $C$18 * B3 + $C$19” and hit Enter.
5) Copy down the formula.
1.4.3 Regression Discussion
Since we are focussed on time series, we have only looked at simple linear regression so far i.e., our
assumed equation is There is a more general form of regression, called multiple linear
regression, where there is more than one type of (independent variable) on the righthand side of the
equation that contributes to predicting For example, if your company sells umbrellas then sales (y)
could be determined (at least in part) by the advertising spend (
) and also rainfall in each month
(
). Formally, the general equation for multiple linear regression is
where
are the slope coefficients and is the -intercept.
Above we only used the TREND function for the simple linear regression case, but it does cater for
multiple linear regression as well.
There are (at least) two other ways of performing regression in Excel:
1. LINEST function
2. Linear regression within the Data Analysis toolpak
The LINEST is an array function that returns an array containing all of the regression coefficients, as
well as some optional statistical information about the model fit. Linear regression performed from
within the Data Analysis toolpak also returns quite detailed statistical information on the model and
how well it fits the data.
Both simple and multiple linear regression assume a linear relationship between the independent ’s
and the dependent As long as our data is approximately linear this can be a very useful model for
making predictions. However, if the true relationship between and is far from linear then the
forecasts produced by linear regression may be misleading. Under certain situations we can move to
another type of model: exponential regression, which can better fit the data. This is discussed in the
next section.
Exponential Regression
It may well be that there is not even a roughly linear relationship between the dependent variable
and the independent variable . This means that using linear regression (FORECAST, TREND etc)
can lead to inaccurate forecasts. For example, if sales (say) increased slowly at first, and then faster
and faster, then using linear regression would not be a good model to use for forecasting. This
situation is called exponential growth and is illustrated in the figure below:
In this setting, where the growth is exponential rather than linear, we can use exponential regression
to obtain a better fitting model.
Mathematically, rather than model sales using the linear model , we model sales using a
nonlinear model
where and are, as usual, constants.
Mathematical aside: it is possible to estimate the constants and by transforming the nonlinear
equation to a linear equation and then estimating the resulting coefficients using linear regression.
Taking logs of the nonlinear model gives us
where is the intercept, is the slope and sales (for example) are reported in log units. Don’t
worry if your math is rusty and this doesn’t make much sense. Luckily Excel has the GROWTH
function which can do all the heavy lifting for us.
GROWTH is the exponential counterpart to the linear regression function TREND. The inputs are
identical for both functions.
GROWTH( known_y's, [known_x's], [new_x's], [const])
where
Known_y’s (required) – a set of dependent y-values that we already know.
Known_x’s (optional) – one or more sets of independent variables (e.g. the periods that the y’s are
observed over.
New_x’s (optional) – one or more sets of new x-values for which we wish to calculate the trend.
Const (optional) – a Boolean (True/False) variable. If False, then the equation
is estimated with set equal to one. If True (or omitted) then excel calculates as described at the
beginning of this section.
The steps to use the GROWTH function to determine exponential growth are identical to those for
using TREND to predict linear growth. NB: GROWTH is also an array function! For this reason
we refer the reader to the TREND instructions and just provide the start and finished result:
1) Create a data range containing known data and corresponding periods.
2) The remaining steps are identical to those presented above for the TREND function. Simply
replace TREND with GROWTH in both ranges.
3) The final result, with a simple scatter plot chart is:
4) The forecast using GROWTH clearly match the sales data much better than our original linear
regression!
Seasonality
The data you are trying to model and forecast may be seasonal. By this we mean that your data
contains variations that occur as specific regular intervals. For example, your sales generally peak in
December, and be at their lowest in February. Here’s a chart showing data with a clear seasonal
pattern.
If we use linear regression via FORECAST.LINEAR say, then we end up with the following:
While our model definitely captures the overall upward trend, it either overestimates, or
underestimates pretty badly depending on the quarter. For this reason, we probably also can’t put
much faith in our predictions for 2021. We can improve our model by incorporating extra
information about the quarterly seasonality via the seasonal index.
Inspecting our data, we observe a very strong quarterly seasonal pattern. The seasonal index is a
multiplicative scaling factor that allows us to adjust the linear forecast value either up or down. The
idea is that multiplying our linear forecast value by the relevant index value should result in a value
close to the actual data value. Since we have quarterly seasonality we will need four scaling factors,
one for each quarter. Say we set our seasonal index values to be [0.9, 0.7, 1.5, 0.9]. This means that
our quarter 1 linear forecasts (which are overestimates generally) will be multiplied by 0.9 i.e.
reduced to 90% of their value. Similarly, the quarter 3 linear forecasts (which are pretty major
underestimates) will be multiplied by 1.5 i.e. increased to 150% of their value.
NB: Since we have quarterly seasonal data, we require the elements of our seasonal index to
sum to four. Similarly, if we have monthly seasonal data then we would need a seasonal index
with 12 elements, and those elements would need to sum to 12.
We implement this in Excel and show the new seasonal forecasts as well as a chart showing the
improved results.
While our new season forecast does not perfectly match the actual data, it is definitely now capturing
both the upward trend as well as the quarterly seasonality.
An obvious question is how to best choose the values for our season index adjustment factors? We
will revisit this example in Chapter 3 where we will make use of Excel’s Solver in order to
automatically select index values!
One final point worth mentioning, is that picking the nature of seasonality in your data set is not
always as easy as in the example we have presented. More realistic data can be a lot messier and
even have multiple types of seasonality present e.g., quarterly and annual.
Summary
We have covered a lot of material in this chapter! We have:
• Introduced the following forecasting models:
o Moving Average (MA)
o Weighted Moving Average (WMA)
o Exponential Smoothing
o Linear Regression
o Exponential Regression
o Seasonal
• Implemented these models in Excel, either manually, using built in functions, or the Data
Analysis toolpak.
• Introduced Excel array type functions (ctrl + shift + enter)
• Introduced the following functions:
o SUMPRODUCT,
o FORECAST.LINEAR,
o TREND,
o SLOPE,
o INTERCEPT
o LINEST, and
o GROWTH
As of Excel 2016, there has been a new tool called Forecast Sheet that can automatically implement
a lot of the forecasting models we have discussed in this chapter. It can also calculate and incorporate
seasonality, as well as provide up and lower confidence bounds! We will properly introduce this tool
in Chapter 4, along with another useful feature Trendlines.
Your Turn!
Exercise 1: Smoothing Models
1. Open the file Session1_excel
2. Open the ex1_smoothing_models tab
3. Using the given data, implement a 1-period and 3-period MA model forecast in columns E
and F respectively.
4. Using weights of 70%, 20% and 10% (in cells I4:I2) implement a WMA forecast model in
column G.
5. Plot sales along with all 3 forecasts in a single scatter plot chart.
6. Experiment with varying the weights for the WMA.
7. Save the file.
Exercise 2: Exponential Smoothing
1. Open the file Session1_excel
2. Open the ex2_exp_smoothing tab
3. Using the given data, set alpha = 0.5 in cell I3, and using the formula given in the notes,
implement an exponential smoothing forecast for column E. What is the forecast for Q1
2020?
4. Using the data analysis toolpak implement an exponential smoothing forecast in column F,
with a damping factor of 0.7 (i.e., corresponds to alpha = 0.3). Generate the forecast chart
automatically.
5. Change alpha in cell I3 to 0.3 and compare the forecasts from both exponential smoothing
models.
6. Save the file.
Exercise 3: Linear Regression
1. Open the file Session1_excel
2. Open the ex3_linear_regression tab
3. Using the FORECAST.LINEAR function implement a regression forecast in column E.
4. Using the TREND function implement a forecast for the known actual sales values in cells
F2:F25. Remember TREND is an array function!
5. Using the TREND function implement a forecast for the 4 quarters of 2020 i.e., cells
F26:F29.
6. Select cell J2 and use the SLOPE function to calculate the slope
7. Select cell J£ and use the INTERCEPT function to calculate the y-intercept.
8. Use the equation with slope and intercept values calculated in 6 & 7 to
calculate a regression forecast in column G.
9. Save the file.
Exercise 4: Exponential Regression
1. Open the file Session1_excel
2. Open the ex4_exp_regression tab
3. Using the FORECAST.LINEAR function implement a linear regression in column E.
4. Using the GROWTH function implement a forecast for the known actual sales values in cells
F2:F17. Remember GROWTH is an array function!
5. Using the GROWTH function implement a forecast for all the quarters of 2020 i.e., cells
F18:F21.
6. Plot the actual sales, along with the linear and exponential forecasts on a single scatter plot.
7. Save the file.
Exercise 5: Seasonal Forecasting
1. Open the file Session1_excel
2. Open the ex5_seasonal tab
3. Plot the sales in column D and inspect for seasonality. What is the frequency?
4. Using the FORECAST.LINEAR function implement a linear regression in column E.
5. Estimate the seasonal index i.e., cells F2:F5. This can be roughly done by looking at the ratio
of actual to forecast. e.g., weight 1 is approximately D2 / E2. Once you have approximate
values for all four weights (to say 2 decimal places), then adjust them manually until the
weights sum to 4.
6. Copy down the seasonal index to the rest of column F.
7. Use the seasonal index and the linear forecast to create a seasonal forecast in column G.
8. Add both the linear and seasonal forecasts to the scatter plot chart.
9. Save the file.
Chapter 2 Forecast Accuracy
In Chapter 1 we introduced a variety of different approaches for forecasting. How should we decide
which method to use? We could always plot the results and visually inspect how well our models
match the actual data, but this will not always provide a way to clearly and impartially decide which
of our models is doing the best job. In this chapter we are going to introduce a variety of error scores
(metrics) that will allow us to impartially decide between models.
Errors
We begin by defining the forecast error. It is simply the difference between the actual data value at a
given time/period and the forecast value at the same time/period. Mathematically we have
,
where
-
is the error at observation period .
-
is the actual data value observed at period .
-
is the forecast for period .
Based on this definition then intuitively a good forecast would be one that results in errors as small as
possible. i.e., our goal will be to minimize the errors. Thus, if we were given forecasts from two
different models (say MA and linear regression), then we could decide which was better by
comparing each model’s overall forecast error. Let’s go back to one of our running examples and
generate the errors:
Goal: Combine the individual errors into a single value representing the overall error for the
forecast.
Approach 1: Sum the individual errors to obtain a total overall error.
Issue: There are two things wrong with this approach: (1) Since we are adding errors, then a forecast
with more predictions will have a greater chance of ending up with a larger error. (2) Because errors
can be positive and negative there is a chance that they may partially cancel each other out when we
sum them. This could leave us with a small overall total error (and false impression of success!) even
though each individual forecast error is large. Say, for example that we had six observations and the
errors were -5, 3, -3, 12, 5, -12. Clearly our model is not forecasting perfectly, but when the errors are
summed the result is zero!
Digressing briefly, the sum of the errors is sometimes called the bias. If the bias is positive that tells
us that the model is consistently underestimating with its forecasts. Conversely, if the bias is negative,
then our model’s forecasts are consistently higher than the actual. This can be an important piece of
information.
Approach 2: Take the average of the individual errors.
Issue: This will fix (1) by removing the explicit dependence of the overall error on the number of
forecasts. However, since the average still involves summing up the errors before dividing them by
the number of observations, (2) is still going to be a problem.
The Fix: So far, the averaging idea is good for getting rid of the issue of dependence on number of
forecasts. We still need to deal with the issue of positive and negative errors. There are a couple of
approaches here. In both cases we don’t work with the errors directly, but rather transform them so
that they are always positive. First method is to take the absolute value of the errors (AE) and the
second is to square each error term (SE). We discuss both of these approaches below.
Mean Absolute Error (MAE)
Mathematically:
If we have n forecasts then we define the Mean Absolute Error (MAE) as follows:
,
where
is the forecast error for the th observation.
We can calculate this in Excel, using the ABS function to convert our errors to absolute errors i.e.,
drop any negative signs, and the AVERAGE function to take the average or mean of the absolute
errors. This is shown in the next two figures.
In this case, we have an MAE of £73,930. (Note that the MAE is measured in the same units as the
data. In this case £’s.)
Mean Squared Error (MSE)
Again, if we have n forecasts then we define the Mean Squared Error (MSE) as follows:
where
is the forecast error for the th observation.
Sometimes it is convenient to work with the square root of MSE. This is called Root Mean Squared
Error (RMSE) and is defined as
.
One reason why RMSE may be preferred is that it allows the overall error to be expressed in the same
units as the actual observations (and forecasts). This follows since, to calculate MSE we need to
square the errors, which changes the units from say £ to
Thus MSE should be expressed in units
squared e.g.,
This is not very intuitive! Taking the square root to convert from MSE to RMSE
switches us back to the original units, say £.
We can calculate this in Excel as follows:
And finally, to calculate the RMSE:
The RMSE is £88,797 in this case.
Mean Absolute Percentage Error (MAPE)
We start by defining a Percentage Error (PE):
This is sometimes convenient since it provides intuitive, scale independent measurements. (Rather
than just a number for which, depending on the setting, it may be hard to distinguish large from
small.)
Since individual errors can be positive or negative it follows that PE can be positive or negative. As
with the errors themselves, we can get around this issue by using the absolute value of the PE’s. From
here we, as usual, take the average in order to remove the explicit dependence on number of forecasts.
Thus, we have the following definition for MAPE:
where is the number of observations/forecasts, and
,
are respectively the forecast error and
actual value for each observation .
As mentioned above, percentage errors have the advantage of being scale independent i.e., they don’t
depend on the units, say £, that our observations are measured in. They do have a disadvantage of
being undefined or extremely large if any of the observations
are zero, or close to zero.
The steps to calculate MAPE in Excel are as follows:
Calculate the percentage errors:
Calculate the absolute percentage errors:
And then finally take the average of the absolute percentage errors:
This gives us a final MAPE value of 5.3%.
Relative Merits of MAE, RMSE and MAPE
MAPE
Advantages:
• Intuitive and easy to explain.
• Scale free and so allows you to compare errors between different forecasts. For example, it
would allow you to compare forecast errors between a high-volume sales product and a low-
volume sales product.
Limitations:
• It cannot be used if any of the actual values are zero. (We would have division by zero.)
• Small values of
, can also lead to large percentage errors.
• For forecasts that are too low, MAPE cannot exceed 100%, while there is no upper bound on
MAPE for forecasts that are too high. (Asymmetric)
• Imposes a heavier penalty on negative errors,
, than on positive errors. This can lead
to a bias towards models that provide low forecasts.
MAE
Advantages:
• Easy to understand: the average of the absolute forecast errors.
• Robust i.e., less sensitive to outliers than RMSE
Limitations:
• May not be sensitive to large errors
RMSE
Advantages
• Since the errors are squared, it penalizes large errors more than small ones. Useful for
situations when a few large errors are more undesirable than many small ones.
• Avoids having to take absolute values which it advantageous in many mathematical
calculations (but does not concern us in this course).
Limitations:
• Harder to interpret than MAE. Not easy to explain.
• Relatively complicated to calculate.
When forecasting, it is probably worthwhile to calculate all of these error metrics and keep track of
them. If MAE and RMSE are roughly the same then that indicates that we have errors that are fairly
constant. If RMSE >> MAE then that indicates that there are some large forecast errors present.
Errors in Seasonal Models
If our data is seasonal then the measures described earlier in this chapter are still appropriate (with
any caveats about that particular method still in place). However, there are other specialized metrics
that can also be used. We discuss one such metric next.
If our data is seasonal with period m say, then we can make use of the Mean Absolute Scaled Error
(MASE). MASE is defined as follows:
This formula looks quite intimidating, but is actually fairly intuitive with a bit of thought. Looking at
the denominator, we see that it is the average of a sequence of naïve forecasts. Recall that a naïve
forecast is simply where we use a single observation as our forecast for the next period.
(Equivalently, we could say that a naïve forecast is an MA forecast with period one.) Since we have
seasonality with period m, we are just using
as our forecast for
. So, the denominator is
basically a measure of how well we would forecast using the simplest model possible. The
numerator, on the other hand is the MAE of our model. Thus, if MASE ends up with a value less than
one, we are doing better than the naïve model. Alternately, if MASE is greater than one, this signifies
that our model is making worse predictions than the naïve model.
We demonstrate this using the seasonal data example from Chapter 1. First step is to calculate the
absolute errors in column I.
The next step is to calculate the MAE by using the AVERAGE function in cell I29. This will be the
value needed for the numerator in the formula for MASE.
The next step is to calculate the naïve quarterly forecast error in column J. Consider quarter 1 2017.
The actual value is 115 (cell E7). The naïve forecast is just the value from the corresponding period
the year before i.e., quarter 1 2016 (cell E3). Thus, the naïve forecast error here will be actual –
forecast i.e., 115 – 105 = 10 and the absolute naïve error will simply be the absolute value of this.
Since 10 is already positive, the result will stay as 10.
Next, calculate the average of the naïve absolute errors. This corresponds to the denominator in the
formula for MASE. See cell J29 below.
Finally, to calculate MASE we simply need to take the ratio of MAE and MNAE. This is done below
in cell K29.
Thus, we have a MASE value of 0.633. Since this is less than 1, it tells us that our seasonal model is
comfortably outperforming the naïve forecasting model.
Tracking Signal (TS)
We say that our forecast is biased if it consistently either underestimates or overestimates the actual
data. This is an undesirable property for forecasting (ideally a forecast should be equally likely to be
above or below the actual value, so that on average we are close to the true values). To help us
identify if there is a consistent bias present in the forecast, we introduce here the Tracking Signal
(TS).
The tracking signal attempts to indicate if there is a persistent tendency for actual data values to be
systematically higher or lower than forecasts. If the forecast is consistently lower than the actual then
the tracking signal will be positive. Alternately, if the forecast is consistently higher than the actual,
then the tracking signal will be negative. Ignoring a bit of mathematics, we have the following rule
of thumb:
If the tracking signal is consistently between -4 and 4 then there is no significant bias in the forecasts.
To calculate the TS as period we need:
- to calculate the running sum of forecast errors (RSFE) up to that period. i.e.,
- and also calculate the mean absolute error (MAE) up to that period. i.e.,
Then we can define the tracking signal at period as
We go back to our running example and calculate the tracking signal at monthly intervals over 2020.
We begin by calculating the error and absolute errors as usual. This is done in columns G and H.
Next, in column I, we calculate the running sum of the forecast errors i.e., the sum of all forecast
errors up to the current time period.
We then repeat the process in column J to calculate the running sum of the absolute errors.
We then move on to column K and calculate the running MAE i.e., the average of the absolute errors
up to each observation time. Since we have the running sum already calculated in column J, the
running MAE is simply the running absolute sum divided by the number of observations to that point
(given by the period number in column B). See an example below in cell K3.
Finally, the tracking signal value at a given observation point is just the ratio of running sum of
forecast errors divided by the running MAE at that point. We see how to do this below in column L.
The end result, after copying down the formula in column L, shows us that the tracking signal stays
between -4 and 4 for the entire year of 2020. This indicates that our forecasting model is not
exhibiting any significant bias during this period.
Summary
In this chapter we have defined and implemented a variety of methods for measuring forecast error.
We looked at:
• Mean Absolute Error (MAE)
• Mean Square Error (MSE)
• Root Mean Square Error (RMSE)
• Mean Absolute Percentage Error (MAPE)
• Mean Absolute Scaled Error (MASE)
These are by no means the only available error metrics, but understanding these will be more than
enough to cover most situations.
Your Turn!
Exercise 1: Errors
1. Open the file Session2_excel
2. Open the ex1_errors tab
3. Using the given sales data, implement a linear regression forecast (using
FORECAST.LINEAR) in column E.
4. Calculate the forecast errors in column F.
5. Calculate the absolute forecast errors in column G.
6. Calculate the percentage errors i.e.,
in column H.
7. Calculate the absolute percentage errors in column I.
8. Calculate the square errors in column J.
9. Calculate MAE, MAPE, MSE and RMSE in cells F31: F34 respectively.
Exercise 2: Tracking Signal
1. Open the file Session2_excel
2. Open the ex2_tracking_signal tab
3. Using the given sales data, implement a linear regression forecast (using
FORECAST.LINEAR) in column E.
4. Calculate the forecast errors in column F.
5. Calculate the absolute forecast errors in column G.
6. Calculate the running sum of forecast errors in column H.
7. Calculate the running sum of absolute errors in column I.
8. Calculate the running MAE in column J.
9. Calculate the tracking signal as the ratio of running sums of errors over the running MAE.
10. Comment on the tracking signal. What does it signify?
11. Save the file.
Chapter 3 Forecasting with Solver
How to Install Solver
We will cover this in class if need be.
Finding for Exponential Smoothing Model
Recall that for the exponential smoothing model the forecast formula is
,
where:
-
is the forecast value at time
- is the smoothing constant taking values between zero and one.
-
is the actual value for time
-
is the forecast value for time
So, the exponential smoothing forecast is a weighted combination of the last actual data point and the
last forecasted point.
Previously, we just assumed a particular value for (say ). Now that we have learned about
quantifying the quality of our forecasts using e.g., MAE or MSE, we can use Solver on our data set to
find the value of that minimizes the error of our forecast.
We use the exponential smoothing example from Chapter 1, with Then we have an MAE of
£126,535 and an RMSE of £133,860.
To open Solver, we click on the Data tab and then from the Analysis group select Solver (far right).
There are three main components we need to fill out:
- Objective cell
- Variable cells
- Constraints
Solver works by finding the optimum value (maximum, minimum, or specified) for the formula in the
Objective cell, by changing the values in the Variable cells while requiring that restrictions imposed
in the Constraints cells remain satisfied.
Objective cell:
The objective cell contains the formula that we want to either maximize, minimize, or achieve a
target value. In our case we wish to minimize either MAE (cell G19) or RMSE (cell H21).
Variable cells:
The Variable cell(s) contain values that can be varied in order to achieve the objective. In our case
we will vary the alpha value in cell B1.
Constraints:
Here we are allowed to impose restrictions that any optimum solution must satisfy. In our setting we
require that alpha lie between 0 and 1. To add a constraint, proceed as follows:
Click the Add button on the right side of the “Subject to the Constraints” box.
In the constraint window enter a constraint. Once it’s entered you can either click on Add if you need
to add further constraints, or OK if you are finished. Below we add the first constraint that alpha
must be less than or equal to 1.
Once we have entered the second constraint, we can click on Solve.
Once solver has finished calculating it will return the Solver Results dialogue box. We select the
“Keep Solver Solution” radio button and then click OK.
We see that Solver has found that an alpha value of 0.26 will reduce MAE to £98,567 and RMSE to
£111,822.
If we repeat the above process, but instead minimize RMSE (cell H21) we find an optimum alpha
value of 0.246, with an MAE of £98,594 and RMSE of £111,790.
Optimizing Weights for WMA Model
Recall from Chapter 1 that the mathematical formula for the WMA forecast is
,
where:
-
is the forecast for time .
-
is the actual value at time
-
is the weight assigned to previous observation value.
- is the number of previous observations we are using for our forecast.
-
.
Here we will use Solver to determine the set of weights that minimize our forecast error. This differs
slightly from the previous section in that now we need to optimize multiple parameters, rather than
just the single parameter
The process for using Solver here, is basically the same as in the previous section, so we will skip
most of the implementation details. We will start with initial weights of 70%, 20% and 10% i.e., we
weight the most recent observation with 70%, second most recent with 20% and third with 10%.
Again, note the weights must sum to 100%. This gives an MAE of £129,222. We call Solver and set
it up as follows:
After completing its calculations, Solver returns a solution for the optimal weights of 14%, 71% and
14%, with an MAE of £84,445.
Optimizing the Seasonality Index for Seasonal Model
We discussed in Chapter 1, how to incorporate seasonality into our forecasts. This was done by
multiplying an existing linear regression forecast by appropriate elements from a seasonality index.
In this section, we again use Solver to determine the elements of the seasonality index by minimizing
the forecast error.
We use the example from Chapter 1 which exhibited quarterly seasonality. Our initial guess for the
seasonality index is 1, 0.5, 1.5 and 1, with an MAE of 14.92. Recall that for quarterly seasonality the
elements of the index must sum to 4. This is true for our initial guess and will need to be set up in the
constraints when we use Solver. Since the details are so similar, we again just show the final Solver
dialogue box prior to clicking “Solve”. As you can see, the fit is not great. It captures the peaks quite
well, but seems to badly under forecast the low periods.
After Solver has finished, we have a seasonality index of 0.92, 0.69, 1.49 and 0.89, with an MAE of
5.29. The results are shown next:
Visually, the fit seems to match the peaks and troughs much better now!
Summary
In this chapter we have briefly introduced optimization type problems and their solutions using the
Solver tool from the Data Analysis add in. We informally introduced the notions of:
- Objective function
- Variables, and
- Constraints
and how to find solutions using Solver. We used this to optimize our forecasts for three types of
models: exponential smoothing, WMA and seasonal.
Your Turn!
Exercise 1: Optimize Exponential Smoothing
1. Open the file Session2_excel
2. Open the ex1_exp_smoothing_solver tab
3. Using the given data, set alpha = 0.8 in cell K5, and using the formula given in the notes,
implement an exponential smoothing forecast for column E.
4. Calculate the forecast errors in column F.
5. Calculate the absolute forecast errors in column G.
6. Calculate the square errors in column H.
7. Calculate the MAE and MSE in cells G32 & H32 respectively.
8. Calculate the RMSE in cell H34.
9. Use Solver to vary alpha (cell K5) until the MAE is a minimum. Set the constraint that alpha
must lie between 0 and 1. Record the optimum alpha value in cell G36
10. Use Solver again, but this time vary alpha (cell K5) until the RMSE is a minimum. Record
the new optimum alpha value in cell H36. Compare both alpha values.
11. Save the file.
Exercise 2: Optimize Weighted Moving Average (WMA)
1. Open the file Session2_excel
2. Open the ex2_wma_solver tab
3. Using the given data, and the weights given in cells K2:K4, implement a 3 period WMA
forecast for column E.
4. Calculate the forecast errors in column F.
5. Calculate the absolute forecast errors in column G.
6. Calculate the square errors in column H.
7. Calculate the MAE and MAPE in cells G32 & H32 respectively.
8. Use Solver to vary the weights (cells K2:K4) until the MAE is a minimum. Set the constraint
that the sum (cell K5) equals 100%. Record the optimum weights in cells K11:K13.
9. Use Solver again, but this time vary the weights (cells K2:K4) until the MAPE is a minimum.
Set the constraint that the sum (cell K5) equal 100%. Record the new optimum weights in
cells K15:K17. Compare both sets of weights.
10. Save the file.
Exercise 3: Optimize Seasonality Index
1. Open the file Session2_excel
2. Open the ex3_seasonal_solver tab
3. Using the given data, and FORECAST.LINEAR, implement a linear forecast in column E.
4. Using the regression forecast, and the default seasonal index weights (column F) implement a
seasonal forecast in column G.
5. Calculate the forecast errors in column F.
6. Calculate the absolute forecast errors in column G.
7. Calculate the square errors in column H.
8. Calculate the MAE and MSE in cells G32 & H32 respectively.
9. Calculate the RMSE in cell H34.
10. Use Solver to vary the weights (cells F2:F5) until the MAE is a minimum. Set the constraint
that the sum (cell M3) equals 4. Record the optimum index weights in cells M9:M12.
11. Use Solver again, but this time vary the weights (cells F2:F5) until the RMSE is a minimum.
Set the constraint that the sum (cell M3) equals 4. Record the new optimum weights in cells
M15:M18. Compare both sets of index weights.
12. Save the file.
