. III Ch A111 Condional Formatting % Format at Table Font Alignment Number Cells Cell Styles Editing SY fx The Equity Analyst has selected to apply a 10% discount rate in today's market conditions. B C D E F G H 92 Click on the box immediately below to see the expression SUMPRODUCTCSM58,0549M545 93 $2,016 94 This calculation is included to briefly introduce the SUMPRODUCT function to students who did not learn about it in the challenge content 95 SUMPRODUCT permits you to multiply the values in a range to those in a similarly shaped other range and to sum the resulting products, 96 It is doing the same calculation as the second presentation, except the second presentation calculated the product each year and then we 97 SUMPRODUCT does not require the interim results to be stored in cells, as we did in the second presentation 98 SUMPRODUCT is actually an extremely powerful function that permits you to create complex logic with multiple conditions. 99 100 Below is Required Homework 101 102 In Valuation (Workbook 11), we will introduce the concept of valuing a stock as the Net Present Value (NPV) of future cash flows (Discunted 103 An Equity Analyst (a Financial Analyst that focuses on valuing stocks) needs to value the stock of a company. 104 The company has recently gone through a period of financial stress and does not plan to pay a dividend this or next year. 105 The company has stated its intent of reintroducing a dividend of $2.00 in 2023 and increasing the dividend by 5% every year thereafter 106 in the yellow cells below write formulas to calculate the expected dividend in each year. 107 108 2021 2022 2023 2024 2025 Terminal $ 109 Dividend/Shares $ 110 111 The Equity Analyst has selected to apply a 10% discount rate in today's market conditions. 112 in Cell G109 write a formula that calculates the terminal value using the discount rate selected 113 114 in the yellow cell below write a formula that calculates the Net Present Value (NPV) of the dividend stream modeled in Row 109 115 116 117 Below is Challenge Homework 118 119 Internal Rate of Return (IRR) 120 Since there is significant judgement/discretion in what the appropriate discount rate is in calculating the present value an alternative approact 121 122 A company makes an investment of $500 in the current year that generates a return in the next 10 future years. 2027 2028
