Financial Model

What is a financial model?

Financial model (German: Finanzmodell) is a calculation model, usually in a spreadsheet, that works out the future development of a company from a common set of assumptions. Its core is an integrated financial plan, hence also the term integrated financial model. Income planning (the planned profit and loss account), balance sheet planning (planned assets and capital) and liquidity planning (planned receipts and payments) are linked in it by calculation. If an assumption changes, such as a price, a hiring rate or a payment term, all three sub-plans work through the consequences.

Financial models are used in funding rounds, in company sales, in loan financing and in ongoing management. Unlike a purely internal plan, a financial model for a funding round or a sale is geared towards capital providers. It calculates several scenarios, shows the key figures investors ask for, and derives the capital requirement or the company value. It supplies the figures for the business plan, the pitch deck and the information memorandum, the detailed presentation of the company in the sales process. Building and maintaining such models is called financial modelling; within the company, this task lies with the finance function, often with external support.

Structure of an integrated financial model

A financial model is built in a fixed direction: from the actual figures and assumptions through the individual plans to the three sub-plans and the key figures. This division into worksheets is widespread:

• Assumptions: all variable input values in one place, such as prices, new customers per month, churn rate, salaries, payment terms and investments.
• Revenue planning: built from the bottom up from quantity and price; for recurring revenue, from the existing base, new business, expansions and cancellations.
• Headcount planning: positions by function and start month, gross salaries, employer contributions and salary adjustments.
• Operating costs and investments: broken down by cost type, with depreciation for capitalised investments, that is, investments recorded as assets in the balance sheet.
• Income planning: the planned profit and loss account down to the net profit after tax.
• Balance sheet planning: fixed assets, receivables, liquid funds, equity, provisions and liabilities at the end of each period.
• Liquidity planning: receipts and payments, derived from income and balance sheet changes, with financing and minimum liquidity.
• Evaluations: key figures such as ARR (annual recurring revenue), gross margin (the share of revenue left after direct costs), burn rate, runway and customer acquisition costs, as well as the comparison of the scenarios.

A model is integrated when the three sub-plans are linked by calculation. The net profit changes the equity, and the cash balance in the balance sheet corresponds to the closing balance of the liquidity plan. In every period the balance sheet balances: the sum of the assets equals the sum of the liabilities and equity. This balance check is the simplest test of whether a model is mathematically closed.

Assumptions, scenarios and sensitivities

The quality of a model depends on its assumptions. Each assumption is entered only once and carried into the calculation from there; no fixed numbers appear in formulas. Colour coding distinguishes input values from formulas and from links to other sheets. Every material assumption has a source or justification: actual values from the past, contract terms, quotations or market data.

Scenarios change several assumptions together and show how the company develops under different conditions. There are usually three: a base case, a cautious case and an optimistic case. Further cases increase the maintenance effort without improving the decision. All cases run through the same formulas and differ only in the input values. The cautious case shows how long the funds last if new business and prices fall short of the plan; this is asked about in almost every funding round.

Sensitivity analyses, by contrast, change a single assumption and make its leverage visible, for example the effect of 20% fewer new customers on capital requirement and break-even point. They show which assumptions carry the plan and therefore have to be supported most carefully in the review.

Liquidity, VAT and payment terms

For growing companies, liquidity planning is the most important part of the model, because income and cash flow diverge in time. Customers pay after the payment term, annual contracts are billed partly in advance and partly monthly, and salaries go out every month. Under taxation according to agreed consideration (accrual basis, Sollversteuerung), VAT arises with the supply of the service. It is in principle paid with the advance return for the month of the supply, even if the customer has not yet paid. In addition there are advance tax payments, whose timing differs from the tax expense, loan repayments, which do not appear in income planning at all, and investments, which become visible there only through depreciation.

Liquidity planning yields the key figures that investors ask about first. These are the monthly burn rate (the cash outflow per month), the length of time the funds last (runway) and the capital requirement up to break-even or the next financing milestone. They belong prominently on the evaluation sheet and must be readable in all three scenarios.

The financial model in due diligence and valuation

In funding rounds and sales processes, the model is an object of review. Investors and buyers are often supported by an audit or advisory firm that examines the financial figures (financial due diligence). Three things are checked: whether the actual figures match the bookkeeping and the monthly closings, whether the past development supports the assumptions, and whether the calculation logic is error-free. The model is handed over as a working file with open formulas. If it exists only as a PDF or with hard-coded values, no scenarios of one's own can be calculated, and the plan is assessed with correspondingly more caution.

