# Overhead Cost Center Rate Build-Up Worksheet

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

## Short answer

Add depreciation, indirect labor, facility, tooling and allocated support cost per cost center and get an overhead rate per productive hour.

## Category

cost-finance

## Industry

Discrete Manufacturing

## Audience

- Cost accountant
- Controller
- Estimator
- ERP administrator

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Cost center

**Key:** center

**Type:** text

#### Type

**Key:** kind

**Type:** select

**Options:**

- Machining
- Fabrication
- Finishing
- Assembly
- Inspection

#### Depreciation

**Key:** dep

**Type:** number

**Format:** currency

**Help:** Annual depreciation of equipment in the center.

#### Indirect labor

**Key:** indirect

**Type:** number

**Format:** currency

**Help:** Supervisors, setup leads, material handlers loaded with fringe.

#### Facility and utilities

**Key:** facility

**Type:** number

**Format:** currency

#### Tooling and consumables

**Key:** tooling

**Type:** number

**Format:** currency

#### Allocated support cost

**Key:** support

**Type:** number

**Format:** currency

**Help:** Share of quality, maintenance, planning and office cost assigned to this center.

#### Total overhead

**Key:** total

**Type:** number

**Expr:** dep + indirect + facility + tooling + support

**Format:** currency

#### Productive hours per year

**Key:** hours

**Type:** number

**Format:** number

**Help:** Machine or direct labor hours that actually earn revenue, not the calendar hours.

#### Overhead rate per hour

**Key:** rate

**Type:** number

**Expr:** total / hours

**Format:** currency

**Rows:**

**Center:** CNC Milling

**Kind:** Machining

**Dep:** 96000

**Indirect:** 54000

**Facility:** 31000

**Tooling:** 62000

**Support:** 40000

**Hours:** 5200

**Center:** CNC Turning

**Kind:** Machining

**Dep:** 58000

**Indirect:** 36000

**Facility:** 22000

**Tooling:** 41000

**Support:** 28000

**Hours:** 4600

**Center:** Welding and Fab

**Kind:** Fabrication

**Dep:** 24000

**Indirect:** 42000

**Facility:** 26000

**Tooling:** 18000

**Support:** 22000

**Hours:** 5800

**Center:** Finishing

**Kind:** Finishing

**Dep:** 15000

**Indirect:** 21000

**Facility:** 12000

**Tooling:** 9000

**Support:** 10000

**Hours:** 3100

**Center:** Assembly and Test

**Kind:** Assembly

**Dep:** 9000

**Indirect:** 33000

**Facility:** 18000

**Tooling:** 7000

**Support:** 16000

**Hours:** 6200

**Center:** Inspection

**Kind:** Inspection

**Dep:** 28000

**Indirect:** 20000

**Facility:** 9000

**Tooling:** 3000

**Support:** 12000

**Hours:** 2400

**Totals:**

#### Total depreciation

**Key:** dep

**Agg:** sum

**Format:** currency

#### Total overhead

**Key:** total

**Agg:** sum

**Format:** currency

#### Total productive hours

**Key:** hours

**Agg:** sum

**Format:** number

#### Highest center rate

**Key:** rate

**Agg:** max

**Format:** currency

**Summary:**

#### Blended overhead rate per hour

**Key:** blended

**Expr:** sum_total / sum_hours

**Format:** currency

**Primary:** true

#### Spread between highest and lowest center rate

**Key:** spread

**Expr:** max_rate - min_rate

**Format:** currency

#### Average overhead per center

**Key:** avg_center

**Expr:** sum_total / count

**Format:** currency

**Notes:** Replace the sample centers with your own. Allocate support cost by a driver you can defend, such as headcount, floor area, machine count or work orders. Use productive hours from the last 12 months of job labor or machine history, not 2080 per machine. A single blended rate hides real differences between centers; compare the spread to decide whether separate rates are worth keeping on routings.

## Content

**Intro:** A blended shop overhead rate is easy to build and easy to get wrong. Costs differ widely by cost center: a five-axis mill carries depreciation and tooling that a bench assembly area never sees. This worksheet builds the rate center by center, so quotes and standard costs reflect where the cost really sits.

**Sections:**

#### How to use it

**Body:** List each cost center or work center. For each, enter annual depreciation of its equipment, the loaded cost of indirect labor working there, its share of facility and utilities, tooling and consumables, and an allocation of support departments. Enter the productive hours for the year. The worksheet totals overhead per center and divides by hours to give an overhead rate. Add or delete rows and export to Excel when finished.

#### Choosing allocation drivers

**Body:** Direct costs such as machine depreciation and tooling can be assigned straight to a center. Shared costs need a driver: floor area for rent and utilities, headcount for HR and supervision, machine count or maintenance work orders for maintenance, inspection hours for quality. Whatever you pick, apply it consistently year to year. The direct method used here allocates support cost straight to production centers; a step-down method also allocates between support departments and is worth using when service departments serve each other heavily.

**Bullets:**

- Floor area for rent, heat and property tax.
- Headcount for supervision, HR and safety.
- Work orders or machine count for maintenance and programming.

#### Reading the rates

**Body:** The blended rate is total overhead divided by total productive hours. Compare it to each center's own rate. In the sample data milling runs well above the blended number while assembly sits below it, so a blended rate would overprice assembly-heavy work and underprice machining-heavy work, quietly steering you toward the wrong jobs. The spread output gives a quick signal of how much that distortion could be.

#### ERP and standard cost notes

**Body:** In most ERP systems the overhead rate lives on the work center or resource group, as a per-hour or percent-of-labor rate, and it drives absorbed overhead on routings. Absorbed overhead rarely equals actual spend, so schedule a periodic review of under or over absorption and adjust the rates when the gap persists. Keep the worksheet as supporting documentation for auditors and for the next rate review.

**Cta:** Netray can connect to your ERP's cost and work center data so actual overhead, hours and absorption are compared against these rates automatically.

**Faq:**

#### Should I use machine hours or direct labor hours?

**Answer:** Use machine hours in highly automated centers where cost follows the machine, and labor hours where people drive the work, such as assembly or hand finishing. Whichever you pick should be the measure that best explains why the overhead is incurred in that center.

#### What is the difference between direct and step-down allocation?

**Answer:** Direct allocation assigns support department cost only to production centers. Step-down allocates a service department to other service departments in sequence before reaching production. Step-down is more accurate when, for example, maintenance serves the quality lab, but direct is simpler and usually fine for small shops.

#### How often should overhead rates be updated?

**Answer:** At least annually, and sooner after a large equipment purchase, lease change or sustained shift in volume. Comparing absorbed overhead to actual overhead each quarter tells you when the rates have drifted far enough to need revisiting.

#### Why use productive hours instead of available hours?

**Answer:** Overhead has to be recovered over hours that produce revenue. Dividing by available hours understates the rate and leaves unrecovered cost in the income statement. Use realistic productive hours after setup, maintenance, breaks and idle time.

## Search terms

overhead rate by cost center excel, machine shop overhead rate worksheet, cost center overhead allocation template, overhead absorption rate per machine hour, departmental overhead rate calculation, manufacturing overhead rate template, shop burden rate worksheet, overhead rate by work center

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