Reference no: EM132695040

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:

Question 1: Using Microsoft Excel, Show a scatter gram by plotting jobs and repair costs on a graph.

Question 2: Use Excel functions to determine the fixed and variable cost components of repair jobs. Clearly state and label these in your answer.

Question 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.

Question 4: Discuss the difference between the two answers from parts b and c. Which method is more reliable? Why?

Question 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 part d as more reliable.