Introducing Financial Modeling
already discovered how important this skill is, and you know that learning finan- cial modeling will increase your employability in finance or financially focused fields.
In this chapter, I define financial modeling — what it is, who uses it, and why it matters. I also show you some examples of financial models. If you’re brand-new to financial modeling, this chapter is a very good place to start.
Before you dive into how to use Microsoft Excel to create financial models, you need to know what financial modeling is, who uses financial models, and why financial modeling matters. In this section, I fill you in.
What it is
When I teach a course on basic financial modeling, I always ask my students for their definitions of the term financial model. Most of them come up with long- winded descriptions using terms like forecast and cash flow and hypothetical out– comes. But I don’t think the definition needs to be that complicated. A financial model is a tool (typically built in Excel) that displays possible solutions to a real- world financial problem. And financial modeling is the task of creating a financial model.
You may have thought that a financial model was basically just an Excel spread- sheet, but as you know, not every spreadsheet is a financial model. People can and do use Excel for all kinds of purposes. So, what makes a financial model distinct from a garden-variety spreadsheet? In contrast to a basic spreadsheet, a financial model
» Is more structured. A fi model contains a set of variable assumptions — inputs, outputs, calculations, and scenarios. It often includes a set of standard
fi forecasts — such as a profi statement, a balance sheet, and a cash fl statement — which are based on those assumptions.
» Is dynamic. A fi model contains inputs that, when changed, impact the calculations and, therefore, the results. A fi model always has built-in
fl to display diff outcomes or fi calculations based on changing a few key inputs.
» Uses relationships between several variables. When the user changes any of the input assumptions, a chain reaction often occurs. For example, chang- ing the growth rate will change the sales volume; when the sales volume
changes, the revenue, sales commissions, and other variable expenses will change.
» Shows forecasts. Financial models are almost always looking into the future. Financial modelers often want to know what their fi projections will look like down the road. For example, if you continue growing at the same
rate, what will your cash fl be in fi years?
» Contains scenarios (hypothetical outcomes). Because a model is looking forward instead of backward, a well-built fi model can be easily used to perform scenario and sensitivity analysis. What would happen if interest rates
went up? How much can we discount before we start making a loss?
More broadly, a financial model is a structure (usually in Excel) that contains inputs and outputs, and is flexible and dynamic.
Many types of people build and use financial models for different purposes and goals. Financial models are usually built to solve real-world problems, and there are as many different financial models as there are real-world problems to solve. Generally, anyone who uses Excel for the purpose of finance will at some point in his career build a financial model for himself or others to use; at the very least, he’ll use a model someone else created.
Bankers, particularly investment bankers, are heavy users of financial models. Due to the very nature of financial institutions, modeling is part of the culture of the company — the business’s core is built on financial models. Banks and finan- cial institutions must comply with current regulatory restrictions, and the tools and controls in place are forever changing and adapting. Because of the risk asso- ciated with lending and other financial activities, these institutions have very complex financial modeling systems in place to ensure that the risk is managed effectively. Anyone working in the banking industry should have at least a work- ing knowledge of spreadsheets and financial models.
Outside the banking industry, accountants are big users of financial models. Bankers are often evaluating other companies for credit risk and other measures. An accountant’s models, however, are often more inward looking, focusing on internal operations reporting and analysis, project evaluation, pricing, and profitability.
A financial model is designed to depict a real-life situation in numbers in order to help people make better financial decisions.
Wherever there are financial problems or situations in the real world that need solving, analyzing, or translating into a numerical format, financial models help. Sometimes it’s just an idea or a concept that needs to be converted into a business case or feasibility proposal. A skilled financial modeler can put substance to the idea by augmenting the details enough to get a working model upon which deci- sions can be made, investor funds can be gained, or staff can be hired.
For example, financial models can help investors decide which project to put their money into, an executive track which marketing campaigns have the highest return on investment, or a factory production manager decide whether to pur- chase a new piece of machinery.
WHAT IT TAKES TO BE A FINANCIAL MODELER
Someone working with fi models typically has an undergraduate degree in busi- ness, fi or commerce. Additionally, she likely has at least one of the following postgraduate qualifi
- An accountancy qualifi such as CA (Certifi Accountant), CPA (Certifi
Public Accountant), CIMA (Chartered Institute of Management Accountants), ACCA
(Association of Chartered Certifi Accountants), CMA (Certifi Management Accountant), or CIA (Certifi Internal Auditor)
- A Master of Business Administration (MBA) degree
- A Chartered Financial Analyst (CFA) designation
- A Financial Risk Manager (FRM) designation
Of course, you don’t need all those letters after your name to build and work with fi – cial models. I know many skilled modelers who come from backgrounds in IT or engi- neering, or who don’t have any formal qualifi at all. Currently, there is no specifi certifi qualifi for fi modeling professionals — at least nothing that is publically recognized — but I expect this might change in the near future. You can fi courses in fi modeling, however. For example, I run a fi -day Certifi in Financial Modeling Using Excel course through George Washington University several times a year in Dubai. And I have colleagues who run similar programs. I would classify these kinds of program as short-course vocational training rather than full certifi
If you simply want to list fi modeling as a skill on your résumé, a short course is suffi (backed up by at least a couple of models you’ve built in the real world). If you’re aiming toward a fi modeling career, you’ll need formal fi qualifi – tions such as those listed here, as well as intense, practical, hands-on work experience.
Looking at Examples of Financial Models
When you then consider the benefits that a financial model can bring, it’s difficult not to get carried away thinking of the application potential of a financial model! When you understand the principles of financial models, you can begin to look at the most common scenarios in which a model would be implemented.
There are a variety of categories of financial models:
» Project fi models: When a large infrastructure project is being assessed for viability, the project fi model helps determine the capital and structure of the project.
» Pricing models: These models are built for the purpose of determining the price that can or should be charged for a product.
» Integrated fi statement models (also known as a three-way fi model): The purpose of this kind of model is to forecast the
fi position of the company as a whole.
» Valuation models: Valuation models value assets or businesses for the purpose of joint ventures, refi contract bids, acquisitions, or other kinds of transactions or “deals.” (The people who build these kinds of models
are often known as deals modelers.)
» Reporting models: These models summarize the history of revenue, expenses, or fi statements (such as the income statement, cash fl statement, or balance sheet).
Modelers generally specialize in one or two of these model categories. You’ll see some overlap between each type of model category, but most models can be clas- sified as one model type.
In this section, I show you some examples of scenarios and places in which these categories of financial models can come in handy, along with the functions and characteristics of each.
Loans and the associated debt repayments are an important part of project finance models, because these projects are normally long term, and lenders need to know whether the project is able to produce enough cash to service the debt. Metrics such as debt service cover ratio (DSCR) are included in the model and can be used as a measure of risk of the project, which may affect the interest rate offered by the lender. At the beginning 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.
The input to a pricing model is the price, and the output is the profitability. To create a pricing model, 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 = Profi
Of course, this kind of model 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 modeler can perform sensitivity analysis on the price entered using a goal seek (see Chapter 7) or a data table (see Chapter 8).
Integrated fi statement models
Not every 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 state- ment models. You may also hear them referred to as “three-way financial mod- els.” The three types of financial statements included in an integrated financial statement model are the following:
» Income statement, also known as a profi (P&L) statement
» Cash fl statement
» Balance sheet
From a financial modeling perspective, it’s very important that when an inte- grated financial statement model is built, the financial statements are linked together properly so that if one statement changes, the others change as well. For an example of how to build an integrated financial statement model, turn to Chapter 12.
Building valuation models requires a specialized knowledge of valuation theory (using the different techniques of valuing an asset), as well as modeling skills. If you’re a casual financial modeler, you probably won’t be required to create from scratch a fully functioning valuation model. But you should at least have an idea of what types of valuation financial models are out there.
Here are three common types of valuation financial models you may encounter:
» Mergers and acquisitions (M&A): These models are built to simulate the
eff of two companies merging or one company taking over the other. M&A
models are normally undertaken in a tightly controlled environment. Due to its confi nature, an M&A model has fewer players than other kinds of models. The project moves quickly because time frames are tight. The few modelers working on an M&A model do so in a concentrated period of time, often working long hours to achieve a complex and detailed model.
» Leveraged buyout (LBO): These models are built to facilitate the purchase of a company or asset with large amounts of debt to fi the deal, called a leveraged buyout. The entity acquiring the “target” company or asset usually
fi the deal with some equity, using the target’s assets as security — in the same way that many home loan mortgages work. LBOs are a popular method of acquisition because they allow the entity to make large purchases without committing a lot of cash. Modeling is an important part of the LBO deal because of its complexity and the high stakes involved.
» Discounted cash fl (DCF): These models calculate the cash expected to be received from the business or asset a company is considering purchasing, and then discounts that cash fl back into today’s dollars to see whether the
opportunity is worth pursuing. Valuing the future cash fl expected from an acquisition is the most common modeling method of valuation. Intrinsic to
the DCF methodology is the concept of the time value of money — in other words, that cash received today is worth a lot more than the same amount of cash received in future years. For an example of how to calculate DCF, turn to Chapter 11.
Because they look historically at what occurred in the past, some people argue that reporting models are not really financial models at all, but I disagree. The prin- ciples, layout, and design that are used to create a reporting model are identical to other financial models. Just because they contain historical rather than projected numbers doesn’t mean they should be categorized any differently.
In fact, reporting models are often used to create actual versus budget reports, which often include forecasts and rolling forecasts, which in turn are driven by assumptions and other drivers. Reporting models often start out as a simple income statement report, but end up being transformed into fully integrated financial statement models, pricing models, project finance models, or valuation models.
PUTTING “FINANCIAL MODELING”
ON YOUR RÉSUMÉ
When you know exactly what’s involved in the modeling process and you have knowl- edge of fi modeling skills that you’ve used in the workplace, you’re ready to put “fi modeling” on your résumé.
Since the economic crisis of 2008, emphasis on fi modeling has increased. In response, there has been a rise in job descriptions specifying fi modeling as a core competency. If you’re applying for a job in fi employers will no doubt look favorably upon this skill, as long as it rings true with the rest of your résumé. You need to be able to fl out the tasks in previous positions you’ve held with examples of what kinds of models you built.
Although short vocational courses in fi modeling (see “What it takes to be a fi – cial modeler,” earlier in this chapter) are well respected, what prospective employers really want to see is the application of fi modeling techniques in your everyday work.
Just reading this book or taking a fi modeling training course doesn’t mean you can add “fi modeling” to your résumé. You need to have actually used your mod- eling skills in the real-world environment. Take every opportunity to use models in your work. If you’re not currently employed, fi example models online, take them apart, and see how you can improve them.
Whatever you do, don’t exaggerate when it comes to the level of experience you have with fi modeling. You may be asked in the interview to back up and discuss in great detail the intricacies of how you created a particular model.
» Comparing diff versions of Excel
» Introducing Modern Excel
» Recognizing the pitfalls of using Excel
» Exploring alternatives to Excel
