Financial Modelling

This article is for FP&A analysts, finance managers and finance business partners who build and use models inside operating companies. It does not cover DCF valuation for investment banking or LBO mechanics for private equity.

Those disciplines have different model requirements and different audiences, and they are already well covered elsewhere.

What Financial Modelling Actually Means in Corporate Finance

In investment banking, financial modelling is usually a transaction tool, built to support a deal, value a company or structure a financing. In corporate finance and FP&A, models are operating tools.

You use them to set the annual budget, project cash flow, evaluate capital allocation decisions, run scenario analysis before a board meeting, or maintain the rolling forecast the business checks against plan every month.

That difference changes almost every design decision. A transaction model might be used once by a small team under time pressure.

An FP&A model might be updated monthly by several people over a multi-year horizon, so it must be easy to audit and update from the first version.

Corporate finance models also need to connect to how the business actually operates. Revenue in an FP&A model is rarely a single growth rate assumption; it is usually built from volume, pricing, product mix, customer cohorts or sales pipeline data.

You need to understand how the business generates revenue and cost before you can build a model that produces useful output.

---

Types of Financial Models: Choosing the Right Tool for the Job

Different modelling tasks call for different model structures. Using the wrong model type creates work and produces output that is harder to act on. The table below maps the most common model types to their use cases, typical owners, and relative complexity.

Three-Statement Models

The three-statement model is the foundation of integrated financial modelling. It connects the income statement (P&L), balance sheet and cash flow statement so that a change in one flows through to the others automatically.

Increase revenue in year two and the model adjusts gross profit, operating income, tax, net income, retained earnings on the balance sheet and, eventually, operating cash flow, with no manual recalculation.

This integration is what makes a three-statement model genuinely useful for FP&A. A standalone P&L projection tells you whether the business will be profitable.

A three-statement model tells you whether it will have the cash to operate, what the balance sheet will look like under different scenarios, and whether working capital changes create a cash timing problem the P&L does not show.

Budget Models

An annual budget model is typically a detailed bottom-up plan for the coming financial year. It captures planned headcount, compensation, non-headcount operating expenses, capital expenditure, and revenue by product or segment.

Most budget models are built in Excel or Google Sheets, though many finance teams are migrating to FP&A platforms for the collaboration and version control benefits.

The challenge with budget models is that they become stale the moment they are finalised. Market conditions change, headcount plans shift, product roadmaps move. A budget model that is not designed for ongoing comparison against actuals quickly becomes a document rather than a working tool.

This is one of the drivers behind the shift toward rolling forecasts.

Driver-Based Models

A driver-based model builds revenue and cost projections from the operational metrics that actually drive them, rather than from line-item extrapolations of historical data. Instead of projecting revenue by taking last year's number and applying a growth rate, a driver-based model starts with inputs like:

• Number of new customers acquired per month
• Average contract value or average transaction size
• Churn rate or renewal rate
• Headcount per functional area
• Revenue per sales rep at full productivity

These inputs are the drivers, and the model translates them into financial outputs. The advantage is that business leaders can debate the drivers in operational terms ("are we assuming we can hold the churn rate below 8%?") instead of interpreting an abstract revenue percentage.

Driver-based models also produce better forecasts because the assumptions are grounded in operational reality. If the sales team knows they can hire and ramp eight new reps in a given period, that constraint belongs in the model.

A simple revenue growth rate assumption has no mechanism to capture it.

Scenario and Sensitivity Models

A scenario model lets you switch between assumption sets and see the financial impact of each, which is the mechanical side of scenario planning.

A typical corporate FP&A scenario model has a base case, an upside case and a downside case, each with its own assumptions for revenue growth, cost inflation and capital requirements.

Sensitivity analysis is related but distinct. Rather than toggling between named scenarios, a sensitivity analysis shows how the output changes as a single input varies across a range.

Showing how EBITDA changes as gross margin moves from 58% to 70% in one-point increments is a sensitivity analysis. It is a useful complement to scenario analysis when you want to identify which assumptions the model output is most responsive to.

Rolling Forecast Models

A rolling forecast model is updated regularly, usually monthly, and always looks forward a fixed number of months, typically 12 to 18. A static annual budget gets less relevant as the year advances; a rolling forecast keeps the same forward horizon all year.

The design implications for a rolling forecast are significant. The model needs to accommodate the addition of a new forecast month each time a historical month closes. Assumptions need to be easy to update. The model needs to distinguish clearly between actuals and forecast periods.

And the comparison logic needs to reflect that "versus budget" becomes less interesting as the year progresses and "versus prior forecast" becomes more relevant.

