Book Image

Hands-On Financial Modeling with Microsoft Excel 2019

By : Shmuel Oluwa
Book Image

Hands-On Financial Modeling with Microsoft Excel 2019

By: Shmuel Oluwa

Overview of this book

Financial modeling is a core skill required by anyone who wants to build a career in finance. Hands-On Financial Modeling with Microsoft Excel 2019 explores terminologies of financial modeling with the help of Excel. This book will provides you with an overview of the steps you should follow to build an integrated financial model. You will explore the design principles, functions, and techniques of building models in a practical manner. Starting with the key concepts of Excel, such as formulas and functions, you will learn about referencing frameworks and other advanced components for building financial models. Later chapters will help you understand your financial projects, build assumptions, and analyze historical data to develop data-driven models and functional growth drivers. The book takes an intuitive approach to model testing and covers best practices and practical use cases. By the end of this book, you will have examined the data from various use cases, and have the skills you need to build financial models to extract the information required to make informed business decisions.
Table of Contents (15 chapters)
Free Chapter
Section 1: Financial Modeling - Overview
Section 2: The Use of Excel - Features and Functions for Financial Modeling
Section 3: Building an Integrated Financial 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):
  1. 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:
  1. 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.

  1. 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:

  1. 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