Everything you need to know about Excel modeling

1. What are the common Excel models?

Broadly speaking, there are 5 major categories of Excel modeling used for different purposes. 

There are two important elements in a model: granularity and flexibility. Granularity refers to how detailed a model is. The more granular the model, the more accurate it is. For example, we can forecast the revenue of Apple next year in aggregate or through price per unit and units of goods sold in each segment of its products. The “high-level” approach using aggregate data is inevitably less accurate than the latter “granular” approach. The level of granularity varies depending on the situation where you need the model. If you are providing a back-of-the-envelope analysis to arrive at an approximate valuation range in the preliminary pitch book, then it is totally acceptable to use the high-level approach. 

The second element is flexibility. A model’s flexibility depends on how often it is used by users or for how many different uses. A template, a model used by many people for many purposes, is very flexible, while a model designed for a specific transaction is way less flexible. 

2. Modeling practices

Just like any other fields, in finance, there are some industry-standard best practices when you design an Excel model. Let’s dive into each of them.

2.1. Formatting

Color-coding: Color-coding is used to visually distinguish between modifiable and unmodifiable cells. A well-built model will also reveal cells that are linked to other worksheets/files or cells that are linked to data providers such as Bloomberg or Capital IQ. Having a set rules of color coding will make it easier for readers of your models to understand what is going on behind the spreadsheet.

Sign convention: the decision on whether to use positive or negative sign conventions must be made before the model is built. There are two options:

• Option 1: All income positive, all expenses negative
  • Advantages: logical and consistent, making calculations less complicated
  • Disadvantages: the method does not align with the public filings’ conventions
• Option 2: All expenses positive, non-operating income negative
  • Advantages: consistent with public filings
  • Disadvantages: calculations are confusing and error-prone
• Option 3: All expenses positive except non-operating expenses
  • Advantages: avoid negative non-operating income presentation
  • Disadvantages: presentation not internally consistent.

Out of the three options, it is best to go with option 1 because it reduces the risk of error, especially in detailed and granular models. 

2.2. Formulas

Avoid partial inputs. Hard-coded cells should never be embedded into a cell reference. The danger is that you’ll likely forget there is an assumption inside a formula. Inputs must be clearly separated from calculations. 

One row, one calculation. Data should be presented from left to right. The right of the historical columns are the forecast columns. The formulas should be consistent across rows and the types of data should be the same across columns. 

Use roll-forward calculations. This approach is very useful in making your model more transparent and easier to get audited. Roll-forward is basically connecting current period forecast to the prior period.

Use simple formulas. While a complicated formula will serve better purposes of accuracy, it may confuse readers of your model. Because transparency is very important, complicated formulas should be avoided at all costs. A complicated formula can be easier to understand by breaking it down into multiple cells and rows. 

2.3. Referencing

Never re-enter inputs. If you put an input cell in A1, always make reference to cell A1 for your calculations. Re-entering inputs in multiple places will cause confusion and inconsistencies in the calculations later on. It is always a good idea to link directly to a source cell to make it easier for other people to audit. 

Avoid referencing multiple worksheets. It is very difficult to audit if the data you use in a calculation are referred from another worksheet. Whenever possible, bring the data from other worksheets into the active worksheet where the calculation is made. 

While each model has a different kind of error checks, there are some common checks such as:

• Sum of all sources of funds = uses of funds
• Ensuring the quarterly results add up to the annual results
• Total forecast depreciation expense does not exceed PP&E
• Debt pay-down does not exceed outstanding principal.  

Scenarios and sensitives. A model should be able to provide actionable insights that otherwise wouldn’t be visible. As models in investment banking rely heavily on forecasts and assumptions, it is critical to present outputs in ranges and scenarios. This is when sensitivity analysis comes into play.

3. Financial statements modeling

Financial statements modeling is the most basic Excel model in finance. The 3-statement model includes a variety of schedules and outputs presented in a way that captures the relationships and inter-linkages of the various line items across the 3 financial statements. the 3-statement model always starts with the income statement.

3.1. The income statement

The income statement measures a company’s profit (loss) over a specific period of time. A business is required by the government to report and record the sales it generates for tax purposes. Income statements, over time and depending on the type of business, have grown more complicated. For this reason, analysts often categorize income statements’ line items into nine major categories:

By categorizing items, others who read the income statement can easily understand what the factors that drive or hamper profitability are. 

When it comes to forecasting, analysts often start with forecasting the revenue, followed by forecasting various expenses. The net result is a forecast of the company’s income and earnings per share.

3.2. The balance sheet

