Valuation Modeling in Excel

What is Valuation Modeling in Excel?

Valuation modeling in Excel may refer to several different types of analysis, including discounted cash flow (DCF)DCF Model Training Free GuideA DCF model is a specific type of financial model used to value a business.  DCF stands for Discounted Cash Flow, so the model is simply a forecast of a company’s unlevered free cash flow discounted back to today’s value.  This free DCF model training guide will teach you the basics, step by step with examples and images, comparable trading multiples, precedent transactionsPrecedent Transaction AnalysisPrecedent transaction analysis is a method of company valuation where past M&A transactions are used to value a comparable business today. Commonly referred to as “precedents”, this method of valuation is used to value an entire business as part of a merger/acquisition commonly prepared by analysts, and ratios such as vertical and horizontal analysis. The above types of analysis may be built from scratch in Excel or may use an existing template/model. This type of work is commonly performed by a wide range of finance professionals.

Why perform valuation modeling in Excel?

There are many reasons to perform valuation modeling in Excel, and professionals across a wide range of industries spend a significant amount of time doing this type of work.

Reasons to perform valuation modeling include:

• Preparing to raise capitalIPO ProcessThe IPO Process is where a private company issues new and/or existing securities to the public for the first time. The 5 steps in an Initial Public Offering are discussed in detail including selecting an investment bank, due diligence & filings, pricing, stabilization, & transition to transition to normal trading from investors (and determining what price shares should be issued at)
• Selling a business and know what range of prices to accept
• Acquiring a companyMergers Acquisitions M&A ProcessThis guide takes you through all the steps in the M&A process. Learn how mergers and acquisitions and deals are completed. In this guide, we'll outline the acquisition process from start to finish, the various types of acquirers (strategic vs. financial buys), the importance of synergies, and transaction costs and knowing how much to pay for it
• For issuing shares to employees (an Employee Share Ownership Plan or ESOP)
• Internal budgeting and planning purposes
• Succession planningSuccession PlanningSuccession planning refers to the process in which employees are recruited and developed with the aim of filling a key role within an organization. It for the business
• Evaluate investment opportunities and capital projects
• Impairment testing (related to any significant re-education in asset values)
• Legal proceedings, including insolvencyInsolvencyInsolvency refers to the situation in which a firm or individual is unable to meet financial obligations to creditors as debts become due. Before beginning legal insolvency proceedings, the firm or individual may get involved in making an informal arrangement with their creditors

How to perform valuation modeling in Excel?

As noted above, there are three main methods (and many more) for valuing a company. Discounted cash flow or DCF analysis is the most detailed method and often the most relied upon approach. Below is a description of how to perform each type of modeling.

#1 Discounted cash flow modeling in Excel

In this approach, an analyst or finance professional takes 3-5 years of historical financial information for a business and puts it into Excel.  Next, they link the three financial statementsHow the 3 Financial Statements are LinkedHow are the 3 financial statements linked together? We explain how to link the 3 financial statements together for financial modeling and valuation in Excel. Connections of net income & retained earnings, PP&E, depreciation and amortization, capital expenditures, working capital, financing activities, and cash balance together so they are dynamically connected. Following that, assumptions are made about how the business will perform in the future, and those assumptions are used in Excel formulas to create a forecast for the future (typically about five years into the future). Finally, they calculate a terminal valueTerminal Value​The terminal value is used in valuing a company. The terminal value exists beyond the forecast period and assumes a going concern for the company. for the business and discount the forecast period and the terminal value back to the present using the company’s weighted average cost of capitalWACCWACC is a firm’s Weighted Average Cost of Capital and represents its blended cost of capital including equity and debt. The WACC formula  is = (E/V x Re) + ((D/V x Rd)  x  (1-T)). This guide will provide an overview of what it is, why its used, how to calculate it, and also provides a downloadable WACC calculator (WACC).

For a more detailed explanation, read this step-by-step guide to DCF modelingDCF Model Training Free GuideA DCF model is a specific type of financial model used to value a business.  DCF stands for Discounted Cash Flow, so the model is simply a forecast of a company’s unlevered free cash flow discounted back to today’s value.  This free DCF model training guide will teach you the basics, step by step with examples and images.

#2 Comparable trading multiples in Excel

This approach to valuation modeling in Excel is very different than a DCF model. In the Excel method, instead of determining a company’s intrinsic value (as above), an analyst will look at the valuations of other publicly traded companies and compare them to the businesses they wish to value. Common examples of valuation multiples include EV/Revenue, EV/EBITDAEV/EBITDAEV/EBITDA is used in valuation to compare the value of similar businesses by evaluating their Enterprise Value (EV) to EBITDA multiple relative to an average. In this guide, we will break down the EV/EBTIDA multiple into its various components, and walk you through how to calculate it step by step, EV/EBIT, Price/EarningsPrice Earnings RatioThe Price Earnings Ratio (P/E Ratio) is the relationship between a company’s stock price and earnings per share. It gives investors a better sense of the value of a company. The P/E shows the expectations of the market and is the price you must pay per unit of current (or future) earnings, and Price/BookValuationFree valuation guides to learn the most important concepts at your own pace. These articles will teach you business valuation best practices and how to value a company using comparable company analysis, discounted cash flow (DCF) modeling, and precedent transactions, as used in investment banking, equity research,.

