Question
The Metals by Design Company is evaluating the proposed acquisition of a new milling machine. The machine's base price is $100,000, and it would cost another $9,000 to modify it for special use by the firm. The machine falls into the MACRS 3 - year class, and it would be sold after three years for $70,000. The machine would require net working capital (inventory) investment of 15.00% of the following year's forecasted revenues (adjust for the amount invested in NWC at the end of the previous period). The milling machine will result in an increase in production of 2,500; 25,000; and 25,000 units in each of the next three years, respectively. The current price per unit is $3.00. You should assume that price per unit will increase by 1%, 2%, and 3% annually over the next three years, respectively. The new milling machine is expected to save the firm $25,000, 40,000, and 40,000 per year for years 1 - 3 in before - tax operating costs, mainly labor. Metals by Design's tax rate is 40%. a. (1) what is the initial investment excluding the investment in NWC at t=0? what is the NWC investment in each year, 0 - 3? b. (1) Calculate the Depreciation Schedule including depreciation expense and book value. Use cost allocation to four decimal positions. Ex. 0.3333 c. (1) what are the incremental after - tax operating cash flows for years 1, 2, and 3? d. (1) what is the net proceeds from the sale of the milling machine at the end of year 3? e. (3) Assuming the project's required rate of return is 22%, should the machine be purchased? You must use nested = IF() logical statements to automatically report your recommendation and why. i. Calculate the payback period and decide whether to accept or reject. Must use a nested =IF() for this calculation and recommendation. Be careful. what if you aren't given a maximum payback period? ii. Calculate the net present value and decide whether to accept or reject and why. iii. Calculate the internal rate of return and decide whether to accept or reject and why. iv. Calculate the profitability index and decide whether to accept or reject and why. v. Calculate the modified internal rate of return and decide whether to accept or reject and why.
Explanation / Answer
Cost of Machine 100000 Modification Expenses 9000 Initial Investment in machiery 109000 Year 1 2 3 MACRS Depreciation rates 0.3333 0.4445 0.1481 Depreciation Amount 36329.70 48450.50 16142.90 Net Proceeds from sale of machine 70000 Year 0 1 2 3 Initial Price of the product 3 Annual growth rate 0.01 0.02 0.03 Sale Price 3.03 3.09 3.18 Increase in production (Units) 2500 25000 25000 Sales Value 7575.00 77265.00 79582.95 NWC (15% of next year sales) 1136.25 11589.75 11937.44 0.00 Incremental NWC 1136.25 10453.50 347.69 -11937.44 Pre-tax Savings in Costs 25000.00 40000.00 40000.00 Depreciation 36329.70 48450.50 16142.90 Pre-tax Savings -11329.70 -8450.50 23857.10 Tax 0.00 0.00 9542.84 After -tax Savings -11329.70 -8450.50 14314.26 After-tax Operating cash flows 25000.00 40000.00 30457.16 Affter Tax incremental cash flows 25000.00 15000.00 -9542.84 Total Cash Flows Associated with the project Year 0 1 2 3 Initial Investment -109000 Increase / Decrease in NWC -1136.25 -10453.5 -347.6925 11937.4425 Operating cash flows 25000.00 40000.00 30457.16 Salvage Value 70000 Total Cash Flows -110136.25 14546.5 39652.3075 112394.603 Internal rate of return (IRR) 18% Modified internal rate of return (MIRR) 18% Payback period 1 2 3 Payback Period 3 The Formula is IF((-B39