Financial Modelling: Definition & Methods

Financial modelling is the process of using historical financial data along with key assumptions to forecast future financial outcomes.

Think of it like a financial analyst’s crystal ball.

Whenever your finance team, accountant, or business consultant needs to make key decisions related to investments, budgeting, raising capital, mergers and acquisitions, and strategic planning, they use this crystal ball to see the financial situation if a given set of assumptions plays out.

Financial models can vary in complexity depending on what you’re building them for. For example, a three-statement model is simple. You make assumptions about revenue growth, bad debts, and a few other metrics, and you’re set.

But building an M&A (merger and acquisition) or leveraged buyout (LBO) model is a lot more complex.

Suppose you’re about to acquire another company. You want to get a preview of your income statement, balance sheet, and cash flows post-acquisition.

To get that preview, you need to build a financial model that:

• Consolidates the financial statements of both companies
• Factors in your assumptions about revenue growth, post-acquisition cost structure, cash flows, and more while keeping the benefits of synergies in mind
• Accounts for the deal’s tax implications
• Factors in the impact of debt, debt covenants, and repayment schedules

• Financial models are a window into your business’s financial future.
• There are various types of financial models, each with its own purpose and varying complexity.
• Easy access to accurate data is critical when building financial models.
• Having cloud-based finance management software that integrates with your financial modelling software can make the process of building highly complex models a lot easier with easy access to data. 

Financial modelling is a powerful decision-making and due diligence tool. Here’s why almost every financial professional uses at some point during their career:

• Scenario analysis and data-backed decisions: Models allow you to forecast financial performance and stress-test your numbers under various scenarios. This allows you to evaluate potential financial outcomes, prepare for adverse situations, and make smarter choices about your company’s finances.
• Valuation and deal structuring: Models are indispensable for M&A, raising capital, and IPOs. They’re a key deal structuring tool that allows the company to assess the deal’s viability and evaluate the impact of various financing options.
• Resource allocation: Companies use capital budget models to identify investments that will yield the highest returns. Capital budgets allow companies to optimise capital expenditure and operational costs to maximise shareholder value.
• Investor communication: Models are often used to communicate future potential to investors, analysts, and stakeholders. If you plan to go on Shark Tank, build a model before going on stage.

Models are a staple for analysts, business managers, CFOs, and consultants because every business owner wants to stay on top of their company’s financial future.

Translation?

They build models to assess the financial impact of almost every major financial transaction or scenario you can think of.

7 types of financial models

Let’s talk about the top 10 types of financial models you might see during your financial career.

1. Three-Statement Model

The three-statement model is a simple financial model that projects your income statement, balance sheet, and cash flow statement into the future. Your financial statements may be standalone, or consolidated if you have multiple businesses under one company.

A preview of future financial statements gives you insights into various aspects of financial performance and health, including profitability, cash flow, and capital structure.

Suppose you’re about to borrow money from a bank. One of your existing debts has a covenant that your interest coverage ratio should always be above 3. To confirm that you’ll have enough income for the foreseeable future, you need to build a three-statement model that includes a pro forma income statement.

Factor in your assumptions about macroeconomic factors, revenue growth, and changes in operational expenses into your model and see if you’ll be able to comply with the covenant three or five years into the future.

Three-statement models have various other use cases, including:

• Valuation: It may be used to calculate future free cash flows needed for DCF analysis.
  Scenario and sensitivity analysis: It may be used to stress-test your financial statements under various scenarios.
• M&A: Three-statement models are often used to analyse potential acquisition targets and simulate post-acquisition scenarios.
• Budgeting: Companies may use three-statement models to plan annual budgets and long-term goals.

2. Discounted Cash Flow Model

Discounted cash flow models are the most widely used valuation models. It involves discounting future free cash flows to the current year. That might sound vague, so let’s use an example.

Suppose you want to value your company’s equity. To do so using DCF, you need the following figures:

• Current year’s free cash flow to equity
• Cost of equity (this will be your discount rate)
• Perpetual growth rate (company’s long-term growth rate, typically the long-term GDP growth of the country)

Your company’s free cash flow to equity for the current year is £100,000.

Free cash flow to equity (FCFE) is the cash generated by the company that’s available to be distributed among equity shareholders. Here’s the formula:

FCFE = Net Income + Depreciation and Amortisation - CAPEX - Changes in Working Capital + Net Debt Issued

We’re using FCFE because we’re valuing equity. To value the entire firm, we must use the free cash flows to the firm (FCFF).

FCFF = Net Operating Profit + Depreciation and Amortisation - CAPEX - Changes in Working Capital

You expect FCFE to grow by 10% a year, your cost of equity is 6%, and you expect the company’s perpetual growth rate to be 3%. To value equity, here’s the formula for the perpetuity growth model of DCF that you’ll need to model in your Excel sheet:

Where:

