Accounting EXCEL ASSIGNMENT

EXCEL ASSIGNMENT

 

Assume the following data (where the xxx is the last three digits of your panther id):

 

MonthMachine Hours (hrs.)Maintenance Costs ($)
11,330102,xxx
21,400103,xxx
31,250103,xxx
41,470108,xxx
51,620116,xxx
61,680115,xxx
71,490107,xxx
81,280101,xxx
91,450106,xxx
101,580113,xxx
111,300100,xxx
121,600113,xxx
131,650113,xxx
141,440109,xxx
151,350102,xxx
161,670114,xxx
171,480106,xxx
181,360103,xxx
191,340103,xxx
201,540112,xxx
211,320102,xxx
221,410104,xxx
231,270101,xxx

 

For example, if your panther id is 1234567, then your maintenance costs would be $102,567 in 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,

  1. the High-Low Method, and
  2. the Linear Regression method.(for help try “Linear Regression in Excel” on YouTube)

Note, to use the linear regression method, you MUST use the Microsoft Excel program.

Make sure to report

  1. the values of a and b;
  2. a scatter plot of the data points, and
  3. the R-square or the adjusted R-square
  4. Which of the above two methods is more appropriate? Why ? (3 sentences maximum)

Last Updated on February 10, 2019