Definition of financial modeling
A financial model is an excel spreadsheet that projects the 3 financial statements of a business into the future. The three financial statements are the income statement, balance sheet, cash flow statement which are based on historical data and expected value drivers.
Uses of financial modeling
A financial model is used to support quantitative decision making & financial analysis usually in the following areas:
- Financing the business through debt or equity
- Mergers & Acquisitions (acquire a business, merge or sell a business)
- Operational Modeling (expanding sales network, logistics, new plant construction)
- Financial Planning & Analysis / Budgeting & Forecasting
- Capital Budgeting for deciding on which projects to invest
- Business & Financial Valuations
Professions involved in financial modeling
The professionals involved in financial modelling to a greater or lesser degree can be categorized in the following career paths:
- Banking professionals (Investment Banking, specific departments of Commercial Banking, Trading, Equity Research) especially at entry and mid-levels.
- Corporate finance professionals (Financial Planning & Analysis, Budgeting & Forecasting, Corporate Development, Investor Relations)
- Institutional Investors (Private Equity, Portfolio Management, Research)
- Public Accounting Firms (Due Diligence, Corporate Finance Advisory, Project Finance).
Acquiring financial modeling skills
To build financial models, you will need to develop the following skills:
- Excel (interface and features, frequently used formulas, techniques to complete typical modeling tasks, methods to import and handle data in Excel). Please note that financial modeling is not about Excel which is a prerequisite, it’s about finance and modeling.
- Finance (accounting & corporate finance, financial analysis (financial statements, adjustments, calculating ratios), valuation techniques)
- Modeling (acquire and manage data for your models, structure your models so that they are transparent and can be easily maintained, learn to separate inputs, logic and outputs, model typical financial schedules)
- Application: apply the above skills to specific financial modeling assignments / tasks.
Financial modeling best practices
- Color coding: Separate inputs (assumptions) and outputs (calculations). You can achieve this by using specific color-coding conventions for inputs, outputs and or call ups / links.
- Model structure: Design your model in a logical, structured and user-friendly way. The main tabs to include in your model include the following:
- Assumptions and drivers
- Calculations and supporting schedules
- Financial statements
- Valuation & sensitivity / scenario analysis
- Charts and graphs / summary dashboard
Step by Step Financial Modeling:
Below you can find a multiple step approach to financial modeling:
- Determine your assumptions and value drivers: calculate various assumptions from the historical financial statements (3 – 5 years of data) such as growth rates, margins, variable costs, fixed costs, working capital days… and use them to forecast future assumptions.
- Start building your income statement: with revenues, costs, gross profit, operating expenses and EBITDA. Depreciation, interest and taxes will be calculated after you are finished with the balance sheet items.
- Next comes the balance sheet: by using revenues and costs as well as your working capital days, you can calculate receivables, payables and inventory. Various schedules are needed before the balance sheet is complete such as the Depreciation & Capex schedules (Property, Plant and Equipment or PPE), Debt & Interest Schedule, and Retained Earnings & Dividends Schedule.
- Finish the income statement: Depreciation & Interest from the balance sheet will feed the Income Statement and EBIT, EBT and Net Income will be calculated accordingly.
- Cash flow statement build up: you can create the cash flow statement by using the indirect method and starting from net income, add back depreciation and adjust for changes in non-cash items and you will get cash flow from operations. Investing cash flows consists of capital expenditures (or capex) as calculated on the schedules and cash from financing are essentially cash inflows from financing (debt drawdown and / or share capital increase) as well as cash repayment of debt (capital repayments) and / or equity (dividends).
- Valuation: you have the option to choose from various available valuation methods to perform the valuation of the company such as free cash flows to the firm, free cash flows to equity, dividend discount model, economic profit, economic value added, comparable companies multiples, comparable transactions multiples.
- Sensitivity and / or scenario analysis: these methods are used in order to evaluate the impact of changes in various value drivers and risk factors on the value of the company.
- Graphical Representations / Summary Page: charts and graphs are a user-friendly way to present the results and outputs of the model. A 1-page executive summary with the main KPIs of the business and the most important charts can also be implemented to give a professional look to your model.
- Checks and Balances: a series of checks should be implemented to ensure your model is working appropriately (i.e. the cash in your balance sheet matches the cash of your cash flow, and that the assets in your balance sheet match the liabilities and equity). Additionally, you should stress test your model by experimenting with extreme assumptions to see whether the model responds accordingly.
An entry level template is available here.