Free Excel templates

A free should-cost model in Excel, to rebuild a supplier’s price

Material net of scrap, machine time, set-up, labour, bought-in parts and finishing, then reject allowance, SG&A and profit, built next to the supplier’s own breakdown: the gap per unit, per year, and driver by driver.

A should-cost model rebuilds a price from what the part needs: kilograms of material, minutes on a machine, the rates for each, and the mark-ups on top. On an illustrative machined housing quoted at 102.03, the file rebuilds a should-cost of 88.50, a gap of 13.53 a unit or 541,185 a year on 40,000 units. Changing the drivers one at a time from the should-cost to the supplier’s figures puts 10.30 of the gap on the cycle time, 27 minutes against 21, 1.78 on the SG&A rate and 1.45 on the metal price. The negotiation stops being about the price and starts being about the minutes.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

Should_Cost_Model.xlsx (14 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This file is one worked case from Contract Management. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 19 paperbacks by Julian R. Sterling on markets, funds and compliance, printed on demand and shipped worldwide. Browse the bookshop →

How the price is built

Material = gross kg × price per kg − scrap kg × scrap price

Manufacturing cost = material + bought-in + machining + set-up + labour + finishing

Price = manufacturing cost × (1 + reject) × (1 + SG&A) × (1 + profit) + freight

What is in the file

4 sheets. Blue type on a pale blue fill is an input; black type is a formula.

Read Me
What the file does, the worked case, how to enter your own part, and why the order of the attribution matters.
Inputs
Sixteen drivers in two blue columns, your should-cost and the supplier’s disclosed breakdown, from gross weight and metal price to cycle time, machine rate, set-up, labour, reject allowance, SG&A and profit; then the annual volume.
Results
The price built layer by layer for both columns, the gap line by line, the gap as a share of the should-cost and per year, then the gap attributed to metal price, cycle time and SG&A by switching them one at a time.
Checks
Four identities that hold for any inputs (each price rebuilds from its layers, the attribution adds up to the gap), then the worked case. The file ends ALL OK.

What the worked case returns

MeasureValue
Should-cost price per unit88.50
Supplier price per unit102.03
Gap per unit13.53
Gap per year, 40,000 units541,185
of which cycle time10.30
of which SG&A rate1.78
of which metal price1.45
Manufacturing cost, should-cost / supplier70.89 / 80.59

Three quarters of the gap is six minutes of machining. That is the point of the exercise: a buyer who says “too expensive” gets a token discount, a buyer who says the cycle time is longer than the operation needs gets a conversation about the machining, and perhaps a better price for both sides. The metal price is better handled by an index clause than by negotiation, and the SG&A rate is a policy question. Note that the profit rate is the same 8 per cent in both columns: a should-cost that squeezes the supplier’s margin to nothing is a negotiating position, not a model.

How to use it on your own figures

  1. On Inputs, type your should-cost drivers in column C. Take weights from the drawing, the metal price from an index, cycle times from a CAM simulation or a time study on a comparable part.
  2. Type the supplier’s disclosed breakdown in column D. Where the supplier has not disclosed a driver, copy your own figure: the attribution will then show zero on that line.
  3. Set the annual volume. Results gives the gap per unit and per year.
  4. Read the attribution. It switches drivers cumulatively in the order metal, cycle time, SG&A; change the order and the split changes slightly, the total does not.
  5. Keep the Checks sheet. It ends ALL OK on any inputs except the worked-case rows.

Questions people ask about it

What is a should-cost model?

A bottom-up estimate of what a product ought to cost to make and sell at a fair profit, built from its drivers: materials, process times, rates, overheads and margin. Buyers use it to test a supplier’s price driver by driver instead of arguing about the total.

How do you build a should-cost model?

Start from the drawing and the route: material weight net of scrap at an index price, each operation’s time at its machine or labour rate, set-up spread over the batch, bought-in parts, then reject allowance, SG&A and profit as percentages of the layer beneath. Compare line by line with the supplier’s breakdown.

What profit margin should a should-cost model use?

A fair one, normally the supplier’s own if it is reasonable for the industry. The worked case keeps 8 per cent on both sides, so the gap comes from the drivers, not from squeezing the margin.

Is this should-cost template really free?

Yes. It is a companion file to a book. You enter an email address, the download opens at once, and you can leave the reader list in one click. It has no macros.

Where do the figures come from?

From an illustrative machined aluminium housing in the book. The part, the supplier and the rates are fictional, and nothing here is advice on any real negotiation.

The rest of the files

This template sits alongside the companion files for Contract Management: the workbooks behind every chapter, the worked cases and the checklists. Free, like this one.

Open the companion files →

Worked questions from the same book

Short articles that each answer one question with the arithmetic shown, on the same ground as this model.

Other free templates

This template comes from Contract Management. The book is on Amazon.

If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.