• CF: Free cash flow to equity
• r: Cost of equity (or weighted average cost of capital when valuing the firm)
• g: growth rate into perpetuity

The first part of the formula is discounted cash flows, while the last part of the formula is called a terminal value. This last part is the value of your business at a specific point in the future under the assumption that future cash flows will remain consistent beyond the forecast period.

It’s the largest value in your DCF model—think of it as the tail that wags the dog. A small change in the growth rate or discount rate of the terminal value can significantly move your business’s total value.

Applying the DCF formula to our example:

That’s the value of your equity according to your DCF model. If you have 100,000 equity shares issued and outstanding, your per-share value would be £57.68. This is a fair value according to DCF and may not necessarily be the market value.

There’s another model you can use to value equity: the exit multiple model.

The only thing you need to do differently to use this model is the way you calculate the terminal value. Instead of assuming perpetual growth, you look at the earnings multiple (P/E when valuing equity, EV/EBIT or EV/EBITDA when valuing the firm) of comparable companies and discount the value back to today.

3. Sum of the Parts (SOTP) Model

SOTP models are complex. Not because of the concept itself—it’s just a summation of the different parts of a large business. However, complexity comes from accounting from synergies, shared overheads, and other factors.

SOTP models are commonly used to value:

• Conglomerates: Companies that own multiple, diverse businesses, such as Berkshire Hathaway and General Electric.
• M&A: Transactions where you’re about to acquire or merge with a company with multiple lines of business.
• Divestitures: An individual unit of a company with diverse businesses is to be spun or sold off.

Suppose you own a company with three divisions with the following DCF values:

• Manufacturing: £10 million
• Retail: £5 million
• Technology: £2 million

To value your company, you’ll need to sum the DCF value for all three companies. The summed value (£12 million) is your company’s SOTP value, but after accounting for any synergies (cost savings because of combined operations) and shared overheads (such as corporate costs).

4. Capital Budget Model

Capital budgets are essentially a DCF model used to assess the viability of investing in an asset instead of valuing the asset. It does so by comparing the value of the investment (the DCF value) to the amount you’re paying for the asset. The difference (net present value), if positive, translates to a good investment, and vice versa.

Net Present Value (NPV) = DCF Value - Price

However, you’ll need to use a different rate to discount your cash flows. Unlike when valuing a company’s equity, which requires using the cost of equity, you’ll need to use the weighted average cost of capital (WACC) for capital budgets.

Let’s use the example we used earlier in the DCF section. According to our example, the value of your company’s equity is £5,768,144. If a bigger company offers you £8,000,000, you might consider selling it because the NPV is positive to the tune of £2,231,856.

The same principles apply to buying business assets like machinery. You can create a DCF to discount the cash flows generated from an asset, compare it with the machine’s cost, and verify if the machine is worth investing in.

5. Option Pricing Model

Option pricing models are used to determine the fair (or theoretical) value of a financial option. A financial option is a derivative instrument that gives the buyer the right, but not the obligation to buy or sell:

• An underlying asset (stock, bond, or any other asset)
• At a predetermined price (called the strike price)
• On or before a specified expiration date

The two most commonly used option pricing models are the Black-Scholes model (for European-style options) and the Binomial model (for American-style options).

The formulas for both models are complex and require professional financial training.

Nevertheless, here are the formulas if you’re curious:

Black-Scholes:

Where:

• C: Call option price
• S₀: Spot price of the underlying asset
• X: Strike price of the option
• r: Risk-free interest rate
• T: Time to expiration
• N(d1​) and N(d2​): Cumulative normal distribution functions based on volatility and other inputs

Binomial Option Pricing Model:

Where:

• Vu​: Value of the option after an up move
• Vd​: Value of the option after a down move
• e⁻rΔt: Discount factor to account for the time value of money
• P: Risk-neutral probability calculated as: p = erΔt - d / u - d  (where r is the risk-free interest rate, u is the up factor, d is the down factor, and Δt is the time interval between steps)

6. Merger Model (M&A)

The end goal of a merger model is to evaluate the impact of a merger or acquisition on the acquiring company’s earnings per share (EPS).

If the post-acquisition EPS is higher, the deal is accretive (will increase the company’s EPS), while a lower post-acquisition EPS is dilutive (lowers the company’s EPS).

Here are the most common reasons a deal can be dilutive:

• You’re acquiring a loss-making company
• Low or negative synergies
• The target company’s P/E ratio is greater than yours (assuming you’re the acquirer)
• The deal creates plenty of intangible assets
• Increased interest expense resulting from debt used to finance the deal

Here’s a quick overview of things your merger model should be able to tell you:

