I need help with I-M
Note: Data for these problems are in the Module 2 Assisted Assignments Data file - there is a tab for each problem All answers should be entered using two decimal places unless otherwise specified. If both decimal places are zeros-then just enter the integer value. Percentages should be entered without'%" sign 3.45% should be entered as 3.45. M2 A1. The last 20 years of annual sales for a small business is shown in Worksheet P1. The data is shown in $1000s so Year 1 is $283,000. I will ask for all answers in terms of $1000s so just enter the data in your worksheets as it is shown in the file. (Note: I have made some modifications to this problem since creating the supporting video - so there is some extra information in the video that you are not responsible for at this time. Specifically, I show you how to use Solver to optimize the answers you are not responsible for using Solver at this point. Also although it shows a different problem number - it is the solution for this problem) a) Plot the data - does the data appear stationary? b) Compute the forecasts using the two-year moving average. What is the MSE? c) Compute the forecast for Year 21 using the two-year moving average. d) Compute the forecasts using the four-year moving average. What is the MAD? e) Compute the forecast for Year 21 using the four-year moving average. f) Do the two and four-year moving averages tend to underestimate or overestimate the actual data? Why? g) Compute the forecasts using the three-year weighted moving average using weights of 0.6, 0.3, and 0.1. What is the MAPE? h) Compute the forecast for Year 21 using the three-year weighted moving average using weights of 0.6, 0.3, and 0.1. i) Compute the forecasts using exponential smoothing with a smoothing constant of 0.2 anda forecast for Year 1 of 283. What is the MSE? j) Compute the forecast for Year 21 using exponential smoothing with a smoothing constant of 0.2 and a forecast for Year 1 of 283. k) Compute the forecasts using linear regression using Excel's slope and intercept function. What is the MAD? 1) Compute the forecast for Year 21 using linear regression. m) Comparing one of the error measurements (MAD, MSE, or MAPE) which is the best forecasting method? Which is the worst forecasting method? x Year A1 Year Sales (in $1000s) 283 1 288 336 388 406 412 416 435 428 9 435 10 462 452 12 474 13 4 476 14 497 15 487 16 523 17 528 18 532 19 552 20 Age of SquareAvg Heating Avg Outside Attic Insulation Furnace (inches) Cost (years)Footage Temp 198 1900 29 355 2800 291 2500 10 230 2000 121 1300 250 2100 36 360 2400 28 164 2300 36 42 1300 59 90 1500 64 271 2300 19 96 1400 57 187 1900 39 235 2100 9 25 138 1800 28 71 1200 53 206 2000 47 398 2900 14 20 319 2600 39 72 1500 60 Note: Data for these problems are in the Module 2 Assisted Assignments Data file - there is a tab for each problem All answers should be entered using two decimal places unless otherwise specified. If both decimal places are zeros-then just enter the integer value. Percentages should be entered without'%" sign 3.45% should be entered as 3.45. M2 A1. The last 20 years of annual sales for a small business is shown in Worksheet P1. The data is shown in $1000s so Year 1 is $283,000. I will ask for all answers in terms of $1000s so just enter the data in your worksheets as it is shown in the file. (Note: I have made some modifications to this problem since creating the supporting video - so there is some extra information in the video that you are not responsible for at this time. Specifically, I show you how to use Solver to optimize the answers you are not responsible for using Solver at this point. Also although it shows a different problem number - it is the solution for this problem) a) Plot the data - does the data appear stationary? b) Compute the forecasts using the two-year moving average. What is the MSE? c) Compute the forecast for Year 21 using the two-year moving average. d) Compute the forecasts using the four-year moving average. What is the MAD? e) Compute the forecast for Year 21 using the four-year moving average. f) Do the two and four-year moving averages tend to underestimate or overestimate the actual data? Why? g) Compute the forecasts using the three-year weighted moving average using weights of 0.6, 0.3, and 0.1. What is the MAPE? h) Compute the forecast for Year 21 using the three-year weighted moving average using weights of 0.6, 0.3, and 0.1. i) Compute the forecasts using exponential smoothing with a smoothing constant of 0.2 anda forecast for Year 1 of 283. What is the MSE? j) Compute the forecast for Year 21 using exponential smoothing with a smoothing constant of 0.2 and a forecast for Year 1 of 283. k) Compute the forecasts using linear regression using Excel's slope and intercept function. What is the MAD? 1) Compute the forecast for Year 21 using linear regression. m) Comparing one of the error measurements (MAD, MSE, or MAPE) which is the best forecasting method? Which is the worst forecasting method? x Year A1 Year Sales (in $1000s) 283 1 288 336 388 406 412 416 435 428 9 435 10 462 452 12 474 13 4 476 14 497 15 487 16 523 17 528 18 532 19 552 20 Age of SquareAvg Heating Avg Outside Attic Insulation Furnace (inches) Cost (years)Footage Temp 198 1900 29 355 2800 291 2500 10 230 2000 121 1300 250 2100 36 360 2400 28 164 2300 36 42 1300 59 90 1500 64 271 2300 19 96 1400 57 187 1900 39 235 2100 9 25 138 1800 28 71 1200 53 206 2000 47 398 2900 14 20 319 2600 39 72 1500 60
