By Dr. Berg
Every spring I teach a Management Science class for MBA’s where we cover the latest in advanced forecasting. For example, in ECC we have options that use factors to discover and adjust for weekly trends, underlying growth patters and changes. We call these Alpha, Beta, Delta and Gamma factors. Unfortunately, these models have limited support in BPC. In this Blog, I will demonstrate how you can make use of these options by a step-by-step example using basic Excel functions.
We are trying to forecast sales by month for a product that has extremely high season changes. The historical unit sales over the last 108 months appear to be erratic as in the figure below. We will use this data to build a model that can forecast sales for 2011.
The problem is to build a model that can predict this sales pattern 100% correctly (yes, it can be done), let us see how:
Step- 1 Determining Base Line Seasonality
We look at the average sales in a time overall period (i.e. year) and establish the mean. We then determine the seasonality as: the actual for the period (month) less the mean for the overall period (year)
Gamma is the term use to see if there are changes in the seasonality. In short, we may have 14 more items sold in July than the other months of the year, but this is also growing at 4 items each year. For example:
In July 2001 we have sales of 115 items. This is 14 items more than the average for the other months of the year. But what if in July 2002 it increases to 119 items (+4) and to in July 2003 to 123 items (+4)? - We may then say that the Gamma represents an increase of + 4.
Step- 2 The First Base Line Forecast
For the last period (month) of the overall period (year), we create the first baseline forecast. This is simply the Actual sale, less the seasonal factor. In our example it is 102 less 1 = 101
Step- 3 The First Trend factor is zero
In the last period (month) of the overall period (year), we have no trends in our forecast yet. We simply flag it as “zero”. Later we will use a term called “Beta” to calculate this (more on this later).
Step- 4 The Overall long-term trend - Alpha
Alpha looks at the ‘big picture’. It is a number from 0 to 1 and is used in relations to trends, seasonal factors and previous forecasts. First, we use Alpha to look at the seasonal change. In our example alpha is 0.5, so we get: 0.5*(actual – seasonal factor same period last year) or: 0.5 * (104 - (-6)) = 55
Second, we use the ‘opposite’ of Alpha to look at the base level and prior period trend. So we get: (1-alpha) * (base level prior period plus seasonal factor last period) or: (1- 0.5) * (101 + 0) = 50.5. We add them together and get the Base level forecast for the current period = 55 + 50.5 = 105.5
Step- 5 Finding the Trend factor – we call it Beta
In step 3, for the last period for the first year, we had no trends in our forecast yet. We simply flagged it as “zero’. For the first forecast for 2002 we introduce the term ‘beta’. This is long-term ‘hidden’ trends in our data. Beta is normally expressed in a number from 0.0 to 1.0, but can sometimes exceed this. In our example, we simply selected a beta of 0.25. We will find the correct beta later.
Beta is calculated in three steps:
First, we look at the base-level forecast for January 2002 and compare it to prior period (Dec. 2001): 105.5 -101 = 4.5. We multiply this with Beta: 4.5 *0.25 = 1.13.
Second, we take the ‘opposite’ of beta, calculated as 1- beta or: 1 - 0.25 = 0.75. We use this to look at the trend prior period: 0.75 * 0 = 0.00
Third, We add this together and find the trend value for the period: 1.13 + 0.00 = 1.13
Step- 6 Bringing together the Real Forecast
This is a simple step. So far we only created a trend, base line forecast and seasonality adjustments. Now we will create the actual forecast based on all these factors. We simply add these together: Baseline forecast 105.50 + Trend 1.13 + Seasonal factor - 6.00 = 100.63
Step-7 Crating a Forecast for all periods we have data for
We can now click and drag the formulas and create a forecast based for all periods we have actual data for. We will see how good our forecasting model is. Is our alpha of 0.5 the best? Is our beta of 0.25 the best? Is our gamma of 2.00 the best? In our example we have 9 years (2001-2010) of data left to test the forecasting model against, and we simply copy the formulas we did in steps one to six to get our forecasts
Step- 8 Examining the forecasting model visually
We graph our forecast and see how our forecast is fitting the actual data. The Blue line is actual sales for 108 months (2001-2010) and the Red line if our forecast. As we can see, or forecasting model is close.
Step- 9 Examining the model – Mean Square Error (MSE)
We now calculate the MSE by:
First, Sum the (The forecasted value – the actual value)2 for all forecasts. I.e. Jan 2002 = (100.63 – 104)2 = 11.357 + Feb 2002 = (101 – 100)2 = 1.000 (this is done for all 108 months)
Second, Divide the sum by number of periods in the forecast (nine years of 12 months) = 108. MSE for our example is now 193.6. Our goal is to reduce the MSE to as small as possible by changing the alpha, beta and gamma
Step- 10 How good is the Forecast Model – MAPE
MAPE is the ‘Mean Absolute Percent Error’. MAPE for each forecasted period is calculated as:
As we use Excel GoalSeek or a BPC script, we see that our forecast is dramatically improving
Data fits perfectly!!! The forecast data fits almost perfectly our real data for 10 years, and we know alpha, beta, and gamma. All this is used to forecast the data for 2011 with a very high degree of accuracy!!
For those interested, you can download my university lecture deck and spreadsheet at the links below. Just look under the spreaadsheet tab called "problem 2.5B".
Other forecasting techniques
There are many other forecasting methods with lesser accuracy. This includes:
- Last months demand
- Average for the previous year
- Rolling average (i.e. 12 months)
- Gut-feeling (very popular)
- Simple or multiple regression
- Clustering by period
- Exponential smoothing
As you can see, exponential smoothing methods with alpha, beta and gamma are popular and great methods, that can be added in Excel or via scripts. However, as some observant readers may notice, I skipped the Delta factor. That is a used to smooth the absolute mean, and that is for another day…
FYI: the SAP ECC default/initial settings are: alpha = 0.2; beta = 0.1; delta = 0.3 and gamma = 0.3