NPV SIMULATION
_Calculations.xlsx__PMAN635IA-WORKING_AUTOSAVED x
Sheet1
| Project A | ||||||||||||||||||||||||||
| Year | Inflow | Outflow | Net | Discount | Inflation | |||||||||||||||||||||
| Flow | Factor | Present | Rate | |||||||||||||||||||||||
| Value | ||||||||||||||||||||||||||
| * | 2011 | $0 | $150,000 | ($150,000) | 1.00 | ($150,000.00) | 0.02 | |||||||||||||||||||
| $100,000 | ($100,000) | 0.893 | ($89,285.71) | |||||||||||||||||||||||
| 2012 | $25,000 | $75,000 | ($50,000) | 0.797 | ($39,859.69) | |||||||||||||||||||||
| 2013 | $120,000 | $95,000 | 0.712 | $67,619.12 | ||||||||||||||||||||||
| 2014 | $400,000 | 0.636 | $254,207.23 | |||||||||||||||||||||||
| Total | $545,000 | $350,000 | $195,000 | $42,680.95 | ||||||||||||||||||||||
| Discount Rate | 10% |
Sheet2
Project A
Year Inflow Outflow Net Discount Net Inflation
Flow Factor Present Rate
Value
$0 $150,000 ($150,000) 1.00 ($150,000.00) 0.02
2011 $0 $100,000 ($100,000) 0.893 ($89,285.71) 0.02
2012 $25,000 $75,000 ($50,000) 0.797 ($39,859.69) 0.02
2013 $120,000 $25,000 $95,000 0.712 $67,619.12 0.02
2014 $400,000 $0 $400,000 0.636 $254,207.23 0.02
Total $545,000 $350,000 $195,000 $42,680.95
Discount Rate 10%
PMAN 635
–
Fall 2020
Session 2 Individual Assignment – IA2
Name:
Forecast the Net Present Value (NPV) 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.
Given the following scenario:
· Project A is a multi-year project; it begins on January
1
,
2011
and is scheduled to end on December 31,
2014
.
· The cash outflow for Project A is estimated at
$150,000
at the beginning of the first year of the project,
$100,000
at the end of the first year,
$75,000
at the end of
2012
, and a final cash payment of
$25,000
at the end of
2013
. Outflows are based on a fixed price vendor quote.
· The cash inflow for Project A is estimated at
$0
for the first year, $25,000 in 2012,
$120,000
at the end of 2013, and finally,
$400,000
in 2014. Inflows occur at the end of the year. Inflows are based on sales forecasts from the Marketing Department.
· The company requires a projected 10% return rate on their investment to consider a 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.
Project A
Year
Inflow
Outflow
Net
Flow
Discount
Factor
Net
Present
Value
Inflation
Rate
*2011
0.02
$0
0.02
0.02
$25,000
0.02
0.02
Total
Table 1 – Project A Cash Flow Analysis
*beginning of the year
Complete Table 1 to calculate Project A’s Total NPV. Attach all calculations.
Answer I: Total NPV = $42,680.95
At first glance, the project might 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 if the costs are greater than 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 previous 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:
1. Using the same table you used for part a, identify your assumption cells. In this case we are using cash inflow and inflation rate. Outflows are not assumptions because they are based on a fixed price contract.
2. Define the assumptions using Crystal Ball. The assumption cells are input cells that contain values we are unsure of; the independent variable of the problem we are trying to solve. The cell must contain simple numeric values, not formulas or text.
a. To define the assumption cells for the cash inflow, we will use the Triangular distribution. This distribution is used when we know very little about the input values, but we can roughly estimate the minimum, maximum, and most likely values – thus creating a triangle. Crystal Ball will also calculate the minimum and maximum values automatically based on your most likely estimate which is the value you are using in the spreadsheet.
Click here for a short video clip on Defining Cash Inflow Assumption cells
(Note: There is no audio with these video clips). Use the following table for cash inflows:
|
Minimum Inflow |
Most Likely Inflow |
Maximum Inflow |
||||
| – | ||||||
|
$5,000 |
$25,000 |
$35,000 |
||||
|
$70,000 |
$120,000 | $150,000 | ||||
|
$200,000 |
$400,000 |
$450,000 |
Table 2: Maximum, Minimum and Most Likely Values for Inflows
b. Define assumption cells for the inflation rate assuming a normal distribution. We use the normal distribution model. The normal distribution assumes that the inflation rate will most likely remain at 2% on average, but could fluctuate either up or down at the same rate and that it will most likely be closer to 2% than farther out in either direction (0% or 4%. We’ll use an inflation rate of 2% +/- 1%. With the normal distribution we can approximate that there will be a 68% chance that the inflation rate will lie within 1% either side of the mean rate of 2%.
Click here for a short video clip on Defining Inflation Rate Assumption Cells
c. Define forecast cell. Here we want to forecast the NPV for Project A.
Click here to view a short video clip on defining a forecast cell
Forecast cells usually contain formulas that refer to one of more of the assumptions cells defined earlier.
3. Now that you have your assumption and forecast cells defined you are ready to run your simulation. Select START on the Run tab (Excel Ribbon). Crystal Ball will run through 1000 trials (or the number you set in your preferences) and return a summary of these trials in the form of a frequency chart (forecast chart) that displays the number (frequency) of values occurring in a given interval. The forecast chart (Figure 3) illustrates the frequency, the probability, and the certainty of a range of values for an investment’s NPV (note, this is not the investment defined by Table 2).
4. Step through this exercise on your own using the data provided. Post the results of your simulation in your excel file. What is your mean NPV?
Answer II: Mean NPV = $8,338
5. What is the probability that your NPV will be positive? To solve this problem, you might want to review the topic “Project Selections” in the “Sample Problems” forum.
Answer III: P (NPV>0) =61%
Post this document together with the excel file with your calculations and the results of your simulation to the assignments folder.
Given Scenario:
|
Project A |
|||||||||||||||
|
Year |
Inflow |
Outflow |
Net Flow |
Discount factor |
Net Present Value |
Inflation Rate |
|||||||||
| $0 | $100,000 |
-$100,000 |
1 | ||||||||||||
|
$50,000 |
-$50,000 |
0.877192982 |
-$43,860 |
||||||||||||
|
-$25,000 |
0.769467528 |
-$19,237 |
|||||||||||||
|
0.674971516 |
$80,997 |
||||||||||||||
|
$15,000 |
$185,000 |
0.592080277 |
$109,535 |
||||||||||||
|
$345,000 |
$215,000 |
$130,000 |
$27,435 |
Alternative Input data scenario:
|
$101,246 |
|
|
$230,000 |
$127,297 |
|
$430,000 |
$84,683 |