About this lesson
A simple DCF, from free cash flow to value per share in Google Sheets
In Google Sheets, discount each year's free cash flow with =B3/(1+$B$5)^B2, value everything after the forecast with a growing perpetuity, =F3*(1+B6)/(B5-B6), and discount that by the final year's factor too. Enterprise value is the sum of the two, and equity value is enterprise value less net debt.
Using Excel? Do this lesson in Excel.
=1/(1+$B$5)^B2What you'll practise
Value the farm software business Mallee Agritech wants to buy: discount five years of free cash flow, add a terminal value, then go from enterprise value to equity value per share.
4 steps · 40 points · the cells outlined in gold below
- DCF
- discount factors
- terminal value
- enterprise value
- equity value
The data
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ($m) | FY26 | FY27 | FY28 | FY29 | FY30 |
| 2 | Year | 1 | 2 | 3 | 4 | 5 |
| 3 | Unlevered free cash flow | 21.4 | 24.0 | 26.9 | 29.3 | 31.2 |
| 4 | ||||||
| 5 | WACC | 9.5% | ||||
| 6 | Long-term growth | 2.5% | ||||
| 7 | Net debt ($m) | 85.0 | ||||
| 8 | Shares on issue (m) | 120 | ||||
| 9 | ||||||
| 10 | Discount factor | |||||
| 11 | PV of free cash flow | |||||
| 12 | ||||||
| 13 | Terminal value at FY30 | |||||
| 14 | PV of terminal value | |||||
| 15 | Enterprise value | |||||
| 16 | Equity value | |||||
| 17 | Value per share ($) |