Individual Assignment wk 2

Session 2 Individual Assignment – IA2

NOTE: Read through the Crystal Ball resources in class (Course Content>> Crystal Ball Resources) and

Crystal Ball FAQ in (Discussions >> Crystal Ball Discussion) before attempting the IA2. Once you finish the

assignment the instructions in https://umgc.zendesk.com/hc/en-us/articles/360022921674-Download-

and-Upload-Files-from-Lab-Computers will help you save the file in your virtual lab as well as your local

hard drive for submission.

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.

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:

1. Enter data for cash inflow, cash outflow, and inflation rate.

2. Calculate net cash flow, discount value, and NPV (yellow highlighted cells) using the formulas

given (Figure 1).

3. Calculate totals (inflow, outflow, net flow, and NPV) using the given formula for SUM (yellow

highlighted cells in Row 8).

Figure 1 – Excel worksheet with formulas

4. Determine your assumption cells. In this case we are using cash inflow and inflation rate.

5. 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, no formulas or text.

a. To define the assumption cells for the cash inflow we 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).

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% +/-

.5%. With the normal distribution we can approximate that there will be a 68% chance that the inflation

rate will lie within .5% either side of the mean rate of 2%. Click here for a short video clip on Defining

Inflation Rate Assumption Cells

6. 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.

Figure 2 – Excel worksheet with CB assumptions/forecast cells defined

7. 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 Project A NPV.

Figure 3 Forecast chart for Project A NPV

Step through this exercise on your own using the data provided and then change the input cells

(assumptions) to see how it impacts the project’s NPV. Run the simulation again and compare the

forecast charts for the two scenarios.

After you review the assignment, make sure to:

1. Run simulation for given scenario;

2. Run simulation for alternate scenario

3. Show simulation data and assumption/forecast cells for both scenarios

4. Show your analysis/interpretation of simulation results

5. Once you have worked through this activity copy and paste the results of your CB simulation

alongside your spreadsheet and submit your Excel worksheet to your assignment folder using the given

data.