For a more detailed explanation, read this guide to comparable company analysisComparable Company AnalysisHow to perform Comparable Company Analysis. This guide shows you step-by-step how to build comparable company analysis ("Comps"), includes a free template and many examples. Comps is a relative valuation methodology that looks at ratios of similar public companies and uses them to derive the value of another business.

#3 Precedent transaction modeling in Excel

With this third approach to valuation modeling in Excel, an analyst will look at the prices paid for mergers and acquisitions (M&A) of similar businesses that took place in the past. This is also a relative form of valuation, but unlike comparable trading multiples, these transactions include takeover premiums (the value of control) and are based in the past (can quickly become out of date).

For a more detailed explanation, read this guide to precedent transactionsPrecedent Transaction AnalysisPrecedent transaction analysis is a method of company valuation where past M&A transactions are used to value a comparable business today. Commonly referred to as “precedents”, this method of valuation is used to value an entire business as part of a merger/acquisition commonly prepared by analysts.

Skills for performing valuation modeling in Excel

To perform this type of analysis, finance professionals require many different skills that can often take years of education and experience to master.

The most important skills include:

• Accounting (principles, methods, financial statements)
• Finance (financial math, formulas, ratios, calculations)
• Excel (MS Excel best practices, shortcuts, functions)
• Strategy (competitive advantage, market analysis)
• Valuation (a combination of the above skills)

CFI’s Financial Analyst CertificationFMVA® CertificationJoin 350,600+ students who work for companies like Amazon, J.P. Morgan, and Ferrari covers all of these in one intensive online program, while other designations may not cover all skill areas.

Jobs that perform valuation modeling in Excel

There are many jobs and career paths that require the skills of being able to value a company, a business unit, or an investment opportunity in Excel.

Some of the most common jobs include:

• Investment bankingIBD - Investment Banking DivisionIBD is an acronym for the Investment Banking Division within the overall investment bank.  IBD has responsibility for working with corporations, institutions, and governments to carry out capital raising (underwriting in equity, debt, and hybrid markets) as well as for executing mergers and acquisitions (analyst and associate level)
• Private equity & venture capital (analyst and associate level)
• Corporate development (analyst and manager level)
• Financial planning & analysisFP&AFinancial Planning and Analysis (FP&A) is an important function at a corporation. FP&A professionals support executive decision making for the CEO, CFO and Board of Directors with planning, analysis, and modeling. Learn what the job of an FP&A analyst, manager, or director entails - salary, requirements, education, skills (analyst, manager and director level)
• Public accounting (transaction advisory, impairment testing)
• Equity researchEquity Research OverviewEquity research professionals are responsible for producing analysis, recommendations, and reports on investment opportunities that investment banks, institutions, or their clients may be interested in.  The Equity Research Division is a group of analysts and associates. This equity research overview guide (associate and analyst level)

Why use Excel for valuation modeling?

The flexibility of Excel is both a blessing and a curse. While larger organizations may attempt to use software to manage their financial modelingWhat is Financial ModelingFinancial modeling is performed in Excel to forecast a company's financial performance. Overview of what is financial modeling, how & why to build a model. A 3 statement model links income statement, balance sheet, and cash flow statement. More advanced types of financial models are built for valuation, plannnig, and, the reality is they often end up reverting back to Excel.

The main reasons Excel is used include:

• Total flexibility and customization
• Extremely low cost to purchase
• Easy to share externally with other parties
• Ubiquitous and understood by all
• Simple to use and easy to audit (no “black box”)

On this flip side, however, this flexibility means models are prone to errors, inaccurate calculations, and poor practices. Analysts and other finance professionals must ensure that they possess strong Excel modeling skills and a thorough understanding of industry-leading best practices.

Additional resources

Thank you for reading this guide and overview to valuation modeling in Excel. Hopefully, by now, you have a solid understanding of what it is, why it’s used, how it’s performed, and the skills required to carry it out.

To learn more about modeling, check out CFI’s Analyst Certification ProgramFMVA® CertificationJoin 350,600+ students who work for companies like Amazon, J.P. Morgan, and Ferrari and these additional free resources below:

• Valuation MethodsValuation MethodsWhen valuing a company as a going concern there are three main valuation methods used: DCF analysis, comparable companies, and precedent transactions. These methods of valuation are used in investment banking, equity research, private equity, corporate development, mergers & acquisitions, leveraged buyouts and finance
• Analysis of Financial StatementsAnalysis of Financial StatementsHow to perform Analysis of Financial Statements. This guide will teach you to perform financial statement analysis of the income statement, balance sheet, and cash flow statement including margins, ratios, growth, liquiditiy, leverage, rates of return and profitability. See examples and step-by-step instruction
• Data Sources in Financial ModelingData Sources in Financial ModelingCollecting and using the right data sources in financial modeling is critical to the success of a business. Financial modeling requires gathering and
• Excel Modeling Best PracticesExcel Modeling Best PracticesThe following excel modeling best practices allow the user to provide the cleanest and most user-friendly modeling experience. Microsoft Excel is an extremely robust tool. Learning to become an Excel power user is almost mandatory for those in the fields of investment banking, corporate finance, and private equity.