Session 2 Individual Assignment - IA2

BEFORE YOU BEGIN:

Before attempting this assignment, make sure to:

1. Read this document

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

3. Make sure to watch the two videos (A and B) referred to in the assignment description (under Session 2 in Content). Although these two videos should suffice, you may also watch the following if you wish to: https://www.youtube.com/watch?v=Wpn9KY91nwg

Once you finish the assignmentthe instructions on pages 7 through 11 in the UMGC VDI Operations Guide (under Access to Microsoft Project and Crystal Ball in Content)will help you save the file in your virtual lab as well as your local hard drive for submission.

ASSIGNMENT DETAILS:

In this exercise we will calculate the Net Present Value (NPV) of a project cost 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

Table1 - Project A Cash Flow Analysis

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