We now have the present value of all future free cash flows for our fictional company, WITW calculated in the spreadsheet. Time to total them up to get an overall value for the company. First, add a Total label:
Click on cell D15 and enter the following: Total:
The total we are looking for is the sum of the values in the PV column, G. You could use a formula such as: =F9+F10+F11... etc., but let's take advantage of a simple built-in function of the spreadsheet, the SUM function. The SUM function takes a range of cells (use a : between cell coordinates to specify a range) and adds them together. Here is what to enter:
Click on cell G15 and enter the following: =SUM(G9:G14)
After that edit, your spreadsheet should look like Figure 12.
Figure 12: Present Value total (click for full image)
Hang in there, we are almost to the finish line! We have the present value of the company based on its future free cash flows, so all that is left is to divide that total by the number of shares to get our intrinsic value. Alright, get going:
Click on cell A20 and enter the following: Intrinsic Value:
Adjust the width of column A if it is not wide enough.
Click on cell B20 and enter the following: =G15/B9
Boom! Did you get a value? Hopefully cell B20 has a value of $16.73 and your spreadsheet looks like Figure 13.
Figure 13: Intrinsic value (click for full image)
There you have it. The theoretical present value of WITW based on its free cash flow and future growth estimates is $16.73. Or is it? If WITW has no cash or debt on the books, then it is. But if it does have cash or debt, we should go ahead and take that into account.
Taking a look at WITW's financial statements, we notice that it has $15 million in cash, and $8 million in long-term debt. To make things simple, all we need to do is add the cash we currently have to our free cash flow estimate, and subtract any debt. Easy enough, so get to it:
Click on cell A11 and enter the following: Cash:
Click on cell B11 and enter the following: $15000000
Click on cell A12 and enter the following: LT Debt:
Click on cell B12 and enter the following: $8000000
Finally, we need to adjust the formula for intrinsic value.
Click on cell B20 and enter the following: =(G15+B11–B12)/B9
Ahh, sweet relief. We finally have our number, $17.61. Your spreadsheet should look like Figure 14. So what does this number mean? If the current price of WITW is $11.75, then it may well be that WITW is currently undervalued. There are any number of reasons why WITW may be priced differently than $17.61, least of all the fact that $17.61 depends on estimates on our part, and estimates can easily be wrong. So don't take that number for anything other than what it is: one factor in helping to evaluate a company.
Figure 14: Intrinsic value updated with cash and debt (click for full image)
If you haven't had enough yet, head on to the next part of the tutorial, and add a few useful items to your spreadsheet.
Take Me To Part 4 | Take Me To Part 6