For valuation, the model supplies the cash flows that are discounted to the present day in a DCF valuation, and the plan values to which multiples are applied. Both routes generally lead first to the enterprise value, the value of the operating business before deducting debt. After deducting net debt, this gives the value of the shares. Buyers usually do not calculate with the seller's base case but with a case of their own, which they derive from the review. The better the assumptions are supported, the smaller the gap between the two cases and the less purchase price is lost in this adjustment.

Worked example: payment terms in the financial model

A software company plans constant monthly revenue of EUR 300,000 net and a monthly net cash outflow after deducting all receipts (net burn) of EUR 180,000. It pays VAT on its revenue on the basis of agreed consideration, and the VAT rate is 19%. The question is what happens if the customers' payment term is extended from 30 to 60 days. For simplicity, all customers pay punctually at the end of the payment term.

• Gross amount of one month's revenue: EUR 300,000 × 1.19 = EUR 357,000
• Receivables balance with a 30-day payment term: one month's revenue gross, i.e. EUR 357,000
• Receivables balance with a 60-day payment term: two months' revenue gross, i.e. EUR 714,000
• Additional liquidity tied up: 714,000 minus 357,000 = EUR 357,000
• Income planning: unchanged, revenue and profit stay the same
• Balance sheet planning: receivables rise by EUR 357,000, liquid funds fall by the same amount
• Effect on the runway: 357,000 divided by 180,000 = 1.98, i.e. around two months less, as a one-off

The gross amount is tied up because the company has already paid the VAT over before the customer pays. A pure income plan does not show this change. Only the link to the balance sheet and liquidity makes it visible that a concession in sales costs around two months of runway. The same mechanics apply to the entire need for working capital, for example for inventories or down payments to suppliers.

Distinction: financial model, integrated financial planning and business plan

• Integrated financial planning: the linking by calculation of the planned profit and loss account, the planned balance sheet and the liquidity plan. Every robust financial model contains it as its core and adds scenarios, investor key figures and the derivation of the capital requirement or company value.
• Business plan: the overall document on strategy, market, organisation and planning. The financial model supplies the figures; the business plan explains them in text and graphics.
• Liquidity planning: one of the three sub-plans of the model. On its own it shows cash flows, but neither income nor the asset position.
• Budget and forecast: The budget is the approved plan for the next financial year, often derived from the first plan year of the model and refined by cost centre. The forecast is the ongoing projection of the current year from actual values and current expectations.

Common mistakes

• Revenue as a flat growth rate. A monthly increase by a fixed percentage is no substitute for a derivation from sales capacity, closing rates and prices.
• Fixed numbers in formulas. Values that sit in formulas rather than on the assumptions sheet are overlooked when things change and cannot be traced in the review.
• Income planning only. Without a balance sheet and liquidity, payment terms, VAT, investments and loan repayments remain invisible, which are exactly the items that decide the runway.
• Scenarios in separate files. If the cases are calculated in copies of the model, the formulas drift apart and the cases are no longer comparable.
• No reconciliation with actual figures. If the historical values do not match the monthly closings, the model loses its credibility before the plan is even discussed.
• Excessive level of detail. Hundreds of rows do not increase accuracy but the susceptibility to errors, and they make the review harder for investors.

Frequently asked questions

What period should a financial model cover?

At least three years, often five. The first 12 to 24 months are planned monthly; after that, quarters or years are sufficient. For a DCF valuation, the planning period is chosen so that at its end the company reaches a steady state, that is, it carries on with even growth and stable margins.

Is a financial model built in a spreadsheet or in planning software?

For funding rounds and sales processes, as a rule in a spreadsheet. Investors and buyers want to trace the formulas and enter assumptions of their own. Planning software is suitable for ongoing management, provided it can output a complete, formula-based model.

Can a financial model be built from a template?

Yes, as a starting point. Spreadsheet templates provide the structure of assumptions, sub-plans and evaluations and usually already contain the balance check. Every company has to build the revenue derivation, headcount planning and payment terms itself. Adopted unchanged, a template rarely fits, for example if it calculates one-off sales but the company lives on recurring revenue.

How often is the financial model updated?

Monthly, with the actual figures from the monthly closing, combined with a budget-vs-actual comparison. The forecast for the current year arises from actual values and the current expectation for the remaining months. The plan itself is revised when assumptions prove permanently inaccurate, and in any case before a funding round.

The Roemer Capital view

We build financial models to a fixed structure that stays the same across all engagements. It consists of an assumptions sheet, integrated income, balance sheet and liquidity planning, and an evaluation that shows the runway and capital requirement in every case. The model covers at least three years and calculates a base, a cautious and an optimistic case through the same formulas.

Before a funding round begins, we use the model to check whether the runway at the start of the process is 12 to 18 months. Less leaves too little room for negotiation in a round that takes six to eight months. In fractional CFO engagements, we keep the model up to date with the monthly closings so that the figures in the data room always correspond to the latest actual position.