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.

To provide some clarity, in this chapter, we will learn some popular definitions and 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.

By the end of the chapter, you should be able to hold your own in any discussion about basic financial modeling.

We will cover the following topics:

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

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 alternative scenarios as described in the following subsections.

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:

• Purchasing new equipment: You may already have the capacity and know-how to make or build the equipment in-house. There may also be similar equipment already in place. Considerations will thus be whether to make, buy, sell, keep, or trade in the existing equipment.
• 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:
  1. The cost of the investment: Isolate all costs specific to the investment, for example, construction, additional manpower, added running costs, adverse effects on existing business, marketing costs, and so on.
  2. The benefit gained from the investment: We could gain additional sales. There will be a boost in other sales as a result of the new investment, along with other quantifiable benefits. Regarding the return on investment (ROI), a positive ROI would indicate that the investment is a good one.

Financing

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

• Individual: 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 deposit so that the bank would lend you the difference. The considerations would be as follows:
  • Interest rates: The higher the interest rate, the less you would seek to finance externally.
  • Tenor of the 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 establish the least amount you will require from the bank, no matter what interest rate they are offering.
  • Amount of monthly repayments: How much you will be required to pay monthly to repay the loan.
• Company: A company would need to decide whether to seek finance from internal sources (approach shareholders for additional equity) or external sources (obtain bank funding). We can see the considerations in the following list:
  • Cost of finance: The cost of finance can be easily obtained with the interest and related charges. These finance charges will have to be paid whether or not the company is making a 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 fall due. This exposes the company to all the consequences of defaulting, including security risk and embarrassment among other things.
  • The desired debt-to-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:

• The expectations of the shareholders: Shareholders provide cheap funds and are generally patient. However, shareholders 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 funds are considered cheap because payment of dividends is not mandatory but at the discretion of the directors.
• The need to retain surplus for future growth: It is the duty of the directors to temper the urge to succumb to pressure to declare as many dividends 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 investors.

You should now have a better understanding of financial decision making. Let's now look at mathematical models that are created to facilitate financial decision making.

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 cheapest option, the option that carries an acceptable level of risk, or the most environmentally friendly option, but is usually a mixture of all these features.

Inevitably, there is 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 that serve as 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. This model is referred to as a financial model.

Wikipedia considers a financial model to be a mathematical model that represents the performance of a financial asset, project, or other investment 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.

BusinessDictionary 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 mathematical model created to resolve a financial decision making situation. The model facilitates decision making by presenting preferred courses of action and their consequences, based on the results of the calculations performed by the model.

This definition mentions financial decision making and a mathematical model. It goes on to explain the relationship between them, which is to facilitate decision making. Importantly, it notes that the model presents preferred courses of action from which the decision maker can make a choice, taking into consideration the consequences of each option.

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

The following models are examples that seek to calculate values.

The 3-Statement model

The 3-Statement model is the starting point for most valuation models and here's what it includes:

• 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 plants, 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).

The following is an example of a balance sheet showing assets, liabilities, and equity. Note how the accounting equation plays out with total assets minus current liabilities being equal to equity plus non-current liabilities:

Figure 1.1 – Balance sheet (statement of financial position)

• 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 in realizing that income over the same period to arrive at a profit (as in this case) or loss:

Figure 1.2 – Income statement (statement of comprehensive income)

• 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 on the balance sheet during the period under review.

The following screenshot shows an example of a cash flow statement:

Figure 1.3 – Cash flow statement

The mathematics of the 3-Statement 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 in more detail later in the book and will become clearer.

The discounted cash flow model

The discounted cash flow (DCF) model 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 flow the company can generate. In practice, 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 Chapter 10, Valuation.

The DCF method applies a valuation model to the 3-Statement model mentioned in the 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 model 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)

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 among analysts, as it provides a quick way of arriving at a rough estimate of a company's value.

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 (a 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 then use the EVs and selected multiples of these companies to arrive at an EV for the target company. These are the steps to follow:

1. Calculate the multiples for each of the companies (such as EV/EBITDA, EV/SALES, and P/E ratio (also known as price-earnings ratio).
2. Then calculate the mean and median of the multiples of the peer group of companies.

The median is often preferred over the mean, as it corrects the effect of outliers. Outliers are those individual items within a sample that are significantly larger or smaller than the other items and will 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 following 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 the earnings per share for all three are calculated. The earnings per share (EPS) is an indicator of a company's profitability. It is calculated as net income divided by the 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 said to be 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 (LBO) model will calculate a value for company B as well as the likely return on the eventual sale of the company.

All these are examples of models created to value something. 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 put down as a deposit.

The following table arranges these assumptions in a logical manner so as to easily accommodate any changes in the assumptions and immediately display the effect on the final output:

Figure 1.4 – Amortization table

The loan repayment schedule model illustrated in the preceding screenshot consists of a section that contains all our assumptions, and another section with the repayment schedule, which is integrated with the assumptions in such a way that any change in the assumptions will automatically update the schedule without further intervention from the user.

The monthly repayment is calculated using Excel's PMT function. The tenor is 10 years, but repayments are monthly (12 repayments per year), giving a total number of repayments 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 minus the customer's deposit.

Selection scroll bars have been added to the model so that the customer's deposit (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 automatically.

The preceding screenshot shows the kind of amortization table banks use in order to turn around customers' 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 result 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 models

Other types of financial models include the following:

• Initial public offer model: A financial model created to support a company's initial public offering prepared to attract investors.
• Sum of the parts model: In this method of valuation, the different divisions or segments of a company are assessed separately. The value of the company is the aggregate of all the parts.
• Consolidation model: This is created by taking the results of several business units or divisions and combining them into one model.
• Options pricing mode: This is a model for mathematically arriving at a theoretical price for an option.

Hopefully, you will now appreciate the diverse range of models that exist and the challenges a modeler will have to ensure that their models are clear, comprehensive, and error-free.

A lot of emphasis must be placed on using the right tool and having a thorough grasp of that tool.

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 are some of the disadvantages of Excel that dedicated 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. (This deficit has been mitigated in Office 365 with the use of Power Query, now integrated as part of Excel. See Chapter 5, An Introduction to Power Query.)
• Risk management: A very important part of financial analysis is risk management. Let's look at some examples of risk management here:
  1. 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.
  2. 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 to observe how this affects the model.

This procedure of substituting alternative values for some assumptions, 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 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. 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. Chapter 11, Model Testing for Reasonableness and Accuracy, includes a simple example of Monte Carlo simulation.

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.

The reasons for this are easy to see:

• 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 an 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 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 another, that can be understood by Excel. Similarly, Excel can produce output in formats that lots of 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 about the connection and relationship between different parts of the model.
• 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. Some of these are highlighted in Chapter 5, An Introduction to Power Query.
• 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 just a few of the navigation tools:

3. Ctrl + PageUp/PageDown: These keys allow you to quickly move from one worksheet to the next. Ctrl + PageDown jumps to the next worksheet and Ctrl + PageUp jumps to the previous worksheet.
4. 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.

You should now have a better idea of what constitutes a financial model. You should also understand the shortcomings of Excel, why some alternative tools are sometimes used, but also why Excel continues to be the favored tool for financial modeling.

In the next chapter, we will learn about and understand the various steps involved in creating a model.