How to Perform a Regression Analysis


Stemming from the trading multiples (or comparable companies) analysis, the regression analysis is part of the Market approach family of valuation methodologies.


For valuation purposes, the selected market multiple (e.g. P/BV) is regressed against an appropriate profitability metric (e.g. ROE) of the comparable companies. After assessing that a statistically significant correlation exists between the two variables, you can calculate the implied valuation multiple resulting from the regression analysis to estimate the Enterprise or Equity value of a subject company.


Let's look at how to perform an effective and meaningful regression analysis.

How to perform a regression analysis


Through a regression analysis, you can identify the linear or quadratic equation that defines the relationship between two variables:

  • Variable 1 will be a market multiple of the comparable companies (plotted on the x-axis in our Excel template)

  • Variable 2 will be a profitability metric of the comparable companies (plotted on the y-axis in our Excel template), which should be closely related to the denominator of Variable 1.

The strength of the relationship between the variables depends on their correlation, quantified by the R-squared characterizing the regression.


Once you have completed your trading multiples analysis, create a marked scatterplot in Excel to plot the comparable companies on a coordinate plane (also called Value Map) as per the 2 selected variables. These variables may include the following:

  • P/BV vs. ROE, commonly used for valuing financial services firms

  • P/TBV vs. ROE, commonly used for valuing financial services firms

  • P/E vs. Profit margin

  • EV/Revenue vs. Revenue growth, used for valuing startups or companies with negative EBITDA

Use the following Excel formulae to determine the linear equation and R-squared characterizing the regression:

  • INTERCEPT(y; x) to calculate the alpha

  • SLOPE(y; x) to calculate the beta

  • RSQ(y; x) to calculate the R-squared.

Once you have determined that a statistically significant correlation exists between the 2 variables (R-squared > 0.5), you may use the regression analysis to value a subject company as follows:


1. Solve for x to determine the implied valuation multiple:


x = (y - alpha) / beta


where y is Variable 2 (the profitability metric) specific to the subject company.


2. Apply the implied multiple to the denominator of Variable 1 (the market multiple) specific to the subject company, i.e.:

  • Book value, when regressing P/BV vs. ROE

  • Tangible book value, when regressing P/TBV vs. ROE

  • Net profit, when regressing P/E vs. Profit margin

  • Revenue, when regressing EV/Revenue vs. Revenue growth.

Lastly, for reporting purposes, add the following to your Excel graph (to be presented in your slides):

  • the trendline, by going to 'Chart Layout > Trendline > Linear Trendline'

  • the linear equation, by going to 'Chart Layout > Trendline > Trendline Options > Display equation on chart'

  • the R-squared, by going to 'Chart Layout > Trendline > Trendline options > Display R-squared value on chart'

NB: Remember to also plot the subject company on the Value Map, setting it apart from the comparable companies by using a different fill color (as shown in our Excel template).

We hope you found this article useful. Make sure to download our Excel model template:

B4C_Template_Regression
.xlsx
Download XLSX • 54KB

For questions or clarifications, feel free to reach out. Thanks for reading, and good luck!

167 views0 comments

Recent Posts

See All