Please answer questions in excel with work shown and formulas

Task 1 - Cash Flow Projections and Project Evaluation 3 4 5 6 7 You have been hired by Target Corp. to assess the viability of a new brand of store specializing in video game sales. The expected sales and costs for this project are listed below. The initial investment is depreciated using a 3-year MACRS schedule. There is a requirement for net working capital to get the project started and an expected salvage value at the end of its life. Calculate the projected cash flows. Then determine if Target Corp. will accept or reject this project. Year 1 80,000 Year 2 110,000 Year 3 140,000 Year 4 190,000 Unit sales Price (per unit) Variable cost (per unit) Fixed costs (per year) $ $ $ 50.00 22.00 900,000 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Initial investment Salvage value Initial NWC $ $ $ 8,500,000 750,000 35,000 Tax rate Required return 21% 11% Year 5 Year 6 Year 7 Year 8 MACRS Schedule 3-year 5-year 7-year Year 1 33.33% 20.00% 14.29% Year 2 44.45% 32.00% 24.49% Year 3 14.81% 19.20% 17.49% Year 4 7.41% 11.52% 12.49% 11.52% 8.93% 5.76% 8.92% 8.93% 4.46% Question 1 - Complete the pro forma income statement for the life of the project and calculate the Operating Cash Flow for each year. (15 points) Year 3 $ 25 25 - 26 27 28 29 30 31 31 32 33 24 34 25 35 26 36 37 37 38 29 39 40 41 42 Year Revenues Variable costs Fixed costs Depreciation EBIT Year 1 4,000,000 $ 88,000,000 900,000 Year 2 5,500,000 $ 121,000,000 900,000 7,000,000 $ 154,000,000 Year 4 9,500,000 209,000,000 900,000 900,000 Taxes (21%) Net income OCF Net income OCF Question 2 - Complete the depreciation schedule below and determine the after-tax salvage value of the investment. (10 points) Year o Year 1 Year 2 Year 3 Year 4 Depreciation expense Book value of fixed assets Market value of salvage Book value of salvage Taxes on sale: After-tax salvage value: Question 3 - Determine the project's yearly cash flows. Then calculate NPV and IRR. Will you advise Target Corp. to accept this project? Use an IF statement to show your decision. (20 points) Year 0 Year 1 Year 2 Year 3 Year 4 OCF Change in NWC Capital spending Total cash flow NPV/IRR Calculations Net present value: Internal rate of return MAN Accept or Reject? Task 1 - Cash Flow Projections and Project Evaluation 3 4 5 6 7 You have been hired by Target Corp. to assess the viability of a new brand of store specializing in video game sales. The expected sales and costs for this project are listed below. The initial investment is depreciated using a 3-year MACRS schedule. There is a requirement for net working capital to get the project started and an expected salvage value at the end of its life. Calculate the projected cash flows. Then determine if Target Corp. will accept or reject this project. Year 1 80,000 Year 2 110,000 Year 3 140,000 Year 4 190,000 Unit sales Price (per unit) Variable cost (per unit) Fixed costs (per year) $ $ $ 50.00 22.00 900,000 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Initial investment Salvage value Initial NWC $ $ $ 8,500,000 750,000 35,000 Tax rate Required return 21% 11% Year 5 Year 6 Year 7 Year 8 MACRS Schedule 3-year 5-year 7-year Year 1 33.33% 20.00% 14.29% Year 2 44.45% 32.00% 24.49% Year 3 14.81% 19.20% 17.49% Year 4 7.41% 11.52% 12.49% 11.52% 8.93% 5.76% 8.92% 8.93% 4.46% Question 1 - Complete the pro forma income statement for the life of the project and calculate the Operating Cash Flow for each year. (15 points) Year 3 $ 25 25 - 26 27 28 29 30 31 31 32 33 24 34 25 35 26 36 37 37 38 29 39 40 41 42 Year Revenues Variable costs Fixed costs Depreciation EBIT Year 1 4,000,000 $ 88,000,000 900,000 Year 2 5,500,000 $ 121,000,000 900,000 7,000,000 $ 154,000,000 Year 4 9,500,000 209,000,000 900,000 900,000 Taxes (21%) Net income OCF Net income OCF Question 2 - Complete the depreciation schedule below and determine the after-tax salvage value of the investment. (10 points) Year o Year 1 Year 2 Year 3 Year 4 Depreciation expense Book value of fixed assets Market value of salvage Book value of salvage Taxes on sale: After-tax salvage value: Question 3 - Determine the project's yearly cash flows. Then calculate NPV and IRR. Will you advise Target Corp. to accept this project? Use an IF statement to show your decision. (20 points) Year 0 Year 1 Year 2 Year 3 Year 4 OCF Change in NWC Capital spending Total cash flow NPV/IRR Calculations Net present value: Internal rate of return MAN Accept or Reject