The last one is a sample. please do it on a spreadsheet. Thank you in advance. if you have any questions let me know!

The learning objective of this assignment is to show you how sensitive the value of a company is to the discount rate investors use to discount future cash flows. The attached PDF file is a direct application of the Dividend Discount model we learned in class (Chapter 9) applied to determine the value of Amazon.com. Your job is to replicate their analysis in an Excel spreadsheet, using their estimated cash flows (dividends) and their estimated growth rates and the number of shares they use. The only change you will make is use a 12% discount rate rather than the 9.7% the use throughout the analysis. Please submit the assignment in an Excel spreadsheet by uploading it on Blackboard. .12 10-year free cash flow (FCF) estimate 2020 2021 2022 2023 2024 2025 20:26 2027 2928 2029 Levered -D D2 D3 04 DS DO 07 DS 09 DIO US$3470 USH350 US$52.30 05874.50 055611D USSD US8.30 US$83.20 51960 FCF Millions) Growth Rate US$26.80 These are your dividends in the abnormal growth stage (your "golden eggs? est I Analyst Analyse Analyst Esto Est Analyst *15 Analyst 6 Estimate 5919 AN 3.784 3.17 2.74 Source Present Values Millions Discounted 9.7 PV (D1) PV(D2) PV (D3) PV (DA) These are the present USS316 U553628 US$43.4% U55515 US. US$49USSAZOK US$44.5% USB USSEZ values of each of the 9.7% is the discount rate used for this valuation abnormal dividends Ex = FCF growth rate estimated by simply Walse) Present Value of 10 year Cash Flow (PVC) = US64356 This is the total present value of all of your abnormal dividends We now need to calculate the Terminat Value, which accounts for all the future cash flows after this ten year period. The Gordon Growth formula is used to calculate Terminal value at a future annual growth rate equal to the 10-year goverment bond rate of 1.7h. We discount the terminal cash flows to today's value at a cost of equity of This is the value of the base of constant Terminal Value (TV= FFUS996 (1. 1.741574-1,7%) US$13 growth dividends (normal) in year 10 PER FCF growth rate estimated by simply Wall St Present Value of 10 year Cash Flow (PVC) - US$4356 - This is the total present value of all of your abnormal dividends We now need to calculate the Terminal Value, which accounts for all the future cash flows after this ten year period. The Gordon Growth formula is used to calculate Terminal Value at a future annual growth rate equal to the 10-year government bond rate of 1.7%. We discount the terminal cash flows to today's value at a cost of equity of 97 This is the value of the basket of constant Terminal Value (TV)-FCF30 *(1+9+48 -10 = US$998*(1 + 1.73 +972-1.741=US51.3 growth dividends ("normal eggs") in year 10 Present Value of Terminal Value (PVTV)-TV/01 +rw US$1.3+(1+9.784196 USSO1b The present value of this basket of normal dividends in year 0. The total value, or equity value is then the sum of the present value of the future cash flows, which in this case is US$9976. The last step is to the divide the equity value by the number of shares outstanding Relative to the The value of the firm's equity is the current stare price of US$1.9k, the company appears around fair value at the time of writing. Remember though, sum of the present value of the that this is just an approximate valuation, and like any complex formula - garbage in garbage out. abnormal dividends and the present value of the normal dividends. INTRINSIC VALUE BASED ON FUTURE CASH FLOWS Finally divide the equity value by the number of shares to get the value per share. That is your estimated value of each share F G https://simplywallst/stocks/us/reta Vandagamen/amazoncom? antamaton.com utm_mediumefinance uterutm_campaign-infographicdum source-apple&blueprint 11480298 intrinsic Value) NadaGSAMEN wole March 28 2020 Important assumptions Now the most important inputs to a discounted cash flow are the discount rate, and of course, the actual cash llows. Part of investing is coming up with your own evaluation of a company's future performance, so try the calculation yourself and check your own assumptions. The DCF also does not consider the possible cyclicality of an industry, or a company's future capital requirements, so it does not give al picture of a company's potential performance. Given that we are looking at Amazon.com as potential shareholders, the cost of equity is used as the discount rate, rather than the cost of capital for weighted average cost of capital WACC) which accounts for det In this calculation were used 9.7, which is based on a leveled beta of 1.462 Beta is a measure of a stocks volatility, compared to the market as a whole. We get our bets from the Industry average beta of obaly comparable companies, with an imposed time between 0.3 and 2.0, which is a reasonable range for a stable busine Now your job is the repeat this analysis and calculate the value of Amazon per share but instead of using the 9.7% discount rate they have used, you should use a discount rate of 12% Recording Autosave Valg Amazon Acrobat Tell me Home Insert Formulas Data View Draw Page Layout Calibri {Body! Review 2 12 Porcentage $% # ! Conditional Formatting Format Table Car Styles Insert Delete Home Tour Paste OA 6 H 1 7 89.00 46.97 8 93.20 44.44 3 9 96.20 K 10 98.80 39.15 4 11 100.48 1,256.00 VE Office Update To keep up to date with security updates, foes, and improvements. Choose Check for Updates N2 : * fx 1.7% B C D E F 1 1 2 3 4 5 6 2 Dividends 34.70 43.50 57.30 74.50 81.10 85.80 3 PV(Dividends) 31.63 36.15 43.40 51.44 51.05 49.23 4 5 PV(Abnormal Dividends) 435.28 6 7 PV(Normal dividends 497.65 PV of the basket of normal dividends 8 9 Total equity value (billions) 932.92 10 Number of shares (billions) 0.4978 497.8 million shares as per CNBC 11 Value per share $ 1,874.10 12 $ 1,874.10 at 9.7% 13 $ 1,410,48 at 12% Terminal Value Year 10 15 16 17 18 19 20 21 77 Sheet