find data in excel pictures

7. Because your client is unlikely to sell all 1 million shares today, at the time of dividend/repurchase, you decide to consider two longer holding periods: Assume that under both plans the client sells all remaining shares of stock 5 years later, or the client sells 10 years later. Assume that the stock will return 10% per year going forward. Also assume that Cisco will pay no other dividends over the next 10 years. a. What would the stock price be after 5 years or 10 years if a dividend is paid now? b. What would the stock price be after 5 years or 10 years if Cisco repurchases shares now? c. Calculate the total after-tax cash flows at both points in time (when the dividend payment or the share repurchase takes place, and when the rest of the shares are sold) for your client if the remaining shares are sold in 5 years under both initiatives. Compute the difference between the cash flows under both initiatives at each point in time. Repeat assuming the shares are sold in 10 years. 8. Repeat Question 7 assuming the stock will return 20% per year going forward. What do you notice about the difference in the cash flows under the two initiatives when the return is 20% and 10%? 9. Calculate the NPV of the difference in the cash flows under both holding period assumptions for a range of discount rates. Based on your answer to Question 8 what the corect discount rate to use? Calibri a Paste BIU EEEEEE Clipboard Font Alignment B86 1 Current Price 2 Shares Outstanding 48.24 Close 11/12/2019 4,280,783,582.09 FY 2019 Quarter Ended October 27, 2018 6 (In Millions) $ 8 ASSETS 9 Current assets: 10 Cash and cash equivalents 11 Investments 12 Accounts receivable, net 13 Inventories 14 Financing receivables, net 15 Other current assets 16 Total current assets 17 Property and equipment, net 18 Financing receivables, net 19 Goodwill 20 Purchased intangible assets, net 21 Deferred tax assets 22 Other assets 23 24 Total Assets 11,750 21,663 5,491 1,383 5,095 2.373 47,755 2,789 4,958 33,529 2,201 4,065 2,496 97,793 26 LIABILITIES AND EQUITY 27 Current liabilities 28 Short-term debt 29 Accounts payable 30 Income taxes payable 31 Accrued compensation 32 Deferred revenue 33 Other current liabilities 10.191 2,059 1,149 3,221 10,668 4,424 Sheet1 Ready Clipboard Font Alignment B86 34 Total current liabilities 31,712 36 Long-term debt 37 Income taxes payable 38 Deferred revenue 39 Other long-term liabilities Total liabilities 14,475 8,927 7.799 1,309 64,222 40 Total equity 33,571 Total Liabilities and Equity $ 97,793 46 47 Cash 48 1/2 of Cash 49 Number of Stocks repurchased 50 Dividend Per Share 51 $ $ 11,750,000,000 5,875,000,000 121,786,899 (rounded down) $ 52 53 Stock Price (2/28/2003) 54 10.90 55 56 Repurchase 57 Cash Received (Before Taxes) 58 Cash Received (After Taxes) $ 1,372,412 *Assumed that stoc 1,097,929.83 59 60 Dividend 61 Cash Received (Before Taxes) 62 Cash Received (After Taxes) $ $ 1,372,412.29 1,097,929.83 64 65 Repurchase 66 Value of Stock 67 Cash Received Sheet1 Bready 48.24 1,097,929.83 $ @ Total equity 33,571 97,793 Total Liabilities and Equity $ 45 46 47 Cash $ 48 1/2 of Cash $ 49 Number of Stocks repurchased 50 Dividend Per Share 51 52 53 Stock Price (2/28/2003) 11,750,000,000 5,875,000,000 121,786,899 (rounded down) 1.37 10.90 $ $ 1,372,412 *Assumed that stoc 1,097,929.83 56 Repurchase 57 Cash Received (Before Taxes) 58 Cash Received (After Taxes) 59 60 Dividend 61 Cash Received (Before Taxes) 62 Cash Received (After Taxes) $ $ 1,372,412.29 1,097,929.83 63 $ 48.24 1,097,929.83 971550 46,867,572.00 $ 64 65 Repurchase 66 Value of Stock 67 Cash Received 68 Stocks Remaining 69 Payout if Sold 70 71 Dividend 72 Value of Stock 73 Cash Received 74 Stocks Remaining 75 Payout if Sold Sheet1 Ready 46.87 1,097,929.83 1,000,000.00 46,867,587.71 7. Because your client is unlikely to sell all 1 million shares today, at the time of dividend/repurchase, you decide to consider two longer holding periods: Assume that under both plans the client sells all remaining shares of stock 5 years later, or the client sells 10 years later. Assume that the stock will return 10% per year going forward. Also assume that Cisco will pay no other dividends over the next 10 years. a. What would the stock price be after 5 years or 10 years if a dividend is paid now? b. What would the stock price be after 5 years or 10 years if Cisco repurchases shares now? c. Calculate the total after-tax cash flows at both points in time (when the dividend payment or the share repurchase takes place, and when the rest of the shares are sold) for your client if the remaining shares are sold in 5 years under both initiatives. Compute the difference between the cash flows under both initiatives at each point in time. Repeat assuming the shares are sold in 10 years. 8. Repeat Question 7 assuming the stock will return 20% per year going forward. What do you notice about the difference in the cash flows under the two initiatives when the return is 20% and 10%? 9. Calculate the NPV of the difference in the cash flows under both holding period assumptions for a range of discount rates. Based on your answer to Question 8 what the corect discount rate to use? Calibri a Paste BIU EEEEEE Clipboard Font Alignment B86 1 Current Price 2 Shares Outstanding 48.24 Close 11/12/2019 4,280,783,582.09 FY 2019 Quarter Ended October 27, 2018 6 (In Millions) $ 8 ASSETS 9 Current assets: 10 Cash and cash equivalents 11 Investments 12 Accounts receivable, net 13 Inventories 14 Financing receivables, net 15 Other current assets 16 Total current assets 17 Property and equipment, net 18 Financing receivables, net 19 Goodwill 20 Purchased intangible assets, net 21 Deferred tax assets 22 Other assets 23 24 Total Assets 11,750 21,663 5,491 1,383 5,095 2.373 47,755 2,789 4,958 33,529 2,201 4,065 2,496 97,793 26 LIABILITIES AND EQUITY 27 Current liabilities 28 Short-term debt 29 Accounts payable 30 Income taxes payable 31 Accrued compensation 32 Deferred revenue 33 Other current liabilities 10.191 2,059 1,149 3,221 10,668 4,424 Sheet1 Ready Clipboard Font Alignment B86 34 Total current liabilities 31,712 36 Long-term debt 37 Income taxes payable 38 Deferred revenue 39 Other long-term liabilities Total liabilities 14,475 8,927 7.799 1,309 64,222 40 Total equity 33,571 Total Liabilities and Equity $ 97,793 46 47 Cash 48 1/2 of Cash 49 Number of Stocks repurchased 50 Dividend Per Share 51 $ $ 11,750,000,000 5,875,000,000 121,786,899 (rounded down) $ 52 53 Stock Price (2/28/2003) 54 10.90 55 56 Repurchase 57 Cash Received (Before Taxes) 58 Cash Received (After Taxes) $ 1,372,412 *Assumed that stoc 1,097,929.83 59 60 Dividend 61 Cash Received (Before Taxes) 62 Cash Received (After Taxes) $ $ 1,372,412.29 1,097,929.83 64 65 Repurchase 66 Value of Stock 67 Cash Received Sheet1 Bready 48.24 1,097,929.83 $ @ Total equity 33,571 97,793 Total Liabilities and Equity $ 45 46 47 Cash $ 48 1/2 of Cash $ 49 Number of Stocks repurchased 50 Dividend Per Share 51 52 53 Stock Price (2/28/2003) 11,750,000,000 5,875,000,000 121,786,899 (rounded down) 1.37 10.90 $ $ 1,372,412 *Assumed that stoc 1,097,929.83 56 Repurchase 57 Cash Received (Before Taxes) 58 Cash Received (After Taxes) 59 60 Dividend 61 Cash Received (Before Taxes) 62 Cash Received (After Taxes) $ $ 1,372,412.29 1,097,929.83 63 $ 48.24 1,097,929.83 971550 46,867,572.00 $ 64 65 Repurchase 66 Value of Stock 67 Cash Received 68 Stocks Remaining 69 Payout if Sold 70 71 Dividend 72 Value of Stock 73 Cash Received 74 Stocks Remaining 75 Payout if Sold Sheet1 Ready 46.87 1,097,929.83 1,000,000.00 46,867,587.71