Question

Develop a real-world "time value of money" example and solution where a company must evaluate three project options at net present value (NPV). The current and future costs for each potential project should be staggered in time (less than a 10-year look-ahead). Descriptions of the three project solutions should all address the problem, but be "different", (thus the three possible solutions). Submit/post the problem in MS-Word, and post/submit the "solution" in MS-Excel.

Answer 1

Let’s consider the below mentioned real world example to evaluate the problem in hand:

Mr. Frank Specter owns a concession stand that sells tacos at a ball park. He has 3 years left on the contract with the ball park, and do not expect it to be renewed since the park will be replaced. The waiting time in lines have limited their sales and profit, so Mr. Specter developed three different project options to reduce the queue and increase profits. The different project options are:

1. Add a new window – investment $75000
2. Build a new stand – investment $125000
3. Rent a larger stand – investment $1000

Below is the information the cash flows across a 3 year period.

YEAR (t)	OPTION 1: ADD	OPTION 2: BUILD	OPTION 3: RENT	Comments
0	-75000	-125000	-1000	Note: Negative sign as investment made is cash outflow
1	44000	70000	12000	Note: Positive sign as considering the net cash flow after earnings and cost of each year in every project option
2	44000	70000	13000
3	44000	70000	14000

The discounting rate considered is 15%

CASH FLOW TECHNIQUE 1: NET PRESENT VALUE (NPV)

Net Present Value (NPV) considers Present value of future cash flows along with the initial investment.

NPV = Present value of Cash Inflows – Present Value of Cash Outflows

Minimum Acceptance criteria: NPV > 0

Ranking Criteria: Choose the option with highest NPV

Excel Formula for Calculating NPV:

=NPV(Discount rate, cash flows from year 1 to year 3)

Note: Excel does not consider the cash flow at year 0 hence we manually consider the investment after applying the above formula.

Solving our problem in excel using the formula mentioned:

YEAR	OPTION 1: ADD	OPTION 2: BUILD	OPTION 3: RENT
0	-75000	-125000	-1000
1	44000	70000	12000
2	44000	70000	13000
3	44000	70000	14000
NET PRESENT VALUE (NPV) for t1 to t3	₹ 1,00,461.91	₹ 1,59,825.76	₹ 29,469.88
Note: Does not consider cash flows at t0 therefore we do NPV for year 1 to 3 and do manually for t0
NPV by adding t0 value	₹ 25,461.91	₹ 34,825.76	₹ 28,469.88

Analysis:

Maximum NPV in Build, therefore we choose Option 2 Build.

Inflows are greater than outflows therefore we are in profit that is the surplus money will be the amount obtained after considering investment.

CASH FLOW TECHNIQUE 2: PROFITABILITY INDEX (PI)

PI =   

Minimum Acceptance criteria: PI > 1

Ranking Criteria: Choose the option with highest PI

Solving our problem in excel using the formula mentioned:

PROFITABILITY INDEX (PI)	OPTION 1: ADD	OPTION 2: BUILD	OPTION 3: RENT
Profitability Index = NPV from t1 to t3 (As year 0 has outflow therefore not considered) / initial investment	=100461.91/75000	=159825.76/125000	=29469.88/1000
	1.34	1.28	29.47

Analysis:

Maximum PI in Rent, therefore we choose Option 3 Rent.

CASH FLOW TECHNIQUE 3: INTERNAL RATE OF RETURN (IRR)

The IRR is the discount rate when the present value of cash inflows equals the original investment.

It is one of the most difficult cash flow techniques in all the techniques discussed, and is usually performed on a financial calculator or on computer using excel as the manual way of calculating IRR is a trial and error approach.

Minimum Acceptance criteria: IRR should exceed the discount rate considered

Ranking Criteria: Choose the option with highest IRR

Excel Formula for Calculating IRR:

=IRR(cash flows from year 0 to year 3)

Note: Initial investment is also considered but with a negative sign as mentioned in the table as it is a cash outflow.

Solving our problem in excel using the formula mentioned:

INTERNAL RATE OF RETURN (IRR) (considering all the cash flows from t0 to t3)	OPTION 1: ADD	OPTION 2: BUILD	OPTION 3: RENT
	35%	31%	1208%

Analysis:

All the options are above the discount rate of 15% considered in our problem, Maximum IRR is for option Rent, and therefore we choose Option 3 Rent.

Depending on the cash flow technique to be considered a project option can be considered to solve the problem of queue for Mr. Frank Specter's Taco stand.