Costing & FinanceFree worksheetPDF, Excel, Word exportDiscrete Manufacturing

Overhead Absorption Rate Setting Worksheet

A predetermined overhead rate spreads budgeted indirect cost across the activity that drives it, so jobs and products carry overhead before the month closes. This worksheet sets the rate per pool, applies it to actual activity and shows how far applied overhead drifts from what you actually spent.

Download:
Overhead poolAbsorption basisBudgeted overheadBudgeted activityPredetermined rateActual overheadActual activityApplied overheadOver / (under) absorbed
60
$519,000
-$42,000
20
$318,000
$20,000
20
$90,000
-$11,500
0.05
$115,500
-$2,500
7
$86,800
$4,300
Total budgeted overhead
$1,150,000
Total actual overhead
$1,161,000
Total applied overhead
$1,129,300
Net over / (under) absorbed
-$31,700
Net variance as percent of actual overhead
-2.7%
Common review trigger: rates drifting beyond a few percent usually need re-setting.

Rates are in currency per unit of the chosen basis. Positive variance means overhead was over absorbed (applied more than incurred). Do not mix bases inside one row: the activity columns must be in the unit named by the basis.

Overhead rate review from your ERP data

Optional: we can show how to feed actual overhead and activity from your ERP into this review automatically.

Optional. The tool and every export work without this. No spam.

How to use the worksheet

Create one row per overhead pool: machining, assembly, stores and so on. Enter the budgeted overhead and the budgeted activity for the same period, usually the fiscal year. The rate column divides one by the other. Then enter actual overhead and actual activity for the period being reviewed. Applied overhead is the rate times actual activity, and the last column compares it to actual spending. Export to Excel to keep a version per quarter, which makes rate re-setting decisions easy to defend to auditors and the plant manager.

The method behind it

Predetermined rate = budgeted overhead divided by budgeted activity. Applied overhead = rate times actual activity. The difference from actual overhead is under or over absorption. Under absorption means jobs were costed too low, so cost of sales is understated until the variance is cleared. Choose a basis that actually correlates with cost: machine hours for capital heavy cells, direct labor hours for hand assembly, material cost for purchasing and stores.

  • Use separate pools when cost behaviour differs strongly.
  • Set budgeted activity at normal or practical capacity, not best case.
  • Review rates at least twice a year in volatile businesses.

Reading the result

In the example, CNC machining is under absorbed because machine hours fell short of plan while fixed overhead stayed put. Assembly is over absorbed because labor hours beat plan. A net variance inside a few percent of actual overhead is usually closed straight to cost of sales. A larger or persistent variance should be prorated across work in process, finished goods and cost of sales, or the rates should be reset.

Common mistakes and ERP notes

The usual errors are budgeting activity at 100 percent capacity, mixing indirect labor that is already in a pool into the basis, and forgetting that rates need re-setting after a big volume shift. In most ERPs the rate lives on the work center or cost center record, so changing it only affects new transactions. Plan the rate change at a period boundary and document the effect on inventory valuation.

Frequently Asked Questions

What is a good overhead absorption basis?

Pick the driver with the strongest link to the cost. Machine hours suit automated cells, direct labor hours suit manual assembly, and material cost suits purchasing and handling pools. If you cannot tell, test two bases against several months of actual overhead and keep the one with the tighter correlation.

Should I use one plant-wide rate or several?

Several rates give more accurate product cost when processes differ, for example heavy machining versus light assembly. A single plant-wide rate is simpler but can subsidise machine-intensive products. Move to departmental rates when product mix differs markedly across departments.

How do I treat under absorbed overhead at month end?

If the amount is small, expense it to cost of sales in the period. If it is material, allocate it across work in process, finished goods and cost of sales in proportion to the overhead already applied. Follow your accounting policy and auditor guidance.

How often should predetermined rates be reset?

Most manufacturers set them annually with the budget. Reset mid-year when volume, cost structure or product mix changes enough that the net variance stays large for several consecutive months. Document each reset with its inventory impact.

If your overhead rates sit in several systems and spreadsheets, Netray can help pull actual activity and cost center spend straight from your ERP so this review runs on live numbers.

Talk to an ExpertExplore More Free Tools

Related Tools

Costing & Finance

Machine Hour Rate Build-Up Worksheet

Calculate a fully loaded machine hour rate per machine from depreciation, power, maintenance, tooling and floor space.

Costing & Finance

Overhead Under and Over Absorption Calculator

Find under or over absorbed overhead for the period and see how much comes from spending versus volume.

Costing & Finance

Service Cost Center Allocation Worksheet

Spread maintenance, quality, stores and other service costs to production lines using a driver, with leftover cost shown.

Costing & Finance

Standard Cost Change Approval Memo Template

Record why standard costs are changing, what the inventory revaluation effect is, and who approved the roll.

More Costing & Finance tools

See all
Back to All Tools