solution

mportant: Re. the quantitative problems, do all calculations in Excel. I’d like to see your work. Just one number in a cell as an answer without the formula or calculations behind it is not sufficient, you will not get points for that. 16 Home Data Insert Draw Formulas BI U abe Review View A .. Calibri (Body) 14 AB EX 123 fx A B ? D E F 1 The table on the right shows widget sales by the time of the day. 25 pts. 2 3 Month 1 a. Visualize the time series. Is it stationary or non-stationary? Explain. 3 pts 4 5 6 Month 2 7 b. Use the trend function to forecast morning, afternoon, and evening sales for Month 6. State the trend model. Measure the accuracy of the forecast and explain the meaning of the numerical measure you used. 9 pts 8 9 Month 3 Widget sales (1000) 10 8 15 16 14 19 18 14 21 25 20 28 50 46 54 10 11 morning afternoon evening morning afternoon evening morning afternoon evening morning afternoon evening morning afternoon evening morning afternoon evening c. Use trend + seasonal variations method to forecast morning, afternoon, and evening sales for Month 6. State the model, measure its accuracy, and explain the meaning of the accuracy measure you got. 13 pts 12 Month 4 13 14 15 25 pts. Month 5 16 17 18 Month 6 19 20 21 22 23 24 25 Home Insert Draw Formulas Data Review View Shape Calibri (Body) BI Uebe ABC 123 x . ? ? D 1 Year 2010 2011 2 3 4 5 6 7 8 9 10 11 12 13 14 Quarter 1 2 3 4 1 2 3 4 1 2 3 4 Sales 82.5 813 81.3 79 76.6 78 78.4 78 788 78.7 78.4 80 80.7 80.7 80.8 2012 The data on the left represent 15 quarters of sales data. a. Visualize the data set. Is this a stationary or non-stationary time series? Why? 3 pts. b. Compute three-quarter simple and weighted moving averages for this time series. Assume the following weights: newest observation (last period): 0.6; next to last period: 0.3, second to last period: 0.1. Find the forecasts for the 4th quarter of 2013 and for the ist quarter of 2014. Which method provides the better forecast?44 pts c. For the same two periods develop an exponential smoothing forecast. Use smoothing constants alpha = 0.4. 3 pts d. Based on b. and c. which method-moving averages or exponential smoothing – provides the better forecast? Explain. 2 pts 2013 1 2 15 3 4 2014 1 16 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 44 45 46 47 48 Intro LP Forecast 1 Forecast 2 Essay + Home Insert Draw Formulas Data Review View Calibri (Body) 11 ? ? ? I U abc ?. . ? fx ? B C D E F G 1 2 3 A firm produces 3 types of cardboards (A, B, and C) and uses 3 types of processes (I, II, and III) to produce them. The table below shows the production hours per unit of the production processes and other relevant data for the problem. 5 4 5 6 a. Derive the LP model for finding the optimal output mix. 5 pts. 7 b. Implement and solve the model in Excel. State the solution. 12 pts 8 9 10 11 12 13 14 15 16 c. Generate the Sensitivity Report and use it to derive the feasible ranges for the objective function coefficients. Explain the meaning one of the feasible ranges. 8 pts 11 Cardboard type A N 17 18 Operations processes 1 2 4 5 5 2 10 3 2 900 400 600 Profit/unit 40 30 B 5 3 19 C 20 20 Max time available