# Lot Size Price Break Table Generator for Machined Parts

> Source: https://netray.co/tools/lot-size-price-break-table-generator-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Enter setup, material, run cost, scrap and target margin for each quantity and get the cost-based unit price and lot price for a quote.

## Category

cost-finance

## Industry

Precision Machining

## Audience

- Estimator
- Sales engineer
- Owner

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Lot quantity

**Key:** qty

**Type:** number

**Format:** integer

#### Setup and first article cost per lot

**Key:** setup

**Type:** number

**Format:** currency

#### Material per part

**Key:** mat

**Type:** number

**Format:** currency

#### Run cost per part

**Key:** run

**Type:** number

**Format:** currency

#### Scrap %

**Key:** scrap

**Type:** number

**Format:** percent

#### Target margin % of price

**Key:** margin

**Type:** number

**Format:** percent

#### Unit cost

**Key:** ucost

**Type:** number

**Expr:** (mat + run) * (1 + scrap / 100) + setup / qty

**Format:** currency

#### Unit price

**Key:** uprice

**Type:** number

**Expr:** ucost / (1 - margin / 100)

**Format:** currency

#### Lot price

**Key:** lprice

**Type:** number

**Expr:** uprice * qty

**Format:** currency

#### Setup share of cost %

**Key:** setup_share

**Type:** number

**Expr:** setup / qty / ucost * 100

**Format:** percent

**Rows:**

**Qty:** 100

**Setup:** 450

**Mat:** 0.62

**Run:** 0.9

**Scrap:** 5

**Margin:** 30

**Qty:** 250

**Setup:** 450

**Mat:** 0.62

**Run:** 0.88

**Scrap:** 4

**Margin:** 30

**Qty:** 500

**Setup:** 450

**Mat:** 0.6

**Run:** 0.86

**Scrap:** 3

**Margin:** 30

**Qty:** 1000

**Setup:** 450

**Mat:** 0.58

**Run:** 0.85

**Scrap:** 2.5

**Margin:** 28

**Qty:** 2500

**Setup:** 450

**Mat:** 0.55

**Run:** 0.84

**Scrap:** 2

**Margin:** 28

**Qty:** 5000

**Setup:** 450

**Mat:** 0.52

**Run:** 0.83

**Scrap:** 2

**Margin:** 26

**Qty:** 10000

**Setup:** 450

**Mat:** 0.5

**Run:** 0.82

**Scrap:** 1.5

**Margin:** 25

**Totals:**

#### Highest unit price

**Key:** uprice

**Agg:** max

**Format:** currency

#### Lowest unit price

**Key:** uprice

**Agg:** min

**Format:** currency

#### Sum of lot prices (for reference)

**Key:** lprice

**Agg:** sum

**Format:** currency

**Summary:**

#### Price drop from smallest to largest lot

**Key:** spread

**Expr:** (max_uprice - min_uprice) / max_uprice * 100

**Format:** percent

**Description:** How much cheaper the best break is than the worst.

**Notes:** Unit cost equals (material plus run) times (1 plus scrap) plus setup divided by quantity. Unit price equals unit cost divided by (1 minus margin), so margin is a share of price, not a markup on cost. Material and run cost can fall with quantity because of bar price breaks and tuned programs, edit each row. Example numbers are illustrative.

## Content

**Intro:** Customers ask for pricing at 100, 500, 1,000 and 5,000 pieces, and the breaks should follow cost, not guesswork. This worksheet builds the table from setup, material, run cost, scrap and margin for each quantity.

**Sections:**

#### How to use it

**Body:** Set one row per quantity break the customer asked for. Enter the setup and first article cost for the lot, material per part, run cost per part from your cycle cost calculation, scrap and your target margin. Unit cost, unit price, lot price and the share of cost that is setup update immediately. Edit rows individually, because material price breaks and learning on a repeat job usually make the larger lots cheaper per part than simple division suggests.

**Bullets:**

- Use the same setup figure unless larger lots need extra tooling.
- Lower material cost on larger rows only when your supplier gives a break.
- Keep margin consistent unless you are deliberately buying volume.

#### Pricing method

**Body:** Unit cost equals material plus run, scaled by the scrap factor, plus setup divided by quantity. Unit price equals unit cost divided by one minus the target margin. This is margin on price, so a 30 percent margin means cost is 70 percent of price, and it is lower than a 30 percent markup on cost. The method is the classic cost-plus quantity break: setup is a fixed cost per lot, so unit cost falls as quantity rises and flattens towards the run cost.

#### Reading the table

**Body:** The setup share column shows why prices fall: at 100 pieces a 450 dollar setup can be over a third of unit cost, at 10,000 it is a few percent. Where setup share drops under about 5 percent, extra quantity buys little. The summary shows the total price drop from the smallest to the largest lot, a useful sanity check against customer expectations. If a customer expects a steeper drop than your table shows, the difference is in your run cost, not in the setup.

#### Common mistakes

**Body:** Pricing the break rows by applying a flat percent discount ignores what really changes, which is setup per part. Another error is using the same scrap for 100 pieces and 10,000, since short runs have higher scrap from setup pieces. Also remember carrying cost and cash: very large lots tie up material and need payment terms. In an ERP, price breaks sit on the quote or sales price list by quantity, so use this table to fill those breaks consistently.

**Cta:** If you maintain price breaks by hand across many customers, Netray can help automate quote price lists from cost data in your ERP.

**Faq:**

#### Why use margin on price rather than markup?

**Answer:** Margin on price is the share of the selling price you keep, which matches how finance reports gross margin. A 30 percent margin needs a price of cost divided by 0.70, which is a 42.9 percent markup on cost.

#### How many quantity breaks should I quote?

**Answer:** Three to five covers most requests. Include the customer's stated annual and release quantities, and one break above to show the benefit of consolidating orders. More breaks create more room for error without adding information.

#### Should setup change by lot size?

**Answer:** Often not, because setup is a fixed lot cost. It may rise for large lots if you need extra bar loads, more tool changes or in-process inspection. Edit the row when that is true.

#### Can I export this to Excel?

**Answer:** Yes. Use the export button to get an Excel file with the inputs and computed columns, which you can attach to a quote or load into your quoting system.

## Search terms

price break table generator, quantity price break calculator, lot size pricing machined parts, unit price by quantity excel, setup amortization per part, quote quantity breaks template, job shop pricing table, cost plus quantity pricing

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