*******I need the answer to (b). Please show ALL formulas in Excel. I already have the answer to (a). ********************

James Polk Hospital has currently unused space in its lobby. In three years, the space will be required for

a planned expansion, but the hospital is considering uses of the space until then. The hospital has decided

that it wants to purchase at least one and maybe two fast food franchises, to take advantage of the high

volume of patients and visitors that walk through the lobby all day long. The hospital plans to purchase

the franchise(s), operate them for three years, and then close them down. The hospital has narrowed its

selection down to two choices:

Franchise L: Lisa's Soups, Salads, and Stuff

Franchise S: Sam's Wonderful Fried Chicken

The net cash flows shown below include the costs of closing down the franchises in Year 3 and the forecast

of how each franchise will do over the three-year period. Franchise L serves breakfast and lunch, while

Franchise S serves only dinner, so it is possible for the hospital to invest in both franchises. The hospital

believes these franchises are perfect complements to one another: The hospital could attract both the

breakfast/lunch and dinner crowds and both the health-conscious and not-so-health-conscious crowds without the

franchises directly competing against one another. The corporate cost of capital is 10 percent.

Net cash flows

Year Franchise S Franchise L

0 -$100 -$100

1 $70 $10

2 $50 $60

3 $20 $80

a. Calculate each franchise's payback period, net present value (NPV), internal rate of return (IRR), and

modified internal rate of return (MIRR).

*************b. Graph the NPV of each franchise at different values of the corporate cost of capital from 0 to 24 percent in

2 percent increments.

- How sensitive are the franchise NPVs to the corporate cost of capital?

- Why do the franchise NPVs differ in their sensitivity to the corporate cost of capital?

- At what cost of capital does each franchise intersect the X-axis? What are these values?