This week you will use an excel spreadsheet to analyze forecasting performance. Your company produces and distributes two lines of Consumer External 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, Forecast Percentage, Mean percentage Error (MPE) and Mean Absolute Percentage Error (MAPE) for (a) each type of terabyte (b) each year, and (c) for each two-year period. The concepts, equations, and examples for how to calculate these forecasting effectiveness are covered in your textbook on pages 107 – 114. Finally, for each set of problems you will provide insight into the sales trends, and forecasting effectiveness with a written summary of your findings.
Assignment Instructions:
- Utilize the Excel template attached below for this assignment. Label your submission document as follows: firstnameinitialastname_Assignment4.xlsx (ex: JJohnson_Assignment4.xlsx)
- Carefully read the problem statement in order to understand what you are asked to analyze.
- Plagiarism in any form can result in an automatic zero for this assignment:
- Using another author's exact words without quotations AND without an in-text citation
- Paraphrasing by changing a couple of another author's words and claiming them to be your own.
- Turning in another student's work
- There are no late assignments accepted after the last day of the course unless (a) prior approval has been obtained from the professor or (b) you have an approved 30-day extension.
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 |