A well-built business case spreadsheet turns a proposed initiative into a set of transparent, updateable assumptions. This guide shows how to model revenue, costs, cash investment, contribution margin, ROI, break-even volume, and scenarios in Excel or another spreadsheet tool so you can compare options and revisit the decision when inputs change.
Overview
A business case spreadsheet is a decision model, not simply a list of projected expenses. It connects operational assumptions to financial outcomes. A useful model should answer questions such as: How much will the initiative cost? What revenue or savings could it create? How many units must be sold to recover fixed costs? How long might it take to recover the initial investment? Which assumptions have the greatest effect on the result?
The model can support a new product, marketing campaign, equipment purchase, software implementation, hiring plan, or other business initiative. Its value comes from separating inputs from calculations. When price, volume, labor cost, or investment changes, you should be able to update one assumption and see the effect across the model.
A practical workbook usually contains five areas:
- Assumptions: prices, volumes, costs, timing, and scenario values.
- Revenue and cost calculations: the operating forecast.
- Profitability metrics: contribution margin, operating profit, ROI, and payback.
- Break-even analysis: the sales volume or revenue required to cover fixed costs.
- Scenario analysis: a comparison of base, best, and worst cases.
For a broader view of decision criteria, risks, and benefits, pair this model with the Business Case Template. The spreadsheet described here focuses on the quantitative side of the decision.
How to estimate
Start by choosing a consistent period, such as one month, one quarter, or one year. Avoid mixing a monthly cost with an annual revenue estimate unless the timing is explicitly converted. Then define the basic unit of activity: units sold, customers acquired, subscriptions started, service hours delivered, or another measurable output.
1. Estimate revenue
For a unit-based initiative, use:
Revenue = Selling price per unit × Units sold
If the initiative has several products or customer groups, calculate revenue separately for each and add the results. This makes the model easier to audit and allows price or volume assumptions to differ by segment.
2. Separate variable and fixed costs
Variable costs change with output. Examples include materials, payment processing, delivery, commissions, and usage-based software fees. Fixed costs remain broadly unchanged over the modeled period, such as setup fees, salaries assigned to the initiative, rent, or a fixed subscription.
Use these formulas:
- Total variable cost = Variable cost per unit × Units sold
- Total cost = Fixed costs + Total variable cost
- Operating profit = Revenue − Total cost
3. Calculate contribution margin
Contribution margin shows how much each sale contributes toward fixed costs and profit after variable costs are covered.
Contribution margin per unit = Selling price per unit − Variable cost per unit
Contribution margin ratio = Contribution margin per unit ÷ Selling price per unit
A gross margin calculation may be useful for product profitability, but do not confuse gross margin with contribution margin. The model should define exactly which costs are included in each measure.
4. Calculate break-even
The standard break-even formula is:
Break-even units = Fixed costs ÷ Contribution margin per unit
Break-even revenue can be estimated with:
Break-even revenue = Fixed costs ÷ Contribution margin ratio
If the contribution margin per unit is zero or negative, the initiative cannot reach break-even through additional volume at the stated price and variable cost. The price, cost structure, or offer would need to be reconsidered.
5. Calculate ROI and payback
When estimating return on investment, define whether the return is profit, cost savings, or another measurable benefit. A simple ROI formula is:
ROI = (Net benefit − Initial investment) ÷ Initial investment × 100
For example, if net benefits are expected to total 18,000 and the initial investment is 12,000, ROI is 50%. The result is only meaningful when the time period is stated. A 50% return over one month is not equivalent to a 50% return over five years.
A simple payback estimate is:
Payback period = Initial investment ÷ Average periodic net cash benefit
Use cash benefits rather than accounting profit when the question is how quickly the initial cash outlay may be recovered. For a fuller comparison, see the Scenario Analysis Spreadsheet guide.
Inputs and assumptions
Create an input sheet with one assumption per row and clear labels. Recommended fields include:
- Analysis start date and end date
- Selling price or average revenue per unit
- Expected units, customers, or billable hours
- Variable cost per unit
- Fixed operating costs
- One-time setup or implementation investment
- Expected cost savings, if applicable
- Ramp-up period and timing of cash flows
- Scenario-specific changes to price, volume, and costs
Use consistent units and document the source or reasoning for each assumption in a notes column. If a number is uncertain, enter a range or create separate scenario values rather than presenting one estimate as a fact.
Keep formulas separate from manually entered assumptions. A simple color convention can help: one color for inputs, another for formulas, and a third for key outputs. Add checks for common errors, such as a negative unit volume, a missing price, or a break-even result calculated from a zero contribution margin.
Include taxes, VAT, discounts, refunds, and financing only when they belong in the decision being modeled. For example, a pricing model may need net revenue after discounts and VAT, while an operating break-even model may use revenue before tax. State whether each figure is gross or net. This prevents a VAT calculation or discount assumption from being applied twice.
Worked examples
Assume a proposed product has a selling price of 80 per unit, a variable cost of 32 per unit, and fixed costs of 12,000 for the period. The initial investment is 15,000, and the expected sales volume is 500 units.
Revenue is 80 × 500, or 40,000. Total variable cost is 32 × 500, or 16,000. Contribution margin per unit is 80 − 32, or 48. Total contribution is 48 × 500, or 24,000. After fixed costs, operating profit is 24,000 − 12,000, or 12,000.
Break-even volume is 12,000 ÷ 48, or 250 units. The contribution margin ratio is 48 ÷ 80, or 60%, so break-even revenue is 12,000 ÷ 60%, or 20,000.
If the 12,000 operating profit is treated as the net benefit and the initial investment is 15,000, the simple ROI is (12,000 − 15,000) ÷ 15,000 × 100, or -20% for this period. This illustrates why operating profit and investment recovery should be shown separately. The initiative may be profitable during the period while still not having recovered its initial cash outlay.
Now test a lower-volume case of 300 units. Revenue falls to 24,000, total variable cost becomes 9,600, and operating profit becomes 24,000 − 9,600 − 12,000, or 2,400. The model remains profitable, but the return is much weaker. A scenario table makes this sensitivity visible without changing the base assumptions.
When to recalculate
Recalculate the model whenever a material input changes. At minimum, revisit it when pricing changes, variable costs move, expected sales volume is revised, a supplier or payroll cost changes, the initial investment increases, or the timing of launch changes.
It is also useful to update the model at regular planning intervals and after actual results become available. Replace estimates with observed price, volume, cost, and conversion data where appropriate. Then compare the original forecast with actual performance and record which assumptions caused the difference.
For labor-heavy initiatives, connect the model to an overtime cost calculator or utilization rate calculator. For marketing initiatives, review customer acquisition cost and cost per lead assumptions. For capacity constraints, use the operational capacity calculator.
Finally, do not use ROI as the only decision rule. Consider cash timing, operational capacity, risk, strategic fit, and the assumptions that would need to be true for the forecast to hold. A clear spreadsheet makes those conditions visible, allowing the decision-maker to test them rather than relying on a single headline percentage.
