How does the template decide between make and buy?

It compares annual costs. Make cost is units made times the avoidable make cost per unit plus avoidable fixed cost; buy cost is units bought times the delivered buy cost plus buy fixed cost. For 25,000 units with 85% made at 14.50 plus 60,000 a year, and the rest bought at 18.00 plus 5,000 a year, the plan costs 440,625 against 455,000 to buy all, a saving of 14,375. That matches the Make vs Buy Cost calculator.

Why should allocated overhead be left out?

Because it does not go away when you buy the part. In the example the full absorbed cost to make is about 11.64 per unit against a delivered buy cost of 8.88, which makes buying look 2.76 cheaper. Counting only avoidable costs, making all costs 153,700 a year against 165,640 to buy all, so making saves about 11,940 a year. Mark a cost Avoidable only if it really stops.

What is the break-even volume?

The annual volume at which making all and buying all cost the same: the difference in fixed costs divided by the difference in unit costs. In the example, (25,000 - 5,800) / (8.88 - 7.15) gives about 11,098 units a year. Above that volume making is cheaper; below it buying is. It is blank when buying costs less per unit at every volume.

Why does a split cost more than making all in the example?

A split pays both sets of fixed costs: the avoidable make fixed cost and the supplier's fixed cost. At 90% made the example plan costs 162,614 a year against 153,700 to make all. A split can still be the right plan when making all would exceed your capacity, which is what the capacity check shows: making all 18,000 brackets needs 2,160 hours against 2,000 available.

How are one-off costs such as tooling and qualification handled?

One-off lines are divided by the decision horizon on Setup to give a cost per year. With a 3-year horizon, 6,000 of supplier tooling and 2,400 of PPAP add 2,800 a year to the buy fixed cost. Money is not discounted; for a decision with large one-off costs over many years, run the cash flows through the Capital Project NPV template as well.

Do the strategic factor scores change the cost result?

No. They are a separate weighted score out of 5 for make and for buy, so the cost result stays a cost result. In the example, make scores 3.70 and buy scores 3.35: making is cheaper and scores better on quality, lead time and know-how, while buying scores better on capacity for growth and cash.

Finance and Investment

Make vs Buy Analysis Excel Template

Compare the avoidable cost of making a part with the delivered cost of buying it, for make all, buy all or any split, with break-even volume, a capacity check and weighted strategic factors.

Overview

This make vs buy workbook is for plant managers, cost engineers, buyers and owners deciding whether to keep making a part, buy it, or split the volume. It ships with a worked example for a fictional hydraulic cylinder maker weighing whether to keep machining a rod-end bracket, so every result and chart is populated the moment you open it. Replace the example cost lines with your own and everything recalculates.

The cost to make counts only avoidable costs: material, labour and variable overhead per unit, plus the fixed costs that stop if you stop making, such as a machine lease you can end or a post you would redeploy. Allocated overhead that stays either way is marked Avoidable No and appears only as the full absorbed cost, because counting it is the classic way to make outsourcing look cheaper than it is. The cost to buy is the delivered cost: price, freight, inspection and handling per unit, plus supplier management and one-off qualification and tooling spread over your decision horizon. Your plan's annual cost, the saving against buying everything and the blended cost per unit follow the same model as the Make vs Buy Cost calculator on this site.

In practice, start with the costs you are sure of, then test the decision. The break-even volume tells you how much demand it takes for making to pay; the scenario grid shows what a split costs, since a split carries both sets of fixed costs; the capacity check tells you whether a make plan fits the hours you have; and the strategic factors record why the cheapest option may still be the wrong one.

What this template includes

Setup with your currency label, a decision horizon for one-off costs, the hours the make resource has available and your near-capacity level
Cost to make table: each line per unit, per year or one-off, with an Avoidable Yes or No flag so allocated overhead that stays is kept out of the decision
Cost to buy table: supplier price, freight, inspection, handling, supplier management and one-off qualification or tooling, spread over the horizon
Named totals: avoidable make cost per unit and per year, delivered buy cost per unit and per year, and the full absorbed make cost for comparison
Your plan: units made and bought, make, buy and total annual cost, cost to make all and to buy all, saving against buying all, cost per good unit, break-even volume and a capacity check in words
Scenario grid of up to five internal shares, each with costs, saving, hours needed and a capacity word
Weighted strategic factors (quality, lead time, capacity, know-how, supply risk, cash) scored 1 to 5 for make and for buy
Dashboard with KPI tiles, annual cost by internal share, make all against buy all by volume, a comparison table and factor scores
Method and Verify sheets: every formula in words, and a self-check against an independent reference including the Make vs Buy Cost calculator's example
A CSV with live formulas of the scenario grid for any spreadsheet app; the dashboard, charts, dropdowns and self-check are in the .xlsx

Suggested use case

Use it to decide whether to keep making a part, outsource it or split the volume, to test a supplier quote against your real avoidable cost, to show why a full-cost report can overstate the saving from outsourcing, and to check that a make plan fits the hours you have.

How to use it

On Setup, type your company, currency label, the decision horizon over which one-off costs are spread, and the hours per year the make resource can give this part.
On Make vs Buy, enter the part, annual demand, your planned internal share and the hours to make one good unit.
List every cost to make with its basis and mark it Avoidable Yes or No; list every cost to buy with its basis.
Read your plan's cost, the saving against buying all, the break-even volume and the capacity check, and type other internal shares in the scenario grid.
Score the strategic factors, then open the Dashboard to present the decision.

Frequently Asked Questions

How does the template decide between make and buy?: It compares annual costs. Make cost is units made times the avoidable make cost per unit plus avoidable fixed cost; buy cost is units bought times the delivered buy cost plus buy fixed cost. For 25,000 units with 85% made at 14.50 plus 60,000 a year, and the rest bought at 18.00 plus 5,000 a year, the plan costs 440,625 against 455,000 to buy all, a saving of 14,375. That matches the Make vs Buy Cost calculator.
Why should allocated overhead be left out?: Because it does not go away when you buy the part. In the example the full absorbed cost to make is about 11.64 per unit against a delivered buy cost of 8.88, which makes buying look 2.76 cheaper. Counting only avoidable costs, making all costs 153,700 a year against 165,640 to buy all, so making saves about 11,940 a year. Mark a cost Avoidable only if it really stops.
What is the break-even volume?: The annual volume at which making all and buying all cost the same: the difference in fixed costs divided by the difference in unit costs. In the example, (25,000 - 5,800) / (8.88 - 7.15) gives about 11,098 units a year. Above that volume making is cheaper; below it buying is. It is blank when buying costs less per unit at every volume.
Why does a split cost more than making all in the example?: A split pays both sets of fixed costs: the avoidable make fixed cost and the supplier's fixed cost. At 90% made the example plan costs 162,614 a year against 153,700 to make all. A split can still be the right plan when making all would exceed your capacity, which is what the capacity check shows: making all 18,000 brackets needs 2,160 hours against 2,000 available.
How are one-off costs such as tooling and qualification handled?: One-off lines are divided by the decision horizon on Setup to give a cost per year. With a 3-year horizon, 6,000 of supplier tooling and 2,400 of PPAP add 2,800 a year to the buy fixed cost. Money is not discounted; for a decision with large one-off costs over many years, run the cash flows through the Capital Project NPV template as well.
Do the strategic factor scores change the cost result?: No. They are a separate weighted score out of 5 for make and for buy, so the cost result stays a cost result. In the example, make scores 3.70 and buy scores 3.35: making is cheaper and scores better on quality, lead time and know-how, while buying scores better on capacity for growth and cash.