What are the different financial models?
An insight into financial modelling, its meaning, objective, types, categories and ten points to follow while creating financial models.
Financial modelling is interpreting numbers of features of a company’s operations. Financial modelling is the task of building an abstract representation, called financial models, of a real-world financial situation. It is a mathematical model constructed to denote a simplified version of the performance of a financial asset or portfolio of a business, project, or any other investment.
Financial models are activities that prepare a model representing a real-world financial situation. they are intended to be used as decision-making tools. Company executives might use financial models to estimate the costs and project the profits of a proposed new project. Financial analysts use financial models to anticipate the impact of an economic policy change or any other event on a company’s stock.
Common types of financial models are Initial Public Offering (IPO) Model and Leveraged Buyout (LBO) Model.
Financial modelling: meaning
Financial modelling is the method performed to build a financial representation of a company. Financial analyst forecast future earnings and performance of the company using these financial models. The analysts use numerous forecast theories and valuations provided by financial modelling through these financial models to recreate business operations. Financial models once completed, display a mathematical depiction of the business events. The primary tool utilized to create the financial model is the excel spreadsheet.
Investopedia definition of Financial modelling: The process by which a firm constructs a financial representation of some, or all, aspects of the firm or given security. The model is usually characterised by performing calculations and makes recommendations based on that information. The model may also summarize particular events for the end user and provide direction regarding possible actions or alternatives
Financial modelling: objectives
Financial models help in steering historical analysis of a company, projecting a company’s financial performance used in various fields.
These financial models are predominantly used by financial analysts and are constructed for many purposes. Financial modelling supports the management in the decision-making and the preparation of financial analysis by creating financial models.
The following are the objectives of creating financial models:
- Valuing a business
- Raising capital
- Growing the business
- Making acquisitions
- Selling or divesting assets and business units
- Capital allocation
- Budgeting and forecasting
The best financial models offer a set of basic assumptions. For example, one commonly forecasted line item is sales growth. Sales growth is documented as the increase, or decrease, in gross in the most recent quarter compared to the previous quarter. For financial modelling, these are the only two inputs financial models need to calculate sales growth.
Financial modelling will create one cell for the prior year’s sales, cell A, and one cell for the current year’s sales, cell B. The third cell, cell C, would be used for a formula that divides the difference between cell A and B by cell A. This will be the growth formula. Cell C, the formula, would be embedded into the model. Cells A and B are input cells that can be changed by the user.
In this case, the purpose of financial modelling and creating financial models is to estimate sales growth if a certain action is taken or a possible event occurs.
Financial modelling: ten points to follow
Financial modelling is an iterative process. Analysts creating financial models must chip away at different sections until they are finally able to tie it all together.
Below is a step-by-step breakdown of where they should start and how to finally connect all the dots.
- Historical results and assumptions
- Start the income statement
- Start the balance sheet
- Build the supporting schedules
- Complete the income statement and balance sheet
- Build the cash flow statement
- Perform the Discounted Cash Flow (DCF) analysis
- Add sensitivity analysis and scenarios
- Build charts and graphs
- Stress test and audit the model
Financial modelling: categories
There are multiple varieties of financial modelling tools that are exercised, based on the purpose and need of doing it. Each category of the financial models solve a different business problem. While the majority of the financial models concentrate on valuation, some are created to calculate and predict risk, performance of portfolio, or economic trends within an industry or a region.
The key to being able to model finance effectively is to have good templates and a solid understanding of corporate finance.
Examples of financial models available include:
Project finance models
When a sizable infrastructure project is being evaluated for feasibility, the project finance model helps determine the capital and structure of the project. Project finance is only possible when the project is capable of producing enough cash to cover all operating and debt-servicing expenses over the whole tenor of the debt.
Loans and the associated debt repayments are an imperative part of project finance models, since these projects are normally long term, and lenders need to be sure if the project can bring sufficient cash against the debt. In other words, project finance model is used as a financial model when the company needs to assess economic feasibility of the project.
Metrics such as debt service cover ratio (DSCR) are included in this category of financial modelling and can be a handy yardstick of the project risk, which may affect the interest rate offered by the lender.
Right at the start of the project, the DSCR and other metrics are agreed upon between the lender and borrower such that the ratio must not go below a certain number.
While the output for a project finance model through financial modelling is uniform and the calculation algorithm is predetermined by accounting rules, the input is highly project specific. Generally, it can be subdivided into the following categories:
- Variables needed for forecasting revenues
- Variables needed for forecasting expenses
- Capital expenditures
- Financing
Pricing models
This category of financial models is built for the idea of establishing the price that can or should be charged for a product.
Price is one of the key variables in the marketing mix. There are four general pricing approaches that companies use to set an appropriate price for their products and services: cost-based pricing, value-based pricing, value pricing and competition-based pricing. The cost of production sets the lower limit while the upper limit is set by consumer perception about the product/service.
The input to a pricing model is the price, and the output is the profitability. To create a pricing model through financial modelling, an income statement, or profit-and-loss statement of the business or product should be created first, based on the current price or a price that has been input as a placeholder. At a very high level:
Units × Price = Revenue
Revenue – Expenses = Profit
However, this category of financial models can be very complex and involve many different tabs and calculations, or it can be quite simple, on a single page. When this structure model is in place, the person doing the financial modelling can perform sensitivity analysis on the price entered using a goal seek or a data table.
Integrated financial statement models
This category of financial models is also known as a three-way financial model.
The three kinds of financial statements included in the financial modelling of an integrated financial statement model are the following:
- Income statement, also known as a profit-and-loss (P&L) statement
- Cash flow statement
- Balance sheet
Not every category of financial model needs to contain all three types of financial statements, but many of them do, and those that do are known as integrated financial statement models.
From a financial modelling outlook, it’s very important that when the financial modelling for an integrated financial statement takes place, the financial statements are linked together properly so that if one statement changes, the others change as well.
Valuation models: This category of financial models value assets or businesses for the purpose of joint ventures, refinancing, contract bids, acquisitions, or other kinds of transactions or deals.
The people who build these kinds of models are often known as deals modelers.
Building this kind of financial models requires a specialized knowledge of valuation theory and using the different techniques of valuing an asset, as well as financial modelling skills.
When valuing a company as a going concern there are three main valuation methods used by industry practitioners:
- DCF analysis
- Comparable company analysis
- Precedent transactions
These are the most common methods of valuation used in investment banking, equity research, private equity, corporate development, mergers & acquisitions (M&A) and most areas of finance. A common example of these category of financial models is Initial Public Offering (IPO) Model and Leveraged Buyout (LBO) Model.
Reporting models
These financial models condense the history of revenue, expenses, or financial statements, like the income statement, cash flow statement, or balance sheet.
Because they look historically at what occurred in the past, there is school of thought that these financial models are not real financial models. The fundamentals like principles, layout, and design that are used in financial modelling are identical to other financial models.
Reporting models are often used to create actual versus budget reports, which include forecasts and rolling forecasts, which in turn are driven by assumptions and other drivers.
Financial modelling: types
In practice, there are many different types of financial models. We have outlined the 10 most commonly used financial models used by financial modelling professionals.
Three-Statement Model
A three-statement model links the income statement, balance sheet, and cash flow statement into one dynamically connected financial model. These financial models are the basis on which more advanced financial models are built such as discounted cash flow DCF models, merger models, leveraged buyout LBO models, and various other types of financial models.
It falls under both the categories of financial models: Reporting models and Integrated financial statement models.
Discounted Cash Flow (DCF) Model
These types of financial models fall under the category of Valuation models and are typically, though not exclusively, used in equity research and other areas of the capital markets.
A DCF model is a specific type of financial model used to value a business. DCF model is a forecast of a company’s unlevered free cash flow discounted back to today’s value, which is called the Net Present Value (NPV).
The basic building block of a DCF model is the three-statement financial model, which links the financials together. The DCF model takes the cash flows from the three-statement financial model, makes some adjustments where necessary, and then uses the XNPV function in Excel to discount them back to today at the company’s Weighted Average Cost of Capital (WACC).
Merger Model (M&A)
The M&A model also falls under the Valuation category of financial models.
As the title suggests, this type of financial modelling is towards a more advanced model applied to assess the pro forma accretion/dilution of a merger or acquisition. It’s common to use a single tab model for each company, where the consolidation is represented as Company A + Company B = Merged Co. The level of complexity can vary widely and is most commonly used in investment banking and/or corporate development.
Initial Public Offering (IPO) Model
Like the previous two type to financial models, the IPO model is also a Valuation model.
Financial professionals like investment bankers develop IPO financial models in Excel to value their business just before going public. These financial models equate company analysis with regards to an assumption about how much investors would be willing to pay for the company in contention. The valuation in an IPO model includes an IPO discount to ensure the stock trades well in the secondary financial market.
Leveraged Buyout (LBO) Model
A leveraged buyout (LBO) is a transaction where a company is acquired using debt as the main source of consideration. These transactions typically occur when a private equity (PE) firm borrows as much as they can from a variety of lenders (up to 70 or 80% of the purchase price) and funds the balance with their own equity.
An LBO transaction typically requires financial modelling with debt schedules and are an advanced form of financial models. An LBO is often one of the most detailed and challenging of all types of financial models as they many layers of financing create circular references and require cash flow waterfalls. These types of models are not very common outside of private equity or investment banking.
When it comes to an LBO transaction, the required financial modelling can get complex. The added complexity comes from the following unique elements of an LBO:
- High degree of leverage
- Multiple tranches of debt financing
- Complex bank covenants
- Issuing of Preferred shares
- Management equity compensation
- Operational improvements targeted in the business
Sum of the Parts Model
Another type of financial model that belongs to the Valuation category of financial models, this model is developed by taking in account a number of DCF financial models and adding them together. Further, any sundry factors of the business that may not be apt for a DCF analysis are added to that value of the business. So, for example, you would sum up, that’s why ‘Sum of the Parts’, the value of business unit A, business unit B, and investments C, minus liabilities D to arrive at the NAV for the company.
Consolidation Model
The Consolidation Model belongs to Reporting Model category of financial models. It includes several business units added into one single model for financial modelling and further analysis. Typically, each business unit is its own tab, with consolidation tab that simply sums up the other business units. This is similar to a Sum of the Parts exercise where Division A and Division B are added together and a new, consolidated worksheet is created.
Budget Model
The Budget model is used to do financial modelling in financial planning & analysis (FP&A) to get the budget together for the next few years, typically in the range of one, three and five years. Budget financial models are meant to be based on monthly or quarterly figures and rely strongly on the income statement.
This is one more model belonging to the Reporting model category of financial models.
Forecasting Model
Similar to the budget model, the forecasting model is also used in FP&A to come up with a forecast that compares to the budget model. Since it is similar to the forecasting model, it also belongs to the Reporting model category of financial models.
The budget and the forecast models are represented one combined workbook and sometimes they are totally separate.
Option Pricing Model
As the name suggests, this model is part of the Pricing model category of financial models. Binomial tree and Black-Sholes are the two main option pricing financial models and are based purely on mathematical financial modelling rather than specific standards and therefore are an upfront calculator built into Excel.
Conclusion
Though financial modelling is a generic term that means different things to different users, the reference usually relates either to accounting and corporate finance applications, or to quantitative finance applications.
What are the areas where you have applied financial modelling and created financial models for your company or clients? Or what are the other specifics on financial modelling, particular financial models or their application that you might be interested in? Do let us know by writing to us.
Was this article helpful?
YesNo
