Question

Now the president is starting to get on your nerves. But, whaddayagonnado? He is the president. You just wish he wasn’t so demanding. Nevertheless, you press on. He has informed you that you need to aid in a decision regarding a new facility. There are 3 mutually exclusive locations being considered each with it’s own startup cost and projected cash flows as shown below:

	Timbuktu	Neverland	Middle Earth
Cost	$3,600	$8,750	$6,500
Year 1 CF	$0	$4,000	$2,000
Year 2 CF	0	4,000	2,000
Year 3 CF	0	1,500	2,000
Year 4 CF	0	0	2,000
Year 5 CF	$8,500	3,000	3,000

The president has asked for a thorough analysis. Keeping in mind DWOTT’s cost of capital (use WACC above)(8.57%), what decision should be made regarding the projects above using each of the following tools:

What is each project's payback period and which would you choose?

Timbuktu:

Neverland:

Middle Earth:

Choice & Why?

What is each project's discounted payback period and which would you choose?

Timbuktu:

Neverland:

Middle Earth:

Choice & Why?

What is each project's net present value and which would you choose?

Timbuktu:

Neverland:

Middle Earth:

Choice & Why?

What is each project's internal rate of return and which would you choose?

Timbuktu:

Neverland:

Middle Earth:

Choice & Why?

What is each project’s modified internal rate of return and which would you choose?

Timbuktu:

Neverland:

Middle Earth:

Choice & Why?

  

Considering the WACC and given the calculations above, which project do you prefer, and why?

Answer 1

Since, there are multiple parts to the question, I have answered the first four with complete details. Also the question is very long, it is not possible to answer all the parts within the available time.

______

Part 1)

The payback project for each period is calculated as below:

Timbuktu

	Cash Flow	Cumulative Cash Flows
Year 0	-3,600.00	-3,600.00
Year 1	0.00	-3,600.00
Year 2	0.00	-3,600.00
Year 3	0.00	-3,600.00
Year 4	0.00	-3,600.00
Year 5	8,500.00	4,900.00

As can be seen from the above table that the cumulative cash flows turn positive from Year 4 to Year 5. Therefore, the payback period will lie between these two years. The formula for calculating payback period is derived as follows:

Payback Period = Years Upto which Partial Recovery is Made + Balance/Cash Flow of the Year in which Full Recovery is Made = 4 + 3,600/8,500 = 4.42 years

_____

Neverland

	Cash Flow	Cumulative Cash Flows
Year 0	-8,750.00	-8,750.00
Year 1	4,000.00	-4,750.00
Year 2	4,000.00	-750.00
Year 3	1,500.00	750.00
Year 4	0.00	750.00
Year 5	3,000.00	3,750.00

As can be seen from the above table that the cumulative cash flows turn positive from Year 2 to Year 3. Therefore, the payback period will lie between these two years. The formula for calculating payback period is derived as follows:

Payback Period = Years Upto which Partial Recovery is Made + Balance/Cash Flow of the Year in which Full Recovery is Made = 2 + 750/1,500 = 2.50 years

_____

Middle Earth

	Cash Flow	Cumulative Cash Flows
Year 0	-6,500.00	-6,500.00
Year 1	2,000.00	-4,500.00
Year 2	2,000.00	-2,500.00
Year 3	2,000.00	-500.00
Year 4	2,000.00	1,500.00
Year 5	3,000.00	4,500.00

As can be seen from the above table that the cumulative cash flows turn positive from Year 3 to Year 4. Therefore, the payback period will lie between these two years. The formula for calculating payback period is derived as follows:

Payback Period = Years Upto which Partial Recovery is Made + Balance/Cash Flow of the Year in which Full Recovery is Made = 3 + 500/2,000 = 3.25 years

_____

Tabular Representation:

	Payback Period
Timbuktu	4.42 years
Neverland	2.50 years
Middle Earth	3.25 years

Based on the above calculations, Neverland project should be accepted as it has the lowest payback period.

_____

Part 2)

The project's discounted payback is calculated as follows:

Timbuktu

	Cash Flows	Discounted Cash Flows	Cumulative Discounted Cash Flows
Year 0	-3,600.00	-3,600.00	-3,600.00
Year 1	0.00	0.00	-3,600.00
Year 2	0.00	0.00	-3,600.00
Year 3	0.00	0.00	-3,600.00
Year 4	0.00	0.00	-3,600.00
Year 5	8,500.00	5,634.69	2,034.69

As can be seen from the above table that the cumulative discounted cash flows turn positive from Year 4 to Year 5. Therefore, the payback period will lie between these two years. The formula for calculating discounted payback period is derived as follows:  

