Introduction to Financial Modeling and Excel

If you asked five professionals the meaning of financial modeling, you would probably get five different answers. The truth is that they would all be correct in their own context. This is inevitable since the boundaries for the use of financial modeling continue to be stretched almost daily, and new users want to define the discipline from their own perspective. In this chapter, you will learn the basic ingredients of a financial model and what my favorite definitions are. You will also learn about the different tools for financial modeling that currently exist in the industry, as well as those features of Excel that make it the ideal tool to use in order to handle the various needs of a financial model.

In this chapter, we will cover the following topics:

• The main ingredients of a financial model
• Understanding mathematical models
• Definitions of financial models
• Types of financial models
• Alternative tools for financial modeling
• Excel—the ideal tool

The main ingredients of a financial model

First of all, there needs to be a situation or problem that requires you to make a financial decision. Your decision will depend on the outcome of two or more options. Let's look at the various aspects of a financial model:

Financial decisions: Financial decisions can be divided into three main types:

• Investment
• Financing
• Distributions or dividends

Investment

We will now look at some reasons for investment decisions:

1. Purchasing new equipment: You may already have the capacity and know how to make or build in-house. There may also be similar equipment already in place. Considerations will thus be whether to make or buy, sell, keep, or trade-in the existing equipment.
2. Business expansion decisions: This could mean taking on new products, opening up a new branch or expanding an existing branch. The considerations would be to compare the following:

• The cost of the investment: Isolates all costs specific to the investment, for example, construction, additional manpower, added running costs, adverse effect on existing business, marketing costs, and so on.
• The benefit gained from the investment: We can gain additional sales. There will be a boost in other sales as a result of the new investment, along with other quantifiable benefits. To get the return on investment (ROI), a positive ROI would indicate that the investment is a good one.

Financing decisions primarily revolve around whether to obtain finance from personal funds or from external sources.

For example, if you decided to get a loan to purchase a car, you would need to decide how much you wanted to put down as your contribution, so that the bank would make up the difference. The considerations would be as follows:

• Interest rates: The higher the interest rate, the lower the amount you would seek to finance externally
• Tenor of loan: The longer the tenor the lower the monthly repayments, but the longer you remain indebted to the bank
• How much you can afford to contribute: This will put a platform on the least amount you will require from the bank, no matter what interest rate they are offering
• Number of monthly repayments: How much you will be required to pay monthly as a result of the foregoing inputs

Financing

A company would need to decide whether to seek finance from internal sources (approach shareholders for additional equity) or external sources (obtain bank facility). We can see the considerations in the following list:

• Cost of finance: The cost of bank finance can be easily obtained as the interest and related charges. These finance charges will have to be paid whether or not the company is making profit. Equity finance is cheaper since the company does not have to pay dividends every year, also the amount paid is at the discretion of the directors.
• Availability of finance: It's generally difficult to squeeze more money out of shareholders, unless perhaps there has been a run of good results and decent dividends. So, the company may have no other choice than external finance.
• The risk inherent in the source: With external finance there is always the risk that the company may find itself unable to meet the repayments as they are due.
• The desired debt or equity ratio: The management of a company will want to maintain a debt to equity ratio that is commensurate with their risk appetite. Risk takers will be comfortable with a ratio of more than 1:1, while risk averse management would prefer a ratio of 1:1 or less.

Dividends

Distributions or dividend decisions are made when there are surplus funds. The decision would be whether to distribute all the surplus, part of the surplus, or none at all. We can see the considerations in the following list:

• Expectation of the shareholders: Shareholders provide the cheap finance options and are generally patient. However, they want to be assured that their investment is worthwhile. This is generally manifested by profits, growth, and in particular, dividends, which have an immediate effect on their finances.
• The need to retain surplus for future growth: It is the duty of the directors to temper the urge to satisfy the pressure to declare as much dividend as possible, with the necessity to retain at least part of the surplus for future growth and contingencies.
• The desire to maintain a good dividend policy: A good dividend policy is necessary to retain the confidence of existing shareholders and to attract potential future investors.

Understanding mathematical models

