Valuation Modeling in Excel

Use Excel to perform valuation modeling

Author: Josh Pupkin
Josh Pupkin
Josh Pupkin
Private Equity | Investment Banking

Josh has extensive experience private equity, business development, and investment banking. Josh started his career working as an investment banking analyst for Barclays before transitioning to a private equity role Neuberger Berman. Currently, Josh is an Associate in the Strategic Finance Group of Accordion Partners, a management consulting firm which advises on, executes, and implements value creation initiatives and 100 day plans for Private Equity-backed companies and their financial sponsors.

Josh graduated Magna Cum Laude from the University of Maryland, College Park with a Bachelor of Science in Finance and is currently an MBA candidate at Duke University Fuqua School of Business with a concentration in Corporate Strategy.

Reviewed By: Patrick Curtis
Patrick Curtis
Patrick Curtis
Private Equity | Investment Banking

Prior to becoming our CEO & Founder at Wall Street Oasis, Patrick spent three years as a Private Equity Associate for Tailwind Capital in New York and two years as an Investment Banking Analyst at Rothschild.

Patrick has an MBA in Entrepreneurial Management from The Wharton School and a BA in Economics from Williams College.

Last Updated:November 15, 2023

What is Valuation Modeling in Excel?

Valuation modeling refers to the forecasting and analysis using several different financial models. It includes discounted cash flow (DCF) analysis, precedent comparables, and comparable trading multiples. These financial models may be developed from the ground up in Excel or developed using existing templates.

Primarily, there are three methods used by practitioners when valuing a company: (1) DCF analysis, (2) comparable company analysis, and (3) precedent transactions. 

They are commonly used in equity research, investment bankingprivate equitymergers & acquisitions (M&A), corporate development, leveraged buyouts (LBO), and many other areas.

Typically, this type of modeling is used to determine the value of an investment, be it an asset or a company’s stock price. For instance, based on its current share price, users can evaluate the value of a company or determine whether it is overvalued or undervalued.

Before we dive deep into the topic, we want you to know that we truly believe that a more hands-on approach always helps gain a much better understanding of a topic. Therefore, we would like you to pick out one of the modeling templates that our finance experts have created for you to explore while reading this page.

Key Takeaways

  • Financial modeling is crucial for valuation, aiding in informed investment decisions.
  • Key components of valuation modeling: historical data, assumptions, projections, discount rates, and sensitivity analysis.
  • Data accuracy is essential for reliable valuation models; thorough verification is important.
  • Leveraging Excel's formulas and functions improves efficiency and accuracy in modeling.
  • Sensitivity analysis and scenario modeling provide insights into risks and opportunities.

Free WSO Financial Modeling Templates

Sign up to receive a FREE swipe file containing a collection of quality financial modeling templates to help your finance skills and prepare for interviews.

Why Perform valuation modeling?

Modeling is a useful tool for finance professionals who are part of various transactions, including:

It is also useful for non-finance professionals like marketing analysts or product development teams to perform market research.

The model can be used independently or in tandem with other models such as sensitivity analysis and scenario analysis. The goal of the model is to estimate the future value of stocks and predict the share price at a certain point in time.

For example, an analyst could use it to figure out how much money they would need to invest in an IPO today to reach their target price when they sell their shares after the IPO.

Modeling in Excel is sometimes the only way to perform valuation analysis if another spreadsheet-based financial application is not accessible or is too expensive. It can also be a more convenient way to perform valuation analysis when spreadsheets are the only tool available for data collection.

It enables one to estimate future financial metrics and use them to calculate ratios, such as:

  • Price/book value (PBV)
  • Price/sales ratio (PSR)
  • Price-earnings-growth (PEG)
  • Dividend yield

In addition, it is possible to evaluate changes in the current market value of an asset or company versus its intrinsic value. It can also examine multiples of peers or other companies within the same company.

This article will now cover how to value companies using the three approaches – discounted cash flow (DCF) analysis, precedent comparables, and comparable trading multiples. These models can be built from scratch in Excel or using an existing model or template. 

Read on for more!

How to perform valuation modeling in Excel?

Valuation modeling in Excel may be performed by using existing templates or by creating a model from scratch.

DCF analysis is a staple of financial modeling and can be performed with a basic template. It involves estimating future cash flows until the end of the forecast period and is an intrinsic value approach.

It is a fundamental approach that provides many options to incorporate various assumptions. Conversely, analyzing trading multiples and precedent comparables are relative value approaches.

