Posted: September 15th, 2015
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:
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 (attached) 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 |
(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.
Crystal Ball allows us to view Project A’s NPV in ranges rather than a single value as seen in the single point value (Total NPV) in the spreadsheet. To determine this range of values we have to consider the variability of certain inputs to our cash flow model. It is likely that cash inflows will not be a fixed amount throughout the project, but we know that if we are dealing with a fixed price contract, the cash outflows are fixed in the years indicated in the spreadsheet. Another potential variability in the model is the inflation rate; it may also change during the project. These variables will be defined as ‘assumptions’ in Crystal Ball, because we are making the assumptions about the values for cash inflows and the inflation rate. The total NPV for Project A is what we want to ‘forecast’.
Steps to develop your Discounted Cash Flow Model in Excel and run Crystal Ball simulation:
Looking for the best essay writer? Click below to have a customized paper written as per your requirements.
Place an order in 3 easy steps. Takes less than 5 mins.