Overhead Cost Pool Allocation Worksheet
A single plant-wide overhead rate hides which departments cost what. This worksheet splits overhead into pools, picks an allocation base for each and shows how much overhead your jobs actually absorbed. Edit the rows and export to Excel for the month-end pack.
| Cost pool | Allocation base | Budgeted annual cost | Budgeted base quantity | Rate per base unit | Actual cost | Actual base quantity | Applied overhead | Over (+) or under (-) absorbed | |
|---|---|---|---|---|---|---|---|---|---|
90 | $513,000 | -$43,000 | |||||||
20 | $218,000 | $13,000 | |||||||
0.06 | $92,400 | -$6,600 | |||||||
0.1667 | $133,333 | $5,333 |
Rate equals budgeted cost divided by budgeted base. Applied overhead equals that rate times actual base. A negative variance means jobs absorbed less than the plant spent. Use a base that actually drives the cost in each pool.
Get overhead variance from your ERP monthly
Optional: see how the same pool variance can be reported from live ERP data.
Optional. The tool and every export work without this. No spam.
How to use the worksheet
Create a row for each cost pool, such as machining, assembly, stores and quality. Choose the base that best drives the cost: machine hours for equipment-heavy cells, direct labor hours for hand assembly, material cost for purchasing and stores. Enter the budgeted annual cost and base, which sets the rate. Later enter actuals to see applied overhead and the variance. Use the totals to reconcile with the general ledger.
- Use budgeted base capacity that you realistically expect to run
- Keep pools homogeneous so the base fits the cost
- Update rates when the budget changes materially
The method behind it
This is predetermined overhead absorption. The rate equals budgeted overhead divided by the budgeted allocation base. Jobs are charged at that rate times the actual base they consume. At period end, the difference between applied and actual overhead is over or under absorbed. Unlike a single rate, multiple pools reflect that a machining hour and an assembly hour do not cost the same. It is a simplified relative of activity based costing.
Reading the result
In the example, CNC machining ran fewer hours than budget while its spend rose, so it under absorbed. Assembly ran more hours than planned and over absorbed. Persistent variance in one pool signals a wrong rate, wrong base or capacity problem. Small variances are usually written to cost of goods sold at period end, while large ones may be prorated across inventory and cost of sales according to your accounting policy and local rules.
Common mistakes and ERP notes
Common faults are mixing fixed and variable cost in one pool without realizing it, choosing labor hours for an automated cell, and never revisiting the budget. In SAP this maps to cost centers, activity types and overheads costing sheets; in other ERPs it is a burden rate on a department or work center. Check how your system applies the rate to routing times, since standard hours and actual hours may differ.
Frequently Asked Questions
How many cost pools should I use?
Start with the departments or work center groups that differ in cost behavior, usually three to six. More pools add accuracy only if the base meaningfully differs. Too many pools create maintenance work without improving decisions.
What is under absorbed overhead?
It means applied overhead was less than actual overhead spend, often because volume or hours came in below the budget used to set the rate. Most companies adjust it through cost of goods sold or prorate it, per policy.
Which allocation base is best?
The one that best explains why the cost occurs. Machine hours suit machine-driven cost, labor hours suit manual work, and material cost suits handling and purchasing. Test by checking whether the pool cost rises when the base does.
Is this the same as activity based costing?
It is a simpler cousin. Pools with different bases capture some of the benefit, but full activity based costing uses more drivers tied to specific activities. For many job shops, pooled rates are sufficient and easier to maintain.
If you want overhead variance by pool pulled from your ERP each month, we can help build a read-only report that shows the numbers and the query behind them.
Related Tools
SAP Overhead Rate and Absorption Calculator
Compute an overhead surcharge rate from planned overhead and base, then see over or under absorption when the actual base moves.
Costing & FinanceStandard Cost Change Request Template
Document why a standard cost is changing, the inventory revaluation impact and who approved it before the roll-up runs.
Costing & FinancePurchase Price Variance Analysis Worksheet
List received parts with standard and purchase price to see PPV in currency and percent, and which items drive it.
Costing & FinanceEstimate Review and Approval Form Template
A one-page gate that records cost, margin, risks and approvals before a manufacturing quote is released.
More Costing & Finance tools
See all- Manufacturing KPI Formula and Definitions Reference
- Manufacturing Cost Variance Types Reference
- Price, Volume and Cost Margin Variance Calculator
- Month End Reporting Readiness Checklist for Manufacturers
- OEE Improvement Value Calculator
- Improve OEE vs Buy a New Machine Calculator
- Six Sigma Project Savings Tracker Worksheet
- Should Cost Model Worksheet for Machined Parts
- Quote Assumptions and Exclusions Template