NEED SOLVED USING TEMPLATE

The CFO of Montag Metals is considering the purchase of a new stamping machine to support its sales growth. The firm has hired you to determine whether it will be a good idea to pursue this project. The new machine will allow the firm to increase annual revenues by $3,500,000. Annual cash costs will increase by $2,000,000. In addition, the firm will need to immediately increase inventory by $500,000. Accounts payable will partly offset this, increasing by $350,000. The new stamping machine costs $4,500,000 installed. It will be depreciated by MACRS method for 3-year property. The expected economic life of the machine is five years. At the end of five years the machine will likely be sold for about 20% of its initial cost. The firms tax rate is 21%. The CFO also asks you to prepare a report recommending whether the firm should go forward with the investment or not. In preparing your recommendation, you should create a spreadsheet (see template) to carefully detail the following calculations: a) The initial investment in the project. (3 points) b) The operating cash flows over the project life. (12 points) c) The (non-operating) terminal cash flow for this project. (6 points) d) The projects internal rate of return (IRR) and its net present value (NPV) at the firms cost of capital of 12%. Also, calculate the payback period. (8 points) e) Based on NPV and IRR, what would be your recommendation (4 points)

Montag Metals Exercise Years 3 Initial Investment 4 New Equipment 5 Net Working Capital Investment 6 Initial Investment 8 Operating Cash Flows Over the Project Life 9Revenues 10 -Costs 11 -Depreciation Expense (3 year MACRS) 12 EBIT 13 -Taxes @ 21% 14 |Operating Income after Tax 15 Depreciation Expense 16 OCF 17 18 19 Terminal Cash Flow 20 Salvage Value 21 Tax@ 21% 22 Salvage Value after Tax 23 24 Net Working Capital Return 25 Terminal Cash Flow 26 27 28 NPV Analysis 29 Project Cash Flows 30 31 Discount rate @ 12% 32 Net Present Value (-sum ofpresent values) 2 5 1,499,969 1,605,05:3 1,324,955 1,255,025 1,185,000 900,000 189,000 711,000 150,000 861,000 2 (4,650,000) 1,499,969 1,605,053 1,324,955 1,255,025 2,046,000 0.12 870,418.2 34 Internal Rate of Return 35 36 Cumulative Project Cash Flows 37 Payback Period 19.13% 4650000 3150031.5 1544979 220024.5 1035000 3081000 3.1753 Years