Excel Cost Estimation Project

Assume the following data (where the xxx is the last three digits of your Panther ID):

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

114,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 Panther ID is 1234567, then your maintenance costs would be $102,567 in month 1, $102,567 is month 2, $103,567, etc.

Assume that the following relationship holds:

Maintenance Costs = a + (b * Machine Hours)

REQUIRED

Estimate the values of a and b, using,

1.the High-Low Method, and

2.the Linear Regression method.

Note, to use the linear regression method, you MUST use the Microsoft Excel program. Please follow the instructions on P.331 of the textbook;DIRECTIONS FOR ADD-Ins: Data Analysis Toolpak.

Make sure to report

1.

1.the values of a and b;

2.a scatter plot of the data points, and

3.the adjusted R-square

YOUR SUBMISSION MUST BE IN EXCEL.ONE WORKBOOK, WITH A WORKSHEET FOR EACH ANALYSIS (i.e., High-Low Method and Regression Analysis respectively).The regression analysis must include the output from the Analysis Datapak.