Financial model calculations: formulas, scenarios and assumptions (steps 10–14)

Financial model calculations are the relationships between metrics written as formulas.

Every formula must follow business logic: sales drive marketing, output drives headcount. Detail only what affects the result.

Build as many scenarios as there are real paths to the goal. The structure is inputs, calculations and outputs, with all assumptions kept in one place.

This is the third article in our financial model series, steps 10–14 of 20.

• The first article — Financial model: what it is, what it consists of and how to build one. A model is a management tool, not a set of calculations. It covers the goals of modelling, typical mistakes, the building blocks and quality requirements.
• The second article — Sales forecast and resource planning. Designing the model: roadmap, success markers, sales plan, resources, business units.

With the architecture defined, we now fill the model with calculations, scenarios and data — without them it remains a theoretical shell.

This is where the model becomes a working tool.

It simulates business scenarios, calculates key metrics and supports decisions, while staying robust to external changes and fitting different management tasks.

Step 10. Justified mathematical relationships

A financial model is a system of linked calculations. An error in the links makes the forecast useless. Key relationships:

• production and sales — part of the output is sold immediately, part goes into stock;
• sales and marketing — as sales grow, advertising and PR spending usually grows;
• headcount and output — more production means more hiring, salaries and taxes.

How to build relationships

The same cost line can be linked to a driver in different ways. Marketing and PR, for example:

• as a percentage of last month’s actual revenue — a smooth link to real results;
• as a percentage of next period’s planned revenue — advertising runs ahead of sales;
• as the cost of acquiring one customer (CAC), if conversion from advertising to sales is known.

Direct production costs:

• grow in proportion to output if unit cost is fixed;
• or more slowly than output if there are economies of scale — bulk raw materials are cheaper, for example.

How to choose the formula

If the project is already running, base the relationships on actual data for the past period. If it is at the idea stage, use data from similar businesses or market benchmarks.

Every relationship must have a logical explanation, otherwise the forecast is meaningless.

A good test: can you tell an investor in one sentence why this line is calculated this way?

Step 11. Level of detail

Depth determines both accuracy and usability. Detailing everything is not the best strategy.

How to decide the right level of detail

• By purpose. For operational management detail matters: costs per employee, monthly raw material price trends. For investors the model must be clear and logical, without excess detail, so the key metrics are easy to read.
• By available data. If accurate data is not available, extra detail will not improve accuracy — it only creates an illusion of control. Aggregated calculations are better.

Examples of different levels of detail

The more detail, the more accurate the calculations — but the more complex the model and the longer it takes to update.

Detail only what affects the key metrics. If detail does not lead to better decisions, it is a waste of time.

Step 12. Scenarios: three base cases are only the start

A single “base” model is almost never enough. Usually several scenarios are built:

• optimistic — everything goes to plan or better;
• realistic — normal market fluctuations and minor delays;
• pessimistic — falling demand, rising purchase prices, supply delays, higher interest rates.

These three are not enough: they do not cover all the ways a project can develop. In practice there are different paths to the goal:

• production capacity can be built, bought or leased;
• the project can run in different jurisdictions with different tax consequences;
• IT infrastructure can be developed in-house or bought off the shelf;
• to scale, you can grow the team or use outsourcing and subcontractors.

Working with scenarios in the model

The model must let you switch quickly between scenarios and see their effect on the result. For example:

• How does profitability change if production is leased rather than built?
• How does payback change with outsourcing instead of in-house staff?
• How much does net profit change with a different tax jurisdiction?

How many scenarios you need

Depending on the project, not 3 but 10 or 20. A real business might model 5 ways of raising finance, 4 locations and 3 market-entry strategies.

The model for reviving the Nauticat yacht brand had 18 development scenarios and more than 300 parameters.

The scenarios were used to choose the brand’s development path, not just to show investors a “good” case.

If the model handles such options flexibly, it stops being a calculation and becomes a decision support system.

Its job is not just to “crunch numbers” but to find the most effective path to the goal. So switching scenarios must be simple and the consequences clear.

