Question

2.Grawburg 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 April May June July August September October November 8,450 8,419 8,460 8,459 8,423 8,434 8,476 8,464 Call Center Cost $ 91,609 $ 91,440 $ 91,668 $ 91,651 $ 91,458 $ 91,496 $ 91,722 $ 91,678 Management believes that the cost of the call center is a mixed cost that depends on the number of calls taken.(10 points) Hint:Watch the Least Squares Regression videos in Chapter 6 Required: Use Excel to 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.) Please attach your Excel spreadsheet to support your answer.

Answer 1

Month	Calls taken(X)	Call centre Cost(Y)(in $)	Square of X	X*Y(in $)
Apr	8,450	91,609	7,14,02,500	77,40,96,050
May	8,419	91,440	7,08,79,561	76,98,33,360
Jun	8,460	91,668	7,15,71,600	77,55,11,280
July	8,459	91,651	7,15,54,681	77,52,75,809
Aug	8,423	91,458	7,09,46,929	77,03,50,734
Sep	8,434	91,496	7,11,32,356	77,16,77,264
Oct	8,476	91,722	7,18,42,576	77,74,35,672
Nov	8,464	91,678	7,16,39,296	77,59,62,592
Total	67,585	7,32,722	57,09,69,499	6,19,01,42,761
	Unit Variable Cost (b) = nΣxy − (Σx)(Σy) /nΣx^2 − (Σx)^2		Total Fixed Cost (a) = Σy − bΣx / n
So here n =8 ,Σx =67,585 Σy =732,722, Σx^2=4,56,77,55,992 ,Σxy =6,19,01,42,761
Variable cost per unit(b) =8*6,19,01,42,761 - (67,585*732,722) / 8*4,56,77,55,992 - 67,585^2
Variable cost per unit(b) =49,52,11,42,088 - 49,52,10,16,370 / 4,56,77,55,992 - 4,56,77,32,225
Variable cost per unit(b) = 125,718 / 23,767 =$5.29
Total Fixed cost =732,722 - ($5.29*67,585) / 8
Total Fixed cost =(732,722 - 357,525) / 8
Total Fixed cost =$375,197 /8 =$46,900
The cost volume formula is:
y =$46,900 + $5.29X