Question

PROBLEM 3–14 High–Low Method; Scattergram Analysis [LO2]
Davidson Engine Repairs provides maintenance and repair services for small engines such as those found in lawn mowers, power saws, and snow throwers. The repair shop costs include the salaries for the mechanics, insurance and depreciation on the repair equipment, materials and parts used for the jobs, and utilities. The repair shop manager would like to develop a model for predicting repair costs and over the past 10 months has carefully recorded the total repair

Chapter 3 Cost Behaviour: Analysis and Use 91costs incurred, along with the number of maintenance and repair jobs completed each month.

The data are shown below:

Month Jobs Repair Costs

jobs. repair cost

January . . . 220. 22,000

February. . . 180. 18,000

March . . . . . 160. 17,600

April . . . . . . . 200. 20,000

May . . . . . . . 260. 24,000

June . . . . . . 240. 22,400

July . . . . . . . 140. 16,000

August. . . . . 120. 12,800

September....100. 13,600

October . . . 80. 9,600

Required:

Using Microsoft Excel, prepare a scattergram by plotting jobs performed and repair costs on a graph. Fit a straight line to the plotted points using the “add trendline” feature in Microsoft Excel. Do repair costs appear to be related to the number of jobs completed in a month? Explain.

Using the high–low method, estimate a cost formula for repair costs. Express the formula in the form Y = a + bX.

Now assume the manager wants to predict costs for a month where 600 jobs will be com- pleted. Should you use the formula developed above to predict costs for a 600-job month? Why or why not?

Answer 1

Q 1 .  Using Microsoft Excel, prepare a scattergram by plotting jobs performed and repair costs on a graph. Fit a straight line to the plotted points using the “add trendline” feature in Microsoft Excel.

Scatter Diagram and Fitted equation is attached below. To draw the scatter select apprpriate graph from Insert-graph.

Q. Do repair costs appear to be related to the number of jobs completed in a month? Explain.

Answer : Yes Repair costs appears to be positively rel;ated to the number of Jobs completed.

The trend indicates a positive relationship. data points are also seen closer to the fitted line Also if you take Rsquare value which is 0.9687 indicates a very good fit.

Question :  Using the high–low method, estimate a cost formula for repair costs. Express the formula in the form Y = a + bX.

at the highest activity (May) : x2 = 260 , y2 = 24000

at the lowest activity (October) x1= 80 y1 =9600

Hence variable cost per unit = (24000-9600)/(260-80)= 14400/180=80 per unit

Total fixed cost : = 24000- 80*260 =24000-20800=3200

Hence Estimate of Cost Formula : Y= 3200 +80X

Question : Now assume the manager wants to predict costs for a month where 600 jobs will be com- pleted. Should you use the formula developed above to predict costs for a 600-job month? Why or why not?

Formula developed above should not be used to predict costs for 600 job month . as analayis is based on highest value of 260 jobs , hence it is not certain that same relationship will hold true for values beyond 260. Its always risky to extrapolate based on regression results.