Sign up  |  Log in

How did you learn how to do three statement modeling?

Yeah that’s what I meant, cash from ops net out the capex. I generally adjust for the DA as well depending on whether Capex = DA, is < DA or > DA. Most businesses run = over long time periods so if the Capex or DA is temporarily low you would want to adjust for that. A lot of companies furlow their Capex coming out of a downturn so the actual cash generation can be less than you think during the early phases of a recovery.

I learned 3 statement modeling in college when we analyzed publicly traded companies for the student fund but sadly haven’t gotten to do it in my current role (but that may be changing soon).  That being said, I went through a method similiar to CvM.  And I always used the CFO formula instead of the NOPAT formula because of its simplicity with higher precision.   I remember the models taking me around 40 hours or so, but that was my first few runs and I wasn’t nearly as versed in Excel as I am now.

bromion wrote:

Just my view, but I always use FCF - Capex. NOPAT is also a viable method but I prefer quick back of the envelope calculations. For every model that takes you a day to build, you could have likely found a high probability mispricing somewhere else by using back of the envelope math. Remember kiddos, there are hundreds of mispriced stocks every day.

Average FCF and capex over a business cycle is generally a better proxy than any one year unless the business is very stable (which in and of itself deserves a higher multiple). I agree with Viceroy that looking at historic E and FCF averages is important, but you have to be careful taking a linear average.

All that said, I use EV/Sales and private market value more than any other metrics since those seem to be the most predictive in my experience. I’m not opposed to investing in companies with zero or even negative FCF or E depending on the circumstances. EV/S is even more predictive for shorts.

Two things.  I dumped ~30 hours into that model for a job interivew.  At said job interview the homie was like, “So you feel this stock is undervalued at the moment, would you invest your own money in it?”  I said, “Yes, I would, my estimates are all conservative and prudent.”  Deep down I wouldn’t have touched said company with a 10 foot pole based on my adversion to my ability to pick stocks.  However I ate crow and said stock went up exponentially after I submitted that model. 


Hi CVM,

Just checked your model.Its a masterpiece for new kids like us.Can you please tell me if we really use Residual income model and Fama french model in ER practically..or they are just bookish concepts…If you were to incorporate RI and FFM into your model what changes you would have made to your current model??

Moreover,i came to know that you were also planning to incorporate scenario analysis and sensitivity analysis into your model.Can you please give me some advice on how to incorporate scenario analysis/sensitivity analysis into a ER model…IF possible please mail me a model where scenario/sensitivy analysis are already done.

Once again..Thanks CVM for sharing your model with me……i really thank you a lot from the bottom of my heart.

I have message you the same in your private message but wanted to share it here on this thread also….

Quality threads are a rare on AF and this is one of such quality threads.

"Unless you are a Warren Buffet,society respects $ Millions +CFA>$Millions/CFA"-My friend.

Ok CvM,

Now 1 question on your EV/EBITDA valuation :

- is there a reason why you did not include a comparable EV/EBITDA valuation ? It would have been consistent with your P/E valuation (which has historical as well as comparable).

And 1 question regarding a difference that I noticed between your FCF and EV/EBITDA valuation :

- in your FCF valuation, you substract net debt to arrive at equity value

- in your EV/EBITDA valuation, you substract net debt as minority interest and pension liability to arrive at equity value 

May I ask why this difference in methods ?

- Fran: You know, in Tibet, if they want something, do you know what they do? They give something away.
- Bernard: They do, do they? That must be why they're such a dominant global power.

oops double post sorry

- Fran: You know, in Tibet, if they want something, do you know what they do? They give something away.
- Bernard: They do, do they? That must be why they're such a dominant global power.

Viceroy wrote:

Ok CvM,

Now 1 question on your EV/EBITDA valuation :

- is there a reason why you did not include a comparable EV/EBITDA valuation ? It would have been consistent with your P/E valuation (which has historical as well as comparable).

And 1 question regarding a difference that I noticed between your FCF and EV/EBITDA valuation :

- in your FCF valuation, you substract net debt to arrive at equity value

- in your EV/EBITDA valuation, you substract net debt as minority interest and pension liability to arrive at equity value 

May I ask why this difference in methods ?

I was running low on time and didn’t get to the EV/EBITDA.  It would have been a nice touch. 

What you found is an oversight on my part.  As I said, I was running low on time and needed to review/format.  Looks like I missed that point regarding equity value. 

I’m going to start calling you the Excel K9 with how well you sniff out the dirt!

rahul roy wrote:

Hi CVM,

Just checked your model.Its a masterpiece for new kids like us.Can you please tell me if we really use Residual income model and Fama french model in ER practically..or they are just bookish concepts…If you were to incorporate RI and FFM into your model what changes you would have made to your current model??

I dunno

Moreover,i came to know that you were also planning to incorporate scenario analysis and sensitivity analysis into your model.Can you please give me some advice on how to incorporate scenario analysis/sensitivity analysis into a ER model…IF possible please mail me a model where scenario/sensitivy analysis are already done.

I dont’ have one I’d like to share with sensitivity/scenario analysis.  Sorry.

Once again..Thanks CVM for sharing your model with me……i really thank you a lot from the bottom of my heart.

I have message you the same in your private message but wanted to share it here on this thread also….

Quality threads are a rare on AF and this is one of such quality threads.

Thanks for the kudos.

CFAvsMBA wrote:

rahul roy wrote:

Hi CVM,

Just checked your model.Its a masterpiece for new kids like us.Can you please tell me if we really use Residual income model and Fama french model in ER practically..or they are just bookish concepts…If you were to incorporate RI and FFM into your model what changes you would have made to your current model??

I dunno

Moreover,i came to know that you were also planning to incorporate scenario analysis and sensitivity analysis into your model.Can you please give me some advice on how to incorporate scenario analysis/sensitivity analysis into a ER model…IF possible please mail me a model where scenario/sensitivy analysis are already done.

I dont’ have one I’d like to share with sensitivity/scenario analysis.  Sorry.

Once again..Thanks CVM for sharing your model with me……i really thank you a lot from the bottom of my heart.

I have message you the same in your private message but wanted to share it here on this thread also….

Quality threads are a rare on AF and this is one of such quality threads.

Thanks for the kudos.

+1.

"Unless you are a Warren Buffet,society respects $ Millions +CFA>$Millions/CFA"-My friend.

Hi CFAvsMBA - I’d highly appreciate if you could pls fwd your three statement model to rudykip7@gmail.com. Many thanks!

Wow, lots of interesting stuff has happened since I’ve learned how to do 3-statement modeling.

We’re gonna win so much, you may even get tired of winning. And you’ll say, 'Please, please. It’s too much winning. We can’t take it anymore. Mr. President, it’s too much.' And I’ll say, 'No, it isn’t!' We have to keep winning!

hpracing007 wrote:

Wow, lots of interesting stuff has happened since I’ve learned how to do 3-statement modeling.

You’ve come so far :-)

Things were never really the same after the Indian war of 2014.

“Visit the Water Cooler forum on Analyst Forum. It is the best forum.”
- Everyone

We lost a lot of good men out there.

#FreeCVM #FreeTurd #2007-2017