The earnings model is the bread and butter for financial analysts. The most complex ones runs into dozens of tabs with financial data going back decades. But I have found that a simple spreadsheet kinda works better. I have used one some time back and I thought it would be worthwhile to put it here and use it for the both the old ideas and new ideas that we would discuss. Here’s how it looks like:
This is the one created for Pepsico, investment idea #6. It tries to capture the most relevant data pertaining to this name and serves as a reference when we need to look back at this in the future.
Numbers in blue mean that they are derived from other numbers on the spreadsheet. Most importantly, we should date when this was updated so that we can compare them over time.
The first column provides the key financial information while the second looks at cost, ratios, other salient datapoints and importantly valuations. This last bit on valuation should be used with the usual triangulation table where we compare EV, FCF and PER (updated below). The last column is about growth, comps or peers, shareholders and management.
Some no.s are simply educated guesswork when I don’t have time to dig them out / calculate them. It is also worth noting that I have some formulas embedded and notes written on the right of the cheatsheet which is not shown here but are vital. For example, the FCF YoY growth is derived from hard coded numbers imputed in CAGR formulas.
Therefore, you should always create these for yourself and make the formulas and notes useful for yourself.
Since our discussion on Pepsico, nothing big has changed, IV remains close to USD200 and what is most useful to me was how Pepsico has grown its staff strength and how the revenue per staff is not that high at USD274k per head.
On many counts, the numbers simply shows this is a formidable business: high returns on asset and equity, low working capital, high FCF generation (as we already know). The business does not need capital, hence equity is a fraction of asset and market cap.
It is also worth noting that a Singaporean (Tan Wern Yuen) is high up in the executive ranks of Pepsico’s senior management team!
Hope to use this more often as we discuss new ideas!
Huat Ah!
Main site: http://8percentpa.blogspot.com