Sign up  |  Log in

How did you learn how to do three statement modeling?

Model has been sent out!  Drop feedback in here so we all can discuss.

CFAvsMBA wrote:

3.  Link forecast to BS, IS, CF.  Increase AR, Invent, AP by % increase in sales on BS, stick with common size on the others.  PPE will follow the CAPEX forecast on the rev tab. 

Stick with an common size COGS %, SGA %, and so forth on the IS going foreard.  Improvements in margins is an aggresive forecast in my opinion.  Don’t bother trying to forecast other income other expenses or extrodinary items.  This gets to be GIGO in a hurry.

4.  Find the WACC or a highwatermark cost of capital.  If all else fails, look for the marginal cost of capital (ie if they had to issue more debt, what would the interest rate be?)

Inventory and AP generally follow COGS, not sales. If margins change, then this won’t be as accurate. Not to mention the DSO and DPO can also change depending on company policy and strategy going forward.

CapEx will generally follow depreciation, if you’re going the dirty way of estimating CapEx using a % of rev, instead of a depreciation table. Forecast net PP&E as a percentage of revenues (this will be an assumption that capital turnover is constant), then forecast depreciation, typically as a percentage of net PP&E, and finally calculate capital expenditures by summing the increase in net PP&E plus depreciation. Other than that, you should value CapEx and depreciation seperately, although the difference between both methods is usually insignificant for the effort needed.

SGA usually follows the gross profit margin, or more generally, revenue. COGS is not always a constant common size, it’s better to break down revenue and cogs on a line item basis, like for example a mixture of price and volume for every good or service.

The WACC also has the cost of equity as part of the equation, so finding out the after-tax Kd is not sufficient. Usually, the Ke is the more difficult variable to estimate.

Corrcet me if I'm worng

id love to take a look.

hugegamma@gmail.com

Which industry and company will be easiest for someone learning to model? I’m assuming a manufacturing or retail business will be way easier to learn with, as compared to a conglomerate or a financial services company. Is there an even easier industry to model?

Additionally, a firm with a simple debt structure will be way simpler for a beginner. Any suggestions for THE SIMPLEST public company for a greenhorn to get his hands dirty?

Any suggestions will be verrryyyy appreciated!

My man Warren B said invest in what you know.  Pick a company of a product you admire and it will it exponentially easier to churn through the pages of the 10k.

CFAvsMBA wrote:

My man Warren B said invest in what you know.  Pick a company of a product you admire and it will it exponentially easier to churn through the pages of the 10k.

This.

The first company I ever attempted to model wasn’t one I found very interesting and after a few hours I gave up and moved onto something I was more interested in.

My second attempt (and all companies I have analysed thereafter) was successfully finished because it was something I was interested in, understood the company and industry, and was something I really wanted to invest in after doing some analysis.

CFAvsMBA (via Warren B) gives really sound advice here.

Put. That coffee. Down!

I went through Wall Streep Prep a few years ago and am using their model as a template, and I still can’t get my own balance sheets to balance after building two 3 statement models now. Anyone interested in taking a gander at one of my models? I’ve got no one to ask at work for advice on this. email me at my throw away account if interested: xmotox at gmail. thanks.

BUMP.

CvM, I had a look at your model. Still interested in discussing it here ?

- 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.

Let’s hear it. Hold nothing back.

Nah man, it’s not at all about criticizing ( I like your model and thanks again for sending it), it’s really about having a discussion.

Ok, I’ll do my questions one by one ( no need to make a boring-ass list) :

- why not define FCF as CFO - Capex ? What advantage do you see in the NOPAT method ? 

- 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.

Hmmm, good question.  First, it’s the way I learned so I stick with it.  Secondly, FCF is just that, free cash that can be used for reinvestments in the business, dividends, or skimmed into my bank account.  Those forecasted free cash flows can then be discounted to a present value which is hard dollars.  Everything else has been stripped out of the profit but the raw dollars. 

I once read a seekingalpha report that went something along the lines of “I feel forecasting net income is a better method for finding value in this stock…”  Hacksaw. 

Net income is only a number.  Cash is king.

I am not discussinf the use of FCF, I am simply debating the formula to get to FCF.

Wouldn’t [CFO - Capex] be a better approach ?

I mean the NOPAT method does not take into account change in some none cash items…

The CFO - Capex, on the other hand, takes into account everything…

- 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.

^ This is news to me.  Can you explain some more?

Well I mean that the NOPAT method (which I admit is widely used, btw.) starts at EBIT, and does not eliminate non-cash items from the Income Statement (which have an EBIT effect).

On the other hand, the non-cash items are always eliminated on the CF Statement to arrive at CFO.

Therefore, my statement is that [CFO - Capex] gives a better definition of FCF.

- 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.

haven’t seen the model, but I just went thru this in the L2 curriculum 

