There are 4 problems in Excel needed to be solved with formulas for Subject Production and Quantitative methods
Problem 1
| The owner of Wine Sales & Service wants to forecast wine demand. Demand for the preceding four years is shown in the following table. Forecast demand for next year using: | ||||||||
| (i) | Exponential smoothing with an alpha of 0.4 (Assume Yr. 1 forecast = 805). | |||||||
| (ii) | Calculate MAD | |||||||
| (iii) | Use solver to determine the best alpha to minimize the error (MAD) | |||||||
| Year | Demand(000 cases) | |||||||
| 1 | 809 | |||||||
| 2 | 810 | |||||||
| 3 | 808 | |||||||
| 4 | 812 | |||||||
Problem 2
| 1. The operations manager of a musical instrument distributor feels that demand for bass drums may be related | |
| to the number of television appearances by the popular rock group Green Shades during the preceding | |
| month. The manager has collected the data shown in the following table: | |
| Demand for Bass Drums | Green Shades TV Appearances |
| 3 | 3 |
| 6 | 4 |
| 7 | 7 |
| 5 | 6 |
| 10 | 8 |
| 8 | 5 |
| a). Use the least squares regression method to derive a forecasting equation. (Hint: you may use Data Analysis – Regression to get the slope and y-intercept) | |
| b). What is your estimate for bass drum sales if the Green Shades performed on TV nine times last month? |
Problem 3
| Using the appropriate control chart, determine two – sigma control limits for each case: | |
| (i) An inspector found an average of 3.9 scratches in the exterior paint of each of the automobiles being prepared for shipment to dealers. | |
| (ii) Before shipping lawn mowers to dealers, an inspector attempts to start each mower and notes any that do not start on the first try. | |
| The lot size is 100 mowers, and an average of 4 did not start (4 percent). | |
Problem 4
| John and Jane Doe make cherry crates for resale to local growers. |
| They and their four employees invest 50 hours per day making 150 crates. |
| a). What is their productivity? |
| b). They have discussed reassigning work so the flow through the shop is smoother. |
| If they are correct and can do the necessary training, they think they can increase |
| crate production to 155 per day. What would be their new productivity? |
| c). What would be their increase in productivity? |
