Question

You are the head of the project selection team at SIMSOX.* Your team is considering three different projects. Based on past history, SIMSOX expects at least a rate of return of 20 percent. Given the following information for each project, which one should be SIMSOX’s first priority? Should SIMSOX fund any of the other projects? If so, what should be the order of priority based on return on investment? (See chart below)

Project: Dust Devils Year Investment Revenue Stream 0 $500,000 0 1 50.000 2 250,000 3 350,000 Project: Osprey Year Investment Revenue Stream 0 $250,000 0 1 75,000 2 75,000 3 75,000 4 50,000 Project: Voyagers Year Investment Revenue Stream 0 $75,000 0 1 15,000 2 25,000 3 50,000 4 50,000 5 150,000

Answer 1

Answer

• The first priority project #3 is Voyagers AS it has the highest internal rate of return (44%) which cover the cost of capital (20%).
• Also , It has the highest NPV and IRR compared to other 2 projects so should be pursued.
• SIMSOX should not invest in others two projects (#1 and #2) as negative NPV and expected IRR is not achieved.

See calculations below.

Project 1
Cost of capital	20%
Year	0	1	2	3
Net cash flow	(500,000)	50,000	250,000	350,000
PV factor	100%	83%	69%	58%	=B10/(1+$B$4)
PV of net cash flow	(500,000)	41,667	173,611	202,546	=C9*C10
Cumulative PV	(500,000)	(458,333)	(284,722)	(82,176)	=B12+C11
Net present value	(82,176)				=NPV(B4,C9:E9)+B9
IRR (Internal Rate of Return)	11%				=IRR(B9:E9,0.1)

Project 2
Cost of capital	20%
Year	0	1	2	3	4
Net cash flow	(250,000)	75,000	75,000	75,000	50,000
PV factor	100%	83%	69%	58%	48%
PV of net cash flow	(250,000)	62,500	52,083	43,403	24,113
Cumulative PV	(250,000)	(187,500)	(135,417)	(92,014)	(67,901)
Net present value	(67,901)
IRR (Internal Rate of Return)	4%

Project 3
Cost of capital	20%
Year	0	1	2	3	4	5
Net cash flow	(75,000)	15,000	25,000	50,000	50,000	150,000
PV factor	100%	83%	69%	58%	48%	40%
PV of net cash flow	(75,000)	12,500	17,361	28,935	24,113	60,282
Cumulative PV	(75,000)	(62,500)	(45,139)	(16,204)	7,909	68,191
Net present value	68,191
IRR (Internal Rate of Return)	44%

Excel

А B с D E F G H 20% 0 1 2 3 (500,000) 50,000 250,000 350,000 100% 83% 69% 58% =B10/(1+$B$4) (500,000) 41,667 173,611 202,546 =C9"C10 (500,000) (458,333) (284,722) (82,176) =B12+C11 (82,176) =NPV(B4,C9:59)+B9 11% =IRR(B9:59,0.1) 20% 0 1 2 3 4 3 Project 1 4 Cost of capital 5 6 7 Year 8 9 Net cash flow 10 PV factor 11 PV of net cash flow 12 Cumulative PV 13 Net present value 14 15 IRR (Internal Rate of Return) 16 21 22 Project 2 23 Cost of capital 24 25 26 Year 27 28 Net cash flow 29 PV factor 30 PV of net cash flow 31 Cumulative PV 32 Net present value 33 34 IRR (Internal Rate of Return) 35 36 Project 3 37 Cost of capital 38 39 40 Year 41 42 Net cash flow 43 PV factor 44 PV of net cash flow 45 Cumulative PV 46 Net present value 47 (250,000) 75,000 75,000 75,000 100% 83% 69% 58% (250,000) 62.500 52.083 43,403 (250,000) (187,500) (135,417) (92,014) (67,901)| 50,000 48% 24,113 (67,901) 4% 20% 0 1 2 3 4 5 (75,000) 100% (75,000)| (75,000) 68,191 15,000 83% 12,500 (62.500) 25,000 50,000 69% 58% 17,361 28,935 (45,139) (16,204) 50,000 150,000 48% 40% 24,113 60.282 7,909 68,191

If you liked my answer thumbs up pls.