Hello! Welcome to the next lesson in your FP&A curriculum.
In our last session, we built a dynamic pricing model to find the optimal price point that maximizes profit. That analysis is a cornerstone of strategic finance, focusing on the revenue side of the business. Today, we shift our focus to the cost side and tackle another critical operational decision.
This lesson addresses the learning outcome: Construct a make-versus-buy analysis model to inform sourcing and operational decisions. You will learn how to build a model that helps a company decide whether it's more cost-effective and strategic to produce a component or service in-house ("make") or to purchase it from an external supplier ("buy"). This is a classic capital budgeting analysis that is fundamental to the role of an FP&A analyst.
1. The Core of the Decision: Relevant Costs
A make-or-buy decision seems simple on the surface: which option is cheaper? However, a frequent and costly mistake is to compare the wrong costs. The key to a correct analysis is to focus only on relevant costs.
A relevant cost is a future cash flow that will change as a direct result of the decision you make. If a cost is the same regardless of whether you make or buy, it is irrelevant to the decision.
To build a strong theoretical foundation, let's start with a definitive guide on this topic.
This article from ACCA Global is the gold standard for understanding relevant costs in decision-making. Mastering this concept is non-negotiable for accurate financial analysis.
Please read the first part of the article, up to the heading 'Types of decision'. Pay close attention to the four categories of costs that are not relevant: Sunk costs: Money already spent. Committed costs: Future costs you can't avoid. Re-apportionment of existing fixed costs: Spreading existing costs like factory rent over a new project doesn't create a new cash outflow. Depreciation: A non-cash accounting entry based on a past purchase.
The video below provides a simple, practical example that brings this theory to life.
This video from Edspira walks through a classic make-or-buy scenario for a bicycle company. It clearly distinguishes between the total accounting cost and the relevant cost of manufacturing.
Watch from the beginning to the 4:55 mark. Focus on why the instructor excludes 'General Overhead' and 'Depreciation' from the final calculation. This is the practical application of the 'relevant cost' principle we just read about.
This distinction is crucial. The total "cost" to manufacture shown in an accounting system often includes allocated fixed costs that the company would incur anyway. Your job as an analyst is to isolate the true incremental cash flows of the decision.
2. Structuring the Make-vs-Buy Model
Now that we have the theory down, let's build a practical model in Excel. The structure of your model will depend on the nature of the decision.
- Per-Unit Decision: Deciding whether to make or buy a component for ongoing production (like the bicycle seat example).
- Project Decision: A one-time decision, like whether to build custom software in-house or subscribe to a SaaS solution.
The following video demonstrates how to build a model for a project-based decision, which is excellent for understanding how to compare costs over time. The principles are directly transferable to a per-unit analysis.
How to Create a Make or Buy Analysis in Excel
In this tutorial, David McLachlan provides a clear, step-by-step guide to creating a make-or-buy analysis template in Excel. We will use his structure as a foundation for our model.
Watch the video up to the 6:24 mark. As you watch, replicate the structure in a blank Excel sheet. Pay attention to: Layout: Separating costs into 'Initial' and 'Ongoing' categories for both the 'Make' and 'Buy' options. Formulas: Using simple SUM functions for totals. Decision Logic: Implementing the IF and MIN function combination (=IF(TotalMake=MIN(TotalMake, TotalBuy), "Make", "Buy")) to create an automated recommendation. This adds a professional touch to your model.
Your model now has a clear structure for capturing costs and an automated output for the decision based on the lowest total cost.
3. Finding the Break-Even Point
Often, the "make" option involves higher upfront costs (e.g., tooling, setup) but lower ongoing costs, while the "buy" option has low initial costs but higher recurring expenses. A key part of the analysis is to determine the break-even point—the point in time or volume at which the cumulative cost of one option becomes lower than the other.
A line chart is the perfect way to visualize this. Let's add this to our model.
How to Create a Make or Buy Analysis in Excel
We'll continue with the same video to add the break-even analysis and visualization.
Watch from 06:44 to the end of the video. Focus on two key steps: Calculating Cumulative Costs: Understand the formula for creating a running total of costs over the timeline. The use of an absolute reference (e.g., $B$10) for the starting cell is a key Excel technique here. Creating the Chart: Learn how to plot the two cumulative cost series on a line chart to visually identify the crossover point.
This chart transforms your spreadsheet from a static table of numbers into a powerful communication tool. A manager can now see not just which option is cheaper, but when it becomes cheaper.
4. Worked Example: Applying Relevant Cost Analysis
Let's solidify your understanding by completing the analysis from the Edspira video.
Scenario: Speedy Bicycles needs 5,000 saddles.
- Buy Option: An external supplier offers them for $10 each, for a total cost of $50,000.
- Make Option: The internal accounting costs are:
- Direct Materials: $15,000
- Direct Labor: $25,000
- Variable Overhead: $6,000
- General Factory Overhead (Allocated): $20,000
- Depreciation of Saddle Machine: $4,000
- Total Accounting Cost: $70,000
Test your understanding!
Based on the principles of relevant costing, what is the true financial cost of the "Make" option that should be compared against the $50,000 "Buy" cost? Which costs from the list are irrelevant and why?
Show answer
The relevant costs are the ones that would be avoided if the company chooses to buy:
- Direct Materials: $15,000
- Direct Labor: $25,000
- Variable Overhead: $6,000
- Total Relevant Cost to Make = $46,000
The irrelevant costs are:
- General Factory Overhead ($20,000): This is an allocated cost. The company will still have to pay for factory lighting, rent, etc., whether it makes the saddles or not. Therefore, this cash flow does not change with the decision.
- Depreciation ($4,000): This is a sunk cost. The machine was purchased in the past. The decision to make or buy saddles will not change this past expenditure. (We assume the machine has no salvage value or alternative use, otherwise that would be an opportunity cost to consider).
Conclusion: The relevant cost to make is $46,000, which is cheaper than the $50,000 cost to buy. Based purely on quantitative analysis, the company should make the saddles.
5. Beyond the Numbers: Qualitative Factors
The numbers tell an important part of the story, but a top-tier FP&A analyst knows that the final decision is also strategic. Your model should include a section to list the qualitative pros and cons that the numbers don't capture.
Make-or-Buy Decision - Overview, How It Works, Triggers
The Corporate Finance Institute article provides an excellent overview of the strategic factors to consider.
Read the sections 'Make-or-Buy Decision Triggers' and 'Make-or-Buy Decision Criteria'. As you read, make a list of factors that would argue for 'making' and factors that would argue for 'buying'.
Here is a summary of key factors to consider:
| Arguments for Making (In-house) | Arguments for Buying (Outsourcing) |
|---|---|
| Greater control over quality | Supplier may be a low-cost producer due to economies of scale |
| Protect proprietary technology/IP | Access to supplier's specific expertise and technology |
| Utilize excess plant capacity | Frees up internal capacity for core competencies |
| More control over supply and lead times | Increased flexibility and lower risk (can switch suppliers) |
| Stable workforce and skill retention | Less capital investment required |
Your final deliverable to management should be a clear one-page summary that presents the quantitative model, the visual break-even chart, and a concise list of these qualitative factors to support a well-rounded recommendation.
Conclusion
In this lesson, you've moved from theory to a practical and powerful analytical tool. You can now build a robust make-or-buy model that avoids common accounting pitfalls and supports strategic operational decisions.
Key Takeaways:
- Make-or-buy analysis must be based on relevant costs—future, incremental cash flows that differ between alternatives. Sunk costs and non-cash expenses like depreciation are ignored.
- A well-structured Excel model separates costs, calculates totals, and can include an automatic decision recommendation using
IF/MINlogic. - Visualizing cumulative costs over time on a line chart is essential for identifying the break-even point and communicating the long-term impact of the decision.
- The final recommendation must balance the quantitative cost analysis with qualitative strategic factors like quality control, expertise, and risk.
Preview of the Next Lesson:
Our make-or-buy model, like the pricing model before it, relied on single-point estimates for costs. But what if the supplier's price might increase? What if our material costs are volatile? To answer these questions, we need to move beyond deterministic analysis. In our next lesson, you will learn how to use Monte Carlo simulation to model the probability distribution of project outcomes and assess risk. This will allow you to quantify the uncertainty in your models and present a much richer, risk-adjusted view to decision-makers.