In this context, intrinsic value refers to the company’s fair value computed as the sum of its future cash flows and terminal value, discounted at the cost of capital. Other variables being constant, higher future cash flows will translate into a higher intrinsic value in today’s terms.

For example, a person valuing a house using the intrinsic value approach might say, “This house is worth $5 million because if one were to rent this house for many years, they would be paying x amount in annual rent, and the total cash outflow over the years can be discounted to a present value of $5 million.”

Contrary to intrinsic value, relative value derives an asset’s worth by comparing it with the value of similar assets.

For example, if a person were valuing a house using the relative value approach, they would say, “A similar house in the neighborhood sold for $4.5 million, but its area was slightly smaller.

This house under consideration is fairly similar, so adjusting for its slightly larger area, it must be worth $5 million.”

The video below, taken from our Valuation Modeling Course, walks you through differences in intrinsic and relative valuation methods.

On the other hand, market value is a result of demand and supply. The demand for a stock is reflected in its price. The higher the demand, the higher the price. Therefore, a stock trading at a higher price than its intrinsic value is considered overvalued, and vice versa.

However, up and down movements in stock prices are daily occurrences causing stocks to be overvalued and undervalued daily.

Allow us to take you through the steps involved in each of the three approaches to valuing a company.

Discounted cash flow (DCF) modeling in Excel

DCF analysis determines the intrinsic value by finding the value of all expected future free cash flows in present terms. It is an intrinsic form of valuation. Due to that, some experts consider it to be the best way to value a company, especially in periods where the market seems to be over or undervaluing assets.

This method accounts for the time value of money. Experts find free cash flows (operating cash flow minus CapEx) to be one of the best proxies for financial performance.

Investors use this to evaluate whether the value of the expected cash flows today is greater than their investment or whether the returns are greater than the opportunity cost.

The video below is taken from our DCF Modeling Course. It illustrates the purposes of using the DCF approach and when it is used most.

Free Modeling Lessons

Sign up to our FREE 7-Day Financial Modeling Training to kickstart your career

WSO’s finance experts have crafted a free template specifically for DCF analysis with which you can experiment as you go on reading the steps involved in DCF analysis.

  1. The UFCF estimate in the first forecasting period can come from the company’s financial statements or some sell-side analyst reports.
  2. Next, the growth rates can be predicted by creating individual estimates for each year or by applying bulk estimates that apply the same growth rate for different phases. For example, the same growth rate may be applied from years 2 to 6, another growth rate from years 7 to 11, and another to calculate terminal value.
  3. Then, the discount rate, cash and the liability balances, and the number of shares outstanding need to be input in the separate cells.
  4. Once this is done, analysts need to decide which rate to use while discounting the UFCF.
  5. The NPV function is used to calculate the net present value (NPV) of the estimated cash flows and the terminal value by discounting them at the cost of capital.
  6. The current value of debt and cash can be subtracted from the NPV to determine the intrinsic value of equity.
  7. The total intrinsic value can be divided by the shares outstanding to find the intrinsic value per share.

 

Valuation using Comparable trading multiples

Comparable analysis (also called trading multiples analysis, comps analysis, or comps) is a relative value approach to valuation in which analysts observe trading multiples of comparable companies and use those ratios to calculate the value of a company.

Unlike DCF analysis, comps do not account for the time value of money. Calculating relative valuation through comps is routine for analysts working in investment banking, corporate development, equity research, and private equity.

This method is similar to the precedent transaction method in many ways. The key differences are listed in the next section, which is about the precedent transaction method. 

Below is an introductory video on trading comps from our Valuation Course.

Below, we will walk you through the list of steps to build valuation models for trading comps.

  1. Find similar companies
  2. Collect information
  3. Build comps model
  4. Compute relevant multiples
  5. Value the company
  6. Interpret results

Feel free to download this free modeling template on Multiples Analysis which is made with the sole intention of helping you understand this concept better. The above steps to comps analysis are explained below.

  1. Find similar companies

This task may not be as easy as it sounds because defining comparable companies can be subjective. Although data mines such as Bloomberg Terminal and CapIQ can be expensive, they are great portals to find all your needed information. They can give you detailed information about publicly traded companies. Analysts can filter companies based on industry, geography, size, growth, etc. 

  1. Collect information

