**You are a consultant for the Diligent Consulting Group (DCG). You have completedthe 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 adifferent 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 tablabeled �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 calculatedusing SES.Then write a report to your boss in which you discuss the results obtained above. Usingcalculated MAPE values, make a recommendation concerning which method appears to bemore accurate for the Year 2 data: SES or Linear Regression.SLP Assignment ExpectationsAnalysisConduct 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.