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.
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).
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
Measure
Value
Should-cost price per unit
88.50
Supplier price per unit
102.03
Gap per unit
13.53
Gap per year, 40,000 units
541,185
of which cycle time
10.30
of which SG&A rate
1.78
of which metal price
1.45
Manufacturing cost, should-cost / supplier
70.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
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.
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.
Set the annual volume. Results gives the gap per unit and per year.
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.
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.
How do you compare two supplier bids on total cost?Add each clause's expected cost per unit to the price. A bid 1.50 cheaper at 50.50 costs 52.87 once terms, warranty, take-or-pay and freight are priced.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
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.