Question

Please show how to solve this with excel

Jane's Sub Shop is considering investing in toaster ovens for each of its 120 stores located in the southwestern United States. The high-capacity conveyor toaster ovens, manufactured by Lincoln, will require an initial investment of $15,000 per store plus $500 in installation costs, for a total investment of $1,860,000. The new capital (including the costs for installation) will be depreciated over five years using straight-line depreciation toward a zero salvage value. Jane will also incur additional maintenance expenses totaling $120,000 per year to maintain the ovens. At present, firm revenues for the 120 stores total $9 million, and the company estimates that adding the toaster feature will increase revenues by 10%. Jane's weighted average cost of capital is 9%.

a. If Jane faces a 30% tax rate, what expected project FCFs for each of the next five years will result from the investment in toaster ovens?

b. What is the project's NPV? Should the project be accepted?

c. What is the project's IRR? Should the project be accepted?

Answer 1

a) Depreciation per year = $1,860,000 / 5 = $372,000

Tax savings from depreciation per year = 30% x $372000 = $111,600

Annual FCF = (Incremental sales - Annual maintenance) x (1 - Tax rate) + Tax savings from depreciation

or, Annual FCF = ($9,000,000 x 10% - $120,000) x (1 - 0.30) + $111,600 = $657,600

b) NPV = (-)Initial investment + Annual FCF x PVFA (9%, 5 years)

or, NPV = (-)$1,860,000 + $657,600 x 3.8896512633 = $697,834.67074 or $697,834.67

Since the NPV is positive, the project should be accepted.

c) IRR is the rate at which the present value of cash inflows is equal to the intial investment. It is computed as follows -

Step 1

Annual FCF x PVFA (IRR, 5 years) = Initial investment

or, $657,600 x PVFA (IRR, 5 years) = $1,860,000

or, PVFA (IRR, 5 years) = 2.82846715328

Step 2

We refer the above value in the present value factor annuity (PVFA) table at 5 years, and we get -

At 22%, PVFA = 2.8636397615

At 23%, PVFA = 2.80347297767

Our value lies between 22% and 23%.

Step 3 - interpolation

Difference required = 2.8636397615 - 2.82846715328 = 0.03517260822

Total Difference = 2.8636397615 - 2.80347297767 = 0.06016678383

IRR = Lower rate + Difference in rates x (Difference required / Total difference) = 22% + 1% x (0.03517260822 / 0.06016678383) = 22.5845851478% or 22.58%

Since IRR is higher than the cost of capital, the project should be accepted.