A medical company has contracted you as a finance consultant to advise them on an investment opportunity of marketing their…

A medical company has contracted you as a finance consultant to advise them on an investment opportunity of marketing their new drug “Memory” treating Alzheimer disease. More specifically, the company wants you to advise them on whether to make the investment or not and whether it is preferable to use equity or debt financing for the project. Information about the company and the investment opportunity follows:
General
• The company is currently financed by 60% ($6 000 000) equity and 40% debt
• The current after-tax WACC is 9%
• The cost of company debt, Rd, is currently 5% and expected to be constant as long as D/E ≤ 0.8. Thereafter Rd increases with 1% per each ten-percentage-unit increase in D/E. For instance, 0.8 < D/E ≤ 0.9 implies Rd= 6% • The corporate tax rate, Tc, is 25% • It is the 1st of January 2013 The investment opportunity • The initial cost of marketing the investment is $1 000 000 • The project is expected to generate yearly cash flows of $400 000 at the end of each year for 5 years starting 31st of December 2013. • After the five years the investment has no scrap value • The company can either finance the project with 75% debt and 25% equity or with 60% equity and 40% debt. Further, assume that under the 75% debt-financing alternative, project debt is kept fixed during the project’s lifetime. • Assume that the upfront cost per $1 equity issued is $0.07 and that the upfront cost per $1 debt issued is $0.01 • Assume no cost for rebalancing debt Your task Your task is to create a spreadsheet model (using Microsoft Excel) showing whether to undertake the investment or not and if so, what financing alternative that is to prefer. The client more specifically wants you to show the following in your spreadsheet model: a) Would it be worthwhile to finance the investment with equity only? Show by calculation the investment’s worth to the company b) Show the interest tax shield value of each financing alternative. Hence, calculate PV (interest tax shield) c) Use the APV-method (base-case NPV + sum of PV of financing side effects) to show which of the two financing alternatives that is to prefer given the information above. Finally, since the company wants to be able to use this spreadsheet model in the future as well you need to use cell references in the formulas (for tasks a, b and c above).