Using excel to estimate a cost function. Instructions are clear. The deadline is November 12 (Friday) by 12 noon.
You must solve the Linear Regression Method only on Microsoft Excel.
EXCEL ASSIGNMENT
Assume the following data (where the xxx is 873):
| Month | Machine Hours (hrs.) | Maintenance Costs ($) |
| 1 | 1,330 | 102,xxx |
| 2 | 1,400 | 103,xxx |
| 3 | 1,500 | 108,xxx |
| 4 | 1,470 | 108,xxx |
| 5 | 1,620 | 116,xxx |
| 6 | 1,690 | 115,xxx |
| 7 | 1,490 | 107,xxx |
| 8 | 1,310 | 102,xxx |
| 9 | 1,450 | 106,xxx |
| 10 | 1,580 | 113,xxx |
| 11 | 1,300 | 100,xxx |
| 12 | 1,600 | 113,xxx |
| 13 | 1,650 | 113,xxx |
| 14 | 1,440 | 109,xxx |
| 15 | 1,340 | 102,xxx |
| 16 | 1,670 | 114,xxx |
| 17 | 1,480 | 106,xxx |
| 18 | 1,360 | 103,xxx |
| 19 | 1,340 | 103,xxx |
| 20 | 1,540 | 112,xxx |
For example, if your id is 1234567, then your maintenance costs would be $102,567in month 1, $103,567 is month 2, $113, 567 in months 10, 12. and 13.
Assume that the following relationship holds:
Maintenance Costs = a + (b * Machine Hours)
REQUIRED
Estimate the values of a and b, using,
- the High-Low Method, and
- the Linear Regression method.
Note, to use the linear regression method, you MUST use the Microsoft Excel program.
Make sure to report
- the values of a and b;
- a scatter plot of the data points, and
- the adjusted R-square