Question

The controller of Standard Industries has collected the following monthly expense data for analyzing the cost behavior of electricity costs.

	Total Total Electricity	Costs Machine Hours
January	$2,500	300
February	3,000	350
March	3,600	500
April	4,500	690
May	3,200	400
June	4,900	700
July	4,100	650
August	3,800	520
September	5,100	680
October	4,200	630
November	3,300	350
December	6,100	720

Instructions

(a)  

Determine the fixed- and variable-cost components using regression analysis (We recommend the use of Excel.)

(b)  

Prepare a scatter plot using Excel. Present the cost equation line estimated in part (a).

(c)  

What electricity cost does the cost equation estimate for a level of activity of 500 machine hours? By what amount does this differ from March's observed cost for 500 machine hours?

Answer 1

A)

	x y		Calculated y		Error
1.			2611.379321		111.3793209
2.			2904.864583		95.13541699
3.			3785.320369		185.3203693
4.			4900.564365		400.5643652
5.			3198.349845		1.650154894
6.			4959.261418		59.26141766
7.			4665.776156		565.7761556
8.			3902.714474		102.7144741
9.			4841.867313		258.1326872
10.			4548.382051		348.3820507
11.			2904.864583		395.135417
12.			5076.655522		1023.344478

Result:  y = 5.869705242 x + 850.4677484

Correlation Coefficient: r = 9.100540652·10^-1
Residual Sum of Squares: rss = 1941787.566
Coefficient of Determination: R² = 8.281984016·10^-1

B)

Booki - Excel (Product Activation Failed) ? * - - Sign in Doser FILE HOME INSERT PAGE LAYOUT * Cut Calibri - 11 Copy Paste Format Painter B I D 1 Clipboard Font FORMULAS DATA A A == REVIEW VIEW 2 & Wrap Text Merge & Center - General D X AutoSum AY # A S S E $ % 48.08 Conditional Format as Formatting Table Styles Cell Styles Insert Delete Format - Cells Fill- Clear Sort & Find & Filter - Select Editing Alignment Number J20 А J K L M N O P Q R S T months Costs Machine Hours L300 3 4 5 January February March April May June July August September October November December в Total Total Costs Machine Electricity Hours 2,500 3,000 350 3,600 500 4,500 690 3,200 400 4,900 700 4,100 3,800 520 5,100 680 4,200 1630 3,300 350 6,100 720 650 9 10 13 2 4 6 8 10 12 14 Sheet1 + READY @ U -- D OLFO W X + 100% 3:30 AM 11/14/2019

C)

Here is the cost data that was observed:

	Hours	Cost
Highest Observation	720	$6100
Lowest Observation	(300)	(2500)
Difference	420	3600

Slope or Variable Cost: 3600/420 = $8.57

Y Intercept or Fixed Cost: $71

which makes the cost function as Y=8.57X + 71

so for the electricity cost for 500 hours is Y=8.57(500) + 71 = $4356.

where actually observed it is $3600. So its differ by 4356-3600 = $756.