Usually the scenario is selected in one cell on the inputs sheet, and the whole model recalculates.

Sensitivity analysis

A related tool is sensitivity analysis. You change one parameter — price, sales volume or purchase price — by ±10–20% and watch how profit, cash flow and NPV respond.

It shows which assumptions the project depends on most. They need the most careful justification and close monitoring after launch. Stress tests are covered in the fourth article of the series.

Step 13. Model format: fit for purpose

There is no universal format — it depends on the purpose and the client’s requirements.

• For internal use (management accounting, performance analysis) — usually a detailed Excel file or specialised software with in-depth parameters.
• For investors — clarity and structure: clear conclusions, visualised key metrics, easy work with scenarios.

Model structure

Whatever the format, the model is logically divided:

• Inputs — prices, costs, taxes, discount rates, scenario variables.
• Calculations — sales, cost of sales, investment, taxes, production.
• Outputs — profit and loss (P&L), cash flow, balance sheet, financial ratios.
• Analytics — charts, diagrams, dashboards.

Choosing the format

• The more complex the project, the more detail matters: a multi-layered business needs flexible scenarios and detailed analysis.
• The more users, the simpler the interface. If investors and managers use the model as well as finance staff, it must be accessible and clear.
• Data must be easy to update. If figures change often, the structure must allow quick changes without “breaking” the calculations.

A model is not a file but a management tool, and its format must fit the job.

Step 14. Inputs and assumptions: the key drivers

Every model rests on inputs and assumptions. It is not enough to list variables — you need to understand which factors really drive the result.

What goes into the inputs

• Revenue: price and its trend; volumes and seasonality; number of customers and marketing-to-sales conversion.
• Costs: purchase prices; logistics, storage, packaging; operating expenses — rent, utilities, depreciation; payroll — salaries, bonuses, taxes and contributions, indexation.
• Investment: cost and depreciation of equipment; infrastructure; licensing, certification, legal set-up.
• Macro factors: discount rate; inflation and exchange rates; possible tax law changes. For a project in Spain it is convenient to keep tax rates on a separate constants sheet: some corporate tax rates for smaller companies change year by year in 2025–2028.

Static or dynamic

For each variable, decide whether it changes over the project:

• will the raw material price stay fixed or rise;
• will labour costs be indexed;
• how will the tax structure change with a different jurisdiction.

A model usually has 200–300 inputs and assumptions, and complex projects have more.

Why it matters

• Assumptions are the foundation. Wrong inputs make any forecast useless.
• Changing parameters lets you model risk. What if purchase prices rise 15% and the exchange rate moves 20%?
• Transparency. If parameters are documented, any user understands what the calculations rest on and which variables are critical.

A model is a structure of interrelated factors. The clearer the inputs and assumptions, the more realistic the forecast.

Frequently asked questions

How many scenarios should a financial model have?

At least three — optimistic, realistic and pessimistic. Better still, as many as there are real paths to the goal: financing options, locations, market-entry strategies. Complex projects have 10–20 scenarios.

How is scenario analysis different from sensitivity analysis?

A scenario changes a whole set of assumptions at once — for example, “lease instead of build” or “a crisis year”. Sensitivity analysis changes one parameter and shows how strongly the result depends on it.

Can numbers be typed directly into model formulas?

No. All numbers should live on the inputs sheet and formulas should reference them. A number hard-coded into a formula does not change when the scenario changes and breaks the model without anyone noticing.

How detailed should a model for investors be?

Detailed enough for the key metrics to be read at once and for every assumption to be justified. Excess detail gets in the investor’s way; inside the company a more detailed version is needed for management.

Key points about calculations and scenarios

• Every formula follows business logic and rests on actual data or market benchmarks.
• Detail only what affects the key metrics.
• Three scenarios are the minimum; model every real path to the goal.
• Structure: inputs — calculations — outputs; numbers only on the inputs sheet.
• Next: validating, agreeing and using the model, steps 15–20.

All 20 steps in one file — the guide “Financial Model: 20 Practical Steps” (PDF).