Types of financial models
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:
- Calculate the multiples for each of the companies (such as EV/EBITDA, EV/SALES, and P/E ratio (also known as price-earnings ratio).
- 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.
- 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:
- 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.