Question: Did you create a SAS program / PROC ESM to double check your calculations? If not, I highly recommend that you do so. While SAS doesn’t provide a detailed running calculation summary for it’s backcasting, it does do so for the “foreward casting” portion of the analysis. You can use that to double check that your EXCEL formulas are correct, since the only difference is that the trend must be multiplied by -1 and – instead – of calculating things for observations 21 to 1, calculations are given for observations 1 to 21.   I have attached the SAS output.    BACKCASTING (Partial EXCEL)                 ꚙ (constant) ϒ (trend)   ꝺ (seasonal)   ❺-❶               .072110 .001000   .001000                               Year Time Period Time Index Actual #Disks Sold (y) Projected #Disks Sold (ŷ) et   Model Constant (at T) Trend (at T) Seasonal Time Index Seasonal Index (Additive) Model Constant - Trend (at T+1) Projected Sales (ŷ)T+1                   1 -28.025000                       4 -52.257456                       3 102.808333                   21236.000 -173.26579 2 -22.525877 21062.734 21034.709 2021 I 21 21236 21034.709 201.29079   21077.249 -173.251 1 -27.83822 20903.998 20851.741 2020 IV 20 21590 20851.741 738.25944   20957.234 -173.198 4 -51.57243 20784.036 20886.844 2020 III 19 21393 20886.844 506.15580   20820.535 -173.162 3 103.27799 20647.373 20624.847 2020 II 18 20351 20624.847 -273.84734   20627.626 -173.181 2 -22.77998 20454.445 20426.607 ...... 2016 IV 4 18047 18312.625 -265.62512   18344.716 -173.041 4 -51.49149 18171.675 18275.243 2016 III 3 18223 18275.243 -52.24284   18167.908 -173.044 3 103.51900 17994.864 17972.770 2016 II 2 18220 17972.770 247.23005   18012.692 -173.026 2 -21.86446 17839.665 17812.581 2016 I 1 17754 17812.581 -58.58127   17835.441 -173.031 1 -27.13818 17662.410 17610.919                     Re-Normalization 3.024881   FORECASTING (PARTIAL EXCEL)  (note the sign reversal in the trend and the required adjustment in the seasonal values) Year Time Period Time Index Actual Hits (y) Projected Hits (ŷ) et   Model Constant (at T) Trend (at T) Seasonal Time Index Seasonal Index (Additive) Model Constant + Trend (at T+1) Projected Hits (ŷ)T+1         -27.8944         1 -27.894400             -22.62068         2 -22.620700             102.76278         3 102.762800             -52.24771     17663.159 173.0307 4 -52.247700 17836.190 17808.296 2016 I 1 17754 17808.296 -54.296   17832.275 173.0268 1 -27.94478 18005.301 17982.681 2016 II 2 18220 17982.681 237.319   18022.415 173.0439 2 -22.40049 18195.458 18298.221 2016 III 3 18223 18298.221 -75.221   18190.034 173.0385 3 102.69300 18363.073 18310.825 2016 IV 4 18047 18310.825 -263.825   18344.048 173.0195 4 -52.49250 18517.068 18489.123   ........ 2020 II 18 20351 20783.704 -432.704   20774.558 173.0271 2 -22.45757 20947.585 21050.184 2020 III 19 21393 21050.184 342.816   20972.305 173.0518 3 102.91754 21145.357 21092.792 2020 IV 20 21590 21092.792 497.208   21181.210 173.0876 4 -52.10312 21354.298 21326.674 2021 I 21 21236 21326.674 -90.674   21347.760 173.0811 1 -27.70850 21520.841 21498.383       ... View more

