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