Discounted Cash Flow (DCF) Analysis Unlevered

Bristol-Myers Squibb Company (BMY)
Operating Data
Year A/P | 2013 Actual | 2014 Actual | 2015 Actual | 2016 Actual | 2017 Actual | 2018 Actual | 2019 Projected | 2020 Projected | 2021 Projected | 2022 Projected | 2023 Projected |
Revenue | 16,385.00 | 15,878.70 | 16,559.90 | 19,426.42 | 20,774.61 | 22,559.15 | 24,095.43 | 25,736.33 | 27,488.97 | 29,360.97 | 31,360.45 |
---|---|---|---|---|---|---|---|---|---|---|---|
Revenue (%) | |||||||||||
EBITDA | 3,637.47 | 2,823.23 | 2,386.28 | 6,247.54 | 5,951.93 | 6,578.25 | 5,797.36 | 6,192.16 | 6,613.85 | 7,064.25 | 7,545.33 |
EBITDA (%) | |||||||||||
EBIT | 2,873.93 | 2,356.40 | 2,010.37 | 5,864.84 | 5,162.49 | 5,942.08 | 5,055.22 | 5,399.48 | 5,767.19 | 6,159.93 | 6,579.42 |
EBIT (%) | |||||||||||
Depreciation | 763.54 | 466.83 | 375.91 | 382.70 | 789.44 | 636.17 | 742.14 | 792.68 | 846.66 | 904.32 | 965.90 |
Depreciation (%) |
Balance Sheet Data
Year A/P | 2013 Actual | 2014 Actual | 2015 Actual | 2016 Actual | 2017 Actual | 2018 Actual | 2019 Projected | 2020 Projected | 2021 Projected | 2022 Projected | 2023 Projected |
Total Cash | 4,525.54 | 7,434.41 | 4,270.80 | 6,350.50 | 6,812.00 | 8,883.79 | 8,235.82 | 8,796.68 | 9,395.73 | 10,035.58 | 10,719.00 |
---|---|---|---|---|---|---|---|---|---|---|---|
Total Cash (%) | |||||||||||
Account Receivables | 3,360.56 | 3,390.10 | 4,298.95 | 5,542.36 | 6,300.94 | 5,964.64 | 6,149.15 | 6,567.91 | 7,015.19 | 7,492.92 | 8,003.19 |
Account Receivables (%) | |||||||||||
Inventories | 1,497.59 | 1,559.29 | 1,220.46 | 1,241.35 | 1,165.46 | 1,195.64 | 1,751.74 | 1,871.03 | 1,998.45 | 2,134.54 | 2,279.90 |
Inventories (%) | |||||||||||
Accounts Payable | 2,559.34 | 2,486.60 | 1,564.91 | 1,664.84 | 2,247.81 | 1,892.71 | 2,751.70 | 2,939.09 | 3,139.24 | 3,353.02 | 3,581.36 |
Accounts Payable (%) | |||||||||||
Capital Expenditure | -537.43 | -525.59 | -819.72 | -1,214.15 | -1,055.35 | -952.00 | -1,086.70 | -1,160.71 | -1,239.75 | -1,324.18 | -1,414.36 |
Capital Expenditure (%) |
Weighted Average Cost Of Capital
Share price | $ 59.94 |
---|---|
Diluted Shares Outstanding | 1,632.20 |
Cost of Debt | |
Tax Rate | 17.19 |
After-tax Cost of Debt | -0.81 |
Risk-Free Rate | |
Market Risk Premium | |
Cost of Equity | 8.66 |
Total Debt | 5,646.00 |
Total Equity | 97,833.99 |
Total Capital | 103,479.99 |
Debt Weighting | 5.46 |
Equity Weighting | 94.54 |
Wacc |
Build Up Free Cash
Year A/P | 2013 Actual | 2014 Actual | 2015 Actual | 2016 Actual | 2017 Actual | 2018 Actual | 2019 Projected | 2020 Projected | 2021 Projected | 2022 Projected | 2023 Projected |
Revenue | 16,385.00 | 15,878.70 | 16,559.90 | 19,426.42 | 20,774.61 | 22,559.15 | 24,095.43 | 25,736.33 | 27,488.97 | 29,360.97 | 31,360.45 |
---|---|---|---|---|---|---|---|---|---|---|---|
EBITDA | 3,637.47 | 2,823.23 | 2,386.28 | 6,247.54 | 5,951.93 | 6,578.25 | 5,797.36 | 6,192.16 | 6,613.85 | 7,064.25 | 7,545.33 |
EBIT | 2,873.93 | 2,356.40 | 2,010.37 | 5,864.84 | 5,162.49 | 5,942.08 | 5,055.22 | 5,399.48 | 5,767.19 | 6,159.93 | 6,579.42 |
Tax Rate | 10.82% | 14.94% | 22.18% | 24.01% | 80.50% | 17.19% | 28.27% | 28.27% | 28.27% | 28.27% | 28.27% |
EBIAT | 2,562.94 | 2,004.34 | 1,564.51 | 4,456.88 | 1,006.90 | 4,920.89 | 3,626.04 | 3,872.98 | 4,136.73 | 4,418.44 | 4,719.33 |
Depreciation | 763.54 | 466.83 | 375.91 | 382.70 | 789.44 | 636.17 | 742.14 | 792.68 | 846.66 | 904.32 | 965.90 |
Accounts Receivable | - | -29.54 | -908.85 | -1,243.41 | -758.58 | 336.30 | -184.51 | -418.76 | -447.27 | -477.73 | -510.27 |
Inventories | - | -61.70 | 338.82 | -20.88 | 75.89 | -30.18 | -556.10 | -119.29 | -127.42 | -136.09 | -145.36 |
Accounts Payable | - | -72.73 | -921.69 | 99.93 | 582.97 | -355.10 | 858.99 | 187.39 | 200.15 | 213.78 | 228.34 |
Capital Expenditure | - | -11.84 | 294.13 | 394.44 | -158.80 | -103.35 | 134.71 | 74.00 | 79.04 | 84.43 | 90.18 |
UFCF | 3,326.48 | 2,295.36 | 742.83 | 4,069.65 | 1,537.81 | 5,404.73 | 4,621.26 | 4,389.00 | 4,687.89 | 5,007.14 | 5,348.12 |
WACC | |||||||||||
PV UFCF | 4,262.37 | 3,733.76 | 3,678.32 | 3,623.70 | 3,569.88 | ||||||
SUM PV UFCF | 24,519.27 |
Terminal Value
Growth in perpetuity method: | |
---|---|
Long-term growth rate | |
WACC (%) | 8.42 |
Free cash flow (t + 1) | 5,562.05 |
Terminal Value | 125,838.14 |
Present Value of Terminal Value | 83,997.29 |
Intrinsic Value
Enterprise Value | 108,516.56 |
---|---|
Net Debt | -3,237.79 |
Equity Value | 111,754.35 |
Shares Outstanding | 1,632.20 |
Equity Value Per Share | 68.47 |