# Manufacturing Cost Calculation Excel Template (Material, Labor, Overhead, Scrap)

> Source: https://netray.co/tools/manufacturing-cost-calculation-excel-template
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

An editable worksheet that computes per-unit manufacturing cost from material, scrap, run time, setup time, labor rate and overhead rate, then exports to Excel.

## Category

cost-finance

## Audience

- Cost accountant
- Estimator
- Plant controller

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Product / part

**Key:** product

**Type:** text

#### Batch quantity (good units)

**Key:** batch_qty

**Type:** number

#### Material per good unit (before scrap)

**Key:** mat

**Type:** number

**Format:** currency

#### Scrap rate

**Key:** scrap

**Type:** number

**Unit:** %

**Help:** Percent of material lost or scrapped

#### Run time per unit

**Key:** run_min

**Type:** number

**Unit:** min

#### Setup time per batch

**Key:** setup_min

**Type:** number

**Unit:** min

#### Direct labor rate

**Key:** rate

**Type:** number

**Format:** currency

**Unit:** per hr

#### Overhead rate

**Key:** oh_rate

**Type:** number

**Format:** currency

**Unit:** per labor hr

#### Material incl. scrap

**Key:** mat_cost

**Type:** number

**Expr:** mat / (1 - scrap / 100)

**Format:** currency

#### Direct labor per unit

**Key:** labor_cost

**Type:** number

**Expr:** (run_min + setup_min / batch_qty) / 60 * rate

**Format:** currency

#### Overhead per unit

**Key:** oh_cost

**Type:** number

**Expr:** (run_min + setup_min / batch_qty) / 60 * oh_rate

**Format:** currency

#### Total cost per unit

**Key:** unit_cost

**Type:** number

**Expr:** mat / (1 - scrap / 100) + (run_min + setup_min / batch_qty) / 60 * (rate + oh_rate)

**Format:** currency

#### Batch cost

**Key:** batch_cost

**Type:** number

**Expr:** (mat / (1 - scrap / 100) + (run_min + setup_min / batch_qty) / 60 * (rate + oh_rate)) * batch_qty

**Format:** currency

**Rows:**

#### Bracket, 6061 aluminum

**Batch qty:** 250

**Mat:** 3.8

**Scrap:** 4

**Run min:** 6.5

**Setup min:** 90

**Rate:** 28

**Oh rate:** 42

#### Shaft, 4140 steel

**Batch qty:** 100

**Mat:** 7.25

**Scrap:** 6

**Run min:** 11

**Setup min:** 120

**Rate:** 30

**Oh rate:** 45

#### Housing, cast iron

**Batch qty:** 60

**Mat:** 14.5

**Scrap:** 8

**Run min:** 22

**Setup min:** 150

**Rate:** 30

**Oh rate:** 48

#### Cover plate, 304 stainless

**Batch qty:** 400

**Mat:** 2.1

**Scrap:** 3

**Run min:** 3.2

**Setup min:** 60

**Rate:** 26

**Oh rate:** 38

#### Wiring harness assembly

**Batch qty:** 500

**Mat:** 9.4

**Scrap:** 2

**Run min:** 7.5

**Setup min:** 30

**Rate:** 20

**Oh rate:** 22

**Totals:**

#### Total good units

**Key:** batch_qty

**Agg:** sum

**Format:** integer

#### Total batch cost

**Key:** batch_cost

**Agg:** sum

**Format:** currency

#### Highest unit cost

**Key:** unit_cost

**Agg:** max

**Format:** currency

**Summary:**

#### Weighted average cost per unit

**Key:** avg_unit_cost

**Expr:** sum_batch_cost / sum_batch_qty

**Format:** currency

**Description:** Total batch cost divided by total good units across all rows

**Primary:** true

**Notes:** Scrap is applied to material only, as a gross-up: material divided by (1 minus scrap rate). Overhead is absorbed on direct labor hours. If your plant absorbs overhead on machine hours, enter the machine-hour rate in the overhead column and the machine time in run time.

## Content

**Intro:** This manufacturing cost calculation template turns a few inputs per part into a full unit cost: material grossed up for scrap, direct labor, setup spread across the batch, and overhead absorbed per labor hour. Edit the sample rows, add your own parts and export to Excel.

**Sections:**

#### The formulas behind the template

**Body:** Unit cost is material plus conversion cost. Material including scrap is material divided by one minus the scrap rate, so 4 percent scrap on 3.80 of material gives 3.96. Labor hours per unit are run minutes plus setup minutes divided by batch quantity, all over 60. Direct labor is those hours times the labor rate; overhead is the same hours times the overhead rate. Total cost per unit is the sum, and batch cost is unit cost times batch quantity.

**Bullets:**

- Material incl. scrap = material / (1 - scrap rate)
- Labor hours per unit = (run min + setup min / batch qty) / 60
- Labor = hours x labor rate; Overhead = hours x overhead rate
- Unit cost = material incl. scrap + labor + overhead

#### Getting the overhead rate

**Body:** Overhead absorption rate is budgeted overhead for the period divided by budgeted direct labor hours (or machine hours). If indirect labor, depreciation, utilities, rent and maintenance come to 1.2 million for a year and the plant plans 28,000 direct hours, the rate is about 43 per hour. Use the same driver in your ERP cost roll-up so the spreadsheet and the system agree. Review the rate at least twice a year, because volume swings change it sharply.

#### Reading the result and checking it

**Body:** Look at the weighted average at the bottom and at the highest unit cost row. Compare each unit cost to the standard cost in your ERP; a gap of more than a few percent usually means a stale routing time, an old material price or a different scrap assumption. Short batches are dominated by setup: change batch quantity to see how unit cost falls as the batch grows, which is the basis for price breaks.

#### Common mistakes

**Body:** Frequent errors include leaving setup out, applying scrap to labor as well as material, using a loaded labor rate and then adding overhead again, and mixing machine-hour and labor-hour rates in the same sheet. Also remember that this is production cost: add selling, general and administrative cost and margin separately when you build a quote.

**Cta:** If you want these costs pulled from your ERP's routings, BOMs and rates instead of typed in, Netray can build a private cost assistant that reads your data on-prem.

**Faq:**

#### What is the formula for manufacturing cost per unit?

**Answer:** Cost per unit equals direct material plus direct labor plus manufacturing overhead, divided by good units produced. Include scrap by grossing up material, and spread setup cost across the batch. This template does exactly that for each row.

#### How do I include scrap in the cost?

**Answer:** Divide the material cost per good unit by one minus the scrap rate. A 5 percent scrap rate raises material by about 5.3 percent, not 5 percent, because you must start more material to get one good unit.

#### Should overhead be absorbed on labor hours or machine hours?

**Answer:** Use the driver that best explains your overhead. Automated machine shops usually use machine hours; assembly and manual work use labor hours. Keep the choice consistent with your ERP costing so reports reconcile.

#### Can I use this to price a quote?

**Answer:** It gives production cost, which is the floor. Add SG&A, tooling amortization, freight, risk and your target margin on top. The worksheet is a starting point and does not replace your standard costing process.

## Search terms

manufacturing cost calculation excel template, manufacturing cost model template, manufacturing cost estimation formula, unit cost calculation excel, product costing template excel, cost per unit manufacturing formula, overhead absorption rate template, scrap adjusted material cost

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