---

The Structure of a Well-Built Three-Statement Model

For FP&A teams, the three-statement model is the architecture that underlies most integrated planning work. Understanding how to build it correctly is one of the most transferable skills in corporate finance.

Income Statement to Balance Sheet to Cash Flow

The three statements link in a specific sequence. The income statement is the starting point: revenue minus cost of goods sold gives gross profit, minus operating expenses gives EBIT, minus interest and taxes gives net income.

Net income feeds into the balance sheet through retained earnings. Changes in operating working capital items (accounts receivable, inventory, accounts payable) appear on both the balance sheet and the cash flow statement.

Depreciation is a non-cash charge on the income statement that is added back in the operating section of the cash flow statement.

The cash flow statement reconciles from net income to the actual movement in cash:

1. Start with net income 2. Add back non-cash charges (depreciation, amortisation) 3. Adjust for changes in working capital 4. Add investing activities (capital expenditure, asset disposals) 5. Add financing activities (debt raised or repaid, equity issuance, dividends) 6.

The result is net change in cash, which updates the cash balance on the balance sheet

The model balances when total assets equal total liabilities plus equity on the balance sheet. If it does not balance, there is an error somewhere in the linkages. Testing for balance is one of the most basic model integrity checks.

The Revolver: Managing the Circular Reference

Most three-statement models include a revolving credit facility (a revolver) that the company draws on when it needs cash and repays when it has excess cash.

This creates a circular reference: the interest expense on the revolver depends on the revolver balance, which depends on the cash position, which depends on the interest expense.

Excel and Google Sheets handle circular references through iterative calculation, which you have to enable explicitly in the application settings. In a well-designed model, the revolver is the only intentional circular reference.

Any other circular reference, such as income taxes depending on net income, which depends on income taxes, is an error and should be resolved.

---

Driver-Based Modelling: Why It Produces Better Forecasts

The case for driver-based modelling is operational more than technical. When finance teams build forecasts from operational drivers instead of historical line-item trends, they have to make the model's assumptions legible to the people who control the outcomes.

Connecting Finance to Operations

Consider a SaaS business forecasting subscription revenue. A line-item approach might apply a 15% growth rate to last year's recurring revenue. A driver-based approach would instead model:

• Opening ARR (actual)
• New ARR from new customer wins (driven by pipeline conversion rates and sales headcount)
• Expansion ARR from upsells (driven by net revenue retention assumptions)
• Churned ARR (driven by a churn rate assumption)
• Closing ARR = opening + new + expansion − churned
• Monthly revenue = closing ARR / 12

The second approach is more work to build. But when the head of sales says the pipeline coverage is insufficient to hit the new ARR target, the model immediately shows the revenue impact. The driver-based approach makes that conversation happen. The line-item approach obscures it.

Choosing the Right Drivers

Not every line item in a P&L needs to be driver-based. The discipline is to identify which cost and revenue lines are large enough, variable enough, or strategically important enough to warrant a driver. Typical candidates include:

• Revenue by product or segment
• Cost of goods sold where volume is a meaningful factor
• Sales and marketing spend relative to pipeline or customer acquisition
• Headcount-related costs (compensation, benefits, recruiting)
• Variable infrastructure costs in technology businesses

Fixed administrative costs that do not move materially with volume are often better modelled as flat amounts or simple inflation-adjusted projections. Applying driver logic to every line in the model does not make it more accurate. It makes it harder to maintain.

---

Model Design Principles for FP&A Practitioners

A model that produces correct output but cannot be audited, updated, or understood by anyone other than its original builder is a liability. Model design is as important as model mechanics.

One Input, One Place

Every assumption should appear in the model exactly once, in a clearly labelled input section. If the gross margin assumption appears in three cells across two worksheets, one of those cells will eventually be updated and the others will not.

The output will be wrong, and the error may not be obvious.

The principle is simple: inputs belong in an input section. The rest of the model reads from that section using cell references. Changing an assumption means changing one cell.

Separate Inputs from Calculations from Outputs

A well-structured model has three distinct layers:

1. Inputs / Assumptions: All editable variables live here: growth rates, margin targets, headcount plans, pricing assumptions and macro inputs. These cells are left editable (not sheet-protected) and clearly labelled. In Excel, they are typically shaded in a specific colour (yellow is common).

2. Calculations: Formulas that transform inputs into intermediate results. These cells should not be hardcoded. Anyone who opens the model should be able to trace any calculation back to its source inputs. Calculation cells are typically unshaded or grey.

3. Outputs: Summary tables, charts, and dashboard metrics that present results to stakeholders. These cells pull from calculation layers and should never contain assumptions.

