Some notes about this spreadsheet.
This is a tool, not my own prediction of the future. The current figures that are in the spreadsheet are purely for example purposes. You should definitely play with your own figures. There is a lot of potential flexibility in the spreadsheet. You can start off with a minimum amount of interaction and then fine tune as you become more comfortable with it.
In essence, all I have done is to take the 2009 FY profit and loss figures as a base and then applied a growth scenario over a ten year period.
In its simplest form it assumes a static annual growth in sales revenue. You can put whatever growth percentage figure that you fancy into the yellow cell. That figure will be automatically used for each year of the ten year time span.
If you wish, you can also change the Gross Margin from it's current value of 61%. However, it seems that that figure is probably not too far from reality.
The remaining income and expenditure figures are represented purely as a percentage of sales. Again, you can change these figures in the yellow cells.
It will soon become obvious that the scenario resulting from that simple approach is unrealistic. For example, it is probably unlikely that the Marketing expenses would remain at a constant percentage of sales as sales increase. In the example figures supplied, you would think that Marketing expenses when sales reach $400m would not be anywhere near $100m. To achieve a more useful result you can change the percentage figures in any of the pale blue cells. Note that these percentage rates cascade - that is, a percentage figure entered in one year will be automatically replicated for all future years (unless you modify those percentage figure also).
Alternatively, if you cannot achieve the result that you want in this way then you can, of course, modify the actual dollar figures directly. The spreadsheet will still work quite happily.
Ultimately, the spreadsheet will calculate a current DCF value for the share based upon the discount factor that you enter.
As always, I claim no copyright on this spreadsheet. Feel free to do with it as you wish - copy, modify, share, whatever. Of course I would be more than pleased if you provide others with a link back to my blog so that I can have more friends in my dusty little corner of the Internet.
This spreadsheet was written in Open Office. I have, however, saved it in .xls format. It should open happily in both Microsoft Excel and Open Office. If you are like me and refuse to pay hundreds of dollars to purchase Microsoft Office, I suggest you download the free Open Office (which is better, anyway). The spreadsheet will also work happily in Google Docs.
Download here: CST Growth Projection.
If you are not familiar with DCF valuations, I strongly recommend that you read the following.
Discounted Cash Flow.
I find that DCF valuations are often grossly misunderstood, misused, over-complexified and sometimes even miscalculated. In fact, the DCF valuation is a very simple concept but as is so often the case, sometimes simple concepts require lengthy explanations. DCF valuations, whilst most often seen used in valuing equity investments actually have much wider usefulness. They can be used in any situation where money movements and time are involved. They can even be used to compare different Home Mortgage plans.
The DCF valuation does not predict the future - that is up to you and your crystal ball. All it does is enable you to turn your estimate/guess/knowledge of the future cash flows of an investment into something that will assist you in making a sensible investment decision. There is no magic.
Firstly, we need to have some understanding of what it is we mean by "Cashflow". All we mean by cashflow in this instance is the flow of cash back to you, the investor. This is not to be confused with the cashflows within the investment vehicle. For the DCF we are looking purely and simply at the hard cash returns to ourselves. Furthermore, for the DCF valuation exercise we must totally dissociate the concept of Company Profits from our cashflow. Once you get deeply involved in doing DCF valuations an interesting exercise is to perform one on an infrastructure investment vehicle that actually pays out more in dividends to you than it makes in profit.
The DCF valuation is in fact a solution to the problem of comparing two financial investments. Note the emphasis on financial. The DCF looks at the investment purely from a financial point of view - it does not take into account non-financial considerations such as personal circumstances or even risk (more on that later).
Take a simple example. If you have some money to invest then you may have decided to invest in an IBD (Interest Bearing Deposit). If one bank is offering 5.5% per annum and another is offering 5.75%, both with interest paid annually we can all very easily decide which of these investments will give the better return. However, if one bank is offering 5.5% with interest paid monthly and the other is offering 5.75% with interest paid annually it is not so easy to work out. A correctly constructed DCF valuation can answer this question. It can even be tuned to take into account other financial impacts such as fees, taxes etc.
A DCF valuation compares two financial instruments
In the most common guise that we see the DCF valuation compares an equity investment (usually with some risk attached) with a no-risk investment (Government Bond or even, in Australia, a Bank IBD).
Money received in the future has less value than money received today
Perhaps you might like to loan me $10,000. It is obvious that if I offer you the choice that I will repay you the $10,000 today or in 12 months time then you will choose to take the money now. Putting aside all external factors except the pure financial considerations there is a good reason for this. If I give you the $10,000 now then you could invest it in an IBD and receive, say, 6% interest. So by not receiving that interest you will have lost $600. You lost the opportunity to invest that $10,000 for one year. The opportunity cost was $600. (perhaps you even remember that from high school economics).
In a DCF valuation we work that principle backwards. In the above example you know that I am willing to repay you $10,000 in 12 months time and you need to work out how much you will loan me today so that you will not have lost out. It is not $9400 (I mention that because in my travels I have seen DCF valuations that are incorrect and would say $9400). The question we need to ask is "how much money would I need to invest in a 6% IBD today to give me a total return of $10,000 in 12 months time" - that is how much money you would lend me to cover your opportunity cost.
If you remember some basic algebra:
if a = the amount that you will lend me.
a * 1.06 = 10,000
a = 10,000/1.06
a = 9,433.96 (to the nearest cent)
In summary, if I am promising to repay you $10,000 in 12 months time, you would be willing to give me $9,433.96 today to give you an investment that is financially equivalent to you investing your money in a bank IBD at 6%.
Okay, enough of this skirting around the edges.
When we look at an equity investment (eg CST) we can try to make some intelligent estimate of what the future brings. Specifically, we can look at the returns (in cash) that the investment will give us. Typically these returns will come as dividends over time and a final return when we sell the equities. If we believe our estimates about the future then we could add up all those returns to give us a total cash return. However, as we saw above, money that we receive in the future is worth less that money we receive today. So, we have to discount the future cash received (cashflow) back to today's value (exactly as we did with the $10,000 in the example above). The DCF valuation part of my spreadsheet does exactly that - it takes the cash return expected from the investment over a number of years and discounts those returns back to today's value.
It goes without saying that the CST DCF valuation is wholly and totally dependant upon the accuracy of the assumptions that have been made in the spreadsheet. However, we need to start somewhere so we make the best possible estimates that we can. Let's just for the moment make the assumptions that the estimates that I have in the sample spreadsheet provided are correct (I need to point out that the sample figures in the Spreadsheet are a long way short of my own belief - I have done this purposefully to avoid misleading anybody into taking future figures that I provide as being "gospel")
I have made a further assumption that I will hold my CST investment for 10 years (receiving dividends during that time) and then sell the investment, receiving a final cash receipt. I have set the expected share price after 10 years as 10 times that years earnings (ie a PE of 10). I'm not defending any of my figures here - you can change any of the figures as you see fit. When I take those figures and discount them back to current value at a "Discount Rate" (I would prefer to call it simply "Interest Rate" but am once again complying to convention) of 8% I get a current value of $4.82.
So, (and most importantly) what does this tell us?. I look at this way.
To achieve identical financial returns I can either buy one share of CST or put $4.82 in an 8% Bank IBD
If I have absolute faith in my projections, then I have a choice of two investments to give an equal cash return - either a share in CST that I can buy for $3.15 or an IBD that will cost me $4.82. Note that we are not predicting the future value of the investment (and certainly not the future share price). We are simply putting a current financial value on the investment.
Risk. I'll finish with just a final note on risk. Even the biggest CST fan (ie me) has to admit that there is more risk in the CST investment than in the bank IBD investment. How do we allow for risk? It is not uncommon to see people try to account for risk by increasing the "Discount Rate" by some arbitrary figure ("Risk Discount"). To me (and Warren Buffet) this is total rubbish. How do you attach a figure to risk? And what does that figure actually represent? I suspect that sometimes people think that increasing the discount rate by 5% is saying that they think there is a 5% risk of the business failing. This is not the case (you can work the maths for yourself if you are a masochist).
For me,. it is much better to do as I have done. That is, run with the true "opportunity cost" discount rate to come up with a Share Value and then purchase with a margin of safety (with thanks to Charlie Munger).
It is widely reported that Warren Buffett uses DCF valuations extensively. However, from his own pronouncements it seems that he actually performs the same exercise intuitively. That's all well and good for him. The rest of us need to do it the hard way! His basic comment on all of this is "it's like buying $1 notes for 50c". In the example that I used above it actually works out to be buying $1 notes for 65c.
And finally, even though it is an unconventional use, you can use the DCF "backwards". That is, you can fiddle with the discount rate until the DCF value becomes the same as the current price you are paying for the investment (usually the share price). The discount rate that you end up with is the effective interest rate that the investment will be paying you. (using the example spreadsheet, it comes in at around 13%)
Have fun!