Once we have determined the closest matches to the company we are trying to value, we must gather their historical financial data. Again, data mines like Bloomberg Terminal can help cut down a major chunk of the laborious work. Without access to these databases, the data can still be obtained and entered into the model as it is publicly available, but it can get tedious. Depending on the stage of the company in the business lifecycle, we look at different metrics. Profit-related metrics like EBITDA or net income are considered heavily for a mature company. On the other hand, turnover-based metrics like revenue or gross margin carry more weight in the case of early-stage companies.

  1. Build comps model

We can begin working on the comps valuation model with all the requisite data now at hand. We can start by listing the relevant data about the comparable companies. The most used metrics include share price, market cap, revenue, EBITDA, enterprise value (EV), earnings per share (EPS), and some estimates or assumptions for the future.

  1. Compute relevant multiples

The next step is to compute the relevant ratios using the data gathered so far and the future outlook. The most common ratios used in comps analysis are EV/EBITDA, EV/Gross Profit, EV/Revenue, price-to-earnings (P/E), and price-to-book (P/B).

  1. Value the company

The valuation process is summarized below.

  • Compute ratios for each of the comparable companies.
  • Take the average of each ratio - for example, the average of the P/E ratios of all the comparables.
  • Adjust each of these averages based on future outlook.
  • Use the adjusted averages to determine different valuations of the company in question.
  • Use the average of the different valuations of the company to arrive at the final valuation.

  1. Present results

The best way to communicate quantitative results is by using charts and graphs. The results should be graphed in an easily understandable manner to compare them with the results determined using other methods. A football field chart is one of the commonly used charts in comps analysis and precedent transaction analysis as it allows users to present ranges of valuations.

  1. Interpret results

Comps analysis can ultimately tell us which companies are undervalued or overvalued based on different ratios. However, since the analysis ignores qualitative factors, results need to be interpreted with caution. Understanding why the company is overvalued or undervalued compared to other companies can usually mitigate the ambiguity. Companies may be overvalued or undervalued, or appear to be so, due to differences in growth prospects, Capex, capital structure, etc.

Valuation using Precedent transaction modeling in Excel

In the precedent transaction approach to valuation, also known as transaction comps analysis, the company in question is compared to comparable companies in the industry which were recently sold or acquired. Since companies are often acquired at a premium, the valuation figures derived using the precedent transaction method also include a premium component.  Due to that, it generally gives the highest valuation of a company, followed by comps analysis and then DCF analysis. This method is also a relative value approach to valuation like comps analysis. It also uses metrics and ratios of other companies to derive the value of the company being analyzed. Both the methods rely on comparable companies, which are not easy to pick. That said, the two methods have some key differences, as listed below.

  • Data: Comps analysis uses data of publicly traded companies. The precedent transaction method uses transaction data of other recently acquired companies. The data for the latter is harder to gather in comparison with the former. 
  • Validity of results: Since M&A activity is infrequent, the valuations derived using transaction comps become stale over time. On the other hand, comps analysis can be done daily as the public market data is easily available.
  • Acquisition premium: Companies are often acquired at a premium. The precedent transaction method uses companies that are recently acquired. Due to this, a premium component will inflate the valuations of the company being analyzed. Comps analysis does not suffer from this phenomenon.

Transaction comps are particularly useful for valuing companies for mergers and acquisitions (M&A) transactions. However, it is not as prominently used in the valuation business as the other two methods.  The steps to value a company using transaction comps are listed below.

  1. Select relevant transactions
  2. Shortlist transactions
  3. Spreading comps
  4. Value the company
  5. Present results

These steps involved in precedent transaction analysis are explained below. 

  1. Select relevant transactions

Selecting the relevant historical transactions from the universe of all M&A transactions is the first step. Analysts screen for M&A where the target company is comparable to the company being valued. Companies may be considered comparable based on the following criteria–

  • Industry
  • Company size
  • Financial metrics (revenue, EBITDA, etc.)
  • Offerings (products and services)
  • Geography
  • Customer base

Information providers and data aggregators such as CapIQ can make the entire valuation process more efficient, as most of the necessary data, if not all, can be found in a single depository.

  1. Shortlist transactions

No two companies are exactly the same, so it is next to impossible to find perfectly comparable companies. Regardless, we try to find the closest matches in this step of the process. The relevant transactions identified in the previous step can be narrowed down by carefully understanding what the target companies do and discarding those that are not close matches.

  1. Spreading comps

