Cost analysts for River City Brewing Company have selected the following cost dr
ID: 446581 • Letter: C
Question
Cost analysts for River City Brewing Company 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-cleanings in place (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:
Using regression, calculated the x and y components using hL of beer produced as the independent variable and dollars of overhead as the dependent variable.
Do you think beer produced is an adequate driver to predict overhead? Why or why not?
Using regression compute the y and x from the above table using number of batches as the independent variable and dollars of overhead as the dependent variable.
Which driver appears to be the best and why??
Assuming a projected 1,800 hL of beer for next month, compute the projected overhead cost and discuss.
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
Explanation / Answer
The regression is of the form of Y= a+bx This is solved by the method of least square after solving the the two Normal Equations y= na+bx xy=ax+bx2 Month Independent Variable (X) Dependent Variable (y) XY X2 Jan 890.00 57266.00 50966740.00 792100 Feb 980.00 61020.00 59799600.00 960400 Mar 1091.00 64622.00 70502602.00 1190281 Apr 1212.00 68630.00 83179560.00 1468944 May 1262.00 70652.00 89162824.00 1592644 Jun 1494.00 79927.00 119410938.00 2232036 Jul 1557.00 82867.00 129023919.00 2424249 Aug 1528.00 81748.00 124910944.00 2334784 Sep 1215.00 68820.00 83616300.00 1476225 Oct 1145.00 66375.00 75999375.00 1311025 Nov 1072.00 63767.00 68358224.00 1149184 Dec 1032.00 62255.00 64247160.00 1065024 Jan 872.00 56838.00 49562736.00 760384 Feb 1006.00 61298.00 61665788.00 1012036 Mar 1041.00 63179.00 65769339.00 1083681 Apr 1139.00 66107.00 75295873.00 1297321 May 1228.00 69759.00 85664052.00 1507984 Jun 1397.00 76403.00 106734991.00 1951609 Total 21161.00 1221533.00 1463870965.00 25609911.00 y= na+bx xy=ax+bx2 18a+21161b=1221533 21161a+25609911b=1463870965 After the equation we will get 380898a+460978398b=26349677370 380898 460978398 26349677370 380898a+447787921b=25848859813 380898 447787921 25848859813 13190477 b= 500817557 b= 37.96811571 a= 0.084465132 The regression Y = a+bx y= 0.084465132+37.96*X The projected cost of over head when 1800 hl is y= 0.084465132+37.96*X *1800 Projected 68328.08447 Overhead