Tools

Valuation approaches are mainly based on forecats. 

For most listed companies, the French Zonebourse website provides a Reuters' consensus on some core aggregates: 

• Sales
• EBITDA
• EBIT
• Profit Before Tax
• Net income
• CAPEX

The discount rate includes the cost of equity (k) which is used in the DCF approach is based on the Capital Assets Pricing Model (CAPM) : 

k = r_f + beta x [E(R_M)-r_f] where r_f is the risk free rate and E(R_M)-r_f  is the market risk premium.

• The risk free rate corresponds to the 10Y T-Bonds rate and can be easily found on the web for for markets.
• The betas of most listed firms can also be easily fond on the web. They are provided for free on Reuters' website
• The market risk premium is provided every year, country by country, by Aswath Damodaran, a professor of Stern New York University. Its market risk premiums are commonly used by investment bankers. 

There are 2 ways to calculate a stock's valatility

1. Historical volatility which corresponds to the standard deviation of the stock's past returns. This simple approach has 2 main drawbacks: on the one hand there is no unique historical volatilty as the output depends on the observations' period and periodicity; on the other hand, it does not take any forecast into account, assuming that the future replicates the past.
2. Implied volatility for stocks which are financial options' underlying assets. This implied volatility (s) is unique and includes the market's expectations. It is obtained thanks to a the Black & Scholes' option pricing model. Indeed, the call premium, as a listed security is easily read on the web and the 4 other parameters of the Black and Scholes' formula are known: 

• S = spot price of the underlying asset
• E = strike price of the call
• r = risk free rate
• t = time to expiration.

The tables are probably Excel's most useful tool for finance. Indeed, finance is focused on assets' valuation which relies on forecast which are intrinsically uncertain. 

To cope with this uncertainty, the best thing to do is whether to contemplate many contingencies for at least 2 variables which ca, be looked upon as core drivers of the valuation. Investment bankers are used to crossing 2 sets of levels of risk free rate and perpetuity growth rate to get the equity value. 

The variables to be crossed must be that have entered (and not calculated) in the model's central case.  

The table is obtained in "Data - What if analysis - Table". Then a small screen appears. It requests to clik on 

cell in row: first variable in the central case which takes several values in the first line of the table
cell in column: second variable in the central case which takes several values in the first column of the table
Movie

The DCF is a way to value the firm's assets as the sum of the present values of the future Free Cash Flows (FCF).

The discount rate is the Weighted Average Cost of Capital (WACC) = k.E/(E+D)+i.(1-t).D/(E+D) where:

• k = cost of equity based on the CAPM
• i = cost of debt
• t = corporate tax rate
• D = last available net debt
• E = equity value

The equity value has to be an economic one. A proxy could have been the firm's market cap if it was listed or its value based on a listed peers approach. 

But, for the sake of consistency, as an aggregate is supposed to have one single value in a model, the right approach is whether to include the equity value which is the final output of the DCF modelling.

The equity value is required to get the WACC which enables to get it. That means the model has to loop on itself. This is possible as Excel enables the iterative calculations.

To do that, a first calculation of the DCF value has to be done with a first dicount rate. This first discount rate has to be entered in order to build the model and get an equity value. 

Then the first discount rate is replaced by the WACC formula taking the equity value into account.

At the end of the day, to terminate the modelling: "File-Options-Formulas-Activate the iterative calculation"

Excel file

Movie

The volatilty of listed assets can be easily calculated taking the historical or implied volatilty into account as described above.

The valuation of equity based on real options is based on an options pricing model assuming that equity is a call premium on the firm's assets. Then the volatility to be included in the model is that of the firm's assets which are not listed, as least for most of them.

As described in detail in the p.23 of the handout which can be found in the M&A page, Hull, Nelken and White (2004) proposed a calculation of the assets' volatilty which consists in solving a system of 2 equations with 2 unknowns: enterprise value (EV) and volatily (s). The 2 equations are:

1. The Black & Scholes formula
2. A formula to be looked upon as a consequence of the Ito's lemma, assuming a geometric brownian motion for the equity value

Such a non linear system of equations can be solved thanks to Excel's solver. 

The following Excel sheet includes the useful formulas to use Excel's solver based on the following assumptions

• Equity value (based on the firm's market cap): 3 million euros
• Debt: 10 million euros
• Risk free rate: 5%
• Time to expiration: 1 year
• Volatility of equity: 80%

• EV=12,40 million euros
• s=21,23%

Excel model

The followig movie paves the way to use Excel's solver

Movie

Most calculations around financial and real options are based on the Black & Scholes or Merton formulas which assume the log-normal distribution of the underlying asset's price evolution. That's why theses formulas include the use of the standard normal distribution function to calculate P(X*<d₁) and P(X*<d₂).

Practioners use the Excel standard normal distribution function and tape, for P(X*<x): 

• in English: =normsdist(x)
• in French: =loi.normale.standard(x)