Galloway Corporation is considering whether to launch a new product line of pre-fabricated storage garages. The total investment needed to undertake the project is $5,000,000. This amount will be depreciated straight-line to zero over the 5-year life of the equipment. The salvage value is zero, and there are no working capital consequences. Galloway has a required return of 20 percent on new projects and is taxed at 25%. The selling price will be $60,000 per garage. The variable costs will be about half that or $39,000 per garage, and fixed costs will be $655,000 per year. Beginning in year three (3), the variable costs are expected to decrease by 5% per year due to the firm gaining comfort in the new process. Please show all your work in a spreadsheet. All totals should be formula based (i.e. PV, NPV, Total Cash Flow, etc…)

Using the template below, please provide/calculate the following: The NPV of the project at 100 units. The NPV of the project at 150 units  The Financial (i.e. economic) breakeven in units. Assume the 100 and 150 units are produced through the lifetime of the project (not yearly)

 

1 Required Return 2 Tax Rate 3 Discount Rate 4 Net Present Va 5 IRR 6 7 @ 100 units 8 9 #NUM! 20% 25% 9.5% ######### Revenue Variable Costs Fixed Costs ######### Intial Investment $1,000,000.00 Annual Depreciation ######### $ 655,000.00 10 11 12 13 Description 14 Operating Cash: 15 Operating Cash Inflow (after tax) 16 Other Cash Flows.... List here (an 17 0 18 - Taxes 19 After Tax Operating CF 20 21 (+) Depreciation Tax Shield Ben 22 23 Net Working Capital CF 24 NWC 30 Total After-Ta: 31 PV After-Tax CF's 77 $0 25 26 Net Capital Expenditures: 27 Initial Investment 28 After-Tax Salvage 29 $0 $0 1 $0 $0 $0 $0 2 $0 $0 $0 $0 3 $0 $0 $0 $0 4 $0 $0 $0 $0 5 $0 $0 $0 $0 $0