FIN 310 Fall 2017 – Capital Budgeting Assignment

This assignment is due on Blackboard no later than 11:59 pm on Monday, December 4. It will not be accepted late or via e-mail. This assignment must be done in MS-Excel using the formulas and functions in Excel. This means you will NOT receive full credit if you work the problem on paper and then type the numbers into the spreadsheet cells or work portions of the assignment on your calculator. Make sure you have a separate worksheet within your ONE Excel file for each problem and that you label the worksheet tabs. I will NOT accept work done in two Excel files. Your recommendations need to be included in a text box, not typed into a cell.

1. Your company is considering a new product. The marketing department has estimated that the new line will sell four million units in year one at a price of $15 per unit. Unit sales are expected to increase by 3% per year until the product is retired in 8 years. The variable cost per unit is $10 in year one. The price is expected to increase by 2% per year. If you introduce the new product you will sell 1.2 million fewer units per year of an existing product for the first 2 years. The price of the existing product is $8 per unit and the variable cost per unit of the existing product is $4. The price and cost are not expected to change over the next 2 years. The existing line will be discontinued in 2 years regardless of whether we introduce the new product. Fixed costs are expected to be $10 million in year one and increase by 1% per year due to inflation. You can manufacture the product in a factory that is currently sitting idle. The factory originally cost $10 million and is fully depreciated. You could sell the factory for $15 million today, before taxes. If you use the factory now, you estimate that you could sell it in 8 years for $17 million before taxes. The required equipment will cost $15 million and will be depreciated using 5-year MACRS. The expected salvage at the end of year 8 is $1.5 million. The new product will require net working capital equal to 10% of the next year’s sales. This is similar to the NWC requirements for the existing products. The company’s cost of debt is 5%, the required return on projects of this risk is 10% and the marginal tax rate is 35%. Use Excel to create pro forma income statements, compute the relevant cash flows and find the payback period, NPV, and IRR. Based on this analysis should the company produce the new product? Why or why not? (60 points)

2. Compute the cash flows, NPV, and the IRR for the following replacement project. Should you replace the current equipment? (40 points)

Current Machine Proposed Machine Original Cost 16 million Cost 15 million Depreciation 7-year MACRS Depreciation 7-year MACRS Purchased 5 years ago Expected salvage

in 6 years 5 million

Salvage today 6 million Increase in revenue

500,000 per year

Expected salvage in 6 years

2 million Increase in operating cost

400,000 per year

Initial increase in NWC

200,000

Tax rate = 35%; Required Return 4%