Question 1

Business Sales Data sheet in the attached Excel book provides the monthly sales report of a small business. By using this data set, please answer the following questions.

1. Prepare a smooth line graph of the data. Describe the behavior of the data? Is this a stationary or non-stationary data?
2. Create a two-months moving average forecast model. Build your error table, describe your forecasts error by using MAPE (Mean Absolute Percent Error). What is the forecasted sales amount (in dollars) for the month 41?
3. Create a three-month moving average forecast model. Build your error table, describe your models error by using MAPE. What is the forecasted sales amount (in dollars) for the month 41?
4. Create a four-month moving average forecast model. Build your error table, describe your models error by using MAPE. What is the forecasted sales amount (in dollars) for the month 41?
5. Prepare a smooth line graph; compare the actual and forecasted sales.
6. Create a three-period weighted moving average forecast model. Build your error table. Then, by using Solver, determine the optimized weights that minimize the MAPE.
7. What are the optimal values for the weights?
8. Prepare a smooth line graph comparing the actual values and weighted moving average forecast values. What is the forecasted sales amount (in dollars) for the month 41?
9. Create an exponential smoothing model. Build your error table. By using Solver calculate the optimized value of the exponential smoothing factor (alpha) that minimizes MAPE.
10. Prepare a smooth line graph; compare the actual values and exponential smoothing model forecast values. What is the forecasted sales amount (in dollars) for the month 41?
11. Which model would you use to forecast the future of this business? Please discuss your answer by comparing the MAPE values of the models.

If you can't upload excel email to my google mail: studentneedinghelpagain@

- 40 Months Sales Report sum Month 1 2 3 1 2 3 4 5 6 7 8 9 10 4 5 6 7 8 11 12 13 9 10 11 12 14 15 16 13 14 15 16 17 18 19 20 21 22 23 24 25 26 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 Sales $ 283.00 S 288.00 $ 336.00 $ 388.00 $ 406.00 $ 412.00 $ 416.00 $ 435.00 $ 428.00 $ 435.00 $ 462.00 $ 452.00 $ 474.00 $ 476.00 $ 497.00 $ 487.00 $ 523.00 $ 528.00 S 532.00 $ 552.00 $ 533.00 S 569.00 $ 530.00 $ 570.00 $ 540.00 S 585.00 $ 590.00 $ 609.00 S 617.00 $ 634.00 S 620.00 $ 645.00 $ 628.00 $ 652.00 $ 669.00 $ 684.00 $ 674.00 $ 693.00 S 675.00 $ 701.00 27 28 29 30 31 32 33 34 35 36 37 38 39 40 - 40 Months Sales Report sum Month 1 2 3 1 2 3 4 5 6 7 8 9 10 4 5 6 7 8 11 12 13 9 10 11 12 14 15 16 13 14 15 16 17 18 19 20 21 22 23 24 25 26 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 Sales $ 283.00 S 288.00 $ 336.00 $ 388.00 $ 406.00 $ 412.00 $ 416.00 $ 435.00 $ 428.00 $ 435.00 $ 462.00 $ 452.00 $ 474.00 $ 476.00 $ 497.00 $ 487.00 $ 523.00 $ 528.00 S 532.00 $ 552.00 $ 533.00 S 569.00 $ 530.00 $ 570.00 $ 540.00 S 585.00 $ 590.00 $ 609.00 S 617.00 $ 634.00 S 620.00 $ 645.00 $ 628.00 $ 652.00 $ 669.00 $ 684.00 $ 674.00 $ 693.00 S 675.00 $ 701.00 27 28 29 30 31 32 33 34 35 36 37 38 39 40