Firm A is considering producing and selling a new product for 5 years. Sales initially are expected to be 350,000 units and the selling price per unit is $50. Sales areexpected to grow by 5% per year for the first 3 years and then 4% per year for the last 2 years. The fixed operating costs are expected to increase by $2 million a year over their current level and the variable cost is expected to be 20% of sales. The project is also expected to increase sales of another product the firm sells by $400,000 per year. Test marketing costs incurred by this project were $60,000 last year.

Firm A will need to invest in land for $2 million and in machinery that costs $10 million and has a salvage value of $1 million at the end of 5 years. Depreciation is straight line. I will be using this machine to replace old equipment that has a bookvalue of $2 million and a market value of $0.5 million. Tis machinery has remaining depreciation of $200,000 per year for the next 3 years and zero after.

Firm A has a debt to equity ratio of 0.33. it plans to raise the $15 million that it needs in total for land and equipment by barrowing 25% of this total at 10% before tax and selling equity for the remainder. The firms beta is 1.2 and its tax rate is 40%. However because this project is in a different risk class, it is using a proxy company P with a debt to equit ratio of 0.7 and a beta of 0.9. The rate of return on 90 day T Bills is 1% and the expected return on the S&P 500 is 8%.

a) Create a data table in excel

b) Set up a spreadsheet to calculate the expected cash flows as well as the NPV, IRR and Profitabiity Index