“Spreading the comps” is investment banker lingo for creating spreadsheet models to analyze comparable multiples like P/E, P/B, EV/EBITDA, EV/Sales, etc. As part of this step, averages and medians for each ratio are calculated for further use.

  1. Value the company

The averages and the medians of the multiples calculated in the previous step are applied to the corresponding value drivers of the company being valued. Different ratios should produce different values for the company, thus creating a range for its valuation.

  1. Present results

The best way to communicate quantitative results is by using charts and graphs. The results should be graphed in an easily understandable manner to compare them with the results determined using other methods. A football field chart is one of the commonly used charts in comps analysis and precedent transaction analysis as it allows users to present ranges of valuations.

  1. Interpret results

Since precedent transactions analysis ignores qualitative factors, results need to be interpreted with caution. Understanding why a company is overvalued or undervalued compared to other companies can usually mitigate the ambiguity. Reasons for overvaluation or undervaluation could be due to takeover premium or differences in growth prospects, CapEx, capital structure, etc. The video below, taken from WSO’s Valuation Course, gives an overview of breaking down the output from transaction comps analysis.

 

 

 

Skills for performing valuation modeling in Excel

Valuation modeling in Excel is a skill and a practice. Modeling involves a much more comprehensive process than most imagine. It requires the modeler to know more than just Excel.

It’s not about formulas or numbers; it’s about understanding the valuation process and how to use the innumerable tools. These skills can sometimes take years of experience to master. 

Some skills for performing modeling in Excel include:

  • Understanding the benefits of DCF analysis, trading multiples, and precedent transactions
  • Being able to build financial models from scratch (or by using existing templates/models)
  • Being able to build a three-statement model
  • Understanding the significance of ratios
  • Being able to prepare data using pivot tables
  • Attention to detail
  • Forecasting skills
  • Presentation skills
  • Ability to make reasonable and realistic assumptions
  • Ability to simplify complex information

To perform financial modeling in Excel, a finance professional will typically be skilled in Microsoft Excel, Microsoft Access, or both. They should also have the fundamental knowledge of accounting, finance, and strategy.

They should also know about financial analysis software like Bloomberg and Reuters. Additionally, a finance professional must be familiar with finance principles and the various types of analysis that can be performed with these tools.

Apart from these skills, financial modelers should also be aware of the current best practices in the industry to build models that are easy to comprehend by all users, meet their objectives effectively, and are robust.

Which Job Profiles Build valuation models

Valuation modelers typically work in a variety of fields, including commercial banking, private equity, investment banking, and venture capital. They may also be employed by third-party consulting firms that require them to develop or otherwise use valuation models.

Venture capitalists will often require the valuation models used in their investments to be tailored for their specific businesses. They will then present these valuations in pitch meetings with new business opportunities for potential investments or as part of their due diligence process to evaluate investment opportunities.

Companies selling financial products such as stocks, bonds, and mutual funds may also rely on valuation models created by valuation modelers to determine the value of their assets. These assets are typically valued based on their earnings before interest and taxes (EBIT), market capitalization, and other factors.

Financial modeling in Excel can be used in a variety of different scenarios. Some of the most common jobs that may require this skill are:

Excel for valuation modeling

Excel is a versatile tool for data analysis, the best perhaps, and valuation modeling is just one of the many ways it can be used. Because it’s so easy to create a spreadsheet in Excel and the software has wide-ranging capabilities, it’s often the best option for this type of work.

An innumerable amount of data is processed on Excel spreadsheets every day. All this data helps decision-makers make informed decisions. Investors are a subset of all decision-makers that use it to analyze data to optimize investment portfolios and evaluate investment opportunities.

Excel allows users to manipulate large amounts of data easily, create models through formulas, and perform advanced statistical calculations. It also enables users to navigate through humongous amounts of data easily.

In addition, users can use formatting to differentiate between different data and make numbers look more presentable. Finally, it allows users to present the data visually through charts and graphs.

It offers users an extremely wide range of built-in formulae and functions, which play a huge part in financial modeling and data analysis. Even though most users will not end up using many of its functions, it is comforting to know that they are available at the click of a few buttons.

Besides the wide range of functionality that Excel offers, it also has a large user base. The user base acts as a community that shares resources on Excel. The large community can be an especially helpful source of learning resources.

In addition, with such a large user base, any bugs in the software are found and fixed faster, or workarounds are shared rapidly. 

More about financial modeling

To continue learning and advancing your career, check out these additional helpful WSO resources: