Models are Only as Good as Their Formulas

While I was brushing my teeth last night I thought about the discounted cash flow model that I used to value the high-tech HVAC company discussed in the last post.  I had the feeling something was wrong.  I asked myself, was each year of the present value of free cash flow the same?  If they were the same, then I had a problem.

Models are only as good as their inputs and their formulas.  I went back and checked my spreadsheet and found my formula for PV of FCF (Present Value of Free Cash Flow) was incorrect for years two through five.  I was going too fast when I was putting my analysis together.  After pulling the worksheet from another model, I made changes and some how changed the formula.  Each year was only discounted once.  This changes the valuation so it falls in the $3.9 million to$7.2 million range instead of the $5.2 million to$9.6 million.  This is critical and embarrassing to have to admit to the three readers of this blog.

However, when something like this happens, it is best to find the error, make the correction and disclose it.

This mistake is critical because the cornerstone of valuing financial assets is the time value of money.

The correct table for my discounted cash flow using the company’s disclosed free cash flow at zero future growth and a 25% discount rate:

The correct table for my discounted cash flow using my net income estimates as a proxy for free cash flow at zero growth and a 25% discount rate:

I feel much more comfortable with this range of valuations than I did with the first.  So luckily, my instincts were somewhat correct.  More importantly, it shows how the investment process of estimating intrinsic value and buying at a discount to that estimate has validity.  There are so many things that can go wrong in an investment so you need to build a margin of safety.

It is less likely that this business is worth more than $6.2 million based on these calculations. This problem is an example of why I prefer to use a modified dividend discount model like I have in previous case studies. Keeping things simple helps to eliminate these type of mistakes. There are a lot of moving parts in a discounted cash flow model. Val Hughes commented on the original HVAC case study post. He basically used a form of the Gordon growth model. I really like his approach. He assumed a dividend or owners earnings of$1,500,000 with zero growth and a 30% discount rate.  This is a simple and clean model where you can actually do the calculations in your head if you wanted to.  The model and calculation would look like this:

$Intrinsic\:Value = \frac{D}{(r-g)}$

$Intrinsic\:Value = \frac{1,500,000}{0.30-0.0} = 5,000,000$

where
D = Dividend in next year
r = required return or discount rate
g = growth in dividend

While I will continue to use a discounted cash flow model as one of my valuation tools, I certainly will make sure I reach into the tool box for those robust basic models that have stood the test of time because they have survived the errors that occur with additional complexity.

This entry was posted in Analysis, Case Study. Bookmark the permalink.

2 Responses to Models are Only as Good as Their Formulas

1. George Pica says:

Thanks JW. I’d rather catch an error before publishing but it’s better to catch it afterwards than not all. Not catching it myself would be more embarrassing. Over all, I’d rather be embarrassed and know the error of my ways and correct it.

2. JW says:

Great lesson. Very important to think critically. Especially of your own calculations. Many people don’t. They are the ones that should be embarrassed.