Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Based on the data provided in the excel file FCF_Disney, you will use the three-stage (two-stage or one-stage) FCFF models to compute the intrinsic value

Based on the data provided in the excel file FCF_Disney, you will use the three-stage (two-stage or one-stage) FCFF models to compute the intrinsic value of Walt Disney stock. Make sure to provide an analysis that is consistent with the underlying assumptions of the DDM model computed in Module 3. (The usage of the three-stage model versus the one and two stage models, the number of years in the growth and transition stage, ...)

image text in transcribedimage text in transcribedimage text in transcribedimage text in transcribed

DIS US $ C 102.07 +0.39 On 11 May c Vol 7,035,978 1) Create Report DIS US Equity Walt Disney Co/The Cost of Capital - Current Market Value Weight Cost 9.8% 3) Equity 85.9% 4) Debt Cost (A-T) 14.1% 3.0% 5) Preferred Equity 0.0% 0.0% WACC 6) History WACC EVA ROIC EVA Spread WACC 8.8515 10.50- 10.00 2011 2015 2016 2017 FREE_CASH_FLOW_EQUITY -295 -163 1342 971 -81 1623 |-1051 #N/A N/A #N/A N/A 127 9.50- 9.00 8.8515 8.50 2008 2009 Quarterly DIS US Equity Date #NAME? 6/30/2000 9/29/2000 12/29/2000 3/30/2001 6/29/2001 9/28/2001 12/31/2001 3/28/2002 6/28/2002 2010 WACC #N/A N/A 6.4911 7.0687 7.0466 7.2463 7.6869 9.0302 7.8063 7.6054 8.3428 N102.09/102.10N H 102.21Z 2) Output to Excel 22x6 L 101.26D Val 716.454 Weighted Average Cost of Capital Period MR 2018 Q2 Capital Structure (Millions of USD) Market Cap 150,660.0 85.9% ST Debt 5,918.0 3.4% LT Debt 18,766.0 0.0 175,344.0 100.0% 10.7% 0.0% Pref. Eqty Total Economic Value Added (Millions of USD) 7) Net Operating Profit 14094.00 2078.01 8) Cash Operating Taxes NOPAT 12015.99 9) Total Investment Capital 74579.00 Capital Charge 6601.37 Economic Value Added 5414.62 ROIC 16.11% 7.26% EVA Spread IS_ADJ_SALES_REVENUE_TURNOVER #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A Track Annotate Zoom 2012 2013 2014 CF_FREE_CASH_FLOW_FIRM #N/A N/A 481.3527 1556.9326 #N/A N/A #N/A N/A #N/A N/A 428.1882 #N/A N/A 1116.4737 #N/A N/A 0 101.89K W X C 8.4% 0.4% 0.0% 8.9% 2018 EFF_TAX_RATE 69.6875 51.3285 50.0803 58.6626 #N/A N/A 45.1398 56.4706 38.7808 41.498 39.7799 IS_DEPR_EXP CAPITAL_EXPEND 227 -389 -435 262 #N/A N/A -24 237 -456 235 -440 263 -345 252 -554 250 -237 256 -249 255 -254 WORKING CAPITAL 2121 2379 1605 744 2207 3489 585 2409 2933 2424 9/30/2002 12/31/2002 3/31/2003 6/30/2003 9/30/2003 12/31/2003 3/31/2004 6/30/2004 9/30/2004 12/31/2004 3/31/2005 6/30/2005 9/30/2005 12/30/2005 3/31/2006 6/30/2006 9/29/2006 12/29/2006 3/30/2007 6/29/2007 9/28/2007 12/31/2007 3/31/2008 6/30/2008 9/30/2008 12/31/2008 3/31/2009 6/30/2009 9/30/2009 12/31/2009 3/31/2010 6/30/2010 9/30/2010 12/31/2010 3/31/2011 6/30/2011 9/30/2011 12/30/2011 3/30/2012 6/29/2012 9/28/2012 7.6198 9.4146 8.845 8.8636 8.2943 9.0348 8.1686 8.3071 9.2975 8.591 9.2117 9.5437 9.4628 9.1164 9.4208 8.7379 8.3678 8.4562 9.0093 8.9266 8.1678 8.2818 8.6085 9.3021 9.4107 8.9265 9.0966 9.6914 10.1306 9.4557 9.538 10.6942 10.1029 10.6932 10.5205 10.0026 9.9314 9.8655 9.8432 10.1015 9.7982 #N/A N/A #N/A N/A 1195.8296 487.3889 1084.384 #N/A N/A 2334.0997 584.4286 584.2226 -84.5941 602.88 405.6446 907.2495 491.1399 1437.0165 1327.795 1918.5881 388.4825 2045.3763 723.2057 1012.9468 548.3586 2372.115 689.0733 704.3612 77.7722 1444.8521 1219.376 1199.9374 683.3343 1169.5739 1443.3592 1468.2803 -29.3682 1388.4987 1180.9278 1178.9695 1178.7309 417.3433 2222.2994 677.6149 -934 117 818 -420 192 #N/A N/A #N/A N/A -154 -647 -88 300 -199 642 1128 928 997 2654 #N/A N/A #N/A N/A 2945 2177 #N/A N/A #N/A N/A -267 1871 1175 237 9 293 1826 587 861 1013 360 #N/A N/A #N/A N/A #N/A N/A 1665 1674 1262 5 33.218 #N/A N/A 37.3083 37.2685 35.1129 #N/A N/A 37.0717 35.9606 10.0464 34.3173 36.8 33.8473 3.3264 36.3868 35.161 33.674 33.3853 37.5093 38.6968 37.5925 34.641 37.334 37.5726 34.116 35.328 36.4451 34.7652 37.7791 35.3402 36.1573 34.9837 35.5736 32.636 35.3682 35.5867 33.6922 34.2617 32.1285 34.6482 32.7831 34.2479 260 261 269 282 247 273 279 320 326 324 327 347 330 366 355 357 339 365 357 367 370 371 378 387 391 383 393 390 414 411 394 399 398 412 409 424 414 436 449 455 444 -346 -193 -255 |-264 -337 -208 -260 -426 -533 -347 -426 -414 -636 -203 -259 -305 -531 -245 -301 -485 |-667 -249 |-347 -353 -637 -291 -458 -378 -626 -307 -500 -506 -797 -1213 -632 -716 -998 -634 -1477 |-740 -933 30 403 761 455 -355 521 -2028 -1991 -1690 -886 1586 1472 -323 |-426 196 -186 -648 528 -763 1273 -77 885 1140 1204 75 868 1791 2893 2955 1278 2435 3112 1225 1247 417 877 1669 1536 1813 3637 896 #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A 9599 8087 8596 9867 9739 8580 10002 9742 10716 9077 10675 10425 10779 9629 11088 10782 12/31/2012 3/28/2013 6/28/2013 9/30/2013 12/31/2013 3/31/2014 6/30/2014 9/30/2014 12/31/2014 3/31/2015 6/30/2015 9/30/2015 12/31/2015 3/31/2016 6/30/2016 9/30/2016 12/30/2016 3/31/2017 6/30/2017 9/29/2017 12/29/2017 3/29/2018 Yearly DIS US Equity Date #NAME? 12/31/2001 12/31/2002 12/31/2003 12/31/2004 12/30/2005 664.2347 1645.1398 2784.1581 1804.1974 606.4571 1869.3865 2095.7755 2089.5416 903.0547 2055.2916 1693.2968 2160.7208 1094.0707 3482.4985 2546.0538 2652.1493 485.8183 2422.8419 3419.6296 2711.8422 1376.0207 3599.4002 9.8845 9.7544 10.1131 10.0284 9.4176 9.8822 10.1698 10.067 9.0911 9.4358 9.5613 9.4054 8.9887 8.9516 8.5324 8.3898 8.5498 8.7442 8.7663 8.4271 8.6148 8.9275 CF_FREE_CASH_FIWACC 7.057 4705.736 1325.8044 9.0302 1641.3932 7.5979 3333.8705 8.2955 3370.6325 9.0417 2862.6867 9.4628 3854 29.0927 28.7473 1176 961 34.2386 1044 30.6205 1723 35.2381 2027 35.2441 2561 34.0872 791 33.9699 2575 33.254 411 32.8916 2061 33.3922 4302 45.9988 2775 7278 1798 33.2263 33.9524 35.1661 34.3284 33.2081 2356 985 3499 32.3114 3799 31.6197 4115 30.7721 2091 #N/A N/A 3698 20.6976 FREE_CASH_FLOW_EQUITY EFF_TAX_RATE 3216 60.9951 1330 82.5409 3092 38.9498 #N/A N/A 35.0044 #N/A N/A 32.0139 #N/A N/A 31.1262 -545 -574 -690 -987 -658 -701 -889 -1063 -998 |-907 473 496 489 499 504 523 503 534 535 530 520 -1156 -1204 547 550 553 575 642 636 -1406 -2556 -1135 -1082 -1040 627 -883 663 -805 660 -895 677 -981 687 -1063 IS_DEPR_EXP CAPITAL_EXPEND 962 -2013 987 -1795 1021 -1086 0 1059 1198 -1427 1328 -1823 |-176 1550 2864 2405 67 |-116 1854 1877 436 2236 1686 424 |-1028 -74 -455 124 -2652 -1095 57 -3706 -2601 -2920 WORKING_CAPITAL 1605 585 30 -355 -1690 -323 11341 10554 11578 11568 12309 11649 12466 12389 13391 12461 13101 13512 15244 12969 14277 13142 14784 13336 14238 12779 15351 14548 IS_ADJ_SALES REVENUE_TURNOVER #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A 12/29/2006 12/31/2007 12/31/2008 12/31/2009 12/31/2010 12/30/2011 12/31/2012 12/31/2013 12/31/2014 12/31/2015 12/30/2016 12/29/2017 WACC 5201.4008 4323.459 4314.8835 3941.0604 4764.775 3719.3752 4496.6497 6896.7447 6661.1587 6813.1506 8596.0952 9064.3982 + 8.368 8.1678 9.4107 10.1306 10.1029 9.9314 9.7982 10.0284 10.067 9.4054 8.3898 8.4271 #N/A N/A 6552 4388 1714 4287 5082 4606 7035 7102 9349 11303 12423 34.5041 37.2039 36.1119 36.2142 34.9178 34.6264 33.3369 31.0187 34.6399 36.1696 34.1539 32.0714 1417 1459 1527 1580 1602 1659 1784 1957 2064 2132 2320 2586 |-1319 -1566 -1586 -1753 -2110 -3559 -3784 |-2796 -3311 -4265 |-4773 -3623 -648 -77 75 2955 1225 1669 896 2405 1877 424 124 -3706 #N/A N/A #N/A N/A #N/A N/A 36149 38063 40893 42278 45041 48813 52465 55632 55137 DIS US $ C 102.07 +0.39 On 11 May c Vol 7,035,978 1) Create Report DIS US Equity Walt Disney Co/The Cost of Capital - Current Market Value Weight Cost 9.8% 3) Equity 85.9% 4) Debt Cost (A-T) 14.1% 3.0% 5) Preferred Equity 0.0% 0.0% WACC 6) History WACC EVA ROIC EVA Spread WACC 8.8515 10.50- 10.00 2011 2015 2016 2017 FREE_CASH_FLOW_EQUITY -295 -163 1342 971 -81 1623 |-1051 #N/A N/A #N/A N/A 127 9.50- 9.00 8.8515 8.50 2008 2009 Quarterly DIS US Equity Date #NAME? 6/30/2000 9/29/2000 12/29/2000 3/30/2001 6/29/2001 9/28/2001 12/31/2001 3/28/2002 6/28/2002 2010 WACC #N/A N/A 6.4911 7.0687 7.0466 7.2463 7.6869 9.0302 7.8063 7.6054 8.3428 N102.09/102.10N H 102.21Z 2) Output to Excel 22x6 L 101.26D Val 716.454 Weighted Average Cost of Capital Period MR 2018 Q2 Capital Structure (Millions of USD) Market Cap 150,660.0 85.9% ST Debt 5,918.0 3.4% LT Debt 18,766.0 0.0 175,344.0 100.0% 10.7% 0.0% Pref. Eqty Total Economic Value Added (Millions of USD) 7) Net Operating Profit 14094.00 2078.01 8) Cash Operating Taxes NOPAT 12015.99 9) Total Investment Capital 74579.00 Capital Charge 6601.37 Economic Value Added 5414.62 ROIC 16.11% 7.26% EVA Spread IS_ADJ_SALES_REVENUE_TURNOVER #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A Track Annotate Zoom 2012 2013 2014 CF_FREE_CASH_FLOW_FIRM #N/A N/A 481.3527 1556.9326 #N/A N/A #N/A N/A #N/A N/A 428.1882 #N/A N/A 1116.4737 #N/A N/A 0 101.89K W X C 8.4% 0.4% 0.0% 8.9% 2018 EFF_TAX_RATE 69.6875 51.3285 50.0803 58.6626 #N/A N/A 45.1398 56.4706 38.7808 41.498 39.7799 IS_DEPR_EXP CAPITAL_EXPEND 227 -389 -435 262 #N/A N/A -24 237 -456 235 -440 263 -345 252 -554 250 -237 256 -249 255 -254 WORKING CAPITAL 2121 2379 1605 744 2207 3489 585 2409 2933 2424 9/30/2002 12/31/2002 3/31/2003 6/30/2003 9/30/2003 12/31/2003 3/31/2004 6/30/2004 9/30/2004 12/31/2004 3/31/2005 6/30/2005 9/30/2005 12/30/2005 3/31/2006 6/30/2006 9/29/2006 12/29/2006 3/30/2007 6/29/2007 9/28/2007 12/31/2007 3/31/2008 6/30/2008 9/30/2008 12/31/2008 3/31/2009 6/30/2009 9/30/2009 12/31/2009 3/31/2010 6/30/2010 9/30/2010 12/31/2010 3/31/2011 6/30/2011 9/30/2011 12/30/2011 3/30/2012 6/29/2012 9/28/2012 7.6198 9.4146 8.845 8.8636 8.2943 9.0348 8.1686 8.3071 9.2975 8.591 9.2117 9.5437 9.4628 9.1164 9.4208 8.7379 8.3678 8.4562 9.0093 8.9266 8.1678 8.2818 8.6085 9.3021 9.4107 8.9265 9.0966 9.6914 10.1306 9.4557 9.538 10.6942 10.1029 10.6932 10.5205 10.0026 9.9314 9.8655 9.8432 10.1015 9.7982 #N/A N/A #N/A N/A 1195.8296 487.3889 1084.384 #N/A N/A 2334.0997 584.4286 584.2226 -84.5941 602.88 405.6446 907.2495 491.1399 1437.0165 1327.795 1918.5881 388.4825 2045.3763 723.2057 1012.9468 548.3586 2372.115 689.0733 704.3612 77.7722 1444.8521 1219.376 1199.9374 683.3343 1169.5739 1443.3592 1468.2803 -29.3682 1388.4987 1180.9278 1178.9695 1178.7309 417.3433 2222.2994 677.6149 -934 117 818 -420 192 #N/A N/A #N/A N/A -154 -647 -88 300 -199 642 1128 928 997 2654 #N/A N/A #N/A N/A 2945 2177 #N/A N/A #N/A N/A -267 1871 1175 237 9 293 1826 587 861 1013 360 #N/A N/A #N/A N/A #N/A N/A 1665 1674 1262 5 33.218 #N/A N/A 37.3083 37.2685 35.1129 #N/A N/A 37.0717 35.9606 10.0464 34.3173 36.8 33.8473 3.3264 36.3868 35.161 33.674 33.3853 37.5093 38.6968 37.5925 34.641 37.334 37.5726 34.116 35.328 36.4451 34.7652 37.7791 35.3402 36.1573 34.9837 35.5736 32.636 35.3682 35.5867 33.6922 34.2617 32.1285 34.6482 32.7831 34.2479 260 261 269 282 247 273 279 320 326 324 327 347 330 366 355 357 339 365 357 367 370 371 378 387 391 383 393 390 414 411 394 399 398 412 409 424 414 436 449 455 444 -346 -193 -255 |-264 -337 -208 -260 -426 -533 -347 -426 -414 -636 -203 -259 -305 -531 -245 -301 -485 |-667 -249 |-347 -353 -637 -291 -458 -378 -626 -307 -500 -506 -797 -1213 -632 -716 -998 -634 -1477 |-740 -933 30 403 761 455 -355 521 -2028 -1991 -1690 -886 1586 1472 -323 |-426 196 -186 -648 528 -763 1273 -77 885 1140 1204 75 868 1791 2893 2955 1278 2435 3112 1225 1247 417 877 1669 1536 1813 3637 896 #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A 9599 8087 8596 9867 9739 8580 10002 9742 10716 9077 10675 10425 10779 9629 11088 10782 12/31/2012 3/28/2013 6/28/2013 9/30/2013 12/31/2013 3/31/2014 6/30/2014 9/30/2014 12/31/2014 3/31/2015 6/30/2015 9/30/2015 12/31/2015 3/31/2016 6/30/2016 9/30/2016 12/30/2016 3/31/2017 6/30/2017 9/29/2017 12/29/2017 3/29/2018 Yearly DIS US Equity Date #NAME? 12/31/2001 12/31/2002 12/31/2003 12/31/2004 12/30/2005 664.2347 1645.1398 2784.1581 1804.1974 606.4571 1869.3865 2095.7755 2089.5416 903.0547 2055.2916 1693.2968 2160.7208 1094.0707 3482.4985 2546.0538 2652.1493 485.8183 2422.8419 3419.6296 2711.8422 1376.0207 3599.4002 9.8845 9.7544 10.1131 10.0284 9.4176 9.8822 10.1698 10.067 9.0911 9.4358 9.5613 9.4054 8.9887 8.9516 8.5324 8.3898 8.5498 8.7442 8.7663 8.4271 8.6148 8.9275 CF_FREE_CASH_FIWACC 7.057 4705.736 1325.8044 9.0302 1641.3932 7.5979 3333.8705 8.2955 3370.6325 9.0417 2862.6867 9.4628 3854 29.0927 28.7473 1176 961 34.2386 1044 30.6205 1723 35.2381 2027 35.2441 2561 34.0872 791 33.9699 2575 33.254 411 32.8916 2061 33.3922 4302 45.9988 2775 7278 1798 33.2263 33.9524 35.1661 34.3284 33.2081 2356 985 3499 32.3114 3799 31.6197 4115 30.7721 2091 #N/A N/A 3698 20.6976 FREE_CASH_FLOW_EQUITY EFF_TAX_RATE 3216 60.9951 1330 82.5409 3092 38.9498 #N/A N/A 35.0044 #N/A N/A 32.0139 #N/A N/A 31.1262 -545 -574 -690 -987 -658 -701 -889 -1063 -998 |-907 473 496 489 499 504 523 503 534 535 530 520 -1156 -1204 547 550 553 575 642 636 -1406 -2556 -1135 -1082 -1040 627 -883 663 -805 660 -895 677 -981 687 -1063 IS_DEPR_EXP CAPITAL_EXPEND 962 -2013 987 -1795 1021 -1086 0 1059 1198 -1427 1328 -1823 |-176 1550 2864 2405 67 |-116 1854 1877 436 2236 1686 424 |-1028 -74 -455 124 -2652 -1095 57 -3706 -2601 -2920 WORKING_CAPITAL 1605 585 30 -355 -1690 -323 11341 10554 11578 11568 12309 11649 12466 12389 13391 12461 13101 13512 15244 12969 14277 13142 14784 13336 14238 12779 15351 14548 IS_ADJ_SALES REVENUE_TURNOVER #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A #N/A N/A 12/29/2006 12/31/2007 12/31/2008 12/31/2009 12/31/2010 12/30/2011 12/31/2012 12/31/2013 12/31/2014 12/31/2015 12/30/2016 12/29/2017 WACC 5201.4008 4323.459 4314.8835 3941.0604 4764.775 3719.3752 4496.6497 6896.7447 6661.1587 6813.1506 8596.0952 9064.3982 + 8.368 8.1678 9.4107 10.1306 10.1029 9.9314 9.7982 10.0284 10.067 9.4054 8.3898 8.4271 #N/A N/A 6552 4388 1714 4287 5082 4606 7035 7102 9349 11303 12423 34.5041 37.2039 36.1119 36.2142 34.9178 34.6264 33.3369 31.0187 34.6399 36.1696 34.1539 32.0714 1417 1459 1527 1580 1602 1659 1784 1957 2064 2132 2320 2586 |-1319 -1566 -1586 -1753 -2110 -3559 -3784 |-2796 -3311 -4265 |-4773 -3623 -648 -77 75 2955 1225 1669 896 2405 1877 424 124 -3706 #N/A N/A #N/A N/A #N/A N/A 36149 38063 40893 42278 45041 48813 52465 55632 55137

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

TExES Business And Finance Secrets Study Guide

Authors: TExES Exam Secrets Test Prep Team

1st Edition

1516706862, 978-1516706860

More Books

Students also viewed these Finance questions

Question

Describe how we perceive depth in three-dimensional space.

Answered: 1 week ago