How is an activity rate calculated?

Cost pool divided by driver volume, or by practical capacity when you enter one. Setups costing 186,000 a year spread over 360 setups is 516.67 per setup. A product that needed 56 setups is charged 56 x 516.67 = 28,933 for setups, whatever its labour hours.

What is practical capacity and why does it matter?

It is the driver volume the pool could support, such as staffed inspection hours or practical machine hours. With a 420,000 machining pool and 21,000 practical hours the rate is 20.00 per hour; if products use 17,722 hours, 354,440 is assigned and 65,560 shows as unused capacity, a period cost, instead of making every product look more expensive.

How does the template compute traditional costing?

All overhead in the pools is divided by the total of volume x hours per unit on the base you choose on Setup. In the example 1,200,000 over 24,286 direct labour hours is 49.41 per hour, so a product with 3.70 labour hours per unit carries 182.82 of overhead per unit.

Why is the plant margin the same under both methods?

Both methods charge the same total overhead to the same revenue; ABC only changes which products carry it, and shows any unused capacity separately. In the example plant margin is 31.6% either way, while individual product margins move by up to 33 points.

Which cost drivers should I use?

Pick the count that makes each activity's workload grow and that you can get from your systems: setups or production orders from the ERP, purchase orders from purchasing, inspection or engineering hours from timesheets, machine hours from the MES, shipments from despatch. A rough but honest count is usually better than spreading everything on labour hours.

Is there a CSV version?

Yes. The CSV with live formulas holds the Products table with the example rows, units in every header and the settings the formulas read listed below the data, so it opens in any spreadsheet app. Direct cost, traditional overhead, both unit costs, the gap, both margins and the status are live formulas against the plantwide rate and margin target listed below the data; ABC overhead per unit is a typed value because it sums the Driver Usage sheet. The dashboard, charts, dropdowns and self-check are in the .xlsx. Excel in a locale that uses semicolons or non-English function names may need Data > From Text/CSV; the .xlsx works everywhere.

Finance and Investment

Activity-Based Costing Excel Template

Assign overhead to products by the activities they actually use, compare it with a single plantwide rate, and see which products are really profitable.

Overview

This activity-based costing template shows what a single overhead rate hides. It ships with a worked example for a fictional valve maker: eight product families from a high-volume DN25 ball valve to a cryogenic special made 240 a year, seven activity cost pools and a year's budget. On labour hours the cryogenic valve looks like the best product in the range at a 47% margin; charged for the setups, purchase orders, inspection and engineering it really uses, its margin is under 15%, while the runners turn out better than they looked.

The method is the classic one. Overhead is split into activity pools such as machine setups, purchasing, scheduling, inspection, engineering, machining and shipping. Each activity has a cost driver, the thing that makes its workload grow, and a rate: the pool divided by the driver volume, or by the practical capacity when you give one, so the cost of capacity nobody used stays visible as unused capacity instead of loading the products that happen to be made. Each product is then charged its driver quantities times the rates.

The traditional view is computed alongside: all overhead divided by the total labour or machine hours, charged to each product by its hours per unit. The Dashboard compares the two product by product: unit cost, margin against your target, and the overhead each method assigns. Plant margin is the same under both methods; ABC moves overhead between products, which is exactly what pricing, product mix and batch-size decisions need to see.

What this template includes

Setup for the company, period, currency, the traditional allocation base to compare with (direct labour hours or machine hours) and your margin target
Activities table: each overhead cost pool with its cost driver and optional practical capacity; driver volume, activity rate, cost assigned to products, unused capacity and share of overhead
Products table: volume, price, material, direct labour and hours per unit; ABC and traditional overhead per unit, both unit costs, the gap between them, both margins and a margin status in words
Driver Usage table: one row per product and activity with the driver quantity, the activity rate and the cost it assigns
Check columns that name products or activities missing from their lists, pairs entered twice, activities with no usage and use above practical capacity
Dashboard tiles for overhead in the pools, the lowest product margin, products below target, the product the single rate under-costs most, revenue, plant margin, the plantwide rate and unused capacity
Two charts: margin by product under both methods against the target, and the overhead each method gives every product
Tables by product and by activity, plus Method and Verify sheets with the Cost Center Rate calculator's example run through the workbook

Suggested use case

Use it when one overhead rate on labour or machine hours no longer tells you which products make money: a mix of high-volume runners and low-volume specials, customer-specific products, or a pricing review.

How to use it

On Setup, type your company, period and currency, choose the traditional allocation base you use today and set your margin target.
On Activities, list each overhead cost pool with its cost driver, and its practical capacity if you know it.
On Products, type each product's volume, price, material, direct labour and hours per unit for the same period.
On Driver Usage, type how many driver units each product used: setups, purchase orders, inspection hours, machine hours, shipments.
Fix anything the Check columns name, then open the Dashboard to compare margins under both methods.

Frequently Asked Questions

How is an activity rate calculated?: Cost pool divided by driver volume, or by practical capacity when you enter one. Setups costing 186,000 a year spread over 360 setups is 516.67 per setup. A product that needed 56 setups is charged 56 x 516.67 = 28,933 for setups, whatever its labour hours.
What is practical capacity and why does it matter?: It is the driver volume the pool could support, such as staffed inspection hours or practical machine hours. With a 420,000 machining pool and 21,000 practical hours the rate is 20.00 per hour; if products use 17,722 hours, 354,440 is assigned and 65,560 shows as unused capacity, a period cost, instead of making every product look more expensive.
How does the template compute traditional costing?: All overhead in the pools is divided by the total of volume x hours per unit on the base you choose on Setup. In the example 1,200,000 over 24,286 direct labour hours is 49.41 per hour, so a product with 3.70 labour hours per unit carries 182.82 of overhead per unit.
Why is the plant margin the same under both methods?: Both methods charge the same total overhead to the same revenue; ABC only changes which products carry it, and shows any unused capacity separately. In the example plant margin is 31.6% either way, while individual product margins move by up to 33 points.
Which cost drivers should I use?: Pick the count that makes each activity's workload grow and that you can get from your systems: setups or production orders from the ERP, purchase orders from purchasing, inspection or engineering hours from timesheets, machine hours from the MES, shipments from despatch. A rough but honest count is usually better than spreading everything on labour hours.
Is there a CSV version?: Yes. The CSV with live formulas holds the Products table with the example rows, units in every header and the settings the formulas read listed below the data, so it opens in any spreadsheet app. Direct cost, traditional overhead, both unit costs, the gap, both margins and the status are live formulas against the plantwide rate and margin target listed below the data; ABC overhead per unit is a typed value because it sums the Driver Usage sheet. The dashboard, charts, dropdowns and self-check are in the .xlsx. Excel in a locale that uses semicolons or non-English function names may need Data > From Text/CSV; the .xlsx works everywhere.