Excel modeling is a vital part of your career in investment banking. Modeling in general is designed to represent the relationships among the variables of a model in mathematical terms; by doing so, it solves a financial problem, mostly to answer “what-if” questions or to make projections. In creating financial models on Excel, it is important to not only know what types of models to use in a particular situation but also to follow certain industry-standard best practices so that your models can be understood consistently among different verticals and horizontals in the field.
Model | Purpose |
DCF one-pager | Used in a buy-side pitch book to provide a range of valuation for the acquisition targets. |
Fully integrated DCF | Used to value a target company and presented to the acquiring board of directors. |
Comps model | Used as the standard model by the entire industrials team at a bulge bracket bank. |
Restructuring model | Built for multinational companies to stress test the impact of selling 1 or more businesses as part of a restructuring strategy. |
Leveraged finance model | Used in the loan approval process to analyze loan performance under various operating scenarios and credit events. |
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.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.
Types of cells | Excel formula | Color |
Hard-coded numbers | =1234 | Blue |
Formulas | =A1*A2 | Black |
Links to other worksheets | =Sheet2!A1 | Green |
Links to other files | =[Book2]Sheet1!A1 | Red |
Links to data providers | =CIQ(IQ_TOTAL_REV) | Dark Red |
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:
1. Revenue (sales) 2. Cost of goods sold (COGS) 3. Operating expenses 4. Other income 5. Depreciation and amortization | 6. Interest 7. Taxes 8. Non-recurring and extraordinary items 9. Distributions |
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.
Assets = Liabilities + 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.
Balance sheet line items | Driver when forecasting |
Accounts receivable | Grow in-line with revenue growth |
Inventories | Grow in-line with cost of sales growth |
Other current assets | Grow in-line with revenue growth |
Other non current assets | Grow in-line with revenue growth |
Accounts payable | Grow in-line with cost of sales growth |
Other current liabilities | Grow in-line with revenue growth |
Deferred revenue (current and noncurrent) | Grow in-line with revenue growth |
Other non current liabilities | Grow in-line with revenue growth |
Common stock | Increase by stock-based compensation forecasted in the I/S section |
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:
Unlevered Free Cash Flow = Net Operating Profit after Tax + Non-cash adjustments + Change in Net Working Capital – Capital Expenditures
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.
For example, if the peer group has the range for EV/EBITDA from 6x to 8x, the EBITDA of the target company is 20M, the target company can be valued at an EV between $120M to $160M at best.
More ideally, investors are willing to acquire at the higher price if it is a prospective company with potential growth in the future.
Nonetheless, the company would be undervalued if its EV is less than $120M.
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?
For publicly traded target:
- Merger Proxy: a SEC filing required when a public companies do something that must be voted on by shareholders
- Tender offer documents: the documents containing the terms and conditions of the Offer sent to the holders of the target companies
For publicly traded acquirer but private target:
- 8-K: a report of corporate changes at a company that could be important for its shareholders or SEC
- Annual reports and 10-KL: a comprehensive report required by SEC filed annually by a public company about its financial performance
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.
For example, if the range for the EV/EBITDA multiple is 1x–5x, and the EBITDA of the company in question is $100 million. Then, the valuation range for their business would be:
- Low: $100 million
- High: $500 million
4.4. Overview of valuation modeling
Discounted Cash Flow Analysis | Comparable Companies Analysis | Precedent Transactions Analysis | |
Other names | Intrinsic Value Model | Trading Multiples, Peer Group Analysis | Comparable Transactions, M&A Comps |
Approach | Intrinsic Approach | Relative Approach | Relative Approach |
How does it work? | Analysts forecast the business’ free cash flow into the future and discounts it back to today at the firm’s Weighted Average Cost of Capital | Analysts compare the current value of a business to other similar businesses by looking at trading multiples like P/E, EV/EBITDA, or other ratios | Analysts compare the company in question to other businesses that have recently been sold or acquired in the same industry. |
Valuation Metrics | Dividends, cash flow, and the growth rate for a single company | Multiples of similar companies | Multiples of similar transactions |
When to use? | Lots of certainties in the future performance of the company/industry; Financials are transparent. | If no financial data is available; if there are significant uncertainties in the future operations of the business/industry outlook. | If no financial data is available; need to identify potential bidders and sellers |
5. Leveraged Finance Modeling
Leveraged finance is the method of using debts, as opposed to equity and cash, to finance the purchase of an investment. Leveraged finance is often used by private equity or leveraged buyout firms to maximize their internal rate of return (IRR).
Leveraged finance modeling is often the most advanced type of modeling as the method requires high levels of granularity and detail. The most common model in leveraged finance is the LBO model. How to build one from the ground up?
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:
“Since we could realize an IRR of 20% and a MoM multiple of 2.5x in our Base Case and a 1x multiple even in the most pessimistic scenarios, we recommend doing the deal and acquiring Company X for an EV / EBITDA multiple of 10.0x. The company has been spending progressively less on CapEx as a % of revenue over time, even when its revenue is growing year on year. Even if its growth rate declines to the levels it were during the midst of the pandemic recession in March, the math still works. For the deal not to work, the company’s revenue growth would have to decline to (-15%), which is well below even the worst-performing company in the industry.”
6. Conclusion
Modeling on Excel in finance is both an art and a science. It is similar to science in that there are certain principles and rules that must be followed. In addition, modeling is also similar to an art because there is a certain level of creativity in constructing a model. We hope that the article gives you a thorough overview of modeling in Excel and a comprehensive understanding of how to model.