In the scheme of things, the best or optimum solution is usually measured in monetary terms. This could be the option that generates the highest returns, the least cost option, the option that carries an acceptable level of risk, and the most environmentally friendly option, but is usually a mixture of all these features. Inevitably, there is an inherent uncertainty in the situation, which makes it necessary to make assumptions based on past results. The most appropriate way to capture all the variables inherent in the situation or problem is to create a mathematical model. The model will establish relationships between the variables and assumptions, which serve as an input to the model. This model will include a series of calculations to evaluate the input information and to clarify and present the various alternatives and their consequences. It is this model that is referred to as a financial model.

Definitions of financial models

Wikipedia considers a financial model to be a mathematical model that represents the performance of a financial asset, project, or other investments in abstract form.

Corporate Finance Institute believes that a financial model facilitates the forecasting of future financial performance, by utilizing certain variables to estimate the outcome of specific financial decisions.

Business Dictionary agrees with the notion of a mathematical model in that it comprises sets of equations. The model analyzes how an entity will react to different economic situations with a focus on the outcome of financial decisions. It goes on to list some of the statements and schedules you would expect to find in a financial model. Additionally, the publication considers that a model could estimate the financial impact of a company's policies and restrictions put in place by investors and lenders. It goes on to give the example of a cash budget as a simple financial model.

eFinance Management considers a financial model to be a tool with which the financial analyst attempts to predict the earnings and performance of future years. It considers the completed model to be a mathematical representation of business transactions. The publication names Excel as the primary tool for modeling.

Here's my personal definition:

"A financial decision making a situation prompts the creation of a mathematical model to facilitate the decision making. Preferred courses of action and their consequences are based on the results of the calculations performed by the model."

Types of financial models

There are several different types of financial models. The model type depends on the purpose and the audience of the model. Generally speaking, you can create a financial model when you want to value or to project something or have a mixture of the two.

The following models are examples that seek to calculate values.

The 3 statement model

In the following screenshot, we see the starting point for most valuation models and what it includes:

1. Balance sheet (or statement of financial position): This is a statement of assets (which are resources owned by the company that have economic value, and that are usually used to generate income for the company, such as plant, machinery, and inventory), liabilities (which are obligations of the company, such as accounts payable and bank loans) and owner's equity (which is a measure of the owner's investment in the company):

2. Income statement (or statement of comprehensive income): This is a statement that summarizes the performance of a company by comparing the income it has generated within a specified period to the expenses it has incurred over the same period:

3. Cash flow statement: This is a statement that identifies inflow and outflow of cash to and from various sources, operations, and transactions, during the period under review. The net cash inflow should equal the movement in cash and cash equivalents shown in the balance sheet during the period under review.

The mathematics of this model starts with historical data. In other words, the income statement, the balance sheet, and the cash flow statement for the previous 3 to 5 years will be entered into Excel. A set of assumptions will be made and used to drive the financial results as displayed in the three statements, over the next 3 to 5 years. This will be illustrated more in detail later in the book and will become clearer. The following screenshot shows an example of a cash flow statement:

The discounted cash flow model

The discounted cash flow (DCF) method is considered by most experts to be the most accurate for valuing a company. Essentially, the method considers the value of a company to be the sum of all the future cash flows the company can generate. In reality the cash is adjusted for various obligations to arrive at the free cash flow. The method also considers the time value of money, a concept with which we will become much more familiar in a later chapter. The DCF method applies a valuation model to the 3 statement model mentioned in The 3 statement model section. Later, we will encounter and explain fully the technical parameters included in this valuation model.

The comparative companies model

This method relies on the theory that similar companies will have similar multiples. Multiples are, for example, comparing the value of the company or enterprise (enterprise value or EV) to its earnings. There are different levels of earnings, such as the following:

• Earnings before interest, tax, depreciation, and amortization (EBITDA)
• Earnings before interest and tax (EBIT)
• Profit before tax (PBT)
• Profit after tax (PAT)

For each of them, a number of multiples can be generated and used to arrive at a range of EVs for the company. The comparative method is simplistic and highly subjective especially in the choice of comparable companies; however, it is favored amongst analysts, as it provides a quick way of arriving at an indication of the value for a company.

Again, this method relies on the 3 statement model as a starting point. You then identify three to five similar companies with the quoted EVs. In selecting similar companies (peer group), the criteria to consider will include the nature of the business, size in terms of assets and/or turnover, geographical location, and more. We use the following steps to do so:

1. We need to calculate the multiples for each of the companies (such as EV/EBITDA, EV/SALES, P/E ratio).
2. Then calculate the mean and median of the multiples of all the similar companies.

