The management of Wheeler Company has decided to develop cost formulas for its major overhead activities. Wheeler uses a highly automated manufacturing process, and power costs are a significant manufacturing cost. Cost analysts have decided that power costs are mixed. The costs must be broken into their fixed and variable elements so that the cost behavior of the power usage activity can be properly described. Machine hours have been selected as the activity driver for power costs. The following data for the past eight quarters have been collected: Quarter Machine Hours Power cost
1 20000 26000
2 25000 38000
3 30000 42500
4 22000 37000
5 21000 34000
6 18000 29000
7 24000 36000
8 28000 40000
1. Prepare a scattergraph by plotting power costs against machine hours. Does the scattergraph show a near relationship between machine hours and power cost?
2. Using the high and low points (i. e., the high-low method), compute a power cost formula.
3. Use the method of least squares to compute a power cost formula. Evaluate the coefficient of determination.

Explanation:

1-a See the attached photo for the a scattergraph.

1-b. Yes, the scattergraph show a near relationship between machine hours and power cost.

2. The power cost formula using the high and low points is as follows:

Total power cost = -7000 + (1.65 * Machine hours)

3-a. The power cost formula using the method of least squares is as follows:

Total power cost = 6454 + (1.21 * Machine hours)

3-b. R² = Coefficient of determination = 0.8969, or 89.69%

Explanation

1-a. Prepare a scattergraph by plotting power costs against machine hours.

Note: See the attached photo for the a scattergraph by plotting power costs against machine hours.

1-b. Does the scattergraph show a near relationship between machine hours and power cost?

Note that Scattergraph is interpreted by looking by looking for trends in the data as there is movement from left to right.

From the attached a scattergraph, it can be observed that there is an uphill pattern as there is a movement from left to right. This indicates that there a near positive relationship between power costs against machine hours.

Therefore, the scattergraph show a near relationship between machine hours and power cost.

2. Using the high and low points (i.e., the high-low method), compute a power cost formula.

2-a. Calculation of variable cost elements

Variable cost per machine hour = (Highest Power Costs - Lowest Power Costs) / (Highest machine hours – Lowest machine hours) = (42500 - 26000) / (30000 - 20000) = 1.65 per hour

2-b. Calculation of fixed cost elements

Total power cost = Total Fixed Cost + Total Variable Cost (1)

Where;

Total Variable Cost = Variable cost per machine hour * Machine hours ……….. (2)

Substitute equation (2) into equation (1), we have:

Total power cost = Total Fixed Cost + (Variable cost per machine hour * Machine hours) ……………………. (3)

Using highest machine hours and substitute relevant values into equation (3), we have:

42500 = Total Fixed Cost + (1.65 * 3000)

42500 = Total Fixed Cost + 49,500

Total Fixed Cost = 42500 - 49,500

Total Fixed Cost = -7000

2-c Computation of a power cost formula

Substituting Variable cost per machine hour = 1.65 and Total Fixed Cost = -7000 into equation (3), we can compute the power cost formula as follows:

Total power cost = -7000 + (1.65 * Machine hours) ………………. (4)

Equation is the power cost formula.

3. Use the method of least squares to compute a power cost formula. Evaluate the coefficient of determination.

Note: See the attached excel file for the calculations of Total of Machine Hours (x), Power cost (y), xy, x^2, and y^2.

Since Σ = Total of or summation of, we can therefore obtain the following from the attached excel file:

Σx = 190,800    

Σy = 282,500

Σxy = 6,878,400,000

Σx² = 4,666,540,000

Σy² = 10,188,250,000

N = Number of quarters = 8

3-a. Use the method of least squares to compute a power cost formula

Step 1: Calculation of variable cost per rental return

To calculate the variable power cost per machine hour, the following formula is used:

Variable power cost per machine hour = (NΣxy − ΣxΣy) /((NΣx²) − (Σx)²) ……………… (5)

= (Σxy – (1/8)ΣxΣy) /((Σx²) – (1/8)(Σx)²)

=(6,878,400,000 – ((1/8) * 190,800 * 282,500)) / (4,666,540,000 – ((1/8) * 190,800²))

Substituting the relevant values into equation (5), we have:

Variable cost per rental return = ((8 * 6,878,400,000) - (190,800 * 282,500)) /((8 * 4,666,540,000) - 190,800²)

Variable power cost per machine hour = 1.21

Step 2: Calculation of quarterly fixed power cost

This can be calculated using the following formula:

Fixed Cost per quarter = {Σy - (Variable power cost per machine hour * Σx) / N (6)

Substituting the relevant values into equation (6), we have:

Fixed Cost per quarter = (282,500 - (1.21 * 190,800)) / 8

Fixed Cost per quarter = 6,454

Step 3: Computation of the power cost formula

Substituting Variable cost per machine hour = 1.21 and Total Fixed Cost = 6,454 into equation (3) in part 2 above, we can compute the power cost formula as follows:

Total power cost = 6454 + (1.21 * Machine hours) ………………. (4)

Equation (4) is the power cost formula.

3-b. Evaluate the coefficient of determination.

This can be evaluated using the following formula:

R² = Coefficient of determination = (NΣxy – ΣxΣy) / ((NΣx² - (Σx)²) * (NΣy² - (Σy)²))^0.5 ……….. (5)

Substituting the relevant values into equation (5) we have:

R² = ((8 * 6,878,400,000) – (190,800 * 282,500)) / (((8 * 4,666,540,000) – 190.800²) * ((8 * 10,188,250,000) – 282,500²))^0.5

R² = 0.8969, or 89.69%