Please answer 1B using the information below. Thank you

A B C D E F G H 11 Assignment Instructions: There are 3 parts to this assignment, which combines using Excel to perform calculations and answering eight questions on the "Additional Questions" tab. The use of formulas and cell references (rather than hard typing in numbers) should be used in all cases where they can. To guide you, the cells shaded gray are where formulas, 12 numerical inputs or calcuations are needed in order to complete the assignment. Your grace for this assignment will be determined by a combination of the correctness of your financial matn, good use of Excel functionality and spreadsheet formatting, and the completeness of your answers to the 13 questions In this assignment, you are evaluating a potential capital investment project that has a 4 year life. The financial 14 details are as follows: 15 16 1) Useful life of the equipment (years) 4 17 2) New equipment cost* $ (200,000) 18 3) Equipment ship & install costs* $ (35,000) 19 4) Related equipment start-up costs* $ (5,000) 20 5) Inventory increase $ 25,000 21 6) Accounts payable increase $ 5,000 22 7) Equipment salvage value (end of life) $ 15,000 23 8) Projected sales for year 1 $ 200,000 24 9) Projected sales growth per year Low Case 2% 26 Base Case 5% 27 High Case 8% 28 10) Operating cost (as a % of sale -60% $(120,000) 29 11) Depreciation (straight line) per year $ 60,000) 30 12) Corporate marginal tax rate (T) 21% 31 13) Cost of capital or discount rate (1) 10% 32 * Note: Equipment cost, shipping and installation, and start-up costs are all depreciated with equipment 25 PART 1A: Use the Excel cells below to calculate all estimated project cash flows for the BASE CASE revenue growth. Then solve for the NPV, IRR and Payback period for the BASE CASE. CF(0) = Cash Flow at Time 0 CF(0) CF(1) CF(2) CF(3) CF(4) Year 0 1 2 3 4 Investments: 1) Equipment cost $ (200,000) 2) Shipping and Install cost $ (35,000) 3) Start up expenses $(5,000.00) Total Equipment Cost Basis (1+2+3) $ (240,000) 4) Net Working Capital $ (20.000) Total Initial Outlay $ (260,000) Opera ons: Revenue Operating Cost Depreciation EBIT Taxes Net Income $ 200,000 $ 210,000 $ 220,500 $ 231,525 $ 120,000 $126.000 $ 132,300 $ 138.915 $ 60.000 $ 60.000 $ 60.000 $ 60.000 $ 20,000 $ 24,000 $ 28,200 $ 32,610 $ 4,200 $ 5,040 $ 5,922 $ 6.848 $ 15,800 $ 18,960 $ 22,278 $ 25,762 Add back Depreciation $ 60,000 $ 60,000 $ 60,000 $ 60,000 Total Operating Cash Flow $ 75,800 $ 78,960 $ 82,278 $ 85,762 $ $ Terminal Cash Flows (end of Year 4) 1) Change in net WC 2) Salvage value (after tax) [= Salvage value before tax * (1-T)] Total $ 20,000 $ 11,850 $ 31,850 Project Net Cash Flows $ (260,000) $ 75,800 $ 78,960 $ 82,278 $ 117,612 NPV (Base Case) = = $51.193 IRR - 21% Payback = 2.28 Part 1B: Answer the PART 1B questions on the "Additional Questions" tab PART 1B Questions: Capital Budgeting Decisions Question 1: Using 10% as your company's cost of capital and assuming the BASE CASE for revenue growth, would you accept the project on an NPV and IRR? Why? Question 2: Would you accept the project based on the Payback rule if the project cut-off is 3 years? Answer 1: Answer 2