# Overhead Absorption Rate Setting Worksheet

> Source: https://netray.co/tools/overhead-absorption-rate-setting-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Build predetermined overhead rates per cost pool, apply them to actual activity and read the under or over absorbed balance.

## Category

cost-finance

## Industry

Discrete Manufacturing

## Audience

- Controller
- Cost accountant
- CFO

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Overhead pool

**Key:** pool

**Type:** text

#### Absorption basis

**Key:** basis

**Type:** select

**Options:**

- Direct labor hours
- Machine hours
- Direct labor cost
- Material cost
- Units produced

#### Budgeted overhead

**Key:** budget_oh

**Type:** number

**Format:** currency

#### Budgeted activity

**Key:** budget_base

**Type:** number

**Help:** Hours, cost or units matching the basis

#### Predetermined rate

**Key:** rate

**Type:** number

**Expr:** budget_oh / budget_base

**Format:** decimal

#### Actual overhead

**Key:** actual_oh

**Type:** number

**Format:** currency

#### Actual activity

**Key:** actual_base

**Type:** number

#### Applied overhead

**Key:** applied

**Type:** number

**Expr:** budget_oh / budget_base * actual_base

**Format:** currency

#### Over / (under) absorbed

**Key:** variance

**Type:** number

**Expr:** budget_oh / budget_base * actual_base - actual_oh

**Format:** currency

**Rows:**

**Pool:** CNC machining

**Basis:** Machine hours

**Budget oh:** 540000

**Budget base:** 9000

**Actual oh:** 561000

**Actual base:** 8650

**Pool:** Assembly

**Basis:** Direct labor hours

**Budget oh:** 310000

**Budget base:** 15500

**Actual oh:** 298000

**Actual base:** 15900

**Pool:** Finishing and paint

**Basis:** Direct labor hours

**Budget oh:** 96000

**Budget base:** 4800

**Actual oh:** 101500

**Actual base:** 4500

**Pool:** Receiving and stores

**Basis:** Material cost

**Budget oh:** 120000

**Budget base:** 2400000

**Actual oh:** 118000

**Actual base:** 2310000

**Pool:** Test and inspection

**Basis:** Units produced

**Budget oh:** 84000

**Budget base:** 12000

**Actual oh:** 82500

**Actual base:** 12400

**Totals:**

#### Total budgeted overhead

**Key:** budget_oh

**Agg:** sum

**Format:** currency

#### Total actual overhead

**Key:** actual_oh

**Agg:** sum

**Format:** currency

#### Total applied overhead

**Key:** applied

**Agg:** sum

**Format:** currency

#### Net over / (under) absorbed

**Key:** variance

**Agg:** sum

**Format:** currency

**Summary:**

#### Net variance as percent of actual overhead

**Key:** pct_of_actual

**Expr:** sum_variance / sum_actual_oh * 100

**Format:** percent

**Primary:** true

**Description:** Common review trigger: rates drifting beyond a few percent usually need re-setting.

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the worksheet

**Body:** 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

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### What is a good overhead absorption basis?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

overhead absorption rate worksheet, predetermined overhead rate excel, overhead rate calculation template, under absorbed overhead worksheet, manufacturing overhead application, overhead rate by cost pool, burden rate worksheet

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json