pos show formulas & work on excel

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 4- period 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 a and B. (10 pts) beta 0.3 AN 2 Year Quarter Time Units Sold 3 2011 1 1 23 4 2 2 25 5 3 3 36 6 4. 4 31 7 2012 1 5 26 8 2 6 28 9 3 7 48 0 4. 8 36 1 2013 1 9 31 2 10 42 3 3 11 53 4 4 12 43 5 2014 1 13 6 2 14 7 3 15 3 4 16 e 2 N 4 AWN