Using the required rate of return the project given To evaluate, set up the cash flow estimation in the excel spreadsheet posted Your company has raised $5,000,000 to invest in an expansion project All 4 projects will have to begin at the same time, so evaluate the costs of the projects to check for mutually exclusiveness of the projects Depending on the costs and returns, it is possible to invest in multiple projects Each project will have its pros and cons, and each project to choose from will have percentages of costs and returns for Expected and Bad Economies You will need the cash flows for each economy and do a weighted average Set up excel using formulas This project is over the course of 6 years You will need an investment of $3 million in Year 0, $1 million for equipment, and $2 million for inventory for year 1 production Depreciation is accelerated based on IRS tables for computers (found on this image MACRS Table), and salvage value will be $100,000 Total inventory produced will be 90,000 units, and sales are expected to be 15,000 units per year Prices are expected to be sold to the retailers at $40 There is a 35 chance the Economy worsens after year 2, then sales are estimated to be 35 less than the previous year each year, prices will fall by 15 less than the previous year each year, and the remaining inventory at year 5 will become obsolete and destroyed The fixed cost of creating is $200,000 per year and the variable cost is 35 of revenue The estimated tax rate is 40 20 year Table A 1 3 , 5 ,7 , 10 , 15 , and 20 Year Property Half Year Convention Depreciation rate for recovery period Year 3 year 5 year 7 year 10 year 15 year 1 33 33 20 00 14 29 10 00 5 00 2 44 45 32 00 24 49 18 00 9 50 3 14 81 19 20 17 49 14 40 8 55 4 7 41 11 52 12 49 11 52 7 70 5 11 52 8 93 9 22 6 93 3 750 7 219 6 677 6 177 5 713 5 76 6 7 8 9 10 8 92 8 93 4 46 7 37 6 55 6 55 6 56 6 55 6 23 5 90 5 90 5 91 5 90 5 285 4 888 4 522 4 462 4 461 3 28 11 12 13 5 91 5 90 5 91 5 90 5 91 4 462 4 461 4 462 4 461 4 462 14 15 2 95 16 17 18 19 20 4 461 4 462 4 461 4 462 4 461 21 2 231 Lebane 1 Baby an Sunny 20 year Table A 1 3 , 5 ,7 , 10 , 15 , and 20 Year Property Half Year Convention Depreciation rate for recovery period Year 3 year 5 year 7 year 10 year 15 year 1 33 33 20 00 14 29 10 00 5 00 2 44 45 32 00 24 49 18 00 9 50 3 14 81 19 20 17 49 14 40 8 55 4 7 41 11 52 12 49 11 52 7 70 5 11 52 8 93 9 22 6 93 3 750 7 219 6 677 6 177 5 713 5 76 6 7 8 9 10 8 92 8 93 4 46 7 37 6 55 6 55 6 56 6 55 6 23 5 90 5 90 5 91 5 90 5 285 4 888 4 522 4 462 4 461 3 28 11 12 13 5 91 5 90 5 91 5 90 5 91 4 462 4 461 4 462 4 461 4 462 14 15 2 95 16 17 18 19 20 4 461 4 462 4 461 4 462 4 461 21 2 231 Lebane 1 Baby an Sunny

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 02, 2024

Using the required rate of return the project given. To evaluate, set up the cash flow estimation in the excel spreadsheet posted. Your company has

Using the required rate of return the project given. To evaluate, set up the cash flow estimation in the excel spreadsheet posted. Your company has raised $5,000,000 to invest in an expansion project. All 4 projects will have to begin at the same time, so evaluate the costs of the projects to check for mutually exclusiveness of the projects. Depending on the costs and returns, it is possible to invest in multiple projects. Each project will have its pros and cons, and each project to choose from will have percentages of costs and returns for Expected and Bad Economies. You will need the cash flows for each economy and do a weighted average. Set up excel using formulas.
This project is over the course of 6 years. You will need an investment of $3 million in Year 0, $1 million for equipment, and $2 million for inventory for year 1 production. Depreciation is accelerated based on IRS tables for computers (found on this image: MACRS Table), and salvage value will be $100,000. Total inventory produced will be 90,000 units, and sales are expected to be 15,000 units per year. Prices are expected to be sold to the retailers at $40. There is a 35% chance the Economy worsens after year 2, then sales are estimated to be 35% less than the previous year each year, prices will fall by 15% less than the previous year each year, and the remaining inventory at year 5 will become obsolete and destroyed. The fixed cost of creating is $200,000 per year and the variable cost is 35% of revenue. The estimated tax rate is 40%.

20-year Table A-1. 3-, 5-,7-, 10-, 15-, and 20-Year Property Half-Year Convention Depreciation rate for recovery period Year 3-year 5-year 7-year 10-year 15-year 1 33.33% 20.00% 14.29% 10.00% 5.00% 2 44.45 32.00 24.49 18.00 9.50 3 14.81 19.20 17.49 14.40 8.55 4 7.41 11.52 12.49 11.52 7.70 5 11.52 8.93 9.22 6.93 3.750% 7.219 6.677 6.177 5.713 5.76 6 7 8 9 10 8.92 8.93 4.46 7.37 6.55 6.55 6.56 6.55 6.23 5.90 5.90 5.91 5.90 5.285 4.888 4.522 4.462 4.461 3.28 11 12 13 5.91 5.90 5.91 5.90 5.91 4.462 4.461 4.462 4.461 4.462 14 15 2.95 16 17 18 19 20 4.461 4.462 4.461 4.462 4.461 21 2.231 Lebane 1 Baby an Sunny 20-year Table A-1. 3-, 5-,7-, 10-, 15-, and 20-Year Property Half-Year Convention Depreciation rate for recovery period Year 3-year 5-year 7-year 10-year 15-year 1 33.33% 20.00% 14.29% 10.00% 5.00% 2 44.45 32.00 24.49 18.00 9.50 3 14.81 19.20 17.49 14.40 8.55 4 7.41 11.52 12.49 11.52 7.70 5 11.52 8.93 9.22 6.93 3.750% 7.219 6.677 6.177 5.713 5.76 6 7 8 9 10 8.92 8.93 4.46 7.37 6.55 6.55 6.56 6.55 6.23 5.90 5.90 5.91 5.90 5.285 4.888 4.522 4.462 4.461 3.28 11 12 13 5.91 5.90 5.91 5.90 5.91 4.462 4.461 4.462 4.461 4.462 14 15 2.95 16 17 18 19 20 4.461 4.462 4.461 4.462 4.461 21 2.231 Lebane 1 Baby an Sunny