Engineering provides consulting services and custom installation of proprietary industrial wastewater treatment facilities.  They also operate a repair shop for repairs and maintenance of the treatment products.  The repair facility costs include salaries, insurance and depreciation, and materials and parts.  The supervisor would like to develop a model that predicts repair costs.  Data for the past year is below:

 

Month	Jobs	Repair costs
January	55	$24,000
February	45	20,000
March	40	19,600
April	50	22,000
May	65	27,000
June	60	25,400
July	35	19,00 0
August	30	15,800
September	25	15,600
October	20	12,600
November	25	17,100
December	30	16,000

 

A. Using Microsoft Excel, prepare a scattergram by plotting jobs and repair costs on a graph.       

B. Use Excel functions to determine the fixed and variable cost components of repair jobs.  Clearly state and label these in your answer.  Remember this is an amount of money.

C. Use the Hi-Low method to determine the fixed and variable cost components of repair jobs.  Clearly state and label these in your answer.

D. Discuss the difference between the two answers from parts B and C.  

Which method is more reliable?  Why?

E. The supervisor expects to receive 70 repair jobs next month.  What are the predicted costs?  Provide an amount for fixed, variable, and total costs.  Use the formula that you identified in part D as more reliable.