You have just been hired as a consultant to a large manufacturing company of sodas, and have decided to use your new MBA skills to make a recommendation
to management on what should be the cost driver(s) for estimating the monthly overhead cost for one of the plants that manufactures this drink outside the United States.
Month	Volume of liquid produced hectoliters	Water used in hectoliters	Number of Batches	Total Overhead costs for the plant
January	890	6,005	54	$57,266
February	980	6,588	58	61,020
March	1,094	7,336	65	64,622
April	1,212	8,002	73	68,630
May	1,262	8,435	75	70,653
June	1,494	9,940	89	79,927
July	1,557	10,420	95	82,867
August	1,528	10,326	94	81,748
September	1,215	8,284	72	68,819
October	1,145	7,746	69	66,375
November	1,072	7,168	64	63,767
December	1,032	6,933	62	62,545
January	872	5,902	50	56,837
February	1,006	6,759	60	61,298
Do not retype the information above.  You should use this worksheet as your data sheet and your solution should be on separate
sheets.
Required:
Also, make sure you identify which cost driver relates to X1, X2, and X3--I have done the first one for you.(excel reads left to right)
Identify the 2 other X variables(I have identified the X1 variable for you, which will be the first column highlighted):
X1	Volume of liquid produced
X2
X3
3.  Assuming the following level of cost-driver volume for the month, what is the estimated
cost using simple regression for each of the cost drivers and using multiple regression.
You must use cell references on the output tables you generated on separate worksheets from the regression tables to estimate the costs.  You will not earn credit if you type in your numbers or do not  reference the regression worksheets.
You should have 4 computations for this answer.**** I am preparing the answer using volume of liquid produced to estimate the cost, therefore, you
only have to prepare 3 computations.
		Volume of liquid produced	1499
		Water used	10299
		Number of batches	92
	Volume of liquid produced to estimate overhead cost		80,140.42
	I used the simple regression output to cell reference
	and used the intercept as the fixed cost
	and the X variable as the variable cost.
	Water used
	Number of Batches
	Multiple regression

Answer rating: 100% (QA)

To identify the other two potential cost drivers X2 and X3 lets first understand the data and the nature of the manufacturing process for the soda pla... View the full answer