Modeling

Financial modeling builds a spreadsheet model of a company's finances to forecast results and test decisions. See the main model types, an example and tips.

4 min readGlossary
Contents
  1. Types of financial models
  2. How a three-statement model works
  3. Financial modeling example
  4. Financial modeling best practices
  5. Financial modeling vs. forecasting
  6. Frequently asked questions

Financial modeling is the process of building a structured spreadsheet, or a model in planning software, that represents a company's or project's finances and projects them forward from a set of assumptions. A financial model turns inputs such as growth, margins, capital spending and financing terms into projected financial statements and outputs such as cash needs, valuation or investment returns, so decision-makers can see the impact of a choice before they make it.

Models are used to value companies, evaluate acquisitions and investments, set budgets, raise debt or equity, and test how results change when assumptions do.

Types of financial models

  • Three-statement model: links the income statement, balance sheet and cash flow statement so that every assumption flows through all three. Most other models are built on top of one.
  • Discounted cash flow (DCF) model: projects unlevered free cash flow, typically for five to ten years, adds a terminal value and discounts both at the weighted average cost of capital (WACC) to estimate enterprise value. Subtracting net debt gives equity value.
  • Leveraged buyout (LBO) model: models an acquisition funded mostly with debt, the repayment of that debt from the company's cash flow and a sale of the business after several years, to calculate the investor's internal rate of return (IRR) and multiple of invested capital.
  • Merger model: combines a buyer and a target to show whether the deal raises or lowers the buyer's earnings per share (accretion or dilution) and how it is financed.
  • Budget and forecast models: operating models that FP&A teams use to plan revenue, headcount and costs by month and department, often built with driver-based planning.

How a three-statement model works

The three statements are tied together by a few links, and those links are what keep the model consistent:

  • Net income from the income statement starts the cash flow statement and increases retained earnings on the balance sheet, less any dividends.
  • Depreciation is added back in operating cash flow, because it is a non-cash expense, and it reduces property, plant and equipment (PP&E).
  • Capital expenditures appear in investing cash flow and increase PP&E.
  • Changes in receivables, inventory and payables adjust operating cash flow and the corresponding balance sheet accounts.
  • Borrowing and repayments appear in financing cash flow and change the debt balance, and interest on that debt flows back to the income statement.
  • Ending cash from the cash flow statement becomes the cash balance on the balance sheet, which must then balance.

Financial modeling example

A company's model projects net income of $2.0 million for the year, depreciation of $0.5 million, a $0.3 million increase in working capital, $0.8 million of capital spending and $0.4 million of debt repayment. It begins the year with $3.0 million of cash.

  • Cash from operations: $2.0 million + $0.5 million − $0.3 million = $2.2 million
  • Change in cash: $2.2 million − $0.8 million capital spending − $0.4 million debt repayment = $1.0 million
  • Ending cash: $3.0 million + $1.0 million = $4.0 million, which flows to the balance sheet

The balance sheet check confirms the links. Assets rise by $1.6 million: cash up $1.0 million, PP&E up $0.3 million ($0.8 million of capital spending less $0.5 million of depreciation) and non-cash working capital up $0.3 million. Liabilities and equity also rise by $1.6 million: retained earnings up $2.0 million, assuming no dividends, and debt down $0.4 million. If the two sides did not agree, a link somewhere in the model would be broken.

Financial modeling best practices

  • Separate inputs, calculations and outputs. Keep assumptions in one clearly labeled place so they can be changed without touching formulas.
  • Never hard-code numbers in formulas. A figure entered into a formula is invisible to reviewers and easily forgotten when assumptions change.
  • Use a consistent format. A common convention is blue font for inputs, black for formulas and green for links to other sheets.
  • Keep formulas consistent across each row. One formula per row, copied across all periods, makes mistakes much easier to find.
  • Build in checks. Include a balance sheet check, a flag for negative cash and totals that reconcile to their subtotals.
  • Keep it as simple as the decision allows. Additional detail adds maintenance work and error risk without always adding accuracy.
  • Document assumptions and versions. Record the source and date of each key input, and save versions so changes can be traced.
  • Test the outputs. Run sensitivity analysis and scenarios on the assumptions that matter most.

Financial modeling vs. forecasting

A forecast is an estimate of future results, such as next year's revenue. A financial model is the tool that produces forecasts and connects them, showing how a revenue forecast affects margins, cash, debt and valuation. A forecast can be made without a complete model, for example by extending a trend, but a model is needed to see the knock-on effects of a decision. Models also do more than forecast: they value businesses and compare alternatives such as leasing versus buying.

Frequently asked questions

What is financial modeling used for?

Financial models are used to value companies, analyze acquisitions and investments, prepare budgets and forecasts, plan financing and test decisions under varied assumptions. Investment bankers, private equity investors, corporate development teams and FP&A analysts build them most often.

What are the most common types of financial models?

The most common are the three-statement model, the discounted cash flow (DCF) model, the leveraged buyout (LBO) model, the merger (accretion/dilution) model and budget or forecast models.

What software is used for financial modeling?

Microsoft Excel is the standard tool, and Google Sheets is used for simpler or shared models. Larger finance teams frequently move budgeting and forecasting into dedicated planning software connected to their accounting system or ERP.

What is a three-statement model?

It is a model that projects the income statement, balance sheet and cash flow statement together, with each connected to the others. A change to any assumption, such as revenue growth, flows through profit, cash and the balance sheet.

Let’s make AP
work for you.