PLEASE ANSWER QUESTIONS IN EXCEL WITH WORK + FORMULAS SHOWN THANKS!
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 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