Colour Coding Standards

Industry standard colour coding in Excel-based financial models is:

• Blue text: Hardcoded inputs (numbers typed directly into a cell)
• Black text: Formulas referencing cells within the same worksheet
• Green text: Formulas referencing cells on another worksheet
• Red text: Formulas referencing external workbooks (use sparingly; external links are a maintenance problem)

Some teams use cell background colours rather than font colours, or use both. What matters is consistency. A model where the colour logic is inconsistent or absent is significantly harder to audit.

Auditability: Can Anyone Else Check Your Work?

A model is auditable if another trained person can open it, understand its structure within a few minutes, trace any output back to its input assumptions without guidance, and identify the source of a given calculation. Tests for auditability include:

• Are all worksheets named descriptively?
• Are all assumption cells labelled, with units (%, $, number of headcount)?
• Are formulas consistent across rows, or does each cell contain a bespoke formula?
• Is there a model documentation tab or a read-me?
• Are there error checks built in (for example, a balance check on the balance sheet, a check that the cash flow reconciles)?

---

Model Complexity: The Risk of Over-Engineering

One of the most common mistakes in corporate FP&A modelling is building more complexity than the decision requires.

A highly granular model with 47 revenue sub-lines, seven scenario toggles, and a Monte Carlo simulation output is not inherently better than a clean, well-structured model with five revenue drivers and three scenarios. It is often worse, because:

• More complexity means more maintenance
• More complexity means more opportunity for formula errors
• More complexity usually means fewer people can use and trust the model
• Time spent on model engineering is time not spent on analysis and insight

Calibrating Complexity to the Decision

A useful heuristic: build the simplest model that can answer the question being asked without sacrificing structural integrity. For a monthly rolling forecast, the question is usually "where are we tracking versus plan and what does the next 12 months look like?"

That does not require a 15-tab model. For a capital investment evaluation, the question might require a detailed project cashflow model, sensitivity analysis on utilisation rates, and a comparison of financing scenarios.

The right level of complexity is determined by the decision, not by the analyst's desire to demonstrate sophistication.

---

Rolling Forecasts vs Static Annual Budget Models

The move from static annual budgets to rolling forecasts is one of the bigger changes in FP&A practice in recent years. In 2026, most mature FP&A functions keep both: an annual budget for target-setting and external reporting, and a rolling forecast for operational decision-making.

What Changes in the Model Design

A static annual budget model has a fixed structure: 12 months, January through December, with comparative columns for prior year actuals and the budget. Once published, the assumptions do not change during the year.

A rolling forecast model has a different design requirement. Each month, a new month is added to the forecast horizon as the oldest month rolls into actuals. This means:

• The model needs a clear demarcation between actuals (pulled from the accounting system) and forecast periods (driven by assumptions)
• The assumption inputs need to be easy to update without rebuilding the model
• The model should be able to show the current forecast versus the prior forecast, not just versus the original budget
• The time horizon is typically 12 to 18 months forward, regardless of calendar year

Many teams use a separate rolling forecast tab that feeds from a shared assumption set, while keeping the annual budget tab frozen as the original plan. This allows both structures to coexist without conflating them.

---

Common Financial Modelling Errors

Structural Errors

Hardcoded assumptions scattered through the model. Numbers typed directly into formula cells, instead of referencing a central assumption input, are invisible and hard to change, and they will cause version-control problems.

No scenario toggle structure. A model without scenario capability forces the user to overwrite base case assumptions to test a downside, which destroys the base case. A scenario toggle, even a simple dropdown that selects between assumption sets, solves this.

Unintentional circular references. Circular references that are not the deliberate revolver mechanic are errors. They can produce misleading results if iterative calculation is not enabled, or silently incorrect results if it is.

Inconsistent formula logic across rows. In a well-built model, the formula in column C of row 12 should be logically identical to the formula in column D of row 12, just referencing the next period.

If each column contains a bespoke formula, the model is fragile and nearly impossible to audit.

Design Errors

No assumption documentation. A model where the source and rationale for key assumptions are undocumented requires the original builder to be present for any meaningful review. That is a single point of failure.

Outputs mixed with inputs. When summary tables contain hardcoded override numbers mixed with live formula outputs, the model becomes unreliable. Anyone updating the model may change a formula without realising a hardcoded number is sitting on top of it.

Excessive tab proliferation. A model with 25 worksheets, most of which contain minor calculations, is harder to navigate and audit than a model with 8 well-organised tabs. Tab structure should follow the logic of the model, not grow organically over years of additions.