The median is often preferred over the mean, as it corrects the effect of the outliers. Outliers are those individual items within a sample that are significantly larger or smaller than the other items, and thus tend to skew the mean one way or the other.

3. Then adopt the median multiplier for your target company and substitute the earnings, for example, EBITDA, calculated in the 3 statement model in the equation:

4. When you rearrange the formula, you arrive at the EV for the target company:

The merger and acquisition model

When two companies seek to merge, or one seeks to acquire the other, investment analysts build a mergers and acquisitions (M&A) model. Valuation models are first built for the individual companies separately then a model is built for the combined post-merger entity and their earnings per share calculated. The earnings per share (EPS) is an indicator of a company's profitability. It is calculated as net income divided by number of shares. The purpose of the model is to determine the effect of the merger on the acquiring company's EPS. If there is an increase in post-merger EPS then the merger is accretive, otherwise it is dilutive.

The leveraged buyout model

In a leveraged buyout situation, company A acquires company B for a combination of cash (equity) and loan (debt). The debt portion tends to be significant. Company A then runs company B, servicing the debt, and then sells company B after 3 to 5 years. The leveraged buyout model (LBO) model will calculate a value for company B as well as the likely return on the eventual sale of the company.

We will now look at models that project something.

Loan repayment schedule

When you approach your bank for a car loan, your accounts officer takes you through the structure of the loan including loan amount, interest rate, monthly repayments, and sometimes, how much you can afford to contribute towards the cost of the car. Let us look at the various features of loans in the following screenshot:

The preceding screenshot gives us an example of how you would lay out your assumptions for a loan repayment schedule model. The monthly repayment is calculated using Excel's PMT function. The tenure is 10 years, but repayment is monthly (12 repayment periods per year), giving a total number of periods of (nper)() of 12 × 10 = 120. Note that the annual interest rate will have to be converted to a rate per period, which is 10%/12 (rate/periods), to give 0.83% per month in our example. The pv is the loan amount. We also need to keep in mind that the actual loan amount is the cost of the asset less customer's contribution.

Selection scroll bars have been added to the model so that customer's contribution (10%-25%), interest rate (18%-21%), and tenor (5–10 years) can be easily varied and the results immediately observed since the parameters will recalculate at once.

The preceding screenshot shows the kind of amortization table they use in order to turn around your options so quickly.

The budget model

A budget model is a financial plan of cash inflows and outflows of a company. It builds scenarios of required or standard results, for turnover, purchases, assets, debt, and more. It can then compare the actual with the budget or forecast and make decisions based on the results. Budget models are typically monthly or quarterly and focus heavily on the profit and loss account. Other types of financial models include the following:

• Initial public offer model
• Sum of the parts model
• Consolidation model
• Options pricing model

Alternative tools for financial modeling

Excel has always been recognized as the go-to software for financial modeling. However, there are significant shortcomings in Excel that have made the serious modeler look for alternatives, in particular in the case of complex models. The following aspects are some of the disadvantages of Excel that financial modeling software seeks to correct:

• Large datasets: Excel struggles with very large data. After most actions, Excel recalculates all formulas included in your model. For most users, this happens so quickly that you don't even notice. However, with large amounts of data and complex formulas, delays in recalculation become quite noticeable, and can be very frustrating. Alternative software can handle huge multidimensional datasets that include complex formulas.
• Data extraction: In the course of your modeling, you will need to extract data from the internet and other sources. For example, financial statements from a company's website, exchange rates from multiple sources, and more. This data comes in different formats with varying degrees of structure. Excel does a relatively good job of extracting data from these sources. However, it has to be done manually, and thus it is tedious and limited by the skill set of the user. Oracle BI, Tableau, and SAS are built, among other things, to automate the extraction and analysis of data.
• Risk management: A very important part of financial analysis is risk management. Let's look at some examples of risk management here:
• Human error: Here, we talk about the risk associated with the consequences of human error. With Excel, exposure to human error is significant and unavoidable. Most alternative modeling software is built with error prevention as a prime consideration. As many of the procedures are automated, this reduces the possibility of human error to a bare minimum.
• Error in assumptions: When building your model, you need to make a number of assumptions since you are making an educated guess as to what might happen in the future. As essential as these assumptions are, they are necessarily subjective. Different modelers faced with the same set of circumstances may come up with different sets of assumptions leading to quite different outcomes. This is why it is always necessary to test the accuracy of your model by substituting a range of alternative values for key assumptions and observe how this affects the model. This procedure, referred to as sensitivity and scenario analyses, is an essential part of modeling. These analyses can be done in Excel, but they are always limited in scope and are done manually. Alternative software can easily utilize the Monte Carlo simulation for different variables or sets of variables to supply a range of likely results as well as the probability that they will occur. The Monte Carlo simulation is a mathematical technique that substitutes a range of values for various assumptions, and then runs calculations over and over again. The procedure can involve tens of thousands of calculations until it eventually produces a distribution of possible outcomes. The distribution indicates the chance or probability of individual results happening.

