Question

Show all calculations and step by step. Please provide formula.

Month Sales Month Sales 1 110 11 80 2 100 12 90 3 130 13 80 4 90 14 60 5 200 15 150 6 90 16 60 7 110 17 70 8 110 18 70 9 70 19 40 10 170 20 120 Use seasonal model to forecast the monthly sales for the next period.

Answer 1

Seasonal forecast is computed by Time Series decomposition method as shown below:

Time Series Decomposition Step 1: Organize the data in a time series. Plot the data on a graph and also plot the linear regression trend line and display trendline equation on chart. trendline equation can also be determined using EXCEL formulas SLOPE and INTERCEPT. Step 2: Calculate trendline for each month as per the equation displayed on chart (y = ax +b), where a is the slope and bis intercept. Coefficients of Regression equation: a = -2.647 b = 127.789 y = -2.647x + 127.789 Step 3: Calculate Seasonal Ratio by dividing actual sales by trendline. Step 4: Calculate Average Seasonal factors for each of the 4 quarters by averaging the seasonal ratio of the corresponding quarters of all years. For example, seasonal factor for quarter 1 = Average of seasonal ratios of quarter of all years. Similarly Seasonal factor of quarter II = Average of seasonal ratios of quarter Il of all years and so on. Step 5: Calculate Forecast Sales by multiplying the trendline with the Seasonal factor for corresponding quarter.

Forecast for month 21 to 30 is shown in the following table.

Month Sales D Seasonally adjusted Forecast Ft = SOY 106 107 116 110 100 130 198 101 102 Unadjusted Seasonal Seasonal Forecast Ratio Factor Y=aX+bR =DY S = Rt/n 125 0.8790 0.8449 122 0.8164 0.8768 120 1.0847 0.9707 117 0.7679 0.7146 115 1.7459 1.7243 112 0.8042 0.7532 109 | 1.0067 0.9261 107 | 1.0317 0.9525 104 0.6733 0.5947 101 1.6778 1.6404 0.8107 0.8449 0.9372 0.8768 0.8567 0.9707 0.6613 0.7146 1.7028 1.7243 0.7022 0.7532 0.8454 0.9261 | 0.8734 0.9525 0.51610.5947 1.6031 1.6404 0.8449 0.8768 0.9707 0.7146 1.7243 0.7532 0.9261 0.9525 0.5947 1.6404 colo 80 76 61 65 106 MX4989 52

Time Series Plot Sales Seasonally adjusted Forecast Linear (Sales) Sales y=-2.6466x + 127.79 Ahhhh Period

Forecast for the next period, i.e. month 21 = 61