MGMT 3115 Assignment 2 - Time Series Forecasting and Queuing Theory Please show all of your answers in the worksheet corresponding to each question. 1. Moving Average Model (please review spreadsheet model of Forecast 1.xlsx.) Given the annual sales (in $1000s) for a small business (See 1st worksheet in Assignment 2.xlsx), answer the following questions. a) Please use 2-period Moving Average method to forecast the sales in Period 21. Show your result in the spreadsheet. (10 pts) b) Please calculate the MSE by using this method. (5 pts) c) Please try 4-period Moving Average method to forecast the sale in Period 21. Calculate the MSE by using this method. Comparing with the 2-period Moving Average method, which one is better, 2-period Moving Average method or 4period Moving Average method? (5 pts) 2. Exponential Smoothing Model (please review spreadsheet model of Forecast 2.xlsx.) Given the annual sales (in $1000s) for a small business (See 2nd worksheet in Assignment 2.xlsx), answer the following questions. a) Please use Exponential Smoothing method to forecast the sales in Period 21. Show your result in the spreadsheet. Notice that you should use Solver to determine the optimal value of Alpha, where the optimal Alpha minimizes the MSE. (15 pts) b) What is the optimal value of Alpha? (5 pts) c) What the corresponding MSE using this method? (5 pts) 3. Stationary data with additive seasonal effects. (Please review spreadsheet model of Forecast 3.xlsx.) You are given the quarterly data on the number of four-wheel drive, sport utility vehicles sold by a local car dealer during the past three years (See 3rd worksheet in Assignment 2.xlsx). Answer the following questions. a) Use the additive seasonal technique for stationary data to model the data. Use Solver to determine the optimal values of and . (10 pts) b) What is the optimal value of and . (10 pts) c) What are the forecasts for each quarter in 2014 using this technique? (5 pts) 4. Linear trend model with additive seasonal indices. (Please review spreadsheet model of Forecast 5.xlsx.) You are given the quarterly data on the number of four-wheel drive, sport utility vehicles sold by a local car dealer during the past three years (See 4th worksheet in Assignment 2.xlsx). Answer the following questions. a) Use linear regression model to fit a linear trend model to the data set. Show the linear trend value from the beginning to the end of 2014. (10 pts) b) Calculate the ADDITIVE seasonal indices given =0.3. (5) c) Using similar method in with Model 5 in the Lecture, what are the forecasts for each quarter in 2014 using this technique? (5 pts) d) What is the MSE by using this method (linear trend with additive seasonal indices)? (5 pts) Que 1 Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Sales 283 288 336 388 406 412 416 435 428 435 462 452 474 476 497 487 523 528 532 552 Page 1 Que 2 Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Sales 283 288 336 388 406 412 416 435 428 435 462 452 474 476 497 487 523 528 532 552 Page 2 Year Quarter Time Units Sold 2011 1 1 23 2 2 25 3 3 36 4 4 31 2012 1 5 26 2 6 28 3 7 48 4 8 36 2013 1 9 31 2 10 42 3 11 53 4 12 43 2014 1 13 -2 14 -3 15 -4 16 -- Year Quarter Time Units Sold 2011 1 1 23 2 2 25 3 3 36 4 4 31 2012 1 5 26 2 6 28 3 7 48 4 8 36 2013 1 9 31 2 10 42 3 11 53 4 12 43 2014 1 13 -2 14 -3 15 -4 16 -- beta 0.3