Question 1171670: Perform a Trend and Seasonal analysis with the following data. Include the trend formula
and the multiplicative indices for the seasons. Using the model forecast sales for the next
year (all 4 seasons of year 4).
YEar Season Sales
1 sp 140
1 su 50
1 w 130
1 f 520
2 sp 200
2 su 110
2 w 190
2 f 550
3 sp 260
3 su 220
3 w 210
3 f 570
Answer by CPhill(1987)   (Show Source):
You can put this solution on YOUR website! Let's break down this trend and seasonal analysis step-by-step.
**1. Calculate the Centered Moving Averages (CMA)**
* **Calculate the 4-Season Moving Averages (4-SMA):**
* Find the average of the first four sales values (sp, su, w, f of year 1).
* Then, move one season forward and calculate the average of the next four sales values (su, w, f of year 1, and sp of year 2).
* Continue this process until you reach the end of the data.
* **Calculate the Centered Moving Averages (CMA):**
* Find the average of each pair of consecutive 4-SMA values.
Here's the calculation table:
| Year | Season | Sales | 4-SMA | CMA |
| :---- | :----- | :---- | :---- | :--- |
| 1 | sp | 140 | | |
| 1 | su | 50 | | |
| 1 | w | 130 | 210.00 | |
| 1 | f | 520 | 227.50 | 218.75 |
| 2 | sp | 200 | 257.50 | 242.50 |
| 2 | su | 110 | 267.50 | 262.50 |
| 2 | w | 190 | 267.50 | 267.50 |
| 2 | f | 550 | 262.50 | 265.00 |
| 3 | sp | 260 | 282.50 | 272.50 |
| 3 | su | 220 | 315.00 | 298.75 |
| 3 | w | 210 | 342.50 | 328.75 |
| 3 | f | 570 | | |
**Calculations for the 4-SMA:**
* (140+50+130+520)/4 = 210
* (50+130+520+200)/4 = 227.5
* And so on.
**Calculations for the CMA:**
* (210+227.5)/2 = 218.75
* (227.5+257.5)/2 = 242.5
* And so on.
**2. Calculate the Seasonal Indices**
* **Calculate the Seasonal Ratios:**
* Divide the actual sales by the corresponding CMA value.
* **Average the Seasonal Ratios for Each Season:**
* Calculate the average of all seasonal ratios for each season.
* **Adjust the Seasonal Indices:**
* If the sum of the average seasonal indices is not equal to 4 (since there are 4 seasons), adjust them proportionally to make the sum equal to 4.
Seasonal Ratio Calculation:
| Year | Season | Sales | CMA | Ratio |
| :---- | :----- | :---- | :--- | :---- |
| 1 | f | 520 | 218.75 | 2.3769 |
| 2 | sp | 200 | 242.50 | 0.8248 |
| 2 | su | 110 | 262.50 | 0.4190 |
| 2 | w | 190 | 267.50 | 0.7103 |
| 2 | f | 550 | 265.00 | 2.0755 |
| 3 | sp | 260 | 272.50 | 0.9541 |
| 3 | su | 220 | 298.75 | 0.7364 |
| 3 | w | 210 | 328.75 | 0.6388 |
Averaging the seasonal ratio:
* Spring (sp) index = (0.8248+0.9541)/2 = 0.88945
* Summer (su) index = (0.4190+0.7364)/2 = 0.5777
* Winter (w) index = (0.7103+0.6388)/2 = 0.67455
* Fall (f) index = (2.3769+2.0755)/2 = 2.2262
Sum of indices = 0.88945 + 0.5777 + 0.67455 + 2.2262 = 4.3679
Adjustment Factor = 4/4.3679 = 0.91578
Adjusted Seasonal Indices:
* sp = 0.88945 * 0.91578 = 0.8145
* su = 0.5777 * 0.91578 = 0.5289
* w = 0.67455 * 0.91578 = 0.6177
* f = 2.2262 * 0.91578 = 2.0390
**3. Calculate the Trend Line**
* Assign numerical values to the CMAs. For example, the first CMA (218.75) is at time period 1, the second (242.50) is at time period 2, and so on.
* Use linear regression to find the trend line equation (y = mx + b).
Time Periods: 1, 2, 3, 4, 5, 6, 7, 8
CMAs: 218.75, 242.50, 262.50, 267.50, 265.00, 272.50, 298.75, 328.75
Using a calculator or spreadsheet, you will find:
* m (slope) ≈ 13.93
* b (intercept) ≈ 211.79
Trend Formula: y = 13.93x + 211.79
**4. Forecast for Year 4**
* Year 4: time periods 9, 10, 11, 12
* **Trend Forecast:**
* sp (9): 13.93(9) + 211.79 = 337.16
* su (10): 13.93(10) + 211.79 = 351.09
* w (11): 13.93(11) + 211.79 = 365.02
* f (12): 13.93(12) + 211.79 = 378.95
* **Seasonal Adjusted Forecast:**
* sp: 337.16 * 0.8145 = 274.61
* su: 351.09 * 0.5289 = 185.69
* w: 365.02 * 0.6177 = 225.48
* f: 378.95 * 2.0390 = 772.68
**Forecasted Sales for Year 4:**
* Year 4, Spring: 275
* Year 4, Summer: 186
* Year 4, Winter: 225
* Year 4, Fall: 773
|
|
|
