I have created a Discounted Cash Flow Excel Model for Valuation of Common Stock. A Stock’s value is nothing but the story behind the stock and the numbers that keep the story grounded in reality. Hence, I have named the model ‘Numbers and Narratives’. The name is also inspired by one of my all-time favorite YouTube videos by Prof. Aswath Damodaran.
In fact, the model itself is a spin-off of a larger, more complex Valuation model created by the Professor himself. Do visit his website to access more Valuation tools, videos and learning material. They’re invaluable!
Before jumping into the model, let me quickly quote Prof. Aswath Damodaran on a timeless Valuation wisdom from his book ‘The Little Book of Valuation: How to Value a Company, Pick a Stock and Profit’:
“The value of a firm is a function of three variables—its capacity to generate cash flows, its expected growth in these cash flows, and the uncertainty associated with these cash flows.”
Keeping that in mind, here is the Google Sheets link to the updated model (You can download it from File->Download As->Microsoft Excel):
The example Valuation given in the sheet is of KRBL (Note: The example Valuation done on KRBL is not accurate to-the-penny. It is for representation purposes only). The Excel will also show circular referencing errors when you open it for the first time. Please ignore it. If you have iterative calculations turned off, turn it on from the ‘Formulas’ section of your Excel.
Using the Model
The model is pretty much self-explanatory and easy to use (This is the major aim of this model). If you’d still like a hands-on explanation on how to use the model, take some time out and read my blog post. It’s a step-by-step guide on the input methodology and other intricacies of the model:
It’s a fairly simple DCF model, which allows you to anchor your transaction Price to a company’s Intrinsic Value rather than dubious anchors like the P/E, P/B or EV/EBITDA. Quick-fix Valuations like the Multiples Valuation appeal to your emotional mind. However, by allowing you to enter your narratives and reasoning regarding the company and its numbers, the model appeals to your logical mind instead. A logical mind is one of the most important edges of the rational investor.
Disclaimer: This model cannot be used to value BFSI firms. In fact, BFSI firms are like the twilight zone for Valuations in general. Theoretically, it is possible to Value a BFSI firm using a DCF, if you carefully audit every P&L item and Balance Sheet item and arrive at the proper Free Cash Flows. But that will take way too much of time for it to make sense as a regular Valuation tool. Some say the Dividend Discount Model (Courtsey @Yogesh_s ji) could be used as an easier fix for Valuing a BFSI firm, but that’s also not very accurate. Unfortunately, that’s as close as we can get to logically Valuing a BFSI firm. This is owing to their all-cash and extremely leveraged nature of business.
On a lighter note:
If you have any doubts, concerns, improvements regarding the model, feel free to post in this thread and let me know. You can also DM me or write to firstname.lastname@example.org instead. This thread can also be used for general discussion on Valuation, specifically the DCF model.