Discounted Payback Period = Years Upto which Partial Recovery is Made + Balance/Discounted Cash Flow of the Year in which Full Recovery is Made = 4 + 3,600/5,634.69 = 4.64 years

_____

Neverland

	Cash Flows	Discounted Cash Flows	Cumulative Discounted Cash Flows
Year 0	-8,750.00	-8,750.00	-8,750.00
Year 1	4,000.00	3,684.26	-5,065.74
Year 2	4,000.00	3,393.44	-1,672.30
Year 3	1,500.00	1,172.09	-500.21
Year 4	0.00	0.00	-500.21
Year 5	3,000.00	1,988.71	1,488.51

As can be seen from the above table that the cumulative discounted cash flows turn positive from Year 4 to Year 5. Therefore, the payback period will lie between these two years. The formula for calculating discounted payback period is derived as follows:  

Discounted Payback Period = Years Upto which Partial Recovery is Made + Balance/Discounted Cash Flow of the Year in which Full Recovery is Made = 4 + 500.21/1,988.71 = 4.25 years

_____

Middle Earth

	Cash Flows	Discounted Cash Flows	Cumulative Discounted Cash Flows
Cost	-6,500.00	-6,500.00	-6,500.00
Year 1	2,000.00	1,842.13	-4,657.87
Year 2	2,000.00	1,696.72	-2,961.15
Year 3	2,000.00	1,562.79	-1,398.36
Year 4	2,000.00	1,439.43	41.07
Year 5	3,000.00	1,988.71	2,029.78

As can be seen from the above table that the cumulative discounted cash flows turn positive from Year 3 to Year 4. Therefore, the payback period will lie between these two years. The formula for calculating discounted payback period is derived as follows:

Discounted Payback Period = Years Upto which Partial Recovery is Made + Balance/Discounted Cash Flow of the Year in which Full Recovery is Made = 3 + 1,398.36/1,439.43 = 3.97 years

_____

Tabular Representation:

	Discounted Payback Period
Timbuktu	4.64 years
Neverland	4.25 years
Middle Earth	3.97 years

Based on the above calculations, Middle Earth project should be accepted as it has the lowest discounted payback period.

_____

Part 3)

The net present value of each project is calculated as follows:

NPV = Cash Flow Year 0 + Cash Flow Year 1/(1+WACC)^1 + Cash Flow Year 2/(1+WACC)^2 + Cash Flow Year 3/(1+WACC)^3 + Cash Flow Year 4/(1+WACC)^4 + Cash Flow Year 5/(1+WACC)^5

NPV (Timbuktu) = - 3,600 + 0/(1+8.57%)^1 + 0/(1+8.57%)^2 + 0/(1+8.57%)^3 + 0/(1+8.57%)^4 + 8,500/(1+8.57%)^5 = $2,034.69

NPV (Neverland) = -8,750 + 4,000/(1+8.57%)^1 + 4,000/(1+8.57%)^2 + 1,500/(1+8.57%)^3 + 0/(1+8.57%)^4 + 3,000/(1+8.57%)^5 = $1,488.51

NPV (Middle Earth) = -6,500 + 2,000/(1+8.57%)^1 + 2,000/(1+8.57%)^2 + 2,000/(1+8.57%)^3 + 2,000/(1+8.57%)^4 + 3,000/(1+8.57%)^5 = $2,029.78

_____

Tabular Representation:

	NPV
Timbuktu	$2,034.69
Neverland	$1,488.51
Middle Earth	$2,029.78

Based on the above calculations, Timbuktu project should be accepted as it has the highest NPV.

_____

Part 4)

IRR is the minimum rate of return acceptable from a project. It can be calculated with the use of IRR function/formula of EXCEL/Financial Calculator. The basic formula for calculating IRR is given as below:

NPV = 0 = Cash Flow Year 0 + Cash Flow Year 1/(1+IRR)^1 + Cash Flow Year 2/(1+IRR)^2 + Cash Flow Year 3/(1+IRR)^3 + Cash Flow Year 4/(1+IRR)^4 + Cash Flow Year 5/(1+IRR)^5

IRR is calculated with the use of EXCEL as below:

where

IRR (Timbuktu) = IRR(B3:B8) = 18.75%

IRR (Neverland) = IRR(C3:C8) = 16.36%

IRR (Middle Earth) = IRR(D3:D8) = 19.22%

_____

Tabular Representation:

	IRR
Timbuktu	18.75%
Neverland	16.36%
Middle Earth	19.22%

Based on the above calculations, Middle Earth project should be accepted as it has the highest IRR.