Anderson Engineering provides consulting services and custom installations of proprietary industrial waste water 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 $22,000

February 45 18,000

March 40 17,600

April 50 20,000

May 65 24,000

June 60 22,400

July 35 16,000

August 30 12,800

September 25 13,600

October 20 9,600

November 25 14,100

December 30 13,000

Required:

1. Using Microsoft Excel, Show a scatter gram by plotting jobs and repair costs on a graph.
2. Use Excel functions to determine the fixed and variable cost components of repair jobs. Clearly state and label these in your answer.
3. Use the high-low method to determine the fixed and variable cost components of repair jobs. Clearly state and label these in your answer.
4. Discuss the difference between the two answers from partsbandc. Which method is more reliable? Why?
5. 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 partdas more reliable.