Share with your network!

Financial Modeling for Valuation

Having rolled out a complicated macro-enabled excel-based financial model spread across multiple sheets, what do we achieve?

Have we achieved the goal?

But what was the goal to begin with which ultimately led us to roll out the model? Or was the goal even clear and transparent at the start of the exercise? This raises the very basic question as what was the need for valuation.

Valuation is an attempt to figure out the intrinsic value of the equity. A balance sheet doesn’t serve this purpose because all the line items in it are stated at its cost/purchase/acquisition price. If we somehow manage to restate each and every line item in the balance sheet at its market value, the entry against the item “Total Shareholders’ Equity or Total Net worth is the value we are looking to quantify through the financial modeling exercise.

So summarily and precisely through a valuation exercise we are actually trying to restate the balance sheet at its market value and arriving at the market value of equity through the indirect route.

We now use following abbreviations

MV :Market Value

FCFF :Free Cash Flow to the Firm

PV :Present Value

DCF :Discounted Cash Flow

CA :Current Assets

CY :Current Year

I :Investments

LY :Last Year

CL :Current Liabilities

BS :Balance Sheet

So, the above equation changes to,

MV (Equity)=MV (Cash)+ MV (CA)+ MV (Net Block)+ MV (I)- MV (CL)- MV (Debt)

Let’s keep this equation aside. Let’s look at what DCF does mathematically. DCF calculates the present value of all the future FCFF

FCFF=EBIT x (1-tax rate)+ Depreciation-Capex-Working Capital Changes

Working Capital Changes=(CA-CL)CY– (CA-CL)LY

So,PV [FCFF]=PV[EBIT x (1-tax rate)+ Depreciation-Capex]+ PV [CA]- PV [CL]


PV [EBIT x (1-tax rate)+ Depreciation-Capex]=Present Value of all the future cash flows from exiting and future operating assets=MV (Net Block)

So, PV [CA]=MV (CA)  and PV [CL]= MV (CL) and PV [FCFF]=MV (Net Block)  + MV (CA)- MV (CL)

We substitute this equation into Equation 1, we get

MV (Equity)=PV [FCFF]+MV (Cash)+ MV (I)- MV (Debt)

Now,MV (Cash)=Cash on BS and PV [FCFF]=Enterprise Value

So,MV (Equity)= Enterprise Value – MV (Debt)+Cash+MV (I)

Right hand side of the equation is exactly what we calculate under DCF method and thus we indirectly end up calculating the market value of the equity.

If we by any alternate means restate all the assets and the liabilities at its market value then the need for entire financial modeling will be eliminated by the virtue of above illustration.

Alternatively, we can contest that for a listed company we can directly observe the market value of equity through market capitalization and hence there is absolutely no need of financial modeling leading to valuation in case of listed companies. This argument will be acceptable provided following assumptions are true:

  • Markets are perfect and efficient
  • Valuation are actually driven by fundamentals and not by perceptions
  • Markets are not biased
  • Markets never make mistakes in assessing value
  • Valuation is a precise value
  • The perfection and efficiency of markets are always questionable. Valuations are often driven by perceptions. Do you think people across the globe do fundamental analysis and DCF valuation before they trade? One can contest that this piece of work is done by analysts and traders rely on their reports.

    So, what explains different recommendations by different analysts on the same stock for the same time horizon? And when there is an element of perception, markets will tend to be biased and make mistakes in assessing value. If assessment had been correct, as high as two-third of M& A deals would not have failed to achieve the synergies projected in the boardroom presentations.

    Since valuation is also done by an individual or a team of individuals, it has to be biased irrespective of the quality of analysis and information analyzed. The only question is, biasness in which direction (positive or negative) and to what extent (high or low). And if all these things hold true, valuation can never be a precise value. It will always be a range (low bias to high bias, fair value ± error/noise). We, therefore, still need to carry out financial modeling leading to valuation even if stocks are listed and being traded in the market.

    A financial modeling for valuation is thus an attempt towards the quantification of “true”, “precise” and “fair” value by reducing biases, infusing reality over perceptions, eliminating errors and making use of fundamentals rather than perceptions. But whether it successfully achieves its goal or fails to do so is a debate that will continue till eternity.