QUESTION 4 Regression Analysis and Cost Estimation
20 marks - 4 for (a), 12 for (b), 4 for (c).
Quick Copy has recorded the following Maintenance Costs and two possible cost drivers over the last year.
Month Number of photocopies Hours of maintenance service Maintenance costs
January 80,000 525 4,710
February 68,000 505 4,310
March 69,000 310 2,990
April 64,000 495 4,200
May 90,000 315 3,000
June 85,000 485 4,215
July 55,000 315 2,950
August 85,000 405 3,680
September 62,000 475 4,100
October 82,000 345 3,250
November 60,000 350 3,260
December 70,000 335 3,015
a) Using the High-Low method of cost estimation and Number of Photocopies as the cost driver, what would be the resulting cost equation for Maintenance Costs?
b) Using Excel, perform three regression analyses to regress Maintenance Costs against Number of Photocopies, then against number of Hours of Maintenance Service, then against both of them simultaneously. Paste your results into Word. State the cost equation from each. Prepare a report to management including an analysis and comment on the results of each regression with a reasoned recommendation of the best cost driver to use in the future.
c) If the simple regression using Number of Photocopies were adopted, what would be the predicted overhead in a month when there were 85,000 photocopies and 500 hours of maintenance service?