Chapter 4: Using Excel to Make Decisions at Current Designs Part 1
Data
Activity Cost
Designing new models
$ 121,100
Creating and testing prototypes
152,000
Creating molds for kayaks
188,500
Operating oven for the rotomolded kayaks
40,000
Operating the vacuum line for the composite kayaks
28,000
Supervising production employees
180,000
190,400
Total $ 900,000
Composite
Kayaks
Rotomolded
Kayaks
Designing new models Number of models 3 1
Creating and testing prototypes Number of prototypes 6 2
Creating molds for kayaks Number of molds 12 1
Supervising production employees Number of employees 12 12
Curing time Number of days of curing time 15,000 2,000
Direct labor costs for composite kayaks 234,000$
Direct labor costs for rotomolded kayaks 286,000$
Number of composite kayaks 1,000
Number of rotomolded kayaks 4,000
Percentage of employees and percentage of square feet used:
For composite kayaks 50%
For rotomolded kayaks 50%
Required
Follow the step-by-step directions to determine the cost allocations for Current Designs.
(a) Composite Rotomolded
Directly assigned costs 28,000$ 40,000$
(b) Composite Rotomolded
Directly assigned costs 28,000$ 40,000$
Remaining cost allocation:
Curing time (the time that is needed for the chemical processes to
finish before the next step in the production process; many of
these costs are related to the space required in the building)
Activity
Cost Driver
Driver Amount for
(c )
Activity Cost Pools
Estimated
Overhead
Cost
Expected
Use of Cost
Drivers
Design 121,100$ 4 30,275$ per model
Prototypes 152,000$ 8 19,000$ per prototype
Activity Cost Pools
Expected
Use of Cost
Drivers
Overhead
Rates
Cost
Assigned
Expected
Use of Cost
Drivers
Overhead
Rates
Cost
Assigned
Design 3 30,275$ 90,825$ 1 30,275$ 30,275$
Prototypes 6 19,000$ 114,000 2 19,000$ 38,000
(d)
Composite
Rotomolded
Activity based costing assigns significantly more costs to the composite kayaks. Since the cost is divided into
pools and each pool is allocated using a cost driver that is related to those particular costs, it provides a more
Activity-Based Overhead
Rates
Chapter 4: Using Excel to Make Decisions at Current Designs Part 2a
Data
Activity Cost
Designing new models
$ 121,100
Creating and testing prototypes
152,000
Creating molds for kayaks
188,500
Operating oven for the rotomolded kayaks
40,000
Operating the vacuum line for the composite kayaks
28,000
Supervising production employees
180,000
190,400
Total $ 900,000
Composite
Kayaks
Rotomolded
Kayaks
Designing new models Number of models 3 1
Curing time Number of days of curing time 15,000 2,000
Direct labor costs for composite kayaks 234,000$
Direct labor costs for rotomolded kayaks 286,000$
Number of composite kayaks 1,000
Number of rotomolded kayaks 4,000
Number of employees percentage:
For composite kayaks 50%
For rotomolded kayaks 50%
Required
Follow the step-by-step ‘what-if’ directions to determine the cost allocations for Current Designs.
Activity Cost Pools
Estimated
Overhead
Cost
Expected
Use of Cost
Drivers
Design 121,100$ 4 30,275$ per model
Activity Cost Pools
Expected
Use of Cost
Drivers
Overhead
Rates
Cost
Assigned
Expected
Use of Cost
Drivers
Overhead
Rates
Cost
Assigned
Design 3 30,275$ 90,825$ 1 30,275$ 30,275$
Composite
Rotomolded
Curing time (the time that is needed for the chemical processes to
Activity
Cost Driver
Driver Amount for
Activity-Based Overhead
Rates
Prototypes 45 3,234$ 145,532 2 3,234$ 6,468
Molds 12 14,500$ 174,000 1 14,500$ 14,500
What-if?
Part 2a Composite Rotomolded
Chapter 4: Using Excel to Make Decisions at Current Designs Part 2b
Data
Activity Cost
Designing new models
$ 121,100
Operating oven for the rotomolded kayaks
40,000
Operating the vacuum line for the composite kayaks
28,000
Supervising production employees
180,000
190,400
Total $ 1,000,000
Composite
Kayaks
Rotomolded
Kayaks
Designing new models Number of models 3 1
Creating and testing prototypes Number of prototypes 6 2
Direct labor costs for composite kayaks 234,000$
Direct labor costs for rotomolded kayaks 286,000$
Number of composite kayaks 1,000
Number of rotomolded kayaks 4,000
Number of employees percentage:
For composite kayaks 50%
For rotomolded kayaks 50%
Required
Follow the step-by-step ‘what-if- directions to determine the cost allocations for Current Designs.
(c )
Activity Cost Pools
Estimated
Overhead
Cost
Expected
Use of Cost
Drivers
Design 121,100$ 4 30,275$ per model
Prototypes 252,000 8 31,500$ per prototpe
Activity Cost Pools
Expected
Use of Cost
Drivers
Overhead
Rates
Cost
Assigned
Expected
Use of Cost
Drivers
Overhead
Rates
Cost
Assigned
Design 3 30,275$ 90,825$ 1 30,275$ 30,275$
Composite
Rotomolded
Curing time (the time that is needed for the chemical processes to
Activity
Cost Driver
Driver Amount for
Activity-Based Overhead
Rates
252,000
188,500
Prototypes 6 31,500$ 189,000 2 31,500$ 63,000
Molds 12 14,500$ 174,000 1 14,500$ 14,500
Supervision 12 7,500$ 90,000 12 7,500$ 90,000
Curing time 15,000 11.20$ 168,000 2,000 11.20$ 22,400
Total amount allocated 711,825 220,175
Part 2b Composite Rotomolded
Cost allocation 189,000$ 63,000$