Management
CO5: Investigate forecast performance utilizing spreadsheet techniques.
Prompt:
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.
Carefully read the problem statement in order to understand what you are asked to analyze.
ANSWER
Business Forecast
| Year | Period | 1 Tb Actual Sales | 1 Tb Forecast Sales | % Attainment At /Ft X 100 | Error At – Ft | (At – Ft)/At | Absolute Error |At – Ft2| | Absolute % Error |At – Ft2|/At X 100 |
| 1 | 1 | 800 | 1000 | 80 | -200 | -0.25 | -400 | -150 |
| 2 | 950 | 1100 | 86.36364 | -150 | -0.15789 | -350 | -136.842 | |
| 3 | 1100 | 1300 | 84.61538 | -200 | -0.18182 | -400 | -136.364 | |
| 4 | 2000 | 2200 | 90.90909 | -200 | -0.1 | -400 | -120 | |
| 5 | 2150 | 2300 | 93.47826 | -150 | -0.06977 | -450 | -120.93 | |
| 6 | 2750 | 3000 | 91.66667 | -250 | -0.09091 | -350 | -112.727 | |
| 7 | 3200 | 3500 | 91.42857 | -300 | -0.09375 | -100 | -103.125 | |
| 8 | 3300 | 3600 | 91.66667 | -300 | -0.09091 | -700 | -121.212 | |
| 9 | 3000 | 3200 | 93.75 | -200 | -0.06667 | -300 | -110 | |
| 10 | 1900 | 2200 | 86.36364 | -300 | -0.15789 | -1000 | -152.632 | |
| 11 | 2000 | 2100 | 95.2381 | -100 | -0.05 | -600 | -130 | |
| 12 | 3300 | 4200 | 78.57143 | -900 | -0.27273 | -2100 | -163.636 | |
| 2 | 1 | 900 | 1200 | 75 | -300 | -0.33333 | 900 | 0 |
| 2 | 1150 | 1300 | 88.46154 | -150 | -0.13043 | 1150 | 0 | |
| 3 | 1300 | 1500 | 86.66667 | -200 | -0.15385 | 1300 | 0 | |
| 4 | 2100 | 2400 | 87.5 | -300 | -0.14286 | 2100 | 0 | |
| 5 | 2600 | 2600 | 100 | 0 | 0 | 2600 | 0 | |
| 6 | 3050 | 3100 | 98.3871 | -50 | -0.01639 | 3050 | 0 | |
| 7 | 3350 | 3300 | 101.5152 | 50 | 0.014925 | 3350 | 0 | |
| 8 | 3850 | 4000 | 96.25 | -150 | -0.03896 | 3850 | 0 | |
| 9 | 3400 | 3300 | 103.0303 | 100 | 0.029412 | 3400 | 0 | |
| 10 | 2900 | 2900 | 100 | 0 | 0 | 2900 | 0 | |
| 11 | 2200 | 2600 | 84.61538 | -400 | -0.18182 | 2200 | 0 | |
| 12 | 5000 | 5400 | 92.59259 | -400 | -0.08 | 5000 | 0 | |
| Sum | -5050 | -2.61564 | 24650 | -1557.47 | ||||
| Mean | -210.417 | -0.10899 | 1027.083 | -64.8945 |
| a. | Mean Percentage error for the combined Periods of (Y1 + Y2) = -0.10899 | 0.10899 | ||||||
| b. | Mean absolute percentage error (MAPE) for the combined | -64.8945 | ||||||
| periods (Y1 + Y2)
= -64.8945 |
||||||||
What Conclusions can you draw regarding the sale of these two products and the forecasting accuracy? What is this forecasting method missing? Do the differences between the MPE and MAPE mean anything and if yes, what?
Accuracy is measured in mean error. From the data above, the mean error is negative (210.417), meaning the error is big hence the accuracy not attained. The MPE is negative hence the measure is a negative bias. We can say that the projection of this company was overestimated in demand while there was a month to month basis under delivery.
The big difference between MPE and MAPE indicates that the MAPE is biased meaning there is big penalty because of under achievement.
