Cost Accounting Lanen 7th Edition-Excel Examples
Cost Accounting Lanen 7th Edition-Excel Examples
Cost Accounting Lanen 7th Edition-Excel Examples
500,000 41.7%
400,000
35.9%
ROS
Profit
0
1 2 3 4 5 6
Districts
Desert Adventures
Cost to outsource
Fixed cost $ - $ -
Variable cost $ 1,400 $ 1,400
Total cost to outsource $ 140,000 $ 168,000
Profit $ 375,000
Metal Frames Wood Frames
Price $ 50 $ 80
Less variable costs per unit
Material $ 8 $ 22
Labor $ 8 $ 24
Overhead $ 4 $ 4
Contribution margin per unit $ 30 $ 30
Quantity - -
Profit $ (450,000)
Metal Frames Wood Frames
Price $ 50 $ 80
Less variable costs per unit
Material $ 8 $ 22
Labor $ 8 $ 24
Overhead $ 4 $ 4
Contribution margin per unit $ 30 $ 30
Profit $ 375,000
Metal Frames Wood Frames
Price $ 50 $ 80
Less variable costs per unit
Material $ 8 $ 22
Labor $ 8 $ 24
Overhead $ 4 $ 4
Contribution margin per unit $ 30 $ 30
Quantity 40,000 0 0 -
Profit $ 750,000
C20 target is set to $750,000 because the maximum profit is obtainable based on maximum contribution per
constrained resource which is metal frames (0.5 machine hr per frame). 20,000 mhrs/0.5 = 40,000 units
Month Overhead Costs ($) Labor-hours Material Costs ($)
1 38,910 496 2,130
2 35,797 496 2,904
3 59,921 960 7,000
4 43,319 568 3,136
5 35,612 400 3,088
6 41,737 760 2,444
7 55,581 1,136 5,972
8 41,645 688 3,682
9 44,844 896 3,308
10 56,508 1,088 4,200
11 43,428 680 2,490
12 55,852 824 5,400
13 44,881 768 4,400
14 58,050 808 6,220
15 30,297 424 1,504
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.950730787 Corrolation coefficient: Linear relatioship between overhead costs and Labor hours and M
R Square 0.903889029 Coefficient of determination: 90% of changes in Overhead Costs can be explained by chan
The percent of the total variance t
Adjusted R Square 0.887870533
Standard Error 3099.911239
Observations 15
ANOVA
df SS MS F Significance F
Regression 2 1084480910.112 542240455.1 56.4278364 7.882025E-07
Residual 12 115313396.2882 9609449.691
Total 14 1199794306.4
Y = 19,894 + 17.8LHRS + 3.3MC t statistic (Coefficient/Standard Error) > 2, that means LHRS and MC a
P value is close to zero means the probability of coefficient values bei
and Labor hours and Material Cost are strong. The dots are close to the regression line.
an be explained by changes in Labor hours and material costs.
t of the total variance that can be explained by the regression.
t means LHRS and MC are strong indicators of Overhead Cost and they are not fixed type of costs.
of coefficient values being zero is zero.
Unit Produced Labor Time Required to Produce the Xth
Total Cost
(X) Unit (i.e. the last single unit produced) Y Cumulative Total Time
in Labor Hours
1 100.00 100.00 $ 5,000.00
2 80.00 180.00 $ 9,000.00
3 70.21 250.21 $ 12,510.52
4 64.00 314.21 $ 15,710.52
5 59.56 373.77 $ 18,688.71
6 56.17 429.94 $ 21,497.12
7 53.45 483.39 $ 24,169.57
8 51.20 534.59 $ 26,729.57
$ 5,000.00 -0.32192809
$ 4,500.00 -0.32192809
$ 4,170.17 -0.32192809
$ 3,927.63 -0.32192809
$ 3,737.74 -0.32192809
$ 3,582.85 -0.32192809
$ 3,452.80 -0.32192809
$ 3,341.20 -0.32192809
Flow of Units (quantity only) Physical Units Equivalent Units Equivalent Units
Material Conversion
Units to be accounted for:
Beginning-BWIP inventory:
Units started in this period:
Total units to account for:
$100.00
$80.00
$60.00
$40.00
$20.00
$0.00
0.1 1.1 2.1 3.1 4.1 5.1 6.1 7.1 8.1 9.1 10.1 11.1 12.1
Allocation
Formula: (Final Market Price - 20 - 30) X 100,000 units; minus sign means that trasfer price of the Milk D
Conditional formatting light red less that zero. Red highlighted areas show that either division does not agr
amount forsaken because transfer is not realized.
Canyon Diaries` Contribution Margin for Selected Transfer Prices $(000)
Transfer Price
40 45 50 55 60 65 70 75 80 85
0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0
-200 -200 -200 -200 -200 -200 -200 -200 -200 -200
-400 -400 -400 -400 -400 -400 -400 -400 -400 -400
-600 -600 -600 -600 -600 -600 -600 -600 -600 -600
-800 -800 -800 -800 -800 -800 -800 -800 -800 -800
-1,000 -1,000 -1,000 -1,000 -1,000 -1,000 -1,000 -1,000 -1,000 -1,000
-1,200 -1,200 -1,200 -1,200 -1,200 -1,200 -1,200 -1,200 -1,200 -1,200
-1,400 -1,400 -1,400 -1,400 -1,400 -1,400 -1,400 -1,400 -1,400 -1,400
-1,600 -1,600 -1,600 -1,600 -1,600 -1,600 -1,600 -1,600 -1,600 -1,600
-1,800 -1,800 -1,800 -1,800 -1,800 -1,800 -1,800 -1,800 -1,800 -1,800
2,000 -2,000 -2,000 -2,000 -2,000 -2,000 -2,000 -2,000 -2,000 -2,000
2,200 -2,200 -2,200 -2,200 -2,200 -2,200 -2,200 -2,200 -2,200 -2,200
2,400 -2,400 -2,400 -2,400 -2,400 -2,400 -2,400 -2,400 -2,400 -2,400
2,600 2,600 -2,600 -2,600 -2,600 -2,600 -2,600 -2,600 -2,600 -2,600
2,800 2,800 -2,800 -2,800 -2,800 -2,800 -2,800 -2,800 -2,800 -2,800
3,000 3,000 3,000 -3,000 -3,000 -3,000 -3,000 -3,000 -3,000 -3,000
3,200 3,200 3,200 -3,200 -3,200 -3,200 -3,200 -3,200 -3,200 -3,200
3,400 3,400 3,400 -3,400 -3,400 -3,400 -3,400 -3,400 -3,400 -3,400
3,600 3,600 3,600 3,600 -3,600 -3,600 -3,600 -3,600 -3,600 -3,600
3,800 3,800 3,800 3,800 -3,800 -3,800 -3,800 -3,800 -3,800 -3,800
4,000 4,000 4,000 4,000 4,000 -4,000 -4,000 -4,000 -4,000 -4,000
4,200 4,200 4,200 4,200 4,200 -4,200 -4,200 -4,200 -4,200 -4,200
4,400 4,400 4,400 4,400 4,400 -4,400 -4,400 -4,400 -4,400 -4,400
4,600 4,600 4,600 4,600 4,600 4,600 -4,600 -4,600 -4,600 -4,600
4,800 4,800 4,800 4,800 4,800 4,800 -4,800 -4,800 -4,800 -4,800
5,000 5,000 5,000 5,000 5,000 5,000 5,000 -5,000 -5,000 -5,000
5,200 5,200 5,200 5,200 5,200 5,200 5,200 -5,200 -5,200 -5,200
5,400 5,400 5,400 5,400 5,400 5,400 5,400 -5,400 -5,400 -5,400
5,600 5,600 5,600 5,600 5,600 5,600 5,600 5,600 -5,600 -5,600
5,800 5,800 5,800 5,800 5,800 5,800 5,800 5,800 -5,800 -5,800
6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 -6,000
6,200 6,200 6,200 6,200 6,200 6,200 6,200 6,200 6,200 -6,200
6,400 6,400 6,400 6,400 6,400 6,400 6,400 6,400 6,400 -6,400
6,600 6,600 6,600 6,600 6,600 6,600 6,600 6,600 6,600 6,600
6,800 6,800 6,800 6,800 6,800 6,800 6,800 6,800 6,800 6,800
7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000 7,000
7,200 7,200 7,200 7,200 7,200 7,200 7,200 7,200 7,200 7,200
7,400 7,400 7,400 7,400 7,400 7,400 7,400 7,400 7,400 7,400
7,600 7,600 7,600 7,600 7,600 7,600 7,600 7,600 7,600 7,600
7,800 7,800 7,800 7,800 7,800 7,800 7,800 7,800 7,800 7,800
8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000
8,200 8,200 8,200 8,200 8,200 8,200 8,200 8,200 8,200 8,200
8,400 8,400 8,400 8,400 8,400 8,400 8,400 8,400 8,400 8,400
8,600 8,600 8,600 8,600 8,600 8,600 8,600 8,600 8,600 8,600
8,800 8,800 8,800 8,800 8,800 8,800 8,800 8,800 8,800 8,800
9,000 9,000 9,000 9,000 9,000 9,000 9,000 9,000 9,000 9,000
that trasfer price of the Milk Division is below its variable cost of $20
hat either division does not agree, transfer is not realized and the total
90 95 100 105 110
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
-200 -200 -200 -200 -200
-400 -400 -400 -400 -400
-600 -600 -600 -600 -600
-800 -800 -800 -800 -800
-1,000 -1,000 -1,000 -1,000 -1,000
-1,200 -1,200 -1,200 -1,200 -1,200
-1,400 -1,400 -1,400 -1,400 -1,400
-1,600 -1,600 -1,600 -1,600 -1,600
-1,800 -1,800 -1,800 -1,800 -1,800
-2,000 -2,000 -2,000 -2,000 -2,000
-2,200 -2,200 -2,200 -2,200 -2,200
-2,400 -2,400 -2,400 -2,400 -2,400
-2,600 -2,600 -2,600 -2,600 -2,600
-2,800 -2,800 -2,800 -2,800 -2,800
-3,000 -3,000 -3,000 -3,000 -3,000
-3,200 -3,200 -3,200 -3,200 -3,200
-3,400 -3,400 -3,400 -3,400 -3,400
-3,600 -3,600 -3,600 -3,600 -3,600
-3,800 -3,800 -3,800 -3,800 -3,800
-4,000 -4,000 -4,000 -4,000 -4,000
-4,200 -4,200 -4,200 -4,200 -4,200
-4,400 -4,400 -4,400 -4,400 -4,400
-4,600 -4,600 -4,600 -4,600 -4,600
-4,800 -4,800 -4,800 -4,800 -4,800
-5,000 -5,000 -5,000 -5,000 -5,000
-5,200 -5,200 -5,200 -5,200 -5,200
-5,400 -5,400 -5,400 -5,400 -5,400
-5,600 -5,600 -5,600 -5,600 -5,600
-5,800 -5,800 -5,800 -5,800 -5,800
-6,000 -6,000 -6,000 -6,000 -6,000
-6,200 -6,200 -6,200 -6,200 -6,200
-6,400 -6,400 -6,400 -6,400 -6,400
-6,600 -6,600 -6,600 -6,600 -6,600
-6,800 -6,800 -6,800 -6,800 -6,800
7,000 -7,000 -7,000 -7,000 -7,000
7,200 -7,200 -7,200 -7,200 -7,200
7,400 -7,400 -7,400 -7,400 -7,400
7,600 7,600 -7,600 -7,600 -7,600
7,800 7,800 -7,800 -7,800 -7,800
8,000 8,000 8,000 -8,000 -8,000
8,200 8,200 8,200 -8,200 -8,200
8,400 8,400 8,400 -8,400 -8,400
8,600 8,600 8,600 8,600 -8,600
8,800 8,800 8,800 8,800 -8,800
9,000 9,000 9,000 9,000 9,000