In: Operations Management
Forecasting
Illustrate your work to show how the answers were obtained. Round to two decimal places. Feel free to illustrate your answers in tables if this helps you to visualize the mathematical functions taking place.
Based on the following data, develop a forecast for the expected demand for Year 2. Assume the demand for year two will be 130,000 units. This is a seasonal demand forecast. You may illustrate this in tables if you wish, but show your calculation within the table. Round to two decimal places in your calculations
Year 1 --- Past Sales (Units)
Quarter 1 --- 28,000
Quarter 2 --- 36,400
Quarter 3 --- 28,600
Quarter 4 --- 27,850
Given the following data, use a three-quarter moving average to forecast the demand for the third quarter of this year.
------------ Jan --- Feb --- Mar ---
Apr --- May --- Jun --- Jul --- Aug --- Sep --- Oct --- Nov ---
Dec
Last Year 260 --- 280 --- 275 --- 320 --- 315 ---
345 --- 275 --- 290 --- 280 --- 345 --- 368 --- 388
This year 270 --- 275 --- 265 --- 370 --- 325 ---
345
1)
Answer:
Seasons of Year 2 | Forecast sales for Year 2 |
Quarter 1 | 30119.98 |
Quarter 2 | 39155.98 |
Quarter 3 | 30765.41 |
Quarter 4 | 29958.63 |
As, given that Demand for Year 2 is 130000
Hence, Demand for each quarter of Year 2 would be 130000/4
Demand for each quarter of Year 2 =32500
Important formula:
Seasonal Factor=Seasonal Average/Overall Average
Forecast sales for Year 2=Seasonal Factor*Demand for each quarter
1 | B | C | D | E | F |
2 | Forecast is Prepared with Seasonality | ||||
3 | Season | Part Sales of Year 1 | Seasonal Average | Seasonal Factor | Forecast sales for Year 2 |
4 | Quarter 1 | 28000 | 28000 | 0.93 | 30119.98 |
5 | Quarter 2 | 36400 | 36400 | 1.20 | 39155.98 |
6 | Quarter 3 | 28600 | 28600 | 0.95 | 30765.41 |
7 | Quarter 4 | 27850 | 27850 | 0.92 | 29958.63 |
8 | Overall Average | 30212.5 |
Formula:
Forecast is Prepared with Seasonality | ||||
Season | Part Sales of Year 1 | Seasonal Average | Seasonal Factor=Seasonal Average/Overall Average | Forecast sales for Year 2=Seasonal Factor*Demand for each quarter |
Quarter 1 | 28000 | =AVERAGE of 28000 | =28000/30212.5 | =32500*0.93 |
Quarter 2 | 36400 | =AVERAGE of 36400 | =36400/30212.5 | =32500*1.2 |
Quarter 3 | 28600 | =AVERAGE of 28600 | =28600/30212.5 | =32500*0.95 |
Quarter 4 | 27850 | =AVERAGE of 27850 | =27850/30212.5 | =32500*0.92 |
Overall Average | =AVERAGE of 28000,36400,28600 and 27850 |
2)
Answer:
3 Quarter Moving average forecast for This Year Quarter 3: 983.67
Quarter 1: Jan, Feb and March, Quarter 2: April, May and Jun Quarter 3: Jul, Aug and Sep Quarter 4: Oct, Nov and Dec
3 Quarter Moving average forecast of Quarter 4 = average of actual values of Quarter 1 to Quarter 3
Forecast using 3 Quarter Moving average | |||||||||
Month | Last Year | This Year | Season | Last Year | This Year | Year | Season | Actual | 3 Quarter Moving average forecast |
Jan | 260 | 270 | Quarter 1 | 815 | 810 | Year 1 | Quarter 1 | 815 | |
Feb | 280 | 275 | Quarter 2 | 980 | 1040 | Year 1 | Quarter 2 | 980 | |
Mar | 275 | 265 | Quarter 3 | 845 | Year 1 | Quarter 3 | 845 | ||
Apr | 320 | 370 | Quarter 4 | 1101 | Year 1 | Quarter 4 | 1101 | 880.00 | |
May | 315 | 325 | Year 2 | Quarter 1 | 810 | 975.33 | |||
Jun | 345 | 345 | Year 2 | Quarter 2 | 1040 | 918.67 | |||
July | 275 | Year 2 | Quarter 3 | 983.67 | |||||
Aug | 290 | ||||||||
Sep | 280 | ||||||||
Oct | 345 | ||||||||
Nov | 368 | ||||||||
Dec | 388 |
Formula:
Forecast using 3 Quarter Moving average | |||||||||
Month | Last Year | This Year | Season | Last Year | This Year | Year | Season | Actual | 3 Quarter Moving average forecast |
Jan | 260 | 270 | Quarter 1 | =SUM of 260, 280 and 275 | =SUM of 270, 275 and 265 | Year 1 | Quarter 1 | 815 | |
Feb | 280 | 275 | Quarter 2 | =SUM of 320, 315 and 345 | =SUM of 370, 325 and 345 | Year 1 | Quarter 2 | 980 | |
Mar | 275 | 265 | Quarter 3 | =SUM of 275, 290 and 280 | Year 1 | Quarter 3 | 845 | ||
Apr | 320 | 370 | Quarter 4 | =SUM of 345,368 and 388 | Year 1 | Quarter 4 | 1101 | =AVERAGE of 815,980 and 845 | |
May | 315 | 325 | Year 2 | Quarter 1 | 810 | =AVERAGE of 980,845 and 1101 | |||
Jun | 345 | 345 | Year 2 | Quarter 2 | 1040 | =AVERAGE of 845, 1101 and 810 | |||
July | 275 | Year 2 | Quarter 3 | =AVERAGE of 1101,810 and 1040 | |||||
Aug | 290 | ||||||||
Sep | 280 | ||||||||
Oct | 345 | ||||||||
Nov | 368 | ||||||||
Dec | 388 |