You are a consultant for the Diligent Consulting Group (DCG). You have completed
the first assignment, developing and testing a forecasting method that uses Linear Regression
(LR) techniques (Module 2 Case). However, the consulting manager at DCG wants to try a
different forecasting method as well. Now you decide to try Single Exponential Smoothing
(SES) to forecast sales.
Using this Excel template: Data chart for BUS520 SLP 2, do the following:
Calculate the MAPE for Year 2 Linear Regression forecast (use the first spreadsheet tab
labeled �Year 2 Forecast � MAPE�).
Calculate forecasted sales for Year 2 using SES (use the second spreadsheet tab labeled �SES
� MAPE�). Use 0.15 and 0.90 alphas.
Compare the MAPE calculated for the LR forecast (#1 above) with the MAPEs calculated
using SES.
Then write a report to your boss in which you discuss the results obtained above. Using
calculated MAPE values, make a recommendation concerning which method appears to be
more accurate for the Year 2 data: SES or Linear Regression.
SLP Assignment Expectations
Analysis
Conduct accurate and complete SES analysis in Excel.
Introduction
The New Star Grocery Company asked Diligent Consulting Group (DCG) to find out whether there
is a dependence on the customers, who visit the shop (“customer traffic”), and the total sales for
each month. The New Star Grocery Company gave DCG the data for the 12-months period of the
Year 2. This data includes monthly sales for 12 months along with the number of customers and
actual sales data for this period.
2
This aim of this research is to find out whether there exists dependence mentioned above with the
help of simple exponential smoothing (SES) method, and compare obtained results with results,
which were calculated with the help of linear regression method. Also, Mean Absolute Percentage
Error (MAPE) is used to compare results, obtained with the help of linear regression method and
simple exponential smoothing.
Executive Summary
The research showed that MAPE in SES method (when is equal to 0.125, which is the smallest
error value among other researched models, which include LR method (MAPE=0.165) and SES
with (MAPE=0.175).
Despite this fact, such errors are still small. Consequently, these models can be used for prediction.
As well, the research revealed, that the value of coefficient alpha needed to be researched to find an
optimal model, when MAPE is as small as possible (almost zero).
Year 2 Forecast Mean Absolute Percentage Error (MAPE)
Mean Absolute Percentage Error is a measure of prediction accuracy. It is widely used in
forecasting. The aim of using it is to show, whether obtained model has a significant error in
calculations or not. (Chase, 2013) The formula for calculating MAPE is as following:
Here is actual data at each period of time, obtained via a real life observation, – forecasted data. In
other words, this formula shows, how forecasted results are different from a real life result.
Consequently, a conclusion can be made about the validity of the model and its’ accuracy. In the
majority of cases MAPE gives precise results, but sometimes an additional check is necessary.
(Hyndman, et al. 2008)
3
Below there is a table, which shows calculations and a final result for MAPE in linear regression
method along with customers and sales (actual and forecasted) data:
Table 1. MAPE: calculations and results.
Sales
Year 2 Customers (x) Actual Y(t) Forecast F(t) Y(t) – F(t) PE APE
January 215 265000 250963.44 14036.6 0.053 0.053
February 259 388000 279475.02 108525.0 0.280 0.280
March 325 298000 322242.40 -24242.4 -0.081 0.081
April 354 260000 341034.12 -81034.1 -0.312 0.312
May 258 263000 278827.03 -15827.0 -0.060 0.060
June 199 402000 240595.59 161404.4 0.402 0.402
July 254 320000 276235.07 43764.9 0.137 0.137
August 299 310000 305394.64 4605.4 0.015 0.015
September 264 307000 282714.97 24285.0 0.079 0.079
October 198 302000 239947.60 62052.4 0.205 0.205
November 223 225000 256147.36 -31147.4 -0.138 0.138
December 261 361000 280771.00 80229.0 0.222 0.222
28887.647 0.067 0.165
ME MPE MAPE
All calculations can be found in an Excel document, which is attached to this work. Linear
regression method gives 0.165 mean average percentage error. It is quite a small error for this
method, so it still can be used for forecasting.
MAPE for Simple Exponential Smoothing (SES) Method
Simple exponential smoothing technique is used for forecasting as well. The idea of it is a recursive
use of the formula (2), which is shown below. This method is often used with assumptions, such as
seasonality, and uses previous data to make predictions. In some cases, it cannot be used, because
the distribution of data is very complex. (Wang, 2006) In current case data is almost linear, so the
use of simple exponential smoothing is suitable.
4
In some cases, SES is suitable for forecasting and making predictions with no trend data or
seasonality.
The formula for SES model is next:
Here and have forecasted sales for a current and previous moment of time, – actual sales for a
previous moment of time, – smoothing parameter. (Box, et al. 2015)
SES model in this research was used with two different alphas: 0.15 and 0.9.
The results were next:
when , then MAPE=0.125;
when , then MAPE=0.175.
Therefore, the Company made a conclusion, which simple exponential smoothing with is more
reliable.
Moreover, varying alphas, there is an opportunity to find the most optimal model, where the
prediction will be as accurate as possible (MAPE0). This means that the prediction can be built on
some assumptions about the data (etc. how sales increase each year), which is often not possible for
linear regression method.
Conclusions
In this research, Diligent Consulting Group tested two methods: linear regression (LR) and simple
exponential smoothing (SES). MAPE was used to find out which model is more accurate and gives
more precise predictions.
5
The research showed, that SES method is more reliable (MAPE=0.125, ) than LR method
(MAPE=0.165). Despite this fact, LR model is still accurate, and MAPE=0.165 is considered to be a
relatively small error.
DCG made a conclusion that there is an opportunity to test different alphas for finding out an
optimal model so that it will give more precise and accurate results. In this case, the prediction will
be more reliable.
References List
Box, G., Jenkins, G., Reinsel, G., Ljung, G. (2015). Time Series Analysis: Forecasting and
Control. New York. 49-51.
Chase, C. W., (2013). Demand-driven forecasting: A structured approach to forecasting.
New York. 140-143.
Hyndman, R., Koehler, A.B., Ord, J.K., Snyder, R.D. (2008). Forecasting with
Exponential Smoothing. England. 77-78.
6
Wang, S. (2006). Exponential Smoothing for Forecasting and Bayesian Validation of
Computer Models. Boston. 189-192.
BUS520-Simple Exponential Smoothing. How to do simple exponential smoothing (SES)
using Excel, including determining the forecast error.