Analytical Errors

Single-point estimates with no sensitivity context. A five-year revenue projection with no scenario or sensitivity analysis is a single guess dressed up as a forecast. Any material decision based on it is based on false precision.

Confusing nominal and real figures. Mixing inflation-adjusted and nominal assumptions in the same model produces nonsensical outputs. The model should be explicit about whether all figures are in nominal or real terms.

Failing to reconcile to actuals. A model that is never compared against what actually happened has no feedback loop for improving its assumptions. Building a simple actuals-versus-forecast comparison into the model structure forces that discipline.

---

Model Review and Stress-Testing Checklist

Before presenting a model to a CFO, board, or senior stakeholder, run through the following checks.

Structural Integrity

• [ ] The balance sheet balances in every period
• [ ] Cash flow reconciles from net income to ending cash in every period
• [ ] No unintended circular references (check with Excel's formula auditing tools)
• [ ] All tabs are named descriptively and in logical order
• [ ] Colour coding is consistent throughout

Assumption Quality

• [ ] All assumptions are in a dedicated input section, labelled with units
• [ ] Key assumptions are sourced or documented (market data, management guidance, historical actuals)
• [ ] No hardcoded numbers buried in formula cells
• [ ] Assumption periods match the model's time horizon

Scenario and Sensitivity

• [ ] A base case, upside case, and downside case are modelled
• [ ] The model can toggle between scenarios without overwriting assumptions
• [ ] Sensitivity analysis has been run on the two or three assumptions that most affect the key output metric
• [ ] The downside case is genuinely stressful, not a mild variant of the base case

Output and Presentation

• [ ] Output tables and charts pull from calculation cells (no manual overrides)
• [ ] All figures use consistent formatting (currency, decimal places, units)
• [ ] The model has been reviewed by someone other than its builder
• [ ] There is a summary tab that presents key outputs without requiring the reader to navigate the full model

---

Tools: Excel, Google Sheets, and FP&A Platforms

Excel

Excel remains the dominant financial modelling tool in corporate finance. Its formula flexibility, pivot table capability, and broad familiarity make it the default for most FP&A teams.

The main limitations in an FP&A context are version control (who has the latest file?), collaboration (only one person can edit at a time), and the ease with which a formula can be accidentally overwritten.

Well-designed Excel models mitigate these risks through clear structure, protected sheets for output areas, and rigorous naming conventions. But they cannot fully eliminate the version-control and collaboration constraints.

Google Sheets

Google Sheets solves the collaboration and version-control problems that Excel cannot. Multiple users can work in the same file simultaneously, and the revision history is automatic. For smaller, faster-moving finance teams, this is often the right trade-off.

The limitations are formula depth (some complex Excel formulas do not exist or behave differently in Sheets), performance with large datasets, and the absence of features like Power Query that experienced Excel users rely on.

For highly complex integrated models with many tabs and large data ranges, Excel typically still outperforms Sheets.

Purpose-Built FP&A Platforms

Tools like Anaplan, Pigment, Mosaic, Cube, and Planful are purpose-built FP&A software. Mid-market and enterprise finance teams usually move to one when spreadsheets can no longer keep up with scenario planning or with pulling actuals in from the ERP.

What changes about model design in these platforms:

• Driver logic is native. Most FP&A platforms are built around driver-based modelling. The concept of building revenue from operational metrics is embedded in the platform's data model, not something you have to engineer from scratch.
• Actuals integration is automated. Rather than manually importing actuals from the accounting system each month, FP&A platforms typically connect directly to the ERP and pull actuals automatically. This removes a significant source of error and manual effort.
• Version control is built in. Scenarios and versions are first-class objects in most platforms. You can maintain multiple forecast versions without file proliferation.
• The flexibility trade-off is real. FP&A platforms give up some of Excel's formula flexibility in exchange for structure, governance and scale. Complex bespoke calculations that are easy in Excel may need workarounds in a platform. That is no reason to avoid platforms, but you should understand the trade-off before committing.

---

---

Conclusion

Financial modelling in FP&A is a practical discipline. The models that earn their keep are rarely the most sophisticated; they are easy to update and audit, and tied to decisions the business actually has to make.

Matching the model type to the use case, keeping assumptions transparent and in one place, and sizing complexity to the question are what separate a finance team that builds tools from one that builds documents.

---

Sources

This article draws on standard corporate finance and FP&A practice. Model design conventions (colour coding, three-statement linkage, revolver circular reference treatment) follow conventions widely used by practitioners and finance training providers. Tool comparisons reflect the FP&A software market as of early 2026.

No proprietary data sources are cited.