The balance sheet is a measure of a company’s financial position at a specific point in time. There are three sections in a balance sheet: assets, liabilities, and shareholders’ equities. The company’s total value of assets must always equal the sum of its liabilities and shareholders’ equity. 

The balance sheet is driven heavily by the assumptions made in the income statement. Revenue drives the operating assumptions while cost of goods sold drives inventories or accounts payable. This table breaks down the drivers for each line item in the balance sheet.

3.3. The cash flow statement

The final core-element of the 3-statement model is the statement of cash flows. Unlike the income statement or the balance sheet, you don’t actually have to do any data-inputting for the statement of cash flows. Every line item is constructed using the other two statements. 

More on how to construct the cash flow statement using the balance sheet and the income statement, check out our article on the 3 financial statements. 

3.4. Sensitivity analysis

A well-built model is one that includes the ability to juggle multiple scenarios and observe how the outputs would change. In order to do this, we will need a sensitivity analysis. 

Sensitivity (or What-if) analysis in Excel helps us to deal with the uncertainty coming from having to predict, project, and assume different input variables. It allows users to observe different output possibilities given 1 to 2 variables or assumptions. Check out our article on how to perform sensitivity analysis.

4. Valuation Modeling

Valuation is a process of determining the value of a company, an asset, security, and etc using valuation techniques and analyses. There are 3 valuation methodologies frequently used for investment bankers to arrive at an educated guess: 

• Intrinsic Valuation Model: Discounted Cash Flow (DCF)
• Relative Valuation Model:  Precedent Transactions (also called M&A Comps) and Comparable Public Companies (also called Public Comps)

4.1. Discounted Cash Flow Model

DCF (Discounted Cash Flow) analysis is a valuation method for projects, assets, or companies using the concept of the time value of money. The DCF analysis is conducted based on a firm’s projected cash flows, indicating the prospect of a firm in its ability of generating flows of cash, or money in cash. These cash flows are then converted into their present value at the cost of capital and potential risks to the company’s underlying value (also called Discount rate). There are 3 major parts in a DCF model.

Step 1: Calculate levered or unlevered free cash flows 

There are three different types of cash flow including: Free Cash Flow, Levered Free Cash Flow, and Unlevered Free Cash Flow, in which, Unlevered Free Cash Flow is much preferred in DCF analysis since it ignores burdens from leverage and capital structures. 

The formula for Unlevered Free Cash Flow is: 

Step 2: Calculate enterprise value and equity value

The enterprise value can be arrived at by adding the discounted stage 1 free cash flow and the discounted terminal value. When it comes to calculating the terminal value, it is important to notice two key assumptions: long-term growth rate and EBITDA exit multiple. EBITDA exit multiple is often approximated using the mean value of comparable companies’ EV/EBITDAs while long-term growth rate is estimated from the macroeconomic and market factors or the internal drivers of the company. 

Equity value is calculated using the following formula:

Step 3: Sensitivity analysis

Following the five steps above, when we put WACC on the column and long-term growth rate on the row, we get the first sensitivity table:

Similarly, for the EBITDA multiple approach, we put WACC on the column and the exit multiple on the row. We have another sensitivity table:

4.2. Comparable Companies Analysis

Comparable companies analysis is a method of valuation where analysts select a set of comparable public companies, operating in the similar industry or sector and location to the valuation company, then compare the valuation company with those companies to estimate its value which can be fairly traded in the market. The modeling process for comparable companies analysis (CCA) can be broken down into 4 steps:

Step 1: Create a comparable public companies list 

• There are three main sources for you to select comparable companies and get financial information including: Capital IQ, Finviz, Yahoo Finance. In which, Capital IT requires you to log in, while the remaining two sources enable you to access financial information to a certain extent. 
• The most ideal number of comparable companies are 5 – 10, though it depends on the industry you perform analysis. The more you narrow down, the more precise comparables list it can generate. 

Step 2: Refine the lists and determine the metrics and multiples to apply

These are common metrics and multiples usually used by analysts to perform the CCA: 

• Revenue or Sales: An amount of money presenting how much the company operation can generate.
• Enterprise Value:  The formula for Enterprise Value is stated above, it is different from Market Capitalization (Equity Value) of a firm.
• EV/Revenue – Enterprise value to Revenue: the total value of a company (accounting for its stocks, debt, cash) to its annual revenue. It allows analysts to gauge whether the company is valued at the fair price. For example, if the EV/R is low, the company may be undervalued. 
• EBITDA: a type of income standing for Earning Before Interests, Depreciations and Amortizations. It acts as a proxy for cash flow generated from the firm’s operation. 
• EV/EBITDA – Enterprise value to EBITDA: a popular multiple used in valuation, it gives analysts an idea of how much it costs to acquire a firm compared with the value of EBITDA (how many times EBITDA you have to pay)
• P/E – Price to Earnings: measures a company’s current share price relative to its earning per share (EPS)- calculated by dividing the company’s profit by the number of outstanding shares. This multiple gives analysts the profitability of a company relatively.

