Supply Chain Management Exponential Smoothing Forecasting Method

Question :

Find the forecasting accuracy measures such as mean absolute deviation, mean squared error, and mean absolute percentage error for the data set.

Use Excel Solver to identify what value of alpha will give a better fit between the forecasted and actual sales when using exponential smoothing forecasting method.

Answer :

Prerequisites for Exponential Smoothing:

The Simple Exponential Smoothing method is used for forecasting a time series when there is no trend or seasonal pattern, but the mean (or level) of the time series y t is slowly changing over time.

Procedures of Simple Exponential Smoothing Method 

• Step 1: Compute the initial estimate of the mean (or level) of the series at time period t = 0 

• Step 2: Compute the updated estimate by using the smoothing equation 

Where  is a smoothing constant between 0 and 1 i.e. 0 ≤ α ≤ 1.

Note that 

The coefficients measuring the contributions of the observations decrease exponentially over time.

Point forecast made at time T for yT + p

 We use the following terminology: if y1, …, yn represents a time series, then ŷi represents the ith forecasted value, where i ≤ n. For i ≤ n, the ith error ei is then

Our goal is to find a forecast that minimize the errors. Several measures are commonly used to determine the accuracy of a forecast, including the mean absolute error (MAE), mean squared error (MSE) and mean absolute percentage error (MAPE). Note that MAE is also commonly called mean absolute deviation (MAD).

The Solutions:

Using an exponential smoothing method with alpha = 0.7 on the data from 1982 to 2017, find the forecasting accuracy measures such as mean absolute deviation, mean squared error, and mean absolute percentage error for the data set.

Soln All the calculations are shown in the Excel spreadsheet. The steps are described below:

Step 1: On the Data tab, in the Analysis group, click Data Analysis.

Note: If the Data Analysis button can't find then load the Analysis Tool Pak add-in.

Step 2: Select Exponential Smoothing and click OK.

Click in the Input Range box and select the range of the actual cell value.

5. Click in the Damping factor box and type 0.3. Literature often talks about the smoothing constant α (alpha). The value (1- α) is called the damping factor.

6. Click in the Output Range box and select any empty cell.

7. Click OK.

Follow the worksheet for details.

2. Use Excel Solver to identify what value of alpha will give a better fit between the forecasted and actual sales when using exponential smoothing forecasting method.

Soln:

In this problem we will be finding the optimized value of α by minimizing MAD, MAPE, MSE.

The steps for performing this is given below:

Step 1: On the Data tab, in the Analysis group, click Solver.

Note: Solver add in needs to be loaded if it is used for the first time.

Step 2: In the solver window under set objective box we will select the cell containing the value of MAD, MAPE & MSE one by one to find the value for each case. Then select Min under To: option.

The changing variable cells will be the cell containing the value of α.

Under constraints we will add the two constraints: α ≥ 0 & α ≤ 1. 

The rest can be left default and the click solve.

We get the optimized value of α as:

The solutions are kept in Excel. So, follow the spreadsheet to get more intel.