Advantages of Excel

In spite of all the shortcomings of Excel, and the very impressive results from alternative modeling software, Excel continues to be the preferred tool for financial modeling.

Let us take a deeper look at the advantages of Excel in the following section:

• Already on your computer: You probably already have Excel installed on your computer. The alternative modeling software tends to be proprietary and has to be installed on your computer manually.
• Familiar software: About 80% of users already have a working knowledge of Excel. The alternative modeling software will usually have a significant learning curve in order to get used to unfamiliar procedures.
• No extra cost: You will most likely already have a subscription to Microsoft Office including Excel. The cost of installing new specialized software and teaching potential users how to use the software tends to be high and continuous. Each new batch of users has to undergo training on the alternative software at additional cost.
• Flexibility: The alternative modeling software is usually built to handle certain specific sets of conditions, so that while they are structured and accurate under those specific circumstances, they are rigid and cannot be modified to handle cases that differ significantly from the default conditions. Excel is flexible and can be adapted to different purposes.
• Portability: Models prepared with the alternative software cannot be readily shared with other users, or outside of an organization since the other party must have the same software in order to make sense of the model. Excel is the same from user to user right across geographical boundaries.
• Compatibility: Excel communicates very well with other software. Almost all software can produce output, in one form or the other, that can be understood by Excel. Similarly, Excel can produce output in formats that many different software can read. In other words, there is compatibility whether you wish to import or export data.
• Superior learning experience: Building a model from scratch with Excel gives the user a great learning experience. You gain a better understanding of the project and of the entity being modeled. You also learn the connection and relationship between different parts of the model.

Excel – the ideal tool

The following features make Excel the ideal tool for any data:

• Understanding data: No other software mimics human understanding the way Excel does. Excel understands that there are 60 seconds in a minute, 60 minutes in an hour, 24 hours in a day, and so on to weeks, months and years. Excel knows the days of the week, months of the year, and their abbreviations, for example, Wed for Wednesday, Aug for August, and 03 for March! Excel even knows which months have 30 days, which months have 31 days, which years have 28 days in February, and which are leap years and have 29 days. It can differentiate between numbers and text, it also knows that you can add, subtract, multiply, and divide numbers, and we can arrange text in alphabetical order. On the foundation of this human-like understanding of these parameters, Excel has built an amazing array of features and functions that allow the user to extract almost unimaginable detail from an array of data.
• Navigation: Models can very quickly become very large, and with Excel's capacity, most models will be limited only by your imagination and appetite. This can make your model unwieldy and difficult to navigate. Excel is wealthy in navigation tools and shortcuts, it makes the process less stressful and even enjoyable. The following are examples of some of the navigation tools:
• Ctrl + PgUp/PgDn: These keys allow you to quickly move from one worksheet to the next. Ctrl + PgDn jumps to the next worksheet and Ctrl + PgUp jumps to the previous worksheet.
• Ctrl + Arrow Key (→↓←↑­): If the active cell (the cell you're in) is blank, then pressing Ctrl + Arrow key will cause the cursor to jump to the first populated cell in the direction of the cursor. If the active cell is populated, then pressing Ctrl + Arrow key will cause the cursor to jump to the last populated cell before a blank cell, in the direction of the cursor.

Summary

In this chapter, we looked at the main ingredients that make up a financial model. We looked at various types of financial models and how they work in Excel. We also looked at alternative tools for financial modeling and the various advantages that Excel has. Finally, we saw all the various reasons why Excel is the ideal tool for creating financial models.

In the next chapter, we will see the various steps involved in creating a model.