dionne

profileJordanjameire
Assignment4Template.xlsx
Home>Education homework help>dionne

Forecast Error Exercise 1

Your company produces and distributes two lines of Consumer Extermal Hard Drives: 1 Terabyte drives (1 Tb) and 5 Terabyte Drives (5 Tb). The unit sales levels and forecasts for the last two previous years are in the table below. For this exercise, You have been asked to calculate the Forecast error and Forecast Percentage error for (a) each type of terabyte (b) each year, and (c) for each two-year period. Start your analysis by completing the table below, then use the spaces below (in light green) to calculate the error and percent error for each question asked. These equations and an example are covered in your textbook on pages 107 - 111. Finally, in the last space, provide a written summary of your findings regarding the sales trends and the forecasting efficacy by year, by product, and overall.
Year Period 1 Tb Forecast 1 Tb Actual 1 Tb Error 1 TB % Error 5 Tb Forecast 5 Tb Actual 5 Tb Error 5 Tb % Error
1 1 1000 800 -200 -25.0% 500 350 -150 -42.9%
2 1100 950 550 400
3 1300 1100 650 700
4 2200 2000 1100 1200
5 2300 2150 1150 1300
6 3000 2750 1500 1600
7 3500 3200 1750 1900
8 3600 3300 1800 2200
9 3200 3000 1600 1700
10 2200 1900 1100 1200
11 2100 2000 1050 1100
12 4200 3300 2100 2250
Year 1 Sum 29700.0 26450.0 -200.0 -0.8% 14850.0 15900.0 -150.0 -0.9%
Year 1 Avg/mo 2475.0 2204.2 -200.0 -9.1% 1237.5 1325.0 87.5 6.6%
2 1 1200 900 600 1000
2 1300 1150 650 950
3 1500 1300 750 800
4 2400 2100 1200 1300
5 2600 2600 1300 1500
6 3100 3050 1550 1700
7 3300 3350 1650 1900
8 4000 3850 2000 2200
9 3300 3400 1650 3000
10 2900 2900 1450 1850
11 2600 2200 1300 2200
12 5400 5000 2700 4000
Year 2 Sum 33600 31800 0 0.00% 16800 22400 0 0.0%
Year 2 Avg/mo 2800 2650 ERROR:#DIV/0! ERROR:#DIV/0! 1400 1867 ERROR:#DIV/0! ERROR:#DIV/0!
Year 1 & 2 Sum 63300 58250 -200 -0.34% 31650 38300 6650 17.4%
Avg/mo 2638 2427 -210 -8.67% 1319 1596 277 17.4%
a. Year 1 Avg. unit Error/mo. for 1 Tb forecast to Actual +5 Credit 5 pts each per
correct answer a - h
b. Year 2 Avg. unit Error/mo. for 1 Tb Forecast to Actual +5 Credit 20 pts for a
correct answer to k.
c. Years 1 & 2 combined Unit Avg. Error/mo. for 1 TB +5 Total 60 points
d. Year 1 Unit Avg. Error/mo. for 5 Tb forecast to Actual +5
e. Year 2 unit Avg. Error/mo. for 5 Tb Forecast to Actual +5
f. Years 1 & 2 Combined average unit Error/mo. for 5 TB +5
g. Years 1 & 2 Percentage Error for 1 TB +5
h. Years 1 & 2 Percentage unit Error for 5 Tb +5
k. What Conclusions can you draw regarding the sale of these two products and the forecasting accuracy? What is this forecasting method missing?
+20

Forecasting Error Exercise 2

You will use the same actual and forecasted sales for this problem, but only for the 1 TB units. Use the table below to help you calculate the Mean Percentage Error (MPE) and the Mean Absolute Percentager Error (MAPE) for the combined years 1 and 2. The sales levels and forecasts by number of units for the the 1 TB product line are llisted below for the two year period. You have been asked to calculate the Forecast error and Forecast Percentage error for (a) each type of terabyte (b) each year, and (c) for each two-year period. Start your analysis by completing the table below, then use the spaces below (in light green) to calculate the error and percent error for each question asked. These equations and an example are covered in your textbook on pages 112 - 114. Finally, in the space below the table explain your findings regarding the MPE and the MAPE.
Year Period 1 Tb Actual Sales 1 Tb Forecast Sales % Attainment At /Ft Error At - Ft (At - Ft)/At Absolute Error |At - Ft| Absolute % Error |At - Ft|/At
1 1 800 1000 80% -200 -25.0% 200 25%
2 950 1100
3 1100 1300
4 2000 2200
5 2150 2300
6 2750 3000
7 3200 3500
8 3300 3600
9 3000 3200
10 1900 2200
11 2000 2100
12 3300 4200
2 1 900 1200
2 1150 1300
3 1300 1500
4 2100 2400
5 2600 2600
6 3050 3100
7 3350 3300
8 3850 4000
9 3400 3300
10 2900 2900
11 2200 2600
12 5000 5400
Sum 58250 63300 92%
Mean 2427 2638 92% -210.4 -8.67% 200.0 8.24%
a. Mean Percentage error for the combined Periods of (Y1 + Y2) +5 Points
b. Mean absolute percentage error (MAPE) for the combined periods of (Y1 +Y2) +5 Points
c. What Conclusions can you draw regarding the sale of this product and the forecasting accuracy? What is this forecasting method missing? Do the differences between the MPE and MAPE mean anything and if yes, what?
+30 points

image1.jpg