Scribd
Upload
175 views6 pages

Reverse DCF

This document contains a two-stage discounted cash flow valuation for two companies, BBBY and MCK. It includes projected cash flows for 10 years with different growth rates for the first 5 and second 5 years. It calculates the present value of the cash flows and terminal value to determine an intrinsic value, and shows iterating the growth rates to match the current stock price.

Uploaded by

Ervin Khouw
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
175 views6 pages

Reverse DCF

This document contains a two-stage discounted cash flow valuation for two companies, BBBY and MCK. It includes projected cash flows for 10 years with different growth rates for the first 5 and second 5 years. It calculates the present value of the cash flows and terminal value to determine an intrinsic value, and shows iterating the growth rates to match the current stock price.

Uploaded by

Ervin Khouw
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd

Copyright 2017.

The Warren Buffett Spreadsheet

Recerse DCF Calculation

Yellow= manual input

2 Stage DCF valuation

Stock ticker: BBBY


Date: ###

5y average free cash flow: 700

Current stock price 20

Years: 1-5 6-10


Growth Rate: -15% -12%

Terminal Growth Rate: 2% Discount Rate: 11%

Shares Outstanding: 144

Year Flows Growth Value


1 595 -15% $536
2 506 -15% $410
3 430 -15% $314
4 365 -15% $241
5 311 -15% $184
6 273 -12% $146
7 241 -12% $116
8 212 -12% $92
9 186 -12% $73
10 164 -12% $58

Terminal Year 167


PV of Year 1-10 Cash Flows: 2170
Terminal Value: 654
Total PV of Cash Flows: 2824
Number of Shares: 144
Intrinsic Value (IV): 20

Reverse DCF Growth rate -15.0 %

Change the growth rate in cell B42 until intrinsic value in cell B40 is equal or close to equal to cu
close to equal to current stock price in B14. That is the growth rates the market expect considering todays
xpect considering todays stock price
Copyright 2017. The Warren Buffett Spreadsheet

Recerse DCF Calculation

Yellow= manual input

2 Stage DCF valuation

Stock ticker: MCK


Date: ###

5y average free cash flow: 2900

Current stock price 140

Years: 1-5 6-10


Growth Rate: 1% 1%

Terminal Growth Rate: 2% Discount Rate: 11%

Shares Outstanding: 215

Year Flows Growth Value


1 2,923 1% $2,634
2 2,947 1% $2,392
3 2,970 1% $2,172
4 2,994 1% $1,972
5 3,018 1% $1,791
6 3,037 1% $1,624
7 3,057 1% $1,472
8 3,076 1% $1,335
9 3,096 1% $1,210
10 3,116 1% $1,097

Terminal Year 3178


PV of Year 1-10 Cash Flows: 17698
Terminal Value: 12436
Total PV of Cash Flows: 30134
Number of Shares: 215
Intrinsic Value (IV): 140

Reverse DCF Growth rate 0.8 %

Change the growth rate in cell B42 until intrinsic value in cell B40 is equal or close to equal to cu
close to equal to current stock price in B14. That is the growth rates the market expect considering todays
xpect considering todays stock price

You might also like