• Structure of the deal: Value the target company, add the transaction costs (legal fees, integration costs, etc.), and decide how you’ll finance the acquisition.
• Determine whether the deal is accretive or dilutive: Create a three-statement model for the combined company (called pro forma consolidated financial statements). Factor in the cost savings or revenue enhancements resulting from the merger or acquisition (also called synergies). The pro forma income statement will show whether the deal is accretive or dilutive.
• Stress-test numbers: Run a sensitivity analysis to understand the impact of changes in purchase price, synergies, financing mix, interest rates, and other variables to get a comprehensive view of the deal and related risks.

7. Leveraged Buyout Model

A leveraged buyout (LBO) is a transaction where you acquire a company by financing a major portion of the purchase consideration with debt. The idea is to generate a high return on equity through financial leverage, which means there’s greater risk than a typical acquisition.

Building an LBO model helps evaluate the potential to generate returns that justify the risks. LBO models are quite extensive and include plenty of details, but here are some key elements of an LBO model:

• Purchase price: The price you plan to pay for the company. Suppose it's 8x EBITDA or £800 million.
• Debt and equity mix: The capital structure of the deal, with a major portion composed of debt. Let’s say 70% of the purchase price (or £560 million) is financed through debt.
• Operating projections: Add a three-statement model projecting financial statements over three to seven years to assess the company’s ability to repay debt and generate value for your equity.
• Debt schedule: Factor in debt repayments in the financial statements to understand how much the company can afford to pay each year without draining cash.
• Exit assumptions: Assume that you can sell it at 8x EBITDA and the company’s projected EBITDA after five years is £125 million, so the exit valuation is £1 billion. Your equity’s worth? £1 billion minus the remaining debt.

Private equity firms generally enter LBO deals with a target internal rate of return (IRR) of 20% to 25%. It’s the discount rate at which the NPV of future cash flows (both inflows and outflows) from an investment equals zero.

Think about our DCF example for a moment. If we didn’t have a discount rate, and we assumed the NPV to be zero, plugging in the rest of the numbers and solving the formula for the discount rate would give us the IRR.

The specific data needed to build a model depends on the method you want to use. Here’s a general list of things you need before you start building your model:

• Historical financial data: You need at least three years of financial data to spot trends that may continue in the future. Historical data provides a foundation for the assumptions in your model. The most efficient way to get historical data into your model is by using cloud-based finance software that integrates with Excel or your preferred financial modelling tool.
• Assumptions: Assumptions are based on historical data and your opinion about how historical trends could change in the future. A financial model revolves around your assumptions, and you can never be one hundred percent correct, so try to get as close to reality as you reasonably can.
• External data: Your assumptions may be driven by various external factors, such as industry trends, the macroeconomic environment, and government policy. Consider these when you make assumptions.
• Expert insights: You need people who can share insights about strategic areas of business and how they will manifest on the financial statements. For example, you need a management executive to paint a picture of how synergies between the two companies will impact revenue and cost structure so you can bake that information into your financial model.
• Modelling software: You can build a financial model in Excel. It’s a great tool. But if you need to build highly complex models regularly, consider using a dedicated modelling software like Anaplan, Quantrix, or Adaptive Insights.

Each modelling technique requires a different process. Let’s talk about how you can model an income statement (one of the three statements required for a three-statement model) in Excel.

Step 1: Add historical data

Start by building a layout for the income statement. If you use finance software that’s integrated with Excel, you can pull the income statement data directly into Excel. Ideally, you should start with three years of historical data.

Step 2: Make assumptions

An income statement requires assumptions for the following:

Revenue: Will revenue grow or decline, and by how much?

Cost of goods sold: Will the cost of sourcing or manufacturing your primary goods, or the cost of delivering your primary service, increase or decrease, and by how much?

Operating expenses: Operating expenses include rent, marketing, payroll, and more. Do you expect these to increase or decrease, and by how much?

Non-operating expenses: Non-operating expenses include interest, inventory write-offs, lawsuit settlements, and more. Estimate these amounts based on your best judgement.

Taxes: The tax rate may increase or decrease next year. Make an assumption based on your best judgement.

Step 3: Build your Excel model

You can use Excel or Google Sheets, whichever works for you. Google Sheets is better if you plan to invite others on the team to collaborate.

Here’s what your income statement model might look like:

This is an overly simplified version of a modelled income statement. An income statement model is generally part of a bigger, three-statement model, where figures between all three statements (income statement, balance sheet, and cash flow statement) tie into each other.

Here’s an example of what an extensive income statement model would look like:

You might also need to build schedules for depreciation, debt payments, and more based on your specific case.

Financial data is the most critical part of building an accurate model. Imagine having to manually insert data from financial statements into an Excel sheet to build a model that’s already highly complex.

Use finance management software that integrates with Excel and can transfer the required data directly into an Excel sheet. This saves plenty of time when building a model and eliminates the possibility of human errors.

If you’re looking for cloud-based finance software, book a demo for Access Financials to understand how it can help streamline your financial processes and offer instant access to data needed to build financial models.