Question

Forecast the Net Present Value of a project given the cash inflows and cash outflows of the project. Then use this information to simulate the uncertainty of forecasting a project’s NPV.

A likely scenario might be:

• Project A is a multi-year project; it begins on January 1, 2011 and is scheduled to end on December 31, 2014 (fixed cost is $215,000)
• The cash outflow for Project A is estimated at $100,000 at the beginning in the first year of the project, $50,000 at the end of the year, $50,000 in 2012, and a final cash payment of $15,000 in 2014.
• The cash inflow for Project A is estimated at $0 for the first year, $25,000 in 2012, $120,000 in 2013, and finally, $200,000 in 2014.
• The company desires a 12% return rate on their investment to consider the project. The company also believes that inflation will remain constant at 2% per year.

Given this information we can determine the NPV of Project A using a simple Excel spreadsheet. We can then use Crystal Ball to simulate the uncertainty associated with forecasting the NPV of Project A. Table 1 is an example of the spreadsheet, or Discounted Cash Flow model, developed to calculate Project A’s NPV. The Excel spreadsheet (Figure 1) contains the actual data and formulas used for this exercise.

Project A
Year	Inflow	Outflow	Net Flow	Discount Factor	Net Present Value	Inflation Rate
*2011	$0	$100,000				0.02
2011	$0	$50,000				0.02
2012	$25,000	$50,000				0.02
2013	$120,000	$0				0.02
2014	$200,000	$15,000				0.02
Total

Table 1 – Project A Cash Flow Analysis

(a). Complete Table 1 to calculate Project A’s NPV. The net cash flow of Project A is calculated by taking the total of all years’ net flow, and when discounted at the rate of 12% (required rate of return for project selection) plus the annual inflation rate of 2%, the net present value of the project’s cash flow can be estimated. So, at first glance, the project would seem to be a good candidate for selection. But there are uncertainties to this scenario. What if Project A does not generate the cash inflows estimated here, or at the time the inflows are expected? Perhaps the annual inflation rate is 3% rather than 2%. We can use Crystal Ball to simulate the risk, or uncertainty, involved in using NPV for project selection.

Answer 1

	Inflation rate 2%
	Calculation of cost capital = 1+r = (1+0.12)(1+0.02) >>> r = 14.24%
	Year	Inflows	Outflows	Net flow	Discounted Factor 14.24%	Net Present Value	Inflation rate
	2011	$                         -	$     1,00,000.00	$     -1,00,000.00	1.00	-1,00,000.00	0.02
	2011	$                         -	$         50,000.00	$         -50,000.00	1.00	-50,000.00	0.02
	2012	$          25,000.00	$         50,000.00	$         -25,000.00	0.88	-21,883.75	0.02
	2013	$      1,20,000.00	$                        -	$       1,20,000.00	0.77	91,948.54	0.02
	2014	$      2,00,000.00	$         15,000.00	$       1,85,000.00	0.67	1,24,084.39	0.02
					NPV	44,149.18
	Inflation rate 3%
	Calculation of cost capital = 1+r = (1+0.12)(1+0.03) >>> r = 15.36%
	Year	Inflows	Outflows	Net flow	Discounted Factor 15.36%	Net Present Value	Inflation rate
	2011	$                         -	$     1,00,000.00	$     -1,00,000.00	1.00	-1,00,000.00	0.03
	2011	$                         -	$         50,000.00	$         -50,000.00	1.00	-50,000.00	0.03
	2012	$          25,000.00	$         50,000.00	$         -25,000.00	0.87	-21,671.29	0.03
	2013	$      1,20,000.00	$                        -	$       1,20,000.00	0.75	90,171.80	0.03
	2014	$      2,00,000.00	$         15,000.00	$       1,85,000.00	0.65	1,20,505.26	0.03
					NPV	39,005.77