• 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 4 years. You will need an investment of $5 million in year 0, $2.5 million for equipment, and $2.5 million to produce inventory for year 1. Depreciation is straight line over the 4 years, and the new equipment purchased is sold at the end of the project for a salvage value of $250,000. Fixed costs are $500,000 per year and the variable costs are 25% of sales revenues. Total inventory produced will be 85,000 units. Sales for year 1 are 15,000, year 2 are estimated to be 20,000 units at $200 each and Year 3 and 4 at 25,000 units at $125 each. The estimated tax rate is 35%. There is a 30% chance that the Economy worsens by Year 2. If so, sales are estimated to decrease 25% less than the previous year each year with no change in price after year 2 and inventory to be liquidated will be at a loss of 25%.
• 

A B C D E F G H I J L M N Good Economy Years 1 Bad Economy Years 2 0 2 3 4 0 1 3 4 Price of liquidated inventory Liquidated 1 2 3 4 Investment outlays at Time=0 5 5 Initial Investment Cost of inventory manufacturing for 6 year 1 sales 7 8 Operating Cash Flows 9 Unit Sales 10 Sales price 11 Sales revenues 12 13 Variable Costs 14 Fixed operating costs 15 Depreciation 16 Total operating costs 17 18 EBIT 19 Taxes on EBIT 20 Gross Income 21 Add back depreciation 22 23 Net Income 24 25 Salvage Value 26 Taxes on salvage value of equipment 27 Cash flow from sale of equipment 28 29 Project free cash flows 30 31 Depreciation 32 Cost 33 34 35 Rate 36 Depreciation per year 37 38 39 Cash Flow Scenario 40 Good Economy 41 Bad Economy 42 Weighted average cash flow 44 45 46 WACC 47 NPV 48 Blended IRR 49 MIRR 50 Payback Year 1 2 3 Predicted Cash Flow 2 Odds 0 1 3 4 Project Evaluation Good Economy IRR Bad Economy IRR A B C D E F G H I J L M N Good Economy Years 1 Bad Economy Years 2 0 2 3 4 0 1 3 4 Price of liquidated inventory Liquidated 1 2 3 4 Investment outlays at Time=0 5 5 Initial Investment Cost of inventory manufacturing for 6 year 1 sales 7 8 Operating Cash Flows 9 Unit Sales 10 Sales price 11 Sales revenues 12 13 Variable Costs 14 Fixed operating costs 15 Depreciation 16 Total operating costs 17 18 EBIT 19 Taxes on EBIT 20 Gross Income 21 Add back depreciation 22 23 Net Income 24 25 Salvage Value 26 Taxes on salvage value of equipment 27 Cash flow from sale of equipment 28 29 Project free cash flows 30 31 Depreciation 32 Cost 33 34 35 Rate 36 Depreciation per year 37 38 39 Cash Flow Scenario 40 Good Economy 41 Bad Economy 42 Weighted average cash flow 44 45 46 WACC 47 NPV 48 Blended IRR 49 MIRR 50 Payback Year 1 2 3 Predicted Cash Flow 2 Odds 0 1 3 4 Project Evaluation Good Economy IRR Bad Economy IRR