You have been hired as a consultant by a local firm to analyze a new three year project. The company must ramp up production on a three year project. The firms will be able to sell 35,000, 45,000 and 30,000 units each of the next three years respectively and then cease production. The company will be able to sell the units at $35 each. The fixed costs for the project will be $270,000 per year. The equipment necessary for production will cost $1,200,000 and be depreciated to zero on a straight line basis. At the end of the project the equipment may be sold for a salvage value of $145,000 before tax. Net working capital of $150,000 will be required to begin the project and variable costs are 30 percent of sales. The tax rate is 21% and the discount rate is 12%. Calculate the NPV and the IRR for this project. What is your recommendation, should the company invest in the project or not? (Please include excel functions, thank you so much!

35,000 45.000 30,000 s s s 15 270,000 1,200,000 32 33% 11.11% 22.22% 14 Units sold per year 1s Price per pair 16 Fixed costs 17 Equipment 18 Depreciation 19 Salvage value 20 NWC 21 VC per unit 22 Tax rate 23 Required return 24 $ $ 145.000 150,000 2196 26 27 3. Sales 22 Variable costs 23 Fixed Costs 34 Depreciation 35 Earnings Before Taxes (EBT) 36 Tax 37 Net income 28 29 OCE 40 4. Capital Equipment Spending 42 After-tax Salvage Cash Flow 43 NWC 44 Total cash flow 45 47 Salvage Sales Price of Equipment 48 Book Value of Equipment 49 Taxes so After-tax Salvage Value Cash Flow Si 52 53 NPV 54 IRR Ss Invest: Yes or No 56 35,000 45.000 30,000 s s s 15 270,000 1,200,000 32 33% 11.11% 22.22% 14 Units sold per year 1s Price per pair 16 Fixed costs 17 Equipment 18 Depreciation 19 Salvage value 20 NWC 21 VC per unit 22 Tax rate 23 Required return 24 $ $ 145.000 150,000 2196 26 27 3. Sales 22 Variable costs 23 Fixed Costs 34 Depreciation 35 Earnings Before Taxes (EBT) 36 Tax 37 Net income 28 29 OCE 40 4. Capital Equipment Spending 42 After-tax Salvage Cash Flow 43 NWC 44 Total cash flow 45 47 Salvage Sales Price of Equipment 48 Book Value of Equipment 49 Taxes so After-tax Salvage Value Cash Flow Si 52 53 NPV 54 IRR Ss Invest: Yes or No 56