Absorption Costing v Activity Based Costing
You have been provided with the following information about two products produced by Bright Ltd:
Pink Purple
Selling price per unit £60 £100
Annual sales volume (units) 10,000 8,000
Number of sales invoices issued each year 400 1,000
Labour time per unit (hours) 3 4
Labour rate per hour £8 £10
Material cost per unit £20 £35
Size of each production batch 500 400
Bought-in parts per unit 3 2
Machine set-ups per batch 4 3
The finance director of Bright Ltd has recently produced the following analysis of overheads and their relevant cost dr
Type of overhead Cost driver
Bought-in parts handlings costs Number of bought-in parts £60,000
Materials handling costs Number of production batches £20,000
Sales invoicing costs Number of invoices issued £10,000
Machine set-up costs Number of machine set-ups £4,000
All other overheads Labour hours £100,000
Total overhead costs £194,000
You are required to:
Calculate the total cost per unit, profit per unit and profit margin for each product using the methods below. Calculat
a) The traditional full cost method of overhead absorption (based on labour hours).
b) The activity-based costing method.
a) Traditional full cost method:
Pink Purple Total
Total labour hours 30,000 32,000 62,000
OH rate based on labour hours £3.13 per direct labour hour
Cost per unit: Pink Purple
Direct materials £20.00 £35.00
Direct labour £24.00 £40.00
Overheads £9.39 £12.52
Total cost per unit £53.39 £87.52
Pink Purple
Selling price per unit £60.00 £100.00
Profit per unit £6.61 £12.48
Profit margin 11.02% 12.48%
b) Activity-based costing method:
Cost driver Cost driver
volume: Pink
Bought-in parts handlings costs Number of bought-in parts 30,000
Materials handling costs Number of production batches 20
Sales invoicing costs Number of invoices issued 400
Machine set-up costs Number of machine set-ups 80
All other overheads Labour hours 30,000
Direct cost per unit: Pink Purple
Direct materials £20.00 £35.00
Direct labour £24.00 £40.00
Total direct cost per unit £44.00 £75.00
Overhead per unit: Pink Purple
Bought-in parts handlings costs £3.91 £2.61
Materials handling costs £1.00 £1.25
Sales invoicing costs £0.29 £0.89
Machine set-up costs £0.23 £0.21
All other overheads £4.84 £6.45
Total overhead per unit £10.27 £11.42
Total cost per unit £54.27 £86.42
Pink Purple
Price per unit £60 £100
Profit per unit £5.73 £13.58
Profit margin 9.56% 13.58%
and their relevant cost drivers:
methods below. Calculate the profit for both methods.
Cost driver Total cost Total OH cost Driver rate Total OH cost: Total OH cost:
volume: Purple driver volume Pink Purple
+ 16,000 = 46,000 £60,000 £1.30 £39,130.43 £20,869.57
+ 20 = 40 £20,000 £500.00 £10,000.00 £10,000.00
+ 1,000 = 1,400 £10,000 £7.14 £2,857.14 £7,142.86
+ 60 = 140 £4,000 £28.57 £2,285.71 £1,714.29
+ 32,000 = 62,000 £100,000 £1.61 £48,387.10 £51,612.90
Learning outcomes:
1. To be able to distinguish between different types of costing and the different results
2. To understand the limitations of absorption costing
3. To understand the complexity of activity based costing
4. To identify different types of cost drivers in different business sectors
5. To introduce the concept of the profit margin
6. To be able to design a complex worksheet whilst using standard formula
Our Microsoft Excel video tutorials can provide help with completing our Excel Project assignments.
For the purpose of grading the project you are required to perform the following tasks:
Step
1
2
5
6
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Learning outcomes:
1. To be able to distinguish between different types of costing and the different results
2. To understand the limitations of absorption costing
3. To understand the complexity of activity based costing
4. To identify different types of cost drivers in different business sectors
5. To introduce the concept of the profit margin
6. To be able to design a complex worksheet whilst using standard formula
Our Microsoft Excel video tutorials can provide help with completing our Excel Project assignments.
For the purpose of grading the project you are required to perform the following tasks:
Instructions
Start Excel – completed.
In cell D33, by using cell references, calculate the total labour hours for Pink. Copy cell D33 and paste it into cell E33.
In cell F33, by using cell references, calculate the total labour hours.
Note: Use cell references to total labour hours for Pink and Purple calculated in Step 2.
In cell D35, by using cell references, calculate the OH rate based on labour hours.
Note: Use cell references to the total labour hours from Step 3 and to the total overhead costs from the data table in the prob
In cell D38, by using a cell reference, determine the direct materials cost per unit for Pink. Copy cell D38 and paste it into cell E
In cell D39, by using cell references, calculate the direct labour cost per unit for Pink. Copy cell D39 and paste it into cell E39.
In cell D40, by using absolute and relative cell references, calculate the overheads cost per unit for Pink. Copy cell D40 and pa
Note: Use cell reference to the OH rate based on labour hours from Step 4.
In cell D41, by using cell references, calculate the total cost per unit for Pink. Copy cell D41 and paste it into cell E41.
Note: Use cell references to the costs amounts previously calculated in part (a).
In cell D44, by using a cell reference, determine the selling price per unit for Pink. Copy cell D44 and paste it into cell E44.
In cell D45, by using cell references, calculate the profit per unit for Pink. Copy cell D45 and paste it into cell E45.
Note: Use cell references to the total cost per unit and to the selling price per unit from Step 8 and Step 9.
In cell D46, by using cell references, calculate the profit margin for Pink. Copy cell D46 and paste it into cell E46.
Note: Use cell references to the selling price per unit and to the profit per unit from Step 9 and Step 10.
In cell G50, by using cell references, calculate the number of bought-in-parts for Pink.
In cell I50, by using cell references, calculate the number of bought-in-parts for Purple.
In cell G51, by using cell references, calculate the number of production batches for Pink.
In cell I51, by using cell references, calculate the number of production batches for Purple.
In cell G52, by using a cell reference, determine the number of invoices issued for Pink.
In cell I52, by using a cell reference, determine the number of invoices issued for Purple.
In cell G53, by using cell references, calculate the number of machine set-ups for Pink.
Note: Use cell reference to the number of production batches from Step 14.
In cell I53, by using cell references, calculate the number of machine set-ups for Pink.
Note: Use cell reference to the number of production batches from Step 15.
In cell G54, by using cell references, calculate the labour hours for Pink.
Note: Use cell reference to the corresponding value calculated in part (a).
In cell I54, by using cell references, calculate the labour hours for Purple.
Note: Use cell reference to the corresponding value calculated in part (a).
In cell K50, by using cell references, calculate the total number of bought-in parts. Copy cell K50 and paste it into cells K51:K5
Note: Use only cell references to the values calculated in part (b).
In cell L50, by using a cell reference, determine the total OH bought-in parts handlings costs for two products. Copy cell L50 an
In cell M50, by using cell references, calculate the driver rate for bought-in parts handlings costs. Copy cell M50 and paste it in
Note: Use only cell references to the values calculated in part (b).
In cell N50, by using cell references, calculate the total OH bought-in parts handlings costs for Pink. Copy cell N50 and paste it
Note: Use cell reference to the number of bought-in-parts and to the driver rate from Step 12 and Step 24.
In cell O50, by using cell references, calculate the total OH bought-in parts handlings costs for Purple. Copy cell O50 and paste
Note: Use cell reference to the number of bought-in-parts and to the driver rate from Step 13 and Step 24.
In cells D57 and D58, by using cell references, determine the direct materials and labour costs per unit for Pink. Copy cells D57
and E58.
Note: Use cell references to cells calculated in part (a).
In cell D59, by using cell references, calculate the total direct cost per unit for Pink. Copy cell D59 and paste it into cell E59.
Note: Use cell reference to the values previously calculated in part (b).
In cell D62, by using absolute and relative cell references, calculate the overhead per unit for bought-in parts handlings costs f
into cells D63:D66.
Note: Use cell reference to the total OH bought-in parts handlings costs from Step 25.
In cell E62, by using absolute and relative cell references, calculate the overhead per unit for bought-in parts handlings costs f
into cells E63:E66.
Note: Use cell reference to the total OH bought-in parts handlings costs from Step 26.
In cell D67, by using cell references, calculate the total overhead per unit for Pink. Copy cell D67 and paste it into cell E67.
Note: Use cell reference to the values previously calculated in part (b).
In cell D68, by using cell references, calculate the total cost per unit for Pink. Copy cell D68 and paste it into cell E68.
Note: Use cell reference to the total direct cost per unit and total overhead per unit from Step 28 and Step 31.
In cell D71, by using a cell reference, determine the price per unit for Pink. Copy cell D71 and paste it into cell E71.
Note: Use cell reference to the data table in the problem statement.
In cell D72, by using cell references, calculate the profit per unit for Pink. Copy cell D72 and paste it into cell E72.
Note: Use cell references to the total cost per unit and to the price per unit from Step 32 and Step 33.
In cell D73, by using cell references, calculate the profit margin for Pink. Copy cell D73 and paste it into cell E73.
Note: Use cell references to the price per unit and to the profit per unit from Step 33 and Step 34.
Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
Excel Project Videos
Points
Possible
0
2
2
2
1
1
1
1
1
1
1
1
2
2
