WHITE CELLS ARE ADJUSTABLE
Kristoffer Burnett - Certified Management Accountant, 2009-2011
Activity Based Costing Example
Product Profitability Analysis
1
Item1
Item2
Item3
Item4
Item5
Sales units
Sales dollars
14,790
$ 2,298,366
12,853
$ 3,435,928
24,311
$ 3,822,905
12,395
$ 3,233,236
31,021
$ 3,156,387
Direct Costs
Direct materials
Direct labor
Total direct costs
(378,953)
(1,696,401)
(1,129,207)
(1,040,302)
(938,312)
(598,320)
(294,637)
(888,315)
(600,617)
(868,180)
$ (977,273) $ (1,991,038) $ (2,017,522) $ (1,640,919) $ (1,806,492)
Activities
Research & development
Procure materials
Manufacturing
Shipping
Marketing/customer service
Total allocated costs
Total item margin
(308,649)
(361,244)
(437,444)
(333,640)
(406,624)
(114,314)
(141,907)
(100,167)
(114,477)
(909,366)
(1,409,101)
$ 411,727 $
35,789 $
Share of unallocated costs
Total item operating profit
Sales
Per Unit Breakdown
Average sales price per unit
Direct and allocated cost per unit
Item margin per unit
Unallocated cost per unit
Operating profit per unit
$
$
$
(397,933)
(437,444)
(375,345)
(417,050)
(126,140)
(137,965)
(100,167)
(114,477)
(999,585)
(1,106,937)
805,798 $
485,380 $
(58,109)
353,618 $
(58,109)
(22,320) $
(58,109)
747,689 $
(58,109)
427,271 $
155.40 $
(127.56)
27.84 $
(3.93)
23.91 $
267.32 $
(264.54)
2.78 $
(4.52)
(1.74) $
157.25 $
(124.10)
33.15 $
(2.39)
30.76 $
260.85 $
(221.69)
39.16 $
(4.69)
34.47 $
(308,649)
(262,466)
(239,804)
(86,721)
(71,548)
(969,188)
380,707
(58,109)
322,598
101.75 Sales dollars / Sales units
(89.48) (Total direct costs + Total allocated costs) / Sales units
12.27
(1.87) Share of unallocated costs / Sales units
10.40
Operating Profit by Item
$300
$250
$200
$150
Sales Price
Full cost
$100
Operating profit
$50
$0
-$50
Item1
Item2
Notes
1 Costs are reflected as (negative) in this section to better contrast with sales.
Item3
Item4
Item5
WHITE CELLS ARE ADJUSTABLE
Kristoffer Burnett - Certified Management Accountant, 2009-2011
Activity Based Costing Example
Item and Sales Information
Item
Sales units
Sales dollars
Item1
Item2
Item3
Item4
Item5
14,790
12,853
24,311
12,395
31,021
$ 2,298,366 $ 3,435,928 $ 3,822,905 $ 3,233,236 $ 3,156,387
Direct Cost Information
Direct materials
Direct labor
Activity Metric Breakdown
Number of products designed
Number of POs
Number of manufacturing batches
Number of shipments
Number of customers
378,953
598,320
1,696,401
294,637
1,129,207
888,315
1,040,302
600,617
The activity metrics are specified on the Analysis worksheet
Item1
Item2
Item3
Item4
1
128
155
141
155
32
39
36
40
29
36
32
35
7
8
7
8
938,312
868,180
Item5
Total
$ 5,183,175
3,250,069
Total
1
93
23
22
5
Notes
1 For the purposes of this example, a value of "1" means that this item was developed during the relevant period.
2
672
170
154
35
WHITE CELLS ARE ADJUSTABLE
Kristoffer Burnett - Certified Management Accountant, 2009 -2011
Activity Based Costing Example
Cost Outline
COGS Expenses
Direct materials
Direct labor
Indirect labor
Misc employee expenses
Supplies
Maintenance
Freight in
Depreciation
Utilities
Lease expense
Total COGS expenses
Operating Expenses
Salaries
Misc employee expense
Supplies
Maintenance
Shipping
Depreciation
Utilities
Lease expense
Total operating expenses
Amount
$ 5,183,175 Direct costs are broken out by item on the Data worksheet
3,250,069 "
499,999
100,000
157,142
271,428
642,856
499,999
328,571
357,142
$11,290,381
1
Amount
466,552
268,621
42,414
212,069
664,483
342,845
307,500
523,103
$ 2,827,587
$
Identify Activities
Activity
Research & development
Procure materials
Manufacturing
Shipping
Marketing/customer service
Metric
Number of products designed
Number of POs
Number of manufacturing batches
Number of shipments
Number of customers
Cost Breakdown by Activity
COGS Expenses
Direct materials
Direct labor
Indirect labor
Misc employee expenses
Supplies
Maintenance
Freight in
Depreciation
Utilities
Lease expense
Operating Expenses
Salaries
Misc employee expense
Supplies
Maintenance
Shipping
Depreciation
Utilities
Lease expense
Totals
Activity Metric
Number of products designed
Number of POs
Number of manufacturing batches
Number of shipments
Number of customers
Activity cost per measure
Research & development
$ 5,183,175
3,250,069
499,999
100,000
157,142
271,428
642,856
499,999
328,571
357,142
0%
0%
0%
0%
0%
0%
0%
0%
466,552
268,621
42,414
212,069
664,483
342,845
307,500
523,103
25%
20%
20%
25%
25%
20%
15%
20%
Procure materials
Manufacturing
Shipping
Marketing/customer service
Unallocated
Direct costs are already traceable to the individual items and do not need to be allocated
The sum of the percentages must = 100%
116,638
53,724
8,483
53,017
166,121
68,569
46,125
104,621
617,298
50%
30%
75%
30%
70%
55%
30%
40%
250,000
30,000
117,857
81,428
449,999
274,999
98,571
142,857
50%
35%
25%
70%
30%
45%
65%
55%
250,000
35,000
39,286
190,000
192,857
225,000
213,571
196,428
0%
0%
0%
0%
0%
0%
0%
0%
25%
15%
10%
15%
10%
15%
20%
15%
116,638
40,293
4,241
31,810
66,448
51,427
61,500
78,465
1,896,535
10%
20%
25%
15%
10%
25%
10%
20%
25%
10%
20%
20%
20%
30%
15%
25%
46,655
53,724
10,604
31,810
66,448
85,711
30,750
104,621
1,772,464
10,426
116,638
26,862
8,483
42,414
132,897
102,854
46,125
130,776
607,048
3,941.87
0%
0%
0%
0%
0%
0%
0%
0%
15%
25%
25%
25%
15%
10%
20%
20%
$
69,983
67,155
10,604
53,017
99,672
34,285
61,500
104,621
500,836
35
14,309.61
0%
35%
0%
0%
0%
0%
5%
5%
35,000
16,429
17,857
0%
10%
0%
0%
20%
0%
20%
0%
$
26,862
132,897
61,500
290,544
290,544 totals / Total of the Activity Metric on the Item Information worksheet
These totals are pulled from the Data worksheet
2
672
170
154
$
308,649
2,822
Notes
1 These figures should represent actual amounts spent. Therefore, they may not match what is shown on the income statement due to the use of accrual accounting.
2 For simplicities sake, only a handful of activities are initially recommended. This is to help assure that the costs associated with data collection
does not outweigh the benefits.
