Financial Model: What is it?
The basics of financial modeling
A financial model represents how an organization's financial health is expected to evolve over time under certain assumptions. They are often created using spreadsheet software such as Microsoft Excel.
They are most commonly used for investment analyses, as they help identify risks and potential returns on investments. They can also be used to test pricing strategies for new products or evaluate alternative investments like real estate and commodities.
Although "financial modeling" is an umbrella term defined flexibly by different users depending on its intended uses, it is usually used in the context of corporate finance, accounting, or quantitative finance.
In accounting and corporate finance, modeling usually involves forecasting the financial statements and financial analysis. In quantitative finance, it involves developing complex mathematical models dealing with market movements, asset prices, and portfolio returns.
This article explores what these models are, how to build one, the common types, and what software you can use and is a great starting point if you are new to them.
What is a financial model?
"Are financial models always large and complex? Do they always have an enormous amount of data?"
"Do models always use formulas that I have never heard of?"
"Is an Excel workbook a model?"
If you have wondered about or asked similar questions, you're at the right place to learn more about these magical things called financial models.
Models can be of varying sizes and complexity. However, all models share some commonalities. They may be used for statistical investigation, optimization, simulation, or forecasting. They can be applied in various fields ranging from natural sciences and engineering to economics and finance.
SR 11-7 is a regulatory standard set out by the US Federal Reserve that guides model risk management. The standard defines a model as "a quantitative method, system, or approach that applies statistical, economic, financial, or mathematical theories, techniques, and assumptions to process input data into quantitative estimates."
It also tells us that "a model consists of three components: an information input component, which delivers assumptions and data to the model; a processing component, which transforms inputs into estimates; and a reporting component, which translates the estimates into useful business information."
Therefore, a structured representation must meet the following criteria to be considered a model:
- Uses academic theory.
- Converts data into useful information.
- Gives quantitative output. (Inputs may be partially or wholly qualitative)
A model is a simplified representation of a finance setting from the real world. It is a structured representation of how an organization's financial health, indicated by its assets, liabilities, revenues, and expenses, is expected to evolve over time under certain assumptions. Capturing all the complex and dynamic real-world settings is impossible, so some simplification is inevitable.
They are often created using spreadsheet software such as Microsoft Excel due to large amounts of data and complex functions, but simpler ones can even be built with pen and paper. For example, a simple household budget is a model too!
Financial modeling process
Depending on what type of financial model is required, different steps may follow.
For example, a standard model known as a three-statement model links the three financial statements and forecasts them based on assumptions about the future. Again, the key is to understand what you want the result to look like and who the end-users are which can generally be achieved by asking the right questions.
Although the steps involved in building different types of models may differ, we have listed a broad outline of the process that is common to most types.
- Data collection and validation
Collecting historical data to use as inputs is the first step in building a model. It is of paramount importance that modelers validate input data before proceeding further because the model's accuracy will depend on the quality of data provided. It can be done manually or through software. Remember, Garbage in = Garbage out.
- Model drivers
The validated data is analyzed for trends and patterns to establish some assumptions, also called model drivers, as they drive all forecasts in the model.
They generally include the revenue growth rate, the expected operating margin, the return on investment (ROI), and working capital needs. In addition, modelers must ensure that the assumptions are rationally sound as the model's accuracy depends on it.
- Project forecasts
Using the inputs and the assumptions, users can now make the desired forecasts which may be presented as a result or further analyzed.
- Further analyses
We can add additional analyses to the forecasted data or build other models upon it depending on the objectives of the exercise.
For instance, if the objective is to find the intrinsic value, we may further build a discounted cash flow (DCF) model. If we are forecasting the results of a merger, we may build a mergers and acquisitions (M&A) model.
- Stress testing and audit
A good model must be foolproof. The chances of errors and misuse must be minimized. It is important to find flaws in the model and them. Hence, the model is tested with extreme inputs and assumptions to see whether it behaves as expected.
Certain audit tools and techniques can be useful in confirming whether the model is accurate and that the formulae are working correctly.
- Presenting the results
Presenting the results of a model is equally as important as the modeling process itself. The model's usefulness is determined by its ability to assist in decision-making. If the results of a model cannot be effectively communicated, it cannot help in the decision-making process.
Visual tools like charts and graphs are a great way to communicate information to executives who do not have the time to look at minute details in blocks of text.
Types of financial models
"You said there are many types of them? Where's my Poké Ball? Gotta catch 'em all!"
Although all models differ to some extent, they can be categorized into a few types based on their objectives.
The most common type of financial model is the three-statement model. It dynamically links the three financial statements – the income statement, the balance sheet, and the cash flow statement. In addition, it is often used as a base to build other use- or industry-specific models.
Here are some other common types.
- DCF model
It is developed on the three-statement model. It is used to value companies by discounting their projected cash flows. The resulting value of the company represents the present value of all its future cash flows. It is based on the intrinsic value approach and is part of fundamental analysis.
It is often used for investments that generate or are expected to generate a steady cash flow and for investments with no comparable investments.
- Trading comps model
This model uses comparable analysis by employing trading valuation multiples of comparable companies to derive the value of a company. Unlike the DCF method, it uses the relative value approach to valuation.
- Precedent transaction model
This model is similar to the trading comps model. However, instead of trading multiples, it uses precedent transactions to derive the value of a company. Transaction multiples are calculated based on the transaction price of an M&A transaction. Like the trading comps model, this method also uses the relative value approach to valuation.
- LBO model
It is prepared for LBO transactions which involve modeling for complex debt schedules. It is considered one of the most challenging types of models. There are often many layers of circularity.
- Accretion-Dilution model
It is used to assess the accretion or dilution from potential M&A transactions. In addition, it evaluates whether a prospective deal creates value for the shareholders or destroys it.
- Real estate model
In real estate modeling, the risks and returns of investing in a property are evaluated from an investor's point of view.
Uses of a financial model
"What good is a financial model anyway?"
"Do I even need to learn financial modeling?"
The end goal of a model is to provide insight into the potential future of an organization. It is used by internal departments within a company and external stakeholders such as investors. Industries that use it include healthcare, automotive, banking, insurance, and retail.
A key goal while creating one is to make it concise and clear. Reading through a model should allow users to understand it quickly and make decisions.
The users need to understand its objectives before building one because the focus will vary depending on that. For example, if your objective is to find a fair value for an early-stage company, the model would be created differently than if the objective was to determine the intrinsic value of a call option.
The objectives of building these models can range from something as simple as linking the three financial statements to something complex like evaluating a potential merger. The most common objectives of are listed below.
- Building a three-statement model to dynamically link the three financial statements
- Forecasting the future performance of a business
- Valuing companies or investment opportunities
- Modeling different scenarios to understand each of their impact on the company's financial health
- Understanding the sensitivity of the company's growth prospects to the changes in market factors
- Evaluating mergers and acquisitions (M&A) transactions
The video below explains why modeling is important and why you need to learn it ASAP.
Financial modeling is a requisite skill for many jobs. Companies increasingly seek professional talent to process raw input into meaningful forecasts and insights. Although traditionally speaking, this skill has been associated with investment banking, it is now commonly sought after by corporates, institutions, and banks alike. The most common job functions that involve this skill are:
- Investment banking
- Portfolio management
- Financial planning and analysis
- Private equity analysis
- Venture capital analysis
- Commercial banking
Hence, it is of utmost priority to get proficient at this skill if you are pursuing a career in finance. To help you learn modeling better with a hands-on approach, seasoned finance veterans at WSO have made some amazing templates for different kinds of models, resumes, presentations, and more!
Financial modeling software
"Is Excel all I've got?"
The most used software for financial modeling is Microsoft Excel followed by Microsoft Access (very rare but still used, especially with large datasets), and both are offered as a part of the Microsoft Office suite by Microsoft. Excel is a spreadsheet program that allows users to create models quickly and easily. It also comes with a wide range of built-in functions for manipulating data and performing calculations.
Access is a database management software that includes features like business intelligence (BI) and data mining tools, making it a powerful tool for financial analysis, especially when working with vast data.
Each allows users to choose between a flat database and a relational database data structure. A flat database is a database that stores data in a single table, like a single spreadsheet. On the other hand, a relational database uses information in one table to structure information in other tables. Although relational databases are like Excel workbooks, they are generally more flexible and powerful.
Both Access and Excel are complex software that can take long periods to master. That said, Excel seems to be more popular because of how easy it is to use. Beginners can get started with creating spreadsheets in it quickly. In addition, its large userbase acts as a useful source of unlimited learning resources.
If users find themselves having to manage multiple worksheets with large amounts of data and complex calculations, Access might be better suited to meet their needs. Although it may require some time and effort initially to design the layout of the database, it is efficient to create the formulas that produce the final results. In addition, users can review the templates offered by both software and pick the one that suits their requirements better.