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.
In the following screenshot, we see the starting point for most valuation models and what it includes:
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 (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.
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:
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:
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.
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.
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.
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.
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:
