Activity-based costing for manufacturers: from spreadsheet to system
The five-step method, where it breaks down, and when you need a system
Activity-based costing assigns overhead to the activities that actually consume it. Instead of spreading all overhead by labor hours, it asks: what activities does each product consume, and what does each activity cost?
Most manufacturers under $100M still use a single plantwide rate. The math is simple, but it hides cross-subsidies. A high-volume product absorbs overhead it did not cause. A low-volume product hides the setup and handling costs it drove. (Why a single overhead rate distorts margins →)
What is activity-based costing?
Activity-based costing traces overhead to activities — setup, inspection, material handling, machine operation — then assigns those costs to products based on actual consumption.
Traditional costing | Activity-based costing | |
|---|---|---|
Cost pools | One or two | One per major activity |
Allocation base | Volume-based (labor hours) | Activity drivers (setups, lots, moves) |
Accuracy | Fine when products are similar | Better when products differ in complexity |
How to implement ABC — five steps
Most teams start in a spreadsheet. The method is the same whether you run it manually or inside a system.
1. List five to eight major activities. Walk the floor. Common ones: machine operation, setup, quality inspection, material handling, scheduling. Keep the list short — every pool needs monthly driver data.
2. Assign overhead to each pool. Split your GL overhead across the activities. A production supervisor who oversees machining and QC gets split 60/40. Precision to the dollar is not the goal. Directionally correct is.
3. Pick a cost driver for each activity. The driver must correlate with the cost and be countable. Machine hours for machine operation. Number of setups for setup costs. Number of inspection lots for QC. If you cannot count it, it is not a driver.
4. Compute the activity rate. Pool cost ÷ total driver quantity for the period.
Activity | Pool cost | Driver qty | Rate |
|---|---|---|---|
Machine operation | $290,000 | 8,700 hrs | $33.33/hr |
Setup | $85,000 | 420 setups | $202.38/setup |
Inspection | $66,000 | 1,100 lots | $60.00/lot |
Handling | $50,000 | 2,000 moves | $25.00/move |
5. Allocate to products. Multiply each product's driver consumption by the activity rate.
Product A (high-vol) | Product B (low-vol, complex) | |
|---|---|---|
Machine hours × $33.33 | $99,990 | $26,664 |
Setups × $202.38 | $2,429 | $17,202 |
Lots × $60.00 | $3,000 | $12,000 |
Moves × $25.00 | $2,500 | $10,000 |
Total overhead | $107,919 | $65,866 |
Units produced | 50,000 | 5,000 |
Per unit | $2.16 | $13.17 |
Under a single rate, Product B absorbs only $26,664. ABC reveals its true load is $65,866 — driven by 85 setups and 200 inspection lots the plantwide rate ignored.
Where the spreadsheet breaks down
The five-step method works for one quarter. Keeping it running is where teams struggle.
Driver data goes stale. Setup counts, lot counts, and machine hours need refreshing every period. In a spreadsheet, someone has to pull and paste them manually. By month three, most teams stop updating.
Rate recalculation falls behind. Wages, volumes, and processes shift. A rate computed in January is wrong by April. Without automated recalculation, the allocations drift from reality — and no one notices until the close.
Product mix changes break the model. Add a new product line or discontinue one, and every driver quantity and rate needs rebuilding. In a spreadsheet, that is a full afternoon. In a system with stored activity drivers, it is a settings change.
Thirteen cost pools need thirteen driver decisions. Real ABC is not one allocation — it is a separate driver choice for each cost pool (depreciation, utilities, labor, handling, and so on). In a spreadsheet, every pool is another sheet, another formula chain, another place for a broken link. In a system with configurable pools and stored drivers, each pool gets its own driver from a dropdown and the allocation runs in one pass.
The spreadsheet is the right starting point. It proves the concept and reveals the first cross-subsidies. But if ABC is going to survive past the first quarter and actually drive decisions at every close, it needs a system that recalculates automatically.
Common mistakes
Too many pools. Twenty pools means twenty data series. Within three months, half are stale. Start with five. Add one only when a decision would change.
Labor hours as the driver for everything. Labor hours are easy to count, so they become the default for all pools. That just rebuilds the plantwide rate with extra steps.
Never updating rates. Recalculate quarterly at minimum. The first model will be wrong — run it for one quarter, compare to your existing costing, and refine.
Quick reference
Step | What you need |
|---|---|
List activities | Floor walk + GL review |
Assign costs | GL detail by account |
Pick drivers | Production data (setups, hours, moves) |
Compute rates | One period of driver data |
Allocate | Driver qty per product × rate |
Minimum viable data: one quarter of production records with setup counts, machine hours, and lot counts by product.
Numen Books runs activity-based allocation with stored cost drivers, automated rate recalculation, and 13 configurable cost pools — so ABC survives past the first quarter. Try it on sample data. No card required.
See activity-based allocation in action
Get FP&A insights validated on the floor — delivered every Tuesday morning.
