Question

Galarneau Inc. maintains a call center to take orders, answer questions, and handle complaints. The costs of the call center for a number of recent months are listed below:

	Calls Taken			Call Center Cost
February		7,320		$	79,358
March		7,289		$	79,167
April		7,330		$	79,424
May		7,327		$	79,386
June		7,296		$	79,221
July		7,309		$	79,292
August		7,350		$	79,537
September		7,335		$	79,449

Management believes that the cost of the call center is a mixed cost that depends on the number of calls taken.

Required:

Use Microsoft Excel to run a least-squares regression analysis on the above data, and estimate the variable cost per call and fixed cost per month using the least-squares regression method.  (Round the "Variable cost" to 2 decimal places and the "Fixed cost" to the nearest dollar amount.)

Variable cost	$	per call
Fixed cost	$	per month

Answer 1

	As per least squares regression method, Y=a+b.X
	Where,
	a=Total fixed cost
	b=Unit variable cost
	b=(n?xy- ?x.?y)/[n?x^2-(?x)^2]
	a=(?y-b.?x)/n
	x=calls taken
	y=Call center cost
	x	Y	x^2	xy
	7320	79358	53582400	580900560
	7289	79167	53129521	577048263
	7330	79424	53728900	582177920
	7327	79386	53684929	581661222
	7296	79221	53231616	577996416
	7309	79292	53421481	579545228
	7350	79537	54022500	584596950
	7335	79449	53802225	582758415
	58556	634834	428603572	4646684974
	n=8
	?x=58556
	?y=634834
	?x^2=428603572
	?xy=37173339704
	b=[(8*37173339704)-(58556*634834)]/[(8*428603572)-(58556)^2]=5.98
	a=[634834-(5.98*58556)]/8=35583.64=35584
	Variable cost per call=$ 5.98
	Fixed cost per month=$ 35584