problem 4.13, please show to put problem in Excel

RODUCTION TO OPERATIONS MANAGEL a) Use a 2-month moving average on all the data and plot the averages and the prices. b) Use a 3-month moving average and add the 3-month plot to the graph created in part(a) c) Which is better (using the mean absolute deviation the 2-month average or the 3-month average? d) Compute the forecasts for sach month using exponential smoothing, with an initial forecast for January of St. Use a - 1. then a = 3. and finally a - 5. Using MAD, which a is the best? PX .. 4.10 Data collected on the yearly registrations for a Sex Sigma seminar at the Quality College are shown in the following table: 6 YEAR REGISTRATIONS (0) 1 4 2 6 3 4 4 5 5 10 7 7 8 9 9 10 11 12 14 15 smoothing costa recast for you a) Develop a 3-year moving average to forecast registrations from year 4 to year 12 b) Estimate demand again for years 4 to 12 with a 3-year weighted moving average in which registrations in the most recent year are given in weight of 2, and registrations in the other 2 years are each given a weight of l. c) Graph the original data and the two forecasts. Which of the two forecasting methods seems better PX . 4.11 Use exponential smoothing with a smoothing con- stant of 0.3 to forecast the registrations at the seminar given in Problem 4.10. To begin the procedure, assume that the forecast for year I was 5,000 people signing up a) What is the MAD? PX b) What is the MSE? .. 4.12 Consider the following actual and forecast demand levels for Big Mac hamburgers at a local McDonald's restaurant: a) Use exponential smoothing list with a smoothing 6 and then with one of.9. to develop forecasts through 6. bi se Lugar moving average to forecast demand in and in years s Use the trend-projection method to forecast demandin through 6. With MAD as the criterion, which of the four for methods is best PX 14 Following are two weekly forecasts made by to ferent methods for the number of gallons of gasoline, in the sands demanded at a local gasoline station. Also show actual demand levels, in thousands of gallons WEEK FORECASTS METHOO 1 METHOD 090 080 1.05 120 0.95 1.20 1.11 ACTUAL DEMAND 0.70 1.00 100 1.00 090 DAY ACTUAL DEMAND FORECAST DEMAND Monday 88 Tuesday Wednesday Thursday 48 80 Friday The forecast for Monday was derived by observing Monday's demand level and setting Monday's forecast level equal to this demand level. Subsequent forecasts were derived by using expo nential smoothing with a smoothing constant of 0.25. Using this exponential smoothing method, what is the forecast for Big Mac demand for Friday? PX ... 4.13 As you can see in the following table, demand for heart transplant surgery at Washington General Hospital has increased steadily in the past few years: What are the MAD and MSE for each method? 4.15 Refer to Solved Problem 4.1 on page 144 a) Use a 3-year moving average to forecast the sales of Volkswagen Beetles in Nevada through year 6. b) What is the MADPX c) What is the MSE? .4.16 Refer to Solved Problem 4.1 on page 144. a) Usin a) Using the trend projection (regression) method, develop a forecast for the sales of Volkswagen Beetles in Nevada through year 6. b) What is the MAD? PX c) What is the MSE? . 4.17 Refer to Solved Problem 4.1 on page 144. Using smoothing constants of 6 and 9, develop forecasts for the sales of VW Beetles. What effect did the smoothing constant have the forecast? Use MAD to determine which of the three smolle ing constants (13..6, or 9) gives the most accurate forecast ... 4.18 Consider the following actual (4.) and forecast demand levels for a commercial multiline telephone at O : 4 5 6 YEAR 2 50 3 52 45 56 HEART TRANSPLANTS The director of medical services predicted 6 years ago that demand in year I would be 41 surgeries