Excel guide · 5 min read
Make or buy analysis in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Input | Value | Result | Value | |
| 2 | Annual volume (units) | 12000 | Variable cost to make per unit | 9.8 | |
| 3 | Materials per unit (BOM) | 4.2 | Landed cost to buy per unit | 11.215 | |
| 4 | Labor hours per unit | 0.15 | Total cost to make | 135600 | |
| 5 | Labor rate per hour | 30 | Total cost to buy | 134580 | |
| 6 | Variable overhead per unit | 1.1 | Break-even volume | 12720.848057 | |
| 7 | Avoidable fixed cost per year | 18000 | Verdict | Buy | |
| 8 | Allocated fixed overhead per unit | 3 | |||
| 9 | Supplier price per unit | 10.6 | |||
| 10 | Freight per unit | 0.35 | |||
| 11 | Duty rate | 0.025 |
Download this sheet as an Excel practice file (.xlsx)
Set up the inputs: what changes if you buy?
The sample is a machined bracket a manufacturer makes 12,000 of a year and has a supplier quote for. Every number sits in an input cell in column B, so the whole analysis updates when a quote or rate changes.
The make side is materials per unit (taken from the bill of materials roll-up), labor hours times the labor rate, and variable overhead such as machine power and consumables. Then there's the fixed cost you'd stop paying if you bought: here $18,000 a year for a tooling lease and part of a setter's time. The buy side is the supplier price, freight per unit and duty as a rate in its own input cell.
The one number that's in the inputs but not in the decision is allocated fixed overhead ($3.00 a unit). That's a share of rent, management and the ERP system. It stays the same whether you make the bracket or not, so it's not a cost of making it.
Result: 9.8
9.8: the variable cost to make one bracket, $4.20 materials + 0.15 h x $30 labor + $1.10 overhead.
Total cost to make vs total cost to buy
Landed cost to buy per unit is =B9*(1+B11)+B10: $10.60 plus 2.5% duty plus $0.35 freight, $11.215. Total cost to buy is that times the volume, =E3*B2, $134,580.
Total cost to make is the variable cost times the volume plus the avoidable fixed cost, =E2*B2+B7: $117,600 + $18,000 = $135,600. At 12,000 units a year buying is $1,020 cheaper. That's close enough that the qualitative points in the last section should decide it.
The verdict cell, =IF(E4<E5,"Make","Buy"), says Buy. Show the difference too, =E4-E5 (positive means buying saves that much), so a reader sees whether it's a landslide or a coin toss.
Result: spills 2 rows × 2 columns
| Make | 135600 |
| Buy | 134580 |
Make 135600, Buy 134580: buying saves $1,020 a year at this volume.
Break-even volume
Making has a fixed cost and a lower cost per unit; buying has no fixed cost and a higher cost per unit. So there's a volume where they cost the same: avoidable fixed cost / (buy cost per unit - variable make cost per unit). Each bracket you make instead of buy saves $1.415, and you need 12,721 of them to cover the $18,000.
The formula in E6 guards the case where the supplier is cheaper per unit than your own variable cost. Then the denominator is zero or negative, there is no break-even, and buying wins at every volume, which the IF says in words instead of returning a negative volume.
Result: 12721
12721: below about 12,700 brackets a year buying is cheaper; above it, making is.
A sensitivity table for volume
Volume forecasts are uncertain, so show the decision across a range. LET with an array constant builds a small table in one cell (Excel 365): volume, cost to make, cost to buy and the verdict for each.
The crossover sits between 12,000 and 14,000 units. If sales expect volume to grow next year, making starts to pay; if 12,000 is a peak, buying is the safer choice. In older Excel, type the volumes down a column and fill =G2*$E$2+$B$7 and =G2*$E$3 beside them.
Result: spills 5 rows × 4 columns
| 8000 | 96400 | 89720 | Buy |
| 10000 | 116000 | 112150 | Buy |
| 12000 | 135600 | 134580 | Buy |
| 14000 | 155200 | 157010 | Make |
| 16000 | 174800 | 179440 | Make |
8,000: 96,400 vs 89,720 Buy; 10,000: 116,000 vs 112,150 Buy; 12,000: 135,600 vs 134,580 Buy; 14,000: 155,200 vs 157,010 Make; 16,000: 174,800 vs 179,440 Make.
The allocated overhead trap
The most common make-or-buy mistake is comparing the supplier's price with the "full cost" from the costing system. Here that's variable cost + avoidable fixed per unit + allocated overhead: $9.80 + $1.50 + $3.00 = $14.30 a bracket. Against $11.215 to buy, outsourcing looks like it saves $3.09 a unit, about $37,000 a year.
It doesn't. If you buy, the $36,000 of allocated overhead (12,000 x $3.00) is still paid: it just gets spread over the other products, which then look more expensive. The real saving is the $1,020 from the relevant-cost comparison.
The same logic applies to labor. If the machinists would stay on the payroll anyway with nothing else to do, their wages aren't avoidable and belong with fixed overhead, not in the cost to make.
Result: 37020
37020: the "saving" a full-cost comparison would show, almost all of it overhead that doesn't go away.
What the numbers leave out
Capacity: if the machines are full, making the bracket means not making something else. Add the contribution you'd give up as an extra cost on the make side. Fixed costs also move in steps: the $18,000 holds up to the capacity of one machine, and a second shift or machine adds another step, so check the sensitivity table doesn't go beyond it.
Supply risk and quality: a supplier adds lead time, minimum order quantities and a dependency. Price that in where you can (safety stock you'd hold, incoming inspection per unit) and add it to the buy side as its own input row. A close result like this one is often decided by these points, not by the $1,020.
Common mistakes
Including allocated fixed overhead in the cost to make
Only include costs that disappear if you buy. Allocated overhead stays, so leave it out of the comparison.
Comparing the supplier's price without freight and duty
Use the landed cost per unit: price x (1 + duty rate) + freight, with each rate in its own input cell.
A negative break-even volume
It means the buy cost per unit is below your variable make cost, so buying wins at any volume. Guard it with IF(E3<=E2,"Buy at any volume",...).
Deciding on one volume
Show a small sensitivity table across the realistic range; a close call at the forecast volume can flip either way.
FAQ
What costs are relevant in a make-or-buy decision?
Costs that are different between the two options: materials, variable labor and overhead, fixed costs you'd avoid by buying, and the supplier's landed cost. Costs that stay either way, like allocated rent or management, are not relevant.
How do you calculate the break-even point in make or buy?
Divide the fixed cost you'd avoid by buying by the difference per unit: =AvoidableFixed/(BuyCostPerUnit-VariableMakeCostPerUnit). Above that volume, making is cheaper.
Should opportunity cost be included in make or buy?
Yes, when capacity is limited. If making the part uses machine time that could make something else, add the contribution you'd lose to the cost of making.
Where does the materials cost come from?
From the bill of materials: each component's quantity times its unit cost, added up. The BOM cost roll-up guide shows how to build it with XLOOKUP so price changes flow through.
Functions used
IFPicks a result from a testLETNames values inside a formulaHSTACKJoins arrays side by sideVSTACKStacks arrays one under anotherROUNDRounds to digits
Related guides
- How to calculate bill of materials (BOM) cost in Excel
- How to calculate landed cost in Excel
- How to calculate the break-even point in Excel
- How to calculate cost per unit in Excel (formula and examples)
- Earned value analysis in Excel
- How to calculate schedule adherence in Excel
Updated