1) In the Data tab, enter your name in cell B2, and your 8 digit student number in cell B3. Your answer is based on your student number. If more than one CWID is entered into a submitted file, the students associated with all of the CWIDs entered into that file will receive a 0 for the project. 2) Enter the inflow and outflow data from cells F9 - F13 into column C. Inflows (sales of stock) and outflows (purchases of stock) should be calculated by multiplying the number of shares sold or purchased times the stock price on the day of the sale or purchase. Outflows should be negative numbers and inflows should be positive numbers. For example, if 2,000 shares are purchased on 11/1 and the stock price on 11/1 is $60, the outflow is -$120,000. 3) Calculate the estimated NPV using the example below. B23 Hypothetical 7% Annual Investment Actual Annual Investment $100,000 investment (Initial investment) 7% annual interest rate $100,000 investment (total outflows) $105,000 (total inflows) $7,000 annual return at 7% $5,000 net annual return (total inflows and outflows) Rough estimate of NPV = $5,000 $7,000 = -$2,000 In other words, your actual earnings were $2,000 less than the hypothetical investment. 4) Calculate the net present value (Excel financial function NPV and XNPV) of the inflows and outflows. Use the provided annual interest rate. 5) Calculate the number of days where price is above $60. Find and execute 2 additional formulas to analyze the data provided. Be sure you can explain your formula choice. 365 Estimated NPV XNPV (i = 7%) NPV (i = 7%) # of transactions # Days Where Price is Above $60 Additional 1 Additional 2 Assume that Assume that Assume that 900 shares are purchased on 90 shares are sold on 250 shares are purchased on 860 shares are sold on 200 shares are sold on 11/1/20 1/15/21 4/1/21 (Outflows) Inflows Assume that Assume that 8/15/21 10/31/21 Date Price 11/01/20 11/02/20 11/03/20 60.00 60.15 60.05 60.30 59.00 59.10 59.00 59.30 59.05 59.35 11/04/20 11/05/20 11/06/20 11/07/20 11/08/20 11/09/20 11/10/20 11/11/20 11/12/20 11/13/20 11/14/20 11/15/20 11/16/20 11/17/20 11/18/20 11/19/20 59.20 59.30 58.90 59.35 59.25 59.35 59.20 59.50 59.20 1) In the Data tab, enter your name in cell B2, and your 8 digit student number in cell B3. Your answer is based on your student number. If more than one CWID is entered into a submitted file, the students associated with all of the CWIDs entered into that file will receive a 0 for the project. 2) Enter the inflow and outflow data from cells F9 - F13 into column C. Inflows (sales of stock) and outflows (purchases of stock) should be calculated by multiplying the number of shares sold or purchased times the stock price on the day of the sale or purchase. Outflows should be negative numbers and inflows should be positive numbers. For example, if 2,000 shares are purchased on 11/1 and the stock price on 11/1 is $60, the outflow is -$120,000. 3) Calculate the estimated NPV using the example below. B23 Hypothetical 7% Annual Investment Actual Annual Investment $100,000 investment (Initial investment) 7% annual interest rate $100,000 investment (total outflows) $105,000 (total inflows) $7,000 annual return at 7% $5,000 net annual return (total inflows and outflows) Rough estimate of NPV = $5,000 $7,000 = -$2,000 In other words, your actual earnings were $2,000 less than the hypothetical investment. 4) Calculate the net present value (Excel financial function NPV and XNPV) of the inflows and outflows. Use the provided annual interest rate. 5) Calculate the number of days where price is above $60. Find and execute 2 additional formulas to analyze the data provided. Be sure you can explain your formula choice. 365 Estimated NPV XNPV (i = 7%) NPV (i = 7%) # of transactions # Days Where Price is Above $60 Additional 1 Additional 2 Assume that Assume that Assume that 900 shares are purchased on 90 shares are sold on 250 shares are purchased on 860 shares are sold on 200 shares are sold on 11/1/20 1/15/21 4/1/21 (Outflows) Inflows Assume that Assume that 8/15/21 10/31/21 Date Price 11/01/20 11/02/20 11/03/20 60.00 60.15 60.05 60.30 59.00 59.10 59.00 59.30 59.05 59.35 11/04/20 11/05/20 11/06/20 11/07/20 11/08/20 11/09/20 11/10/20 11/11/20 11/12/20 11/13/20 11/14/20 11/15/20 11/16/20 11/17/20 11/18/20 11/19/20 59.20 59.30 58.90 59.35 59.25 59.35 59.20 59.50 59.20