Supply Chain Forecast using Winter’s Model

The first step is to obtain initial estimates of level and trend using linear regression. We first run a linear regression (using the Excel tool Data | Data Analysis | Regression) between demand and time periods. The estimate of initial level L0 is obtained as the intercept coefficient, and the trend T0 is obtained as the X variable coefficient (or the slope).

SUMMARY OUTPUT
Regression Statistics
Multiple R0.06759668       
R Square0.004569311       
Adjusted R Square  -0.029755885       
Standard Error  52373.11059       
Observations31       
    ANOVA        
   df  SS  MS  FSignificance F   
Regression1365135826.8365135826.80.1331182840.717867896   
Residual29795453386612742942712     
Total3079910474488      
         
   CoefficientsStandard Error  t Stat  P-value  Lower 95%  Upper 95%  Lower 95.0%  Upper 95.0%
Intercept453780.077419277.5645923.53928451.90048E-20414353.0309493207.1239414353.0309493207.1239
  X Variable 1– 383.7084677  1051.677368– 0.364853784  0.717867896– 2534.630196  1767.21326– 2534.630196  1767.21326

L0 (Level) = 453780.0774 T0 (Trend) = 383.708

Therefore, calculation of DE seasonalized demand and seasonal factor is being carried out by sing the below formula.

DE Seasonalized demand = L0 + Time period * T0 Seasonal factor (SF) = Demand / De Seasonalized demand

The remaining Level, Trend and Forecasted load of period 1 is being calculated as follows

L1= 0.05 *(Demand / seasonal factor) + (1- 0.05) * ( L0 + T0) T1= 0.1*(L1-L0) + (1-0.1) * T0

Forecasted load = (L1 +T1) *SF1

Methodology

The below table depicts the different Forecasting Methods and their Applicability, thus the problem is based on predicting the shipment load on festive seasons I have used winter’s model for forecasting.

Moving averageNo trend or seasonality
Simple exponential smoothingNo trend or seasonality
Holt’s modelTrend but no seasonality
Winter’s modelTrend and seasonality

TREND- AND SEASONALITY-CORRECTED EXPONENTIAL SMOOTHING (WINTER’S MODEL)

This method is appropriate when the systematic component of demand has a level, a trend, and a seasonal factor. In this case we have Assume periodicity of demand to be p. To begin, we need initial estimates of level (L0), trend (T0), and seasonal factors (S1, . . . , Sp). We obtain these estimates using the procedure for static forecasting described earlier.

In Period t, given estimates of level, Lt, trend, Tt, and seasonal factors, the forecast for future periods is given by

Ft+1 = 1Lt + Tt2St+1 and Ft+l = 1Lt + lTt2St+l

On observing demand for Period t + 1, we revise the estimates for level, trend, and seasonal factors as follows:

Lt+1 = a1Dt+1/St+12 + 11 – a21Lt + Tt2 Tt+1 = b1Lt+1 – Lt2 + 11 – b2Tt

Results and discussion

Seasonal effect may occur within a year, month, week or day. To measure seasonal effect, we construct seasonal indexes, which attempt to gauge the degree to which the seasons differ from one another. One requirement for this method is that we have a time series sufficiently long to allow us to observe the variable over several seasons. The seasonal indexes, forecasted load, error, % error are computed as shown below.

ABCDEFGHIJ
    Period    DemandDeseaso nalized Demand    Level    TrendSeasonal Factor    Forecast    Error    ABS error    %error
0  453780.1383.708     
1466470454163.8454163.8383.7081.027096466470000
2458244454547.5454547.5383.70791.0081324582440.0141140.0141143.07999E-06
3451192454931.2454931.2383.70780.9917814511920.0200640.0200644.44683E-06
4450398455314.9455314.9383.70770.9892014503980.0257660.0257665.72073E-06
5467142455698.6455698.6383.70751.0251124671420.0322330.0322336.90009E-06
6448107456082.3456082.3383.70740.9825134481070.0357760.0357767.98382E-06
7521330456466459021.5639.25971.027096468834.6-52495.452495.3810.06951021
8426843456849.7457847.8457.95461.008132463398.936555.9136555.918.564252148
9363636457233.4453722.9-0.326110.991781454538.890902.7990902.7924.99829317
10437180457617.1453134.1-59.17610.989201448822.911642.8911642.892.663179926
11451169458000.8452427-123.9661.025112464452.413283.4313283.432.944225401
12470223458384.5453617.57.4780930.982513444393.8-25829.225829.175.492960942
13415815458768.2450974.1-257.6091.037961470845550305503013.23425086
14482692459151.9452302.1-99.05131.000547450963.1-31728.931728.886.573317675
15483214459535.6454430.4123.69190.972748439879.4-43334.643334.598.967992122
16334889459919.3448795.5-452.1670.98676448535.9113646.9113646.933.93569727
17477366460303449273.3-359.1711.022323458351.5-19014.519014.453.983202148
18401284460686.7446777.9-572.7930.987923443492.642208.5542208.5510.51837392
19514268461070.4448947.7-298.541.026369457971-5629756297.0410.94702428
20445836461454.1448348.9-328.5661.007211451884.66048.5526048.5521.356676361
21513576461837.8451773.946.795920.981807439869.4-73706.673706.5814.35164045
22448279462221.5452512115.92170.962704434969.5-13309.513309.532.969027399
23326238462605.2445889.7-557.8961.026343464551.6138313.6138313.642.39652761
24455956462988.9446353.3-455.7490.978948435956.7-19999.319999.354.386244759
25435323463372.6444566.3-588.8731.038281462967.127644.1527644.156.350260642
26471483463756.3445213.8-465.2421.00593446610.2-24872.824872.855.275449379
27521869464140448675-72.59110.997306443550.4-78318.678318.6215.00733348
28509597464523.7452562.7323.43420.965498433124.7-76472.376472.3115.00642928
29448823464907.4452753.3310.15450.996875451470.62647.632647.630.58990515
30335625465291.1447478.2-248.3710.98320444545410982910982932.72373133
31442796465674.8446313.9-339.9681.032374461708.518912.518912.54.271154772
   423675.2-2569.841.011237450985.4   
   400050.1-4675.361.013889426954   
   375606-6652.240.981551388080.3   
   350506.1-8497.010.996319367595.6   
   324908.6-10207.10.959888328290.3   
CellFormulaCopied to
C3= $D$3+A4*$E$3C4 to C31
D3= 0.05*(B4/F4)+(1-0.05)*(D3+E3)D4 to D31
E3= 0.1*(D4-D3)+(1-0.1)*E3E4 to E31
F3=B4/C4F4 to F31
G3=(D3+E3)*F4G4 to G31
H3= G4-B4H4 to H31
I3= ABS(H4)I4 to I31
J3=100*(I4/B4)J4 to J31
Purecane
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like