Make vs Buy Analysis
Real-Life Excel Model
Stop making sourcing decisions based on gut feel. This fully-structured Excel template gives you a rigorous cost model, qualitative scorecard, NPV analysis, sensitivity tables, and a one-page executive summary — so you can defend every make or buy decision with data.
Five sheets. One complete decision.
Every sheet connects automatically. Change an input and the entire model updates — costs, NPV, scorecard, and the final recommendation all recalculate in seconds.
A plain-English guide that walks any user through the model in six steps. No training required. Hand this to a junior analyst and they will be running scenarios by end of day.
- Six-step usage guide with clear instructions
- Colour coding legend (blue inputs, black formulas, green totals)
- Notes on when to use make vs buy logic
- Reference tips for customising to your product
Pre-loaded with a real example — Hydraulic Pump Assembly at 50,000 units per year — so you can see the model in action before you overwrite it with your own data.
- Direct labour, material, and variable overhead (per unit)
- Fixed overhead allocation and annual maintenance
- Capital investment with automatic amortisation
- Supplier quote, freight, duty, QC, and management overhead
- Transition and switching cost (one-time)
- Scrap rate and risk buffer inputs
The analytical core of the model. Every cost input flows here automatically. No manual calculation. No pivot tables. One sheet that answers the question your CFO is asking.
- Fully-loaded unit cost (variable + amortised capex)
- Annual total cost at planned volume
- 5-year NPV comparison with configurable discount rate
- Break-even volume calculation
- Sensitivity table across 7 volume scenarios (10k to 200k units)
- Traffic-light cost decision indicator
Cost is rarely the only factor. The scorecard captures the non-financial dimensions that determine whether the cheaper option is actually the right one for your business.
- Core competency alignment and IP protection
- Quality consistency and lead time reliability
- Supplier dependency and regulatory risk
- Capacity scalability and workforce availability
- Configurable weights — adjust to your strategic priorities
- Weighted total score drives the qualitative recommendation
Combines cost analysis and scorecard into a single decision-ready view. Print it. Share it. Present it. The rationale box lets you document your decision logic for governance and audit trail.
- Side-by-side cost and NPV summary
- Qualitative score comparison
- Cost recommendation and scorecard recommendation displayed separately
- Free-text decision rationale box for sign-off documentation
- All values update automatically from Inputs and Scorecard
Every cost that matters. Nothing missing.
Most make vs buy models fail because they miss hidden costs on both sides. This template was built from real manufacturing and procurement projects — every cost driver is accounted for.
MAKE — In-House Cost Inputs
10 cost line items, unit and annual
BUY — Outsource Cost Inputs
9 cost line items, unit and annual
Built for real supply chain decisions
Whether you are in manufacturing, procurement, operations, or consulting — this template gives you the structured output your stakeholders need.
Manufacturing Operations Teams
Evaluating whether to bring a component in-house or continue outsourcing. Build the full business case with capex justification and break-even volume in minutes.
Procurement and Sourcing Leaders
Comparing total cost of ownership for a new supplier against internal production capability. The scorecard captures the supplier risk and dependency factors most models ignore.
Supply Chain Consultants
Deploying a credible, client-ready model on engagements without rebuilding from scratch each time. Pre-loaded example data makes it demo-ready from the first meeting.
S&OP and Finance Business Partners
Producing a defensible NPV analysis for capital investment review boards. The 5-year NPV comparison and sensitivity table give finance exactly what they need to approve or challenge the numbers.
Category Managers and Strategic Buyers
Running a structured analysis before committing to a long-term contract or vertical integration. Use the qualitative scorecard to surface risks your cost model cannot capture.
Students and Professionals in Training
Learning the make vs buy decision framework with a real, working model. The Guide sheet explains the logic step by step — ideal for MBA students and supply chain certification candidates.
From download to decision in under an hour
The model is pre-loaded with a working example. Follow the steps in order and you will have a boardroom-ready analysis before your next meeting.
Go to the Inputs sheet. Enter the component name, annual volume, planning horizon (years), and your discount rate for NPV. These four cells control the entire model.
Enter your direct labour, material, variable overhead, fixed overhead, quality costs, scrap rate, capital investment, and maintenance. All blue cells.
Enter the supplier quoted price, volume discount, freight, import duty, incoming QC cost, supplier management overhead, safety stock carry cost, switching cost, and obsolescence risk.
Every number flows automatically. You will see the fully-loaded unit cost, annual total cost, 5-year NPV, break-even volume, and the sensitivity table across seven volume scenarios.
Go to the Scorecard sheet. Rate each of the 11 strategic and operational factors from 1 to 5 for both Make and Buy. Adjust the weights to reflect your business priorities.
The Summary sheet combines cost and scorecard outputs into a single executive view. Add your decision rationale in the text box, print or export, and present to your leadership team.
Industry-standard colour coding throughout
Every cell follows the same convention used in professional financial models. Anyone familiar with Excel modelling standards will know exactly what to edit and what to leave alone.
Blue on Yellow — User Inputs
These are the cells you change. All assumptions, volumes, rates, and costs.
Black on White — Formulas
Calculated cells. Do not edit. These update automatically when inputs change.
Dark Green on Light Green — Totals
Section totals and key results. Bold rows that summarise each analysis block.
Green Text — Cross-Sheet Links
Values pulled from another sheet in the workbook. Confirms the model is wired correctly.
Questions answered
Do I need advanced Excel skills to use this template?
No. All you need to do is fill in the blue cells. The model handles every calculation automatically. The Guide sheet walks you through each step in plain language. If you can enter numbers into a spreadsheet you can run this model.
Can I use this for services as well as physical components?
Yes. While the default example uses a physical component (Hydraulic Pump Assembly), the framework applies equally to service outsourcing decisions — logistics, IT, quality inspection, warehousing, or any other function. Adjust the input labels and cost line items to fit your context. The formulas are fully flexible.
What does "fully-loaded cost" mean in this model?
Fully-loaded cost means every cost associated with the option is included — not just the obvious unit costs. For Make, this includes fixed overhead allocation, amortised capital investment, maintenance, and engineering overhead. For Buy, it includes freight, duty, incoming inspection, supplier management overhead, safety stock costs, and switching risk. A simple unit cost comparison almost always leads to the wrong decision.
How does the NPV calculation work?
The model uses a standard present value of annuity formula: NPV = Annual Cost x (1 - (1 + r)^-n) / r, where r is your discount rate and n is your planning horizon. Enter your discount rate (cost of capital or WACC) in the Inputs sheet and the model handles the rest. This gives you a time-adjusted view of total cost over the full planning period, not just the current year.
What is the break-even volume telling me?
Break-even volume is the annual unit quantity at which the total cost of making equals the total cost of buying. Below that volume, buying is cheaper (fixed overhead per unit is too high). Above it, making is cheaper. Use this alongside the sensitivity table to understand how volume risk affects your decision — particularly useful when demand is uncertain.
Can I share or present this directly with my leadership team?
Yes. The Summary sheet is designed specifically for this purpose. Print it or screenshot it. The rationale text box lets you document your decision logic for governance, sign-off, or audit trail purposes. The colour scheme and formatting are clean and professional — no design work required before presenting.
Can I adjust the qualitative scorecard weights?
Absolutely — this is one of the most important features of the model. Different businesses should weight factors differently. A company competing on innovation will weight IP protection heavily. A contract manufacturer will weight capacity scalability and cost above all else. The scorecard weights are input cells (blue). Adjust them to reflect your strategic priorities and the weighted scores update immediately.
Why every procurement professional needs this
Make vs buy is one of the highest-stakes decisions in procurement. Yet most professionals still run it in a messy spreadsheet they built themselves, or worse, on gut feel.
Most make vs buy decisions are made without a complete cost picture
Procurement teams routinely compare the supplier quote against a rough internal cost estimate and call it an analysis. The result is a decision that looks right on paper and turns out to be expensive in practice.
Building the model from scratch wastes 6 to 8 hours every time
A rigorous make vs buy model requires NPV logic, break-even formulas, sensitivity tables, a qualitative scorecard, and a governance-ready summary. Building that from scratch takes a senior analyst the better part of a working day.
Cost analysis alone does not protect you from the wrong decision
The cheapest option is not always the right option. If a supplier is your only source for a critical component, if the process contains your core IP, or if quality consistency is non-negotiable — the cost analysis will point you in the wrong direction.
A fully-loaded cost model that accounts for every cost driver
19 cost line items across Make and Buy. Unit costs, annual fixed costs, and one-time investments — all handled. Capex is automatically amortised over your volume and planning horizon. No manual calculation. No missing lines.
From inputs to boardroom presentation in under an hour
Enter your numbers into the blue cells. Every formula, every total, every NPV, and every sensitivity scenario updates automatically. The Summary sheet is your stakeholder deliverable — print it, share it, or export it.
A weighted scorecard that captures what cost models cannot
Eleven qualitative factors covering strategic alignment, IP protection, supply chain control, quality, lead time, risk, scalability, and financial impact. Fully configurable weights so the model reflects your business priorities.
Built by a practitioner, for practitioners
Download the Make vs Buy Analysis Tool
A complete, boardroom-ready Excel model. Pre-loaded with a real example. Zero formula errors. Ready to use today.
Other tools from SCMDOJO
Paste your PO and invoice data, hit Refresh All, and get a full procurement spend dashboard. Supplier rankings, category split, OTD KPIs, and payment terms — all in one view.
Download Template →A structured, data-driven tool to identify, evaluate, and mitigate risks across all levels of your supply chain. Designed for risk reviews and board presentations.
Download Template →A simple but powerful Excel-based scorecard for measuring procurement and supply chain performance. Start making data-driven decisions today — no complex setup required.
Download Template →
