Managerial accounting, ABC/ activity based costing

profilepulzx2d4
CopyofCh7ExcelSupportwithHWCanvas.xlsx
Home>Business & Finance homework help>Accounting homework help>Managerial accounting, ABC/ activity based costing

Ch.7 ABC Excel

ACC220
Rider University
HCT--Ch 7.---ABC
How can we categorize Expense:
Fixed, variable
Product & period
Functional
Natural
Job or Project
this Chapter "Activity Based Costs" = ABC
Financial Reporting
Classic Brass Example #1
Income Statement Brief [FAC]
Year Ended December 31, 2xx1
Sales $ 775,000
Cost of goods sold 543,300
Gross Profit [margin] $ 231,700
Selling and administrative expenses 200,585
Net operating income $ 31,115
Categorization of Expense: Example #1
Function ►►►► Sales Admin Production Distribution Totals: Variable Fixed
Direct Materials 150,000 150,000 150,000 - 0
Production Direct labor - 0 - 0 115,000 - 0 115,000 115,000 - 0
Salary Expense 25,000 17,000 36,000 9,000 87,000 12,000 75,000
Wage Expense 16,000 9,000 58,000 12,500 95,500 - 0 95,500
Commission expense 12,000 12,000 12,000 - 0
Fringe benefit expense 13,000 8,800 74,000 6,400 102,200 51,000 51,200
Travel & entertainment Exp. 7,000 2,600 700 - 0 10,300 2,000 8,300
Advertising expense 9,500 400 750 200 10,850 10,850
Natural Building rent expense 3,200 3,600 12,500 4,800 24,100 24,100
Expense Equipment lease expense 400 2,300 6,900 2,230 11,830 11,830
Supply expense 1,900 1,400 2,300 600 6,200 2,300 3,900
Contract labor Expense - 0 - 0 34,000 3,320 37,320 34,000 3,320
Warranty services 3,600 450 2,000 300 6,350 4,000 2,350
Professional services 1,900 2,150 3,200 - 0 7,250 7,250
Bank charges/fees - 0 200 - 0 - 0 200 200
Depreciation expense 2,200 1,975 44,000 11,000 59,175 59,175
Miscellaneous expense 1,400 1,100 3,950 2,160 8,610 215 8,395
Total 97,100 50,975 543,300 52,510 743,885 382,515 361,370
Period Period PRODUCT Period 200,585
200,585
Job ►►►► Job AB1 Job AB2 Job AB3 Totals 543,300 743,885
Salary Expense 12,000 3,000 - 0 15,000
Wage Expense 2,300 6,200 - 0 8,500
Production Direct labor 21,000 23,000 66,000 110,000
Fringe benefit expense 6,500 9,100 2,200 17,800
Commission expense 2,500 - 0 1,300 3,800
Advertising expense - 0 - 0 - 0 - 0
Building rent expense - 0 - 0 - 0 - 0
Equipment lease expense - 0 - 0 2,150 2,150
Office supply expense - 0 - 0 - 0 - 0
Contract labor Expense 1,500 2,650 11,500 15,650
Travel/entertainment exp. 250 350 600 1,200
Professional services 1,110 850 1,950 3,910
Bank charges/fees - 0 - 0 - 0 - 0
Depreciation expense - 0 - 0 - 0 - 0
Miscellaneous expense 875 625 325 1,825
Totals: 48,035 45,775 86,025 179,835 Not all assigned to jobs
Categorization of Expense: Functional:
Examples of Functional Areas
Manufacturing Sales & Marketing General & Administrative
Mfg. Admin. Sales Admin Office of CEO
Process Engineering Field sales Finance
Warehouse' Product development Human Resources
Material handling Advertising General Counsel [legal]
Testing Distribution/warehouse
Quality Assurance & control
Purchasing [Plant Level]
Line Supervision Operations; [non-manufacturing]
Equipment maintenance Operations Admin
Plant Human Resources Corporate purchasing
Plant maintenance
Many others - can vary by company
Excel A
Baxter Battery Company
Income Statement GAAP
Year Ended December 31, 2xx1
Sales 50,000,000
Cost of goods sold
Direct Direct materials 15,000,000
Direct Direct labor 12,000,000
Manufacturing overhead 14,000,000 41,000,000 no change in inventory
Gross margin [Gross Profit] 9,000,000
Selling and administrative expenses
Direct Shipping expenses 3,000,000 Each product line has its own warehouse - shipping charges are freight & direct to product lines
Marketing expenses 2,000,000 2/3 Product A, 1/3 Product B This analysis not shown here
General administrative expenses 6,000,000 11,000,000
Operating loss (2,000,000) LOSS
Expense [& Cost]
Direct materials 15,000,000 Traced through Cost System to products
Direct labor 12,000,000 Traced through Cost System to products
Manufacturing overhead 14,000,000 Traditional use manufacturing basis to product chose not to send with products
Shipping expenses 3,000,000 Traced to Orders/customer directly through products
Marketing expenses 2,000,000
General administrative expenses 6,000,000 52,000,000 30,000,000 Cost Sys. 22,000,000
total Direct to Products Through ABC Pools
To be put into ABC pools to be allocated to Activities 22,000,000 ABC Pools
Direct trace 30,000,000
Overhead Costs at Baxter Battery Natural Expense
(Manufacturing and Nonmanufacturing) & Function
Production Department
Indirect factory wages 6,000,000 Data Given
Factory equipment depreciation 3,500,000
Factory utilities 2,500,000
Factory building lease 2,000,000 14,000,000
General Administrative Department 9 expense categories to be allocated
Administrative wages and salaries 4,000,000
Office equipment depreciation 900,000
Administrative building lease 1,100,000 6,000,000
Marketing Department
Marketing wages and salaries 1,500,000
Selling expenses 500,000 2,000,000
Total overhead costs for allocation 22,000,000
POOLS [2] Assign Overhead Costs to Activity Cost Pools
Activity Cost Pools Order Size* [Machine Hours] Customer Relations [# OF CUSTOMERS] Sustaining
[1] Define Pools →→→→ Customer Orders Design Changes Other Not allocated Total
Production Department
Indirect factory wages 30% 30% 20% 10% 10% 100%
Factory equipment depreciation 20% 10% 60% 0% 10% 100%
Factory utilities 0% 10% 60% 0% 30% 100%
Factory building lease 0% 0% 0% 0% 100% 100%
General Administrative Department
Administrative wages and salaries 30% 10% 10% 30% 20% 100%
Office equipment depreciation 30% 10% 0% 20% 40% 100%
Administrative building lease 0% 0% 0% 0% 100% 100%
Marketing Department Results of ABC study to determine allocation levels
Marketing wages and salaries 30% 10% 0% 50% 10% 100%
Selling expenses 20% 0% 0% 70% 10% 100%
* ORDER SIZE = PROXY FOR MACHINE HOURS
Activity Cost Pools Customer Relations [# OF CUSTOMERS] Sustaining
Multiply Amounts by % = Customer Orders Design changes Order Size* Other Not allocated Total Product of %s above X Total Amounts for department
Production Department 30% X $6000,000 6,000,000
Indirect factory wages 1,800,000 1,800,000 1,200,000 600,000 600,000 6,000,000 Manufacturing overhead 3,500,000
20%X$3500K Factory equipment depreciation 700,000 350,000 2,100,000 - 0 350,000 3,500,000 14,000,000 2,500,000
Factory utilities - 0 250,000 1,500,000 - 0 750,000 2,500,000 3,700,000 unassigned ABC 2,000,000
Factory building lease - 0 - 0 - 0 - 0 2,000,000 2,000,000 26.4% 14,000,000 All unassigned
General Administrative Department - 0 - 0 - 0 - 0 - 0 3,700,000
Administrative wages and salaries 1,200,000 400,000 400,000 1,200,000 800,000 4,000,000 General Administrative Department 2,260,000
Office equipment depreciation 270,000 90,000 - 0 180,000 360,000 900,000 6,000,000 37.7% 200,000
Administrative building lease - 0 - 0 - 0 - 0 1,100,000 1,100,000 2,260,000 unassigned ABC 6,160,000
Marketing Department unassigned ABC
Marketing wages and salaries 450,000 150,000 - 0 750,000 150,000 1,500,000 Marketing Department 200,000
Selling expenses 100,000 - 0 - 0 350,000 50,000 500,000 2,000,000 unassigned ABC
Total 4,520,000 3,040,000 5,200,000 3,080,000 6,160,000 22,000,000
Money is in the pool Given dataè
Activity Level [3] Customer Sustaining
Pool [1a] Customer Orders Design changes Order Size Customer Relations Other Not allocated Total
Pool $s [1a] from above 4,520,000 3,040,000 5,200,000 3,080,000 6,160,000 22,000,000
ACTIVITY Unit [1b] Customer Orders Design changes Machine Hours Number of Customers Not allocated
Allocated 15,840,000
Rate: Activity Level [3]
ACTIVITY Unit [1b] Customer Orders Design changes Machine Hours Number of Customer Not allocated Given dataé
Z = ACTIVITY Units TO ALLOCATE [1b] 10,000 4,000 800,000 2,000 N/A Hrs. Units Extd. Hrs. ABC rate $ 6.50
$Amt. per Activity unit [3] $ 452.00 $ 760.00 $ 6.50 $ 1,540.00 N/A -B- 0.8 400000 320000 Machine Hrs 448
Denominator - Qty. of the pool activity = Z Order size as Proxy -A- 0.6 800000 480000 800000 Assigned $s $ 2,912
Summary
Allocation ABC Direct or otherwise traced
Direct materials 15,000,000 Traced as per data set above
Direct labor 12,000,000 Traced as per data set above
Manufacturing overhead 14,000,000
Shipping expenses 3,000,000 Traced as per data set above
Marketing expenses 2,000,000
General administrative expenses 6,000,000 Total Expenses
Total Traced via ABC 22,000,000 30,000,000 52,000,000
Not traced to ABC object (6,160,000)
To ABC Objects 15,840,000 15,840,000 30.5% in ABC pools
------Product Lines------
Pools $ in Pool Activity Measure Qty of Act. Measure Rate Sure Starts [A] Long Lifes [B] Total
Customer Orders 4,520,000 Customer Orders 10,000 $ 452.00 4,000 6,000 10,000
Design changes 3,040,000 Design changes 4,000 $ 760.00 - 0 4,000 4,000
Order Size 5,200,000 Machine Hours 800,000 $ 6.50 480,000 320,000 800,000
Customer Relations 3,080,000 Number of Customers 2,000 $ 1,540.00 Measured Actual Measured Actual From data set given
15,840,000 (6,160,000) Not in ABC Pools 22,000,000 Sum ABC analysis
Excel B
[A] SureStart (a) (b) (a) × (b)
Activity Cost Pools Activity Rate Activity ABC Cost
Customer orders $ 452.00 4,000 $ 1,808,000
Design changes 760.00 - 0 - 0
Order size [object is Product] 6.50 480,000 3,120,000 to Sure start
Total Measured Actual $ 4,928,000 31.1%
5,200,000 Order size
[B] LongLife (a) (b) (a) × (b)
Activity Cost Pools Activity Rate Activity ABC Cost $ 4,928,000
Customer orders $ 452.00 6,000 $ 2,712,000 $ 7,832,000 A B
Design changes 760.00 4,000 3,040,000 $ 12,760,000 36 48 Min
Order size {object is Product} 6.50 320,000 2,080,000 to Long Life 15,840,000 total to objects 800000 400000 Qty
Total Measured Actual $ 7,832,000 49.4% 3,080,000 to objects not to products $ 1,808,000 Customer Orders 28800000 19200000 Min
$ 12,760,000 80.6% total ABC to Products $ 2,712,000 4,520,000 480000 320000
Each Assignable costs $ 3,080,000 to customers $ 4,520,000 100% Hrs Hrs
$ 452.00 Customer Orders 4,520,000 Product A 4,928,000 Order
$ 760.00 Design changes 3,040,000 Product B 7,832,000 # designs 0.00 Design changes
$ 6.50 Order Size 5,200,000 NO 3,040,000 3,040,000
SUM 12,760,000 12,760,000 Cust. Relations 3,040,000 100%
$3,080,000
Cust. basis-Not assignable to Prod.A or B 3,080,000 Product A 480,000 Mach.Hrs. to Products $ 3,120,000 Order Size
Product B 320,000 Mach.Hrs. $ 2,080,000 5,200,000
Rate = $ 6.50 $ 5,200,000 100%
ABC P&L Sure Starts [A] Long Lifes [B] Total From: Excel C
Sales 31,300,000 18,700,000 50,000,000 31,300,000 18,700,000
Direct costs --- Direct to product SureStart LongLife
Direct material 9,000,000 6,000,000 15,000,000 Cost system data from cost system Product line A B Total
Direct labor 7,000,000 5,000,000 12,000,000 Cost system data from cost system Quantity 800,000 400,000 1,200,000 A/seach B/each
Shipping [ Direct to Prod.line] 2,000,000 1,000,000 3,000,000 Each product line has its own warehouse - shipping charges are freight & direct to product lines Sales $31,300,000 $18,700,000 $50,000,000 $39 $47
Subtotal 18,000,000 12,000,000 30,000,000 Given dataÚ
Contrib $s 13,300,000 6,700,000 20,000,000 Varable traced DirectCosts through cost system
Contrib % 42% 36% 40% with ABC costs Material; $9,000,000 $6,000,000 $15,000,000 $11 $15
Activity Pool Costs -Product Related: ABC Assigned DL $7,000,000 $5,000,000 $12,000,000 $9 $13
Order Size-Machine Hours 3,120,000 2,080,000 5,200,000 Shipping $2,000,000 $1,000,000 $3,000,000 $3 $3
Customer Orders 1,808,000 2,712,000 4,520,000 ABC Sum $18,000,000 $12,000,000 $30,000,000 $23 $30
Design changes - 0 3,040,000 3,040,000 ABC
Subtotal 4,928,000 7,832,000 12,760,000 ABC Contribution margin $13,300,000 $6,700,000 $20,000,000 $17 $17
ABC Traceable/Assigned Expenses/Costs 42.5% 35.8% 40.0% 42.5% 35.8%
Product Margin %%% ******** 27% -6% 14%
Product Margin $$$ ******** 8,372,000 (1,132,000) 7,240,000 ********
ABC Unassigned to Product Lines 6,160,000 Unallocated 6,160,000 not in a ABC pool
*** ABC # of Customers 'Customer common to both A & B 3,080,000 ABC/Product çin a pool but not to products
Operating Income [Loss] (2,000,000)
+ Customer Relations 3080000 Number of Customers
*** while can ABC to an activity can't get back to Product
GAAP/Traditional/FAC Sure Starts [A] Long Lifes [B] Total From: Excel D
Sales 31,300,000 18,700,000 50,000,000
Cost of Goods Sold [no ∆ in inventory] No change in inventory Productcost VS. Period costs
Direct material 9,000,000 6,000,000 15,000,000 Cost system Same as ABC
Direct labor 7,000,000 5,000,000 12,000,000 Cost system Same as ABC
Manufacturing 8,400,000 5,600,000 14,000,000 Cost system
Total CoGS 24,400,000 16,600,000 41,000,000 Total manufacturing OH allocated via Mach.Hrs
14,000,000 Product A 480000 60.0%
Gross Profit 6,900,000 2,100,000 9,000,000 800,000 Product B 320000 40.0%
22% 11% 18% $ 17.50 Sum 800,000 100.0%
Mach.Hrs. $3,000,000 Shipping expenses
Selling and administrative expenses 11,000,000 all non-manufacturing expenses $2,000,000 Marketing expenses
$6,000,000 General administrative expenses
Operating Income [Loss] (2,000,000) $11,000,000
Variable/Contribution Sure Starts [A] Long Lifes [B] Total From: Var. Mfg. OH
Sales 31,300,000 18,700,000 50,000,000 Var.Mfg. OH A B
No change in inventory $ 17.50
Variable costs & expenses 45%
Direct material 9,000,000 6,000,000 15,000,000 Cost system Same as GAAP/ABC $ 7.88 480000 320000
Direct labor 7,000,000 5,000,000 12,000,000 Cost system Same as GAAP/ABC 2/3 Product A, 1/3 Product B $ 3,780,000 $ 2,520,000 $7,500 +
Shipping [ Direct to Prod.line] 2,000,000 1,000,000 3,000,000 45% is a Given % $6,700 +
Manufacturing: 45% variable 3,780,000 2,520,000 6,300,000 Cost system Prod. Cost From GAAP 8,400,000 5,600,000 $1,700 +
Variable Sell/Admin 8% of sales 2,504,000 1,496,000 4,000,000 8% is a Given % Commission Sum 45% 45% $15,900 =
Total Variable Costs & expenses 24,284,000 16,016,000 40,300,000 6,300,000 3,780,000 2,520,000
Contribution Margin 7,016,000 2,684,000 9,700,000 Shipping [ Direct to Prod.line] 3,000,000 V
22.4% 14.4% 19.4% 22,000,000 Total overhead costs for allocation Variable Sell/Admin 8% of sales 4,000,000 V
Fixed (6,300,000) Manufacturing: 45% variable General administrative expenses 4,000,000 F
Manufacturing 7,700,000 (4,000,000) Variable Sell/Admin 8% of sales 11,000,000
General administrative expenses 4,000,000 11,700,000 Fixed
V Mfg OH Fxd. Mfg OH Total Mfg OH same as
Operating Income [Loss] (2,000,000) 6,300,000 7,700,000 14,000,000 above
Comparison Sure Starts [A] Long Lifes [B] Total
Gross profit/Gross Margin/Contribution Margin
ABC P&L 8,372,000 (1,132,000) 7,240,000
GAAP/Traditional/FAC 6,900,000 2,100,000 9,000,000
Variable/Contribution 7,016,000 2,684,000 9,700,000
% of Sales
ABC P&L 26.7% -6.1% 14.5% Product Margin
GAAP/Traditional/FAC 22.0% 11.2% 18.0% Gross Profit
Variable/Contribution 22.4% 14.4% 19.4% Contrib.Margin
% of Total
ABC P&L 115.6% -15.6% 100.0%
GAAP/Traditional/FAC 76.7% 23.3% 100.0%
Variable/Contribution 72.3% 27.7% 100.0%
back 42

HCT---&P of &N---&D,&T---&F,&A

Business Study to determine units

an objection to ABC is forcing a distribution

an objection to ABC is treating fixed as variable

Given data

Given data

HW Ch 7

ACC220 HW Ch.7 ABC For Both ABC P&L AND for Var., Cost P&L
Rider University Direct Materials Traced
HCT--Ch.7.---ABC Direct Labor Direct Materials
ClassCo Variable Mfg. OH Direct Labor
Income Statement {FAC} Variable Selling Expense[not Shipping] Variable Mfg. OH
Variable Shipping Variable Selling Expense[not Shipping]
Sales $75,000 Direct tracable to Product A or B Variable Shipping
12,000 10,000
traced Direct Materials $20,000 materials per unit below 640 640
traced Direct Labor $10,000 spread this based on Hours [computed with info below] 18.75 15.625
traced Variable Mfg. OH $12,000 spread this based on Hours [computed with info below]
Through ABC Fixed. Mfg. OH $13,000 use DL hrs for Fxd. Mfg. OH to product under FAC
Total CoGS $55,000
Gross Profit $20,000
Variable Selling Expense[not Shipping] $3,000 Sum % of sales traced
Variable Shipping $4,000 7,000 9.33% traced
Through ABC Fixed Selling & Admin & R&D $11,000
Total Operating Expense $18,000
Operating Income 2,000
Toital $ Direct Labor
total QTY Variable Mfg. OH
CoGS & Expenses Direct Indirect Rate Hrs
Direct Materials 20,000 Traced through Cost System to products QTY sold
Direct Labor 10,000 Traced through Cost System to products
Variable Mfg. OH 12,000 10,000
Fixed. Mfg. OH 13,000 12,000
Variable Selling Expense[not Shipping] 3,000 sum of both 13,000
Variable Shipping 4,000 7,000 35,000
Fixed Selling & Admin & R&D 11,000
Only two products so all of sales/Cogs ARE THESE TWO PRODUCTS
Product A sell price each = $ 30.00 Sales $
DL Hrs. unit 0.20
Units sold 1,100 $ 33,000.00 220.00
Materials per unit = $ 5.00 Total hrs.
# orders 70
Product B sell price each = $ 40.00
DL Hrs. unit 0.40 Total hrs. 10,000
Units sold 1,050 $ 42,000.00 420.00 - 0 640.00
Materials per unit = $ 13.81 $ 75,000.00 640.00 15.63
# orders 40 Total company Hrs.
12,000
Shipping & Variable selling is % of sales $s 9.33% of sales $ 640.00
7,000 18.75
Cost per DL Hr. = [compute]
customers = 40 40
# projects = 100 100
Departments of Indirect Overhead
Fixed. Mfg. OH
For Production Dept. ZZZ 6,000
For Production Dept. QQQ 7,000 13,000
Fixed Selling & Admin & R&D
Selling 3400
Admin 4000
R&D 3600 11000
[per customer]
Activities► Departments▼ Order Processing # of Orders Products DL Hrs ClassCo Projects # of Projects Maintain Customer # of Customers Sustaining General Unallocated Total
Production Dept. ZZZ 10% 60% 15% 10% 5% 100%
For Production Dept. QQQ 20% 50% 10% 10% 10% 100%
Selling 20% 20% 20% 25% 15% 100%
Admin 10% 25% 20% 10% 35% 100%
R&D 15% 25% 35% 10% 15% 100%
TO DO:
A. Compute activity pools amounts & Cost per activity Unit [need to compute total # of Hrs.]
B. Customer BBB in the period placed two orders for [in total]
bought 100 Product A & 120 Product B
Profit for Cust. BBB using FAC & ABC costing
C. Profit by product & Total company using, Variable & ABC

Sheet1

SureStarts [A] LongLifes [B] Total
Sales $ 31,300,000 $ 18,700,000 $ 50,000,000
Direct costs
Direct material 9,000,000 6,000,000 15,000,000
Direct labor 7,000,000 5,000,000 12,000,000
Shipping 2,000,000 1,000,000 3,000,000

Sheet2

Sheet3

SureStarts [A]LongLifes [B]Total

Sales31,300,000$ 18,700,000$ 50,000,000$

Direct costs

Direct material9,000,000 6,000,000 15,000,000

Direct labor7,000,000 5,000,000 12,000,000

Shipping2,000,000 1,000,000 3,000,000

Product A: SureStart1.Requires no new design resources.2.800,000 batteries ordered with 4,000 separate orders.

3.Each SureStartrequires 36 minutes of machine

time for a total of 480,000 machine-hours. [480K x $6.50 = $3120K]

Product B: LongLife1.Requires new design resources.2.400,000 batteries ordered with 6,000 separate orders.3.4,000 custom designs prepared.

4.Each LongLiferequires 48minutes of machine

time for a total of 320,000 machine-hours.[320K x $6.50 = $2080K]

The ABC team determined that Baxter Battery will have these total activities for each activity cost pool . . .

10,000 customer orders,

4,000 design changes,

800,000 machine-hours,2,000 customers served.

Given data

Given data