Sorry for the delayed response, but I wanted to plug yourtime series into both SAS’s PROC ESM and my EXCEL spreadsheet to confirm. Backcasting “STATE=BACKCAST” Seasonal values are (Note that these values MUST added upto zero):   S*1 =  -28.0250      (adjustedseason-1) S*2 =  -22.5259     (adjusted season-2) S*3 =  -102.8083    (adjusted season-3) S*4 = -52.2575       (adjusted season-4)   Trend: Since you are (in effect) forecasting from period21 to period 1, the trend (B1) calculated from the regression model MUSTbe multiplied by -1 (i.e., it is now -173.2658).   Constant: This is simply the actual value for period 21(i.e., 21,236).   Weights SAS calculates the following: Level Weight = .07211 Trend Weight = .001 Seasonal Weight = .001   If you use EXCEL’s “SOLVER” utility you may get a slightlydifferent set of values.   Post Backcasting  (“STATE=STARTING”) Seasonal Values: After the backcasting is completed, the seasonal values willno longer sum to ZERO. Before they can be used in the “forward casting”, youwill need to adjust them (yet again). >From My EXECL calculations: S1 = -27.13818 S2 = -21.86446 S3 = 103.519 S4 = -51.49149   This adds up to 3.024881. This MUST be ZERO’d out beforeyou can proceed. To do this:   S*1 = -27.13818 – (3.024881/4) = -27.8944 S*2 = -21.86446 – (3.024881/4) = -22.62068 S*3 = 103.519 – (3.024881/4) = 102.76278 S*4 = -51.49149 – (3.024881/4) = -52.2477   Trend: Since you are now projecting “forwards” the trendcalculated by the backcasting MUST be multiplied by -1 (i.e., -173.031 * -1 =173.031)   Constant: The final constant calculated by thebackcasting must be adjusted in the same manner as the seasonal values (exceptthat we now ADD 3.024881/4: Constant * = 17662.410 + (3.024881/4) = 17,663.16622 “STATE=FINAL” In EXCEL you should wind up with the following constant termand set of seasonal values: Constant = 21,347.760 Trend = 173.081105 S1 = -27.70850 S2 = -22.45757 S3 = 102.91754 S4 = -52.10312   Lead=4: Y22 = 21,347.760 + 173.081105(1) + (-22.45757)= 21,498.38354 Y23 = 21,347.760 + 173.081105(2) + (102.91754)=  21,796.83975 Y24 = 21,347.760 + 173.081105(3) + (-52.10312)=  21,814.9002 Y25 = 21,347.760 + 173.081105(4) + (-27.70850)=  22,012.3738   SAS CODE   *************************    * AdditiveHolt-Winters *   *************************; proc esm data=a print=(estimates forecasts performancestatistics states) printdetails           seasonality=4 lead=4           out=out_a outest=outest_a outfor=outfor_a outstat=outstat_a;  id periodinterval=qtr;  forecast sales /model=addwinters; title3 'ESM Procedure'; run;   Hope this helps. ... View more

Sorry about that. While the final values from my examplewere correct, the formulas could have been better spelled out. 2.      To “create” the value for the kth index (i.e.,the forth season) a.      Sum(average S1, S2, S3)/k to create S/mean b.      the final S4* (adjusted) is = 0 – S/mean) 3.      The unadjusted seasonal values from step1 now need to be adjusted: SK *= Sk - S/mean In re-creating your time series, I calculated the followingresults: Step 1: B0 = 17669.01                 B1= 173.2658    (trend component)                 S1= 24.23246    (unadjusted season-1)                 S2= 29.73158    (unadjusted season-2)                 S3= 155.06579  (unadjusted season-3)   Step 2:  S/mean = (sum24.23246, 29.73158, 155.06579/4 = 52.2575                S*4= 0 – (52.2575) = -52.2575 (adjusted season-4)   Step 3: S*1 = 24.23246 – ( 52.2575) = -28.0250     (adjusted season-1)              S*2= 29.73158 – ( 52.2575) = -22.5259      (adjusted season-2)              S*3= 155.06579 – ( 52.2575) = -102.8083 (adjusted season-3)                B*0= 17669.01 +(  52.2575) = 17721.268    (adjusted intercept is needed only if youwant to double check in EXCEL).   Verify: sum (-28.0250,-22.5259, 102.8083,-52.2575) = 0 Whether you use the regression model’s coefficients or theadjusted seasonal index values, your predicted y’s (obs:1-4) should be 17866.51,18045.27, 18343.87, 18362.07 Again, I probably copied the “+/-“ sign and the “/4”incorrectly. Sorry about that. Let me know if this answered your question. ... View more

To create the initial seasonal index values: 1.      Run a simple regression model with (k-1) dummyvariables. For example, if you need to create quarterly index values, you wouldhave three dummy variables. The resulting model will generate S1, S2, S3parameters that represent the initial unadjusted seasonal values. (unadjusted). 2.      To “create” the value for the kth index (i.e.,the forth season) a.      average S1, S2, S3 to create S/mean) b.      the final S4* (adjusted) is = 0 – S/mean) 3.      The unadjusted seasonal values from step 1 nowneed to be adjusted: SK *= Sk + S/mean 4.      If you want to create a “double check” the interceptvalue will also need to be adjusted: B0 + S/mean. The OLS forecastwith the raw unadjusted parameters (S1, S2, S3 and B0) should matchthat created by using the adjusted parameters (S1*, S2*, S3*, S4* and B*0).I always do this as a double check. NOTE: The adjusted values should equalthose indicated by PROC ESM under the “Smoothed States”: Backcast. Note: the following example has a trend component in addition to seasonality. Step 1: B0 = 85.36250                 B1= 1.78875    (trend component)                 S1= .44125       (unadjusted season-1)                 S2= -15.89750 (unadjusted season-2)                 S3= -42.41125 (unadjusted season-3) Step 2: S*4 = 0 – average (.44125,-15.89750,-42.41125)= 0 – (-14.4669) = 14.4669 (adjusted season-4) Step 3: S*1 = .44125 + 14.4669 = 14.90815          (adjusted season-1)              S*2= -15.89750 + 14.4669 = -1.4306        (adjusted season-2)              S*3= -42.41125 + 14.4669 = -27.94435  (adjusted season-3)              B*0= 85.36250 – 14.4669 = 70.8956)      (adjustedintercept is needed only if you want to double check in EXCEL).              B*1= B1 * (-1) Required only if your time series has trend and you are usingEXCEL to double check the backcasting and you will need to reverse this (* +1)before proceeding with the forward casting. Note: the sum of the adjusted seasonal index valuesshould be zero! ... View more