Homework SourseCascade Manufacturing produces a compressor motor for air co
Cascade Manufacturing produces a compressor motor for air conditioner units. The company manufactures the motors to order by modifying the base model to meet the specifications requested by the customer. The motors are produced in a batch environment with the batch size equal to the number ordered. Marlene Jacobs is the plant manager for Cascade Manufacturing and she would like to know if there is any relationship between order size and cost of production. She had her quality control group randomly collect a sample of 50 customer orders and record the size of the order and the total production cost. The data collected are in the file \"Cascade Manufacturing\" and that file is attached. If there is a significant relationship between the size of the order and the production cost, Ms. Jacobs would like to be able to estimate production costs based on order size.
1. Use the sample data to develop a regression model with the order size as the independent variable and the cost as the dependent variable.
2. Test the significance of the overall regression model using a significance level of 0.02.
3. Cascade Manufacturing has just received an order from their largest customer for 60 motors.
a. Use the regression model from part 1 to provide a 90% confidence interval for the average cost of an order of 30 motors.
b. Again, using the regression model from part 1, produce a 90% prediction interval for the cost of this particular order.
4. This is the most important part: Write a memorandum to Ms. Jacobs.
a. Explain the coefficients of the regression model.
b. Explain parts 3a and 3b clearly defining the differences in the predictions.
I need to have the work done in excel, please state which data analysis tool you chose. If you could attach the excel file, that would be even better.
| Cascade Manufacturing | |
| Compressor Motors | |
| Order Size | Total Cost |
| 28 | $ 7,531 |
| 27 | $ 6,329 |
| 34 | $ 8,413 |
| 30 | $ 7,793 |
| 19 | $ 5,360 |
| 21 | $ 4,838 |
| 11 | $ 2,551 |
| 17 | $ 3,899 |
| 33 | $ 8,326 |
| 23 | $ 5,465 |
| 7 | $ 2,283 |
| 23 | $ 5,413 |
| 18 | $ 4,238 |
| 26 | $ 6,911 |
| 20 | $ 6,315 |
| 29 | $ 8,243 |
| 10 | $ 2,866 |
| 22 | $ 6,775 |
| 14 | $ 4,289 |
| 6 | $ 1,475 |
| 15 | $ 3,590 |
| 35 | $ 9,439 |
| 23 | $ 6,760 |
| 18 | $ 5,170 |
| 31 | $ 7,780 |
| 21 | $ 4,896 |
| 35 | $ 8,816 |
| 29 | $ 8,116 |
| 21 | $ 6,212 |
| 24 | $ 5,551 |
| 27 | $ 7,080 |
| 36 | $ 9,826 |
| 24 | $ 6,129 |
| 20 | $ 5,094 |
| 13 | $ 3,568 |
| 14 | $ 3,738 |
| 19 | $ 5,332 |
| 14 | $ 3,286 |
| 28 | $ 6,664 |
| 24 | $ 5,990 |
| 27 | $ 7,093 |
| 16 | $ 3,975 |
| 36 | $ 9,046 |
| 20 | $ 4,906 |
| 19 | $ 5,324 |
| 7 | $ 2,734 |
| 32 | $ 8,138 |
| 21 | $ 5,376 |
| 29 | $ 7,763 |
| 23 | $ 5,964 |
Solution
x = order size and y = production cost
y= a+bx
From excel data analysis , y = 283810159+0.003774945X
| SUMMARY OUTPUT | ||||||||||||||
| Regression Statistics | ||||||||||||||
| Multiple R | 0.971941237 | |||||||||||||
| R Square | 0.944669767 | |||||||||||||
| Adjusted R Square | 0.943517054 | |||||||||||||
| Standard Error | 1.855816096 | |||||||||||||
| Observations | 50 | |||||||||||||
| ANOVA | ||||||||||||||
| df | SS | MS | F | Significance F | ||||||||||
| Regression | 1 | 2822.465438 | 2822.465 | 819.5185 | 7.98013E-32 | |||||||||
| Residual | 48 | 165.3145623 | 3.444053 | |||||||||||
| Total | 49 | 2987.78 | ||||||||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% |
| ||||||||
| Intercept | 0.283810159 | 0.815259159 | 0.348123 | 0.72927 | -1.355378213 | 1.922998531 |
| |||||||
| X Variable 1 | 0.003774945 | 0.000131866 | 28.62723 | 7.98E-32 | 0.003509812 | 0.004040079 |
|