if you’re trying to get FCF from the NOPAT you need to add changes in deferred taxes, addback D&A (net noncash charges) and subtract capex along with increases in net working capital 

I would love to see the model as well. CFAvsMBA

ricyan14@hotmail.com

CFAvsMBA wrote:
Let’s hear it. Hold nothing back.

ive got a question

how did you pick 10 yr treas. for your rfr?  I’ve always matched rfr years with the years that the model is forecasting for.  Is that not correct?

I just went on Yahoo Finance and typed in the 10 year treasury rate.  I don’t know if that is correct or prudent, but it’s what I did since that is what I was taught. 

CFAvsMBA wrote:

I just went on Yahoo Finance and typed in the 10 year treasury rate.  I don’t know if that is correct or prudent, but it’s what I did since that is what I was taught. 

sorry, i was unclear.  How did you pick a 10 year maturity?  I was taught for a valuation based ona  5 year forecast, that you would use a 5 year rfr

How long does it take to make one of these from scratch?

This one took about 30 hours.  I’m probably slower than most though.

Thanks for sending, CFAvsMBA.

Respect.

Respect CFAvsMBA

I’d love to see that model as well

willlhz3@gmail.com (3 L’s in a row…))

Thanks for sending. CFAvsMBA.

CFAvsMBA wrote:

^ This is news to me.  Can you explain some more?

Ok, enough on the FCF definition.

Here is my next remark / question, this time concerning your P/E valuation:

- on your historical P/E valuation : you applied the last 3 years average historical P/E on current year forecasted earnings 

- on your comparable P/E valuation : you applied an average of comparable P/Es to next year budgeted earnings

–> Obviously both these P/E valuations are relevant ; now, what would you think about adding an additional P/E valuation, this time using a Schiller-ish component to it ? IMO one of the safest P/E method would be :

 a) to divide the current P by the average of past EPS for each of the 3 comparables in your model, and make an average of these P/Es

b) to apply a) to your target’s current EPS. 

What do you think ?


- 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.

I like how you think.   However, dividing current P by an average of prior Es is not a good metric in my opinion.  EPS is just a number.  It means little by itself unless accompanied by a stock price, dividend payout percentage, or ROE.  I don’t think it would be wise to use an average of Es for the P/E ratio. 

I’m not familiar with the Schillerish component you speak of. 

As a whole, I’m a big believer in reversion to the mean.  This is why I’m using both a time series and industry comparable to assess the current multiple by the target company. 

CFAvsMBA wrote:

I like how you think.   However, dividing current P by an average of prior Es is not a good metric in my opinion.  EPS is just a number.  It means little by itself unless accompanied by a stock price, dividend payout percentage, or ROE.  I don’t think it would be wise to use an average of Es for the P/E ratio. 

I’m not familiar with the Schillerish component you speak of. 

As a whole, I’m a big believer in reversion to the mean.  This is why I’m using both a time series and industry comparable to assess the current multiple by the target company. 

Well, what I called the Shiller-ish component is just a little twist towards a Shiller P/E (beg your pardon, without “c”).

Why do you say that it wouldn’t be wise to you the past average EPS ?

If you think of EPS as the “product you are buying by buying the share” then you should prefer to know what you are buying in terms of “what has been the average product in the past” instead of “what was the product last time” or “what should be the product next time”. Earnings tend to fluctuate and why would you value a company based on just one data point when you can easily value it based on many ? 

- 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.

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.

CFAvsMBA wrote:

I tend to follow this process.

1.  Input financial statements for BS, IS, CF

2.  Forecast Revenue based on industry, competitors, management guidance, analyst estimates (separate tab).  Forecast CAPEX on this same tab.  I like to forecast CAPEX as a % of revenue.

3.  Link forecast to BS, IS, CF.  Increase AR, Invent, AP by % increase in sales on BS, stick with common size on the others.  PPE will follow the CAPEX forecast on the rev tab. 

Stick with an common size COGS %, SGA %, and so forth on the IS going foreard.  Improvements in margins is an aggresive forecast in my opinion.  Don’t bother trying to forecast other income other expenses or extrodinary items.  This gets to be GIGO in a hurry.

4.  Find the WACC or a highwatermark cost of capital.  If all else fails, look for the marginal cost of capital (ie if they had to issue more debt, what would the interest rate be?)

5.  Intrinics/DCF valuation

6.  Relative comparable valuation

7.  Summary weighting with valuations coming to a happy medium.  Scenario and sensitivity analysis (different permutations of growth rates, discount rates, for terminal value and the resulting valuation)

There you have it, the short and sweet.  There is a lot of tinkering along the way to make it all work, but I tend to follow that process.

Best advice ever.

CVM can you please mail me your model at rahulroy321.00@rediffmail.com

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

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.

When you say FCF-Capex do you mean CFO-Capex ?
Because FCF is already net of Capex…

- 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.