Valuation Modeling in Excel

Use Excel to perform valuation modeling

What is Valuation Modeling in Excel?

Valuation modeling in Excel may refer to several different types of analysis, including discounted cash flow (DCF) analysis, comparable trading multiples, precedent transactions, and ratios such as vertical and horizontal analysis. The various types of analyses 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.

 

Valuation Modeling in Excel

 

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 exactly this kind of work. The reasons include:

  • Preparing to raise capital from investors (i.e., determining what price shares should be issued at)
  • Selling a business and identifying what range of prices to accept
  • Acquiring a company 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 planning for the business
  • Evaluate investment opportunities and capital projects
  • Impairment testing (related to any significant reduction in asset values)
  • Legal proceedings, including insolvency

 

How to perform valuation modeling in Excel?

As noted above, there are three primary methods 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

Using the above approach, a finance professional takes 3-5 years of historical financial information about a business and puts it into Excel. Next, they link the three financial statements together so that 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 value for the business and discount the forecast period and the terminal value back to the present, using the company’s weighted average cost of capital (WACC).

 

DCF Valuation Modeling example

 

For a more detailed explanation, read this step-by-step guide to DCF modeling.

 

#2 Comparable trading multiples in Excel

The comparable multiples valuation modeling approach in Excel is very different from that of a DCF model. With this 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 that of the business(es) they wish to value. Common examples of valuation multiples include EV/Revenue, EV/EBITDA, EV/EBIT, Price/Earnings, and Price/Book.

 

comparable company valuation model in Excel

 

For a more detailed explanation, read this guide to comparable company analysis.

 

#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).

 

Precedent transaction analysis example Excel

 

For a more detailed explanation, read this guide to precedent transactions.

 

Skills for performing valuation modeling in Excel

To perform these types of analyses, financial professionals are required to have 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 all of the preceding skills)

CFI’s Financial Analyst Certification 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 careers that require such skills include the following:

  • Investment banking (analyst and associate level)
  • Private equity & venture capital (analyst and associate level)
  • Corporate development (analyst and manager level)
  • Financial planning & analysis (analyst, manager, and director level)
  • Public accounting (transaction advisory, impairment testing)
  • Equity research (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 modeling, 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 the flip side, however, the extreme flexibility of Excel means that models may be 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 of 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 Financial Analyst Certification Program and the additional free CFI resources below:

  • Valuation Methods
  • Analysis of Financial Statements
  • Data Sources in Financial Modeling
  • Excel Modeling Best Practices

Financial Modeling Certification

Become a certified Financial Modeling and Valuation Analyst (FMVA)® by completing CFI’s online financial modeling classes!