Cost analysts for Henry Blue Co. have selected the following cost drivers to pro
ID: 3201999 • Letter: C
Question
Cost analysts for Henry Blue Co. have selected the following cost drivers to project mixed costs: volume of beer produced (in hectoliters, i.e., 1hL = 100 L), total amount of raw materials used (in kilograms), number of batches, volume of water used (in hL), number of cleaning procedures performed (CIPs) and number of new products. Here is the cost data and levels of cost driver activity for 18 months.
Dollars of Beer Raw Number
Total Produced Material of Water New
Month Overhead (hL) (kg) Batches (hL) CIPs Products
Jan 57,266 890 13,573 54 6,005 67 -
Feb 61,020 980 15,013 58 6,588 72 1
Mar 64,622 1,091 16,781 65 7,336 81 -
Apr 68,630 1,212 18,551 73 8,002 88 -
May 70,652 1,262 19,370 75 8,435 93 -
Jun 79,927 1,494 23,182 89 9,940 110 2
Jul 82,867 1,557 24,202 95 10,240 106 3
Aug 81,748 1,528 23,797 94 10,326 112 2
Sep 68,820 1,215 18,537 72 8,284 87 -
Oct 66,375 1,145 17,582 69 7,746 85 -
Nov 63,767 1,072 16,369 64 7,168 76 -
Dec 62,255 1,032 15,628 62 6,933 77 -
Jan 56,838 872 13,158 50 5,902 61 1
Feb 61,298 1,006 15,224 60 6,759 75 -
Mar 63,179 1,041 15,763 62 6,990 81 1
Apr 66,107 1,139 17,246 68 7,629 85 -
May 69,759 1,228 18,593 75 8,205 89 1
Jun 76,403 1,397 21,571 84 9,304 100 2
$ 1,221,533 21,161 324,140 1,269 141,792 1,545 13
Required:
1. Using regression, calculate the x and y components using hL of beer produced as the independent variable anddollars of overhead as the dependent variable.
2. Do you think beer produced is an adequate driver to predict overhead? Why or why not?
3. Using regression, compute the y and x from the above table using number of batches as the independent variableand dollars of overhead as the dependent variable.
4. Which driver appears to be the best and why??
5. Assuming a projected 1,800 hL of beer for next month, compute the projected overhead cost and discuss.
Explanation / Answer
Result:
Required:
Regression Analysis
r²
0.998
n
18
r
0.999
k
1
Std. Error
403.843
Dep. Var.
dollars of overhead
ANOVA table
Source
SS
df
MS
F
p-value
Regression
1,056,394,386.3080
1
1,056,394,386.3080
6477.39
2.67E-22
Residual
2,609,430.6364
16
163,089.4148
Total
1,059,003,816.9444
17
Regression output
confidence interval
variables
coefficients
std. error
t (df=16)
p-value
95% lower
95% upper
Intercept
23,227.2057
562.7126
41.277
1.11E-17
22,034.3082
24,420.1033
Beer produced
37.9681
0.4718
80.482
2.67E-22
36.9680
38.9682
Regression line
dollars of overhead =23,227.2057+37.9681*Beer produced
Calculated F=6477.39,P=0.0000 which is < 0.05 level of significance.
Beer produced is an adequate driver to predict overhead.
Regression Analysis
r²
0.994
n
18
r
0.997
k
1
Std. Error
609.682
Dep. Var.
dollars of overhead
ANOVA table
Source
SS
df
MS
F
p-value
Regression
1,053,056,426.1028
1
1,053,056,426.1028
2832.99
1.95E-19
Residual
5,947,390.8416
16
371,711.9276
Total
1,059,003,816.9444
17
Regression output
confidence interval
variables
coefficients
std. error
t (df=16)
p-value
95% lower
95% upper
Intercept
25,456.5707
809.5812
31.444
8.18E-16
23,740.3352
27,172.8062
Number of Batches
601.5088
11.3011
53.226
1.95E-19
577.5517
625.4660
dollars of overhead =25,456.5707+601.5088*Number of Batches
hL of beer produced is best because R square for this 0.998 is higher than R square value of 0.994.
Predicted dollars of overhead =23,227.2057+37.9681*1800
=91569.7857
projected overhead cost =$91569.79
Regression Analysis
r²
0.998
n
18
r
0.999
k
1
Std. Error
403.843
Dep. Var.
dollars of overhead
ANOVA table
Source
SS
df
MS
F
p-value
Regression
1,056,394,386.3080
1
1,056,394,386.3080
6477.39
2.67E-22
Residual
2,609,430.6364
16
163,089.4148
Total
1,059,003,816.9444
17
Regression output
confidence interval
variables
coefficients
std. error
t (df=16)
p-value
95% lower
95% upper
Intercept
23,227.2057
562.7126
41.277
1.11E-17
22,034.3082
24,420.1033
Beer produced
37.9681
0.4718
80.482
2.67E-22
36.9680
38.9682