Step 3: Calculate the metrics and multiples of peer companies

After refining the comparables list, you calculate each company’s multiples and metrics. Calculation results then will be applied to derive the value of the valuation company. 

Step 4: Use the multiples and metrics to determine a valuation range for the target company

Now that you have values of multiples and metrics needed. You can determine a range for the valuation company. The valuation of the target company can be derived to the value between the lowest level and the highest level of EV. 

4.3. Precedent Transactions Analysis

Precedent Transactions Analysis, also called “M&A Comps” or “Comparable Transactions,”  uses the prices paid for comparable companies in the past to gauge the valuation of the current transaction. It also gives an estimate of the implied stock price in case of an acquisition. However, remember that it yields the acquisition price of the business at the time the transaction is completed, rather than today. 

How to perform precedent transactions analysis on Excel?

Step 1: Select comparable transactions 

In selecting the appropriate transactions for your analysis, it is important to take into account these important factors: 

• Same business & industry
• Similar business size
• Similar sales growth rates and profitability margins
• Similar capital structure
• Similar reasons for transaction (e.g. fire sale, bankruptcy, or strategic motive).
• Same geographic location of operations

Where can we find transaction-specific information for our analysis?

Step 2: Identify relevant information for the analysis

The key information to be looked into include:

• Announcement/Closing date of the transactions
• Bidder: Acquirer
• Target: Acquiree
• Business Description of the target company
• Local currency: the currency in which the transaction took place
• Acquired Stake: the percentage of the target being acquired
• Consideration type (cash or stock)
• Net debt acquired: the net debt of the target company being acquired
• Implied equity value: equity consideration to be paid by bidder
• Grossed-up equity value: the adjusted equity value when the acquired stake is less than 100%
• Implied enterprise value: grossed-up equity value plus net debt acquired

Step 3: Calculate precedent transactions multiples

You will generally use a Revenue multiple (Enterprise value / Revenue), which measures how valuable a firm is relative to its net sales. Another alternative to revenue multiple is profitability multiple (P / E, EV / EBITDA, EV / EBIT, EV / Unlevered FCF, or Equity Value / Levered FCF), all of which measures the value of a company relative to different metrics of profitability. 

Step 4: Apply the multiples to arrive at valuation range for the company

Having had a range of multiples in the previous step, we will apply them to the financials of the company we are valuing in order to arrive at its implied enterprise value.

4.4. Overview of valuation modeling

Step 1: Make basic transaction assumptions

At the most basic level, you need to know the purchase enterprise value and the proportion of debt and equity used. To calculate the acquisition price (or enterprise value) for a public company, you would calculate the purchase equity value and then adjust for cash and debt in order to arrive at the enterprise value.

In making assumptions about how much debt is required for a deal, analysts often use multiple tranches of debt depending on investors’ risk appetite. PE firms might be risk-seeking and go up to 6x Debt/EBITDA. They have to find investors who are willing to make loans that are similar to them in their risk appetite. These more aggressive investors might be hedge funds, merchant banks, or mezzanine funds; they could also be institutional investors that specialize in higher-risk Debt.

Step 2: Project cash flows and debt repayment 

In order to project cash flows and debt repayment, some key assumptions about the business’ financial conditions need to be made. Assumptions, however, vary depending on the level of details given. Oftentimes, analysts will make key forecasts for the following metrics:

Having established forecasts and scenarios, you would then go about modeling financial statements for 5 years into the future. Analysts often start with the income statement, and then the balance sheet, and finally the cash flow statement. In the process of doing this, it is important to remember how the three financial statements are linked together. Another key part that will help with arriving at the final future free cash flow is supporting schedules. Supporting schedules are sections in an LBO model that help fill in information that cannot be worked out using just three financial statements. Those include a working capital and depreciation schedule and debt & interest schedule

Example of working capital and depreciation schedule

Example of debt and interest schedule

Step 3: Make exit assumptions and calculate the returns

At the end of the projection period, we will assume that the PE firm sells the company to another firm, a normal company or another PE firm. Normally, we will use an EBITDA exit multiple to calculate the exit enterprise value. 

The next step is to calculate return metrics. Two return metrics often used in private equity are internal rate of return (IRR) and Money-on-money multiple (otherwise known as MOIC). A detailed elaboration on IRR and MOIC can be found in this article. 

Step 4: Draw conclusions

Your final recommendation should look something like this: 

6. Conclusion