Question

Question 4 (15 points): the Mini Case distributed in-class For the Chapter 8 Mini Case distributed in class, determine the following: a. Calculate the NPV, IRR, and Profitability Index for each option. b. If the projects are mutually exlcusive, what is your decision? Why? c. If the projects are independent, what is your d ecision? Why?

Answer 1

Answer a. First we will find out the present value of all the cash flows, for this you will have to discount the cash flows from the year they flow in to year 0.

You may use the PV function on excel to find the present value of the individual cash flow and then add all the cash flow from the individual projects to get the NPV. OR you may use the function of NPV in excel and get the NPV directly.

First we will find the NPV of cash Flows for Franchise S

To get the PV of CF of $70,000 ,

use the inputs as rate=10%,

nper = 1

PMT= 0

FV= 70000

you will get the PV = $63,636.36

To get the PV of CF of $50,000 ,

use the inputs as rate=10%,

nper = 2

PMT= 0

FV= 50000

you will get the PV = $41,322.31

To get the PV of CF of $20,000 ,

use the inputs as rate=10%,

nper = 3

PMT= 0

FV= 20000

you will get the PV = $15,026.30

The NPV of the Franchise S = $63,636.36 + $41,322.31 + $15,026.30 = $119,984.97

Now we will calculate the NPV of CFs of Franchise L

To get the PV of CF of $10,000 ,

use the inputs as rate=10%,

nper = 1

PMT= 0

FV= 10000

you will get the PV = $9,090.91

To get the PV of CF of $60,000 ,

use the inputs as rate=10%,

nper = 2

PMT= 0

FV= 60000

you will get the PV = $49,586.78

To get the PV of CF of $80,000 ,

use the inputs as rate=10%,

nper = 3

PMT= 0

FV= 80000

you will get the PV = $60,105.18

Thus the NPV of CFs from the Franchise L = $9,090.91 + $49,586.78 + $60,105.18 = $118,782.87

This can be summarized in the table

Year	Franchise S	Franchise L	PV of Franchise S	PV of Franchise L
0	$ -100,000.00	$     -100,000.00
1	$     70,000.00	$         10,000.00	$             63,636.36	$              9,090.91
2	$     50,000.00	$         60,000.00	$             41,322.31	$            49,586.78
3	$     20,000.00	$         80,000.00	$             15,026.30	$            60,105.18
NPV			$           119,984.97	$          118,782.87

Now for IRR you can use the IRR function of the excel, for this just type =IRR on any cell in excel sheet then select the similar option from the drop down menu then select all the cash flows from the year 0 to Year 3.

The IRR for Franchise S= 24%

The IRR for Franchise L= 18%

For profitability index, use the formula Profitability index = NPV/Initial Investment

Thus Profitability Index of franchise S = 119984.97/100000 = 1.20

Profitability Index of franchise L = 118782.87/100000 = 1.19

Answer b. If the projects are mutually exclusive, which means that I can choose only one of the project, then I would choose Franchise S as the NPV of the cash flows is also more as compared to the Franchise L. Also the IRR and Profitability Index is also more as compared to Franchise L, which means that Franchise S is more profitable and will provide better return.

Answer c. If the projects are independent I will choose both the projects as both the projects have positive return and the NPV of the cash flows is more than the initial investment. Also the IRR of both the projects is more than the required rate of return of 10%(as given in the question). Also the profitability index of both the projects is greater than 1.