Question

The Frank Stone Company is considering the introduction of a new product. Generally, the company's products have a life of about 5 years, after which they are deleted from the range of products that the company sells. The new product requires the purchase of new equipment costing $4,000,000, including freight and installation charges. The useful life of the equipment is 5 years, with an estimated resale of equipment of $1,575,000 at the end of that period. The equipment will be fully depreciation to zero value using the straight line (prime cost) method.

The new product will be manufactured in a factory already owned by the company. This factory is currently being rented to another company under a lease agreement that has 5 years to run and provides for an annual rental of $150,000. Under the lease agreement, the Frank Stone Company can cancel the lease by immediately (year 0) paying the lessee compensation equal to 1 year's rental payment.

It is expected that the product will involve the company in sales promotion expenditures that will amount to $500,000 during the first year the product is on the market. Additions to current assets (net working capital) will require $225,000 at the commencement of the project and are assumed to be fully recoverable at the end of the fifth year.

The new product is expected to generate sales revenue as follows:
Year 1: $3,000,000
Year 2: $3,500,000
Year 3: $3,250,000
Year 4: $3,000,000
Year 5: $1,500,000

It is assumed that all cash flows are received at the end of each year. The corporate tax rate is 35%.

Frank Stone Company has an equity beta of 0.5. Its capital structure consists of equal amounts of equity and debt. The risk free rate is 6%. The debt has a pre-tax yield of 10% and the expected rate of return on the market index is 18%.

Using the inputs suggested in the proposed investment, design an Excel financial model that can help to evaluate the project. The model should enable Frank Stone Company to consider the project and forecast the net cash flows. 5 marks will be allocated for the presentation and clarity of your model (e.g., if there are clear headings, clear arrangement for input cells, appropriate use of colors, have some degree of flexibility, generate warning messages for wrong user inputs, etc.).

Answer the following questions using your Excel financial model:

a.
What is Frank Stone Company ’s weighted average cost of capital (WACC)?

b.
Estimate the free cash flows of the project.

c.
What is the net present value (NPV) and internal rate of return (IRR) of the project? Should Frank Stone Company undertake the project based on NPV? How about IRR? Do both NPV and IRR lead to the same decision?

d.
Draw a line graph to explain the sensitivity of the NPV to changes in WACC? The graph should have a chart title, as well as x- and y-axis labels.

Answer 1

Full Excel of the Problem is below

Particulars	Values (Can be Changed)
Tax Rate	35%		Expected Equity Return	12.00%
Beta	0.5		WACC	7.950%
Risk Free Rate	6%
Debt Yield	10%
Returns from Market	18%
Debt/Total Capital	50%
Equity/Total Capital	50%
Debt/Equity Ratio	1.00
Investment	(40,00,000)
Year	0	1	2	3	4	5
Revenue		30,00,000	35,00,000	32,50,000	30,00,000	15,00,000
Minus: Depreciation		8,00,000	8,00,000	8,00,000	8,00,000	8,00,000
Marketing Promotions		5,00,000
Profit Before Tax		17,00,000	27,00,000	24,50,000	22,00,000	7,00,000
Tax Amount		5,95,000	9,45,000	8,57,500	7,70,000	2,45,000
Profit After Tax		11,05,000	17,55,000	15,92,500	14,30,000	4,55,000
Adding Back Depreciation		19,05,000	25,55,000	23,92,500	22,30,000	12,55,000
Cost due to cancellation of Lease Rental	(1,50,000)
Total Cash Flow from Project	(1,50,000)	19,05,000	25,55,000	23,92,500	22,30,000	12,55,000
Working Capital	2,25,000	2,25,000	2,25,000	2,25,000	2,25,000	-
Investment Cash Flow
Revenue from Sale of Equipment						15,75,000
Change in Working Capital	2,25,000	-	-	-	-	(2,25,000)
Cash Flow from Investments	2,25,000	-	-	-	-	13,50,000
Total Cash Flow to Firm	75,000	19,05,000	25,55,000	23,92,500	22,30,000	26,05,000
Discount Factor (by WACC)	1	0.9264	0.8581	0.7949	0.7364	0.6822
PV of the Company	75,000	17,64,706	21,92,530	19,01,884	16,42,155	17,77,029
NPV = Sum of PV - Investment	53,53,305
	(39,25,000)	19,05,000	25,55,000	23,92,500	22,30,000	26,05,000
IRR of Firm	49.56%

A: Frank Stone's Company WACC is 7.950%

B: Free Cash Flow From Project is mentioned in the excel above. Please note that the project returns are different from the cash flow return as the project returns takes into account only the returns from the project. It is not concerned by how the project is funded. However, the NPV is calculated taking into account all the financing, investment and other factors as well.

C: NPV of the Firm is $5,353,305 & IRR is 49.56% . Since IRR is above the WACC, the company should consider taking up the project. NPV is positive. Hence, project is viable and profitable. Therefore, both IRR and NPV are pointing to the same direction.

D:

The sensitivity of NPV to WACCcan be seen in the above graph. The slope of the line shall give us the sensitivity of the NPV to WACC.