Question

Pane of Glass Inc. produces custom glasswork for high-end homes and buildings. The following cost data relate to the company’s labour costs.

	Units Produced	Labour Cost
July	400	$15,000
August	300	13,000
September	320	13,200
October	350	13,800
November	420	16,000
December	410	14,800

Required:

1. Using the scattergraph method, estimate the cost formula.
2. Using the least squares regression method, estimate the cost formula.
3. Are there any factors other than the number of units produced that may contribute to a variation in labour cost?

Answer 1

Solution:-

(a) Using excel, tabulate the given data and plot it to get a scatter graph. Click for the linear trendline to get the regression line. Tick the box beside the Display Equation on chart under drop-down menu of Format Trendline to get the equation for the cost formula.

[ANS.] scatter graph method: y = 22.147x +6,179.6

(b) Using the least squares regression method, find the value of the variable cost per unit (m) and the total fixed cost (b) to complete the estimated cost formula:

*Variable cost per unit: m=nΣx2−(Σx)2nΣxy−(Σx)∗(Σy)​

*Total fixed cost: b=nΣy−mΣx​

[ANS.] least squares regression method: y = 22.15x + 6,179.58

(c) NONE.

Detailed Explanation:

(a) Using the scatter graph method, find the value of the variable cost per unit (or slope, m) and the total fixed cost (b) to complete the estimated cost formula:

Step #1: Tabulate the given values of units produced under the column x and its corresponding labor cost under column y in excel.

Step #2: Using these data, plot a scatter graph.

Step #3: Click the graph, go to Chart Design tab. Select Add Chart Element. Look for Trendline on the drop-down menu and choose Linear to obtain the regression line of the scatter graph.

Step #4: Click on the regression line. Right-click and choose Format Trendline. Tick the box beside Display Equation on chart to get the equation of the regression line which is also equivalent to the cost formula.

From the graph, we get the cost formula which is y = 22.147x +6,179.6

Alternative Method (still using excel): Compute for the value of m using slope function and b using the intercept function.

*Slope function: =SLOPE(known y values; known x values)

=SLOPE(B2:B7; C2:C7) --> Refer to the table below

*Intercept function: =INTERCEPT(known y values; known x values)

=INTERCEPT(B2:B7; C2:C7) --> Refer to the table in below

[ANS.] scatter graph method: y = 22.147x +6,179.6

(b) Using the least squares regression method, find the value of the variable cost per unit (m) and the total fixed cost (b) to complete the estimated cost formula:

*Variable cost per unit: m=nΣx2−(Σx)2nΣxy−(Σx)∗(Σy)​

*Total fixed cost: b=nΣy−mΣx​

where Σx = sum of units produced within the given period

Σy = sum of labor cost within the given period

Σxy = sum of products of units produced & its labor cost per month

Σx2 = sum of squares of the units produced within the given period

n = number of pairs of units produced & its labor cost

m = variable cost per unit

b = total fixed cost

Step #1: Compute for the value of every summation indicated in the formula above.

• Σx = 400 + 300 + 320 + 350 + 420 + 410

= 2,200

• Σy = 15,000 + 13,000 + 13,200 + 13,800 + 16,000 + 14,800

= 85,800

• Σxy = (400)(15,000) + (300)(13,000) + (320)(13,200) + (350)(13,800) + (420)(16,000) + (410)(14,800)

= 6,000,000 + 3,900,000 + 4,224,000 + 4,830,000 + 6,720,000 + 6,068,000

= 31,742,000

• Σx2 = 4002 + 3002 + 3202 + 3502 + 4202 + 4102

= 160,000 + 90,000 + 102,400 + 122,500 + 176,400 + 168,100

= 819,400

Step #2: Compute for the variable cost per unit (m) using the formula above.

*Variable cost per unit: m=nΣx2−(Σx)2nΣxy−(Σx)∗(Σy)​

m=(6∗819,400)−(2,200)2(6∗31,742,000)−(2,200∗85,800)​

m=4,916,400−4,840,000190,452,000−188,760,000​

m=76,4001,692,000​

m = 22.14659686 ≈ 22.15

Step #3: Compute for the total fixed cost (b) using the formula above.

*Total fixed cost: b=nΣy−mΣx​

b=685,800−(22.14659686∗2,200)​

b=685,800−48,722.51309​

b=637,077.48691​

b = 6,179.581152 ≈ 6,179.58

Step #4: Substitute the obtained values of m and b in the format given in the problem for the cost formula.

*Cost formula using least squares regression method: y = mx + b

y = 22.15x + 6,179.58

[ANS.] least squares regression method: y = 22.15x + 6,179.58

(c) If we're strictly speaking about just the given problem, the answer is NONE. (Though, in reality, there are supposedly other factors that may affect variation cost such as worker availability, efficiency, difficulty of task, etc.)

Please , upvote, if your satisfied with my work. Thank you.