Considering Uncertainty in Project Selection Decision Using Crystal
Contents
1. o sso onz 15 573 0 02 2009 3433 pas onn 8 487 oo Oo Total 742 333 360 000 382 333 T siose E E t 0 at the beginning of 2000 fF T ae formale _ I Cell D4 cel E4 Eurer Cal Es Emso Cell E6 1 1 13 G6 A A6 1999 copy to E7 E15 4 CellB17 Sum B4 B15 copy to C17 D17 F17 range shown in the chart that goes from 9 289 to 30 772 And as we have stated be fore as the level of uncertainty increases so does the risk CB provides considerable information about the forecast cell in addition to the fre quency chart including percentile information summary statistics a cumulative chart and a reverse cumulative chart For example to see the summary statistics for a forecast cell select View from the Forecast dialogue box toolbar and then select Statistics from the dropdown menu that appears The Statistics view for the frequency chart Figure 1 9 is illustrated in Figure 1 10 Figure 1 10 contains some interesting information Both the mean and median NPV resulting from the simulation are nicely positive and thus indicate a return above the hurdle rate of 13 percent 15 percent including inflation There are however sev eral negative outcomes those showing a return below the hurdle rate What is the like AENT 1 6 CONFRONTING UNCERTAINTY THE MANAGEMENT OF RISK e 25 Figure 1 9 Frequency chart of the simulation out put for net present v2. Assumption Name textbox at the top of the dialog box enter a descriptive label for example Cash Inflow 2002 Then enter the pessimistic most likely and Ca CellB7 Triangular Distribution x y Assumption Name cash Inflow 2002 Probability 35 000 47 250 gt 35 000 4 60 000 Min 35 000 Likeliest 50 000 Max 60 000 inputs assuming ae ee _ the triangular O Lancet ed distribution Figure 1 7 Crystal Ball 2000 dialog box for model 22 o Figure 1 8 Crystal Ball 2000 dialog box for model inputs assuming the normal distribution CHAPTER 1 THE WORLD OF PROJECT MANAGEMENT optimistic costs of 35 000 50 000 and 60 000 in the Min Likeliest and Max boxes respectively 6 Click on the OK button When you do this step note that the inflow in cell B7 au tomatically changes from the most likely entry or other number you might have en tered to the mean of the triangular distribution which is Min Likeliest Max 3 Now repeat steps 1 6 for the remaining cash inflow assumption cells cells B8 B15 Remember that the proper information to be entered is found in Table 1 2 When finished with the cash inflow cells assumption cells for the inflation values in column G need to be defined For these cells select the Normal distribution We decided earlier to use a 2 percent inflation rate plus or minus 1 percent Recall that the normal distribution is bell shaped and
3. EE ST EROUECT MANAGEMENT Figure 1 11 Cal culating the proba bility that the net present value of the PsychoCeramic Sciences Inc proj ect is equal to or greater than the firm s hurdle rate THE PROJECT PORTFOLIO PROCESS We could for instance alter the degree to which the inflow estimates are uncertain by expanding or contracting the degree to which optimistic and pessimistic estimates vary around the most likely estimate We could increase or decrease the level of inflation Simulation runs made with these changes provide us with the ability to examine just how sensitive the outcomes forecasts are to possible errors in the input data This al lows us to focus on the important risks and to ignore those that have little effect on our decisions We strongly recommend the User Manual for users of CB Crystal Ball 2000 User Manual 2000 The Project Portfolio Process PPP attempts to link the organization s projects directly to the goals and strategy of the organization This occurs not only in the project s initia tion and planning phases but also throughout the life cycle of the projects as they are managed and eventually brought to completion Thus the PPP is also a means for mon itoring and controlling the organization s strategic projects On occasion this will mean shutting down projects prior to their completion because their risks have become exces sive their costs have escalated beyond their expected be
4. 1 6 CONFRONTING UNCERTAINTY THE MANAGEMENT OF RISK e 19 Table 1 1 Single Point Estimates of the Cash Flows for PsychoCeramic Sciences Inc A B Cc D E F G 1 E Discount Net Present Inflation 2 Year Inflow Outflow Net Flow Factor Value Rate 3 A B Cc Wd k p Dx Disc Factor 4 2000 5o 125 000 125 000 1 0000 125 000 0 02 5 200 0 100 000 100 000 0 8696 86 957 0 02 6 2001 o 90 000 90 000 0 7561 68 053 0 02 7 2002 50000 0 50 000 0 6575 32 876 0 02 8 203 120 000 15 000 105 000 0 5718 60 034 0 02 9 204 115 000 0 0 4972 57 175 0 02 10 2005 105 000 15 000 0 4323 38 909 0 02 11 2006 0 3759 36 466 0 02 12 2007 0 3269 24 518 0 02 13 2008 0 2843 23 310 0 02 14 2009 0 2472 16 067 0 02 15 2009 35 000 0 2472 8 651 0 02 16 a 17 Total 759 000 360 000 17 997 18 19 t 0 at the beginning of 2000 20 21 Formulae 22 Cell D4 B4 C4 copy to D5 D15 23 Cell E4 1 1 13 02 0 24 Cell E5 1 1 13 02 1 25 Cell E6 1 1 13 02 A6 1999 copy to E7 E15 26 CellFA D4 E4 copy to F5 F15 27 Cell B17 Sum B4 B15 copy to C17 D17 F17 Therefore we will assume that the triangular distribution will give us a reasonably good fit for the inflow variables The hurdle rate of return is typically fixed by the firm so the only remain
5. alue of Psycho Ceramic Sciences Inc Project Figure 1 10 Sum mary statistics of the PsychoCeramic Sciences Inc simulation ve some ar Dollars gt infinity Certainty IMM 4 infinity lihood that this project will achieve a positive NPV and therefore produce a rate of re turn at or above the hurdle rate With CB the answer is easy Using the display shown in Figure 1 9 erase Infinity from the box in the lower left corner Type 0 or 1 in that box and press Enter Figure 1 9 now changes as shown in Figure 1 11 The boxes at the bottom of Figure 1 11 show that given our estimates and assumptions of the cash flows and the rate of inflation there is a 90 probability that the project will have an NPV between zero and infinity that is a rate of return at or above the 13 percent hurdle rate Even in this simple example the power of including uncertainty in project selection should be obvious Because a manager is always uncertain about the amount of uncer tainty it is also possible to examine various levels of uncertainty quite easily using CB Forecast Net Present Value of Project Statistic 1 000 Mean 10 772 Median 10 697 Mode Standard Deviation 7 716 Variance 59 538 051 Skewness 0 08 Kurtosis 3 03 Coeff of Variability 0 72 Range Minimum 11 164 Range Maximum 35 755 Range Width 46 919 Mean Std Error 244 00 26 CHAPTER 1 THE WORLD OF PROJECT MANAGEMENT EE
6. d inflows are as shown in Table 1 2 and include a minimum pessimistic estimate a most likely estimate and a maximum optimistic estimate In Chapter 5 Scheduling the Project we will deal in more de tail with the methods and meaning of making such estimates Both the beta and the triangular statistical distributions are well suited for modeling variables with these three parameters but fitting a beta distribution is complicated and not particularly intuitive
7. e project could yield a NPV in excess of 30 000 As you can see the amount of uncertainty increases as the width or range of the values in the fre quency diagram increases In other words there would be less uncertainty in the NPV of this project if the range of outcomes had been 2 000 15 000 as opposed to the You may wonder why we spend time with this kind of detail The reason is simple Once you have dealt with this kind of problem and it is common in such analyses you won t make this mistake in the real world where having such errors called to your attention may be quite painful 24 e CHAPTER 1 THE WORLD OF PROJECT MANAGEMENT Table 1 3 Three Point Estimate of Cash Flows and Inflation Rate for PsychoCeramic Sciences Inc All Assumption and Forecast Cells Defined ea Discount a Net Present Inflation Year Inflow Outflow Net Flow Factor Value Rate i p lt A B C D B C 1 1 k p x Disc Factor Seer 2000 0 125 000 125 000 1 125 000 0 02 2000 0 100 000 0 8696 86 957 0 02 2001 o 90 000 90 000 0 7561 68 053 er 2002 48 333 O 48 333 0 6575 31 780 0 02 2003 117 000 0 5718 58 319 0 02 2004 113 333 ol 113 333 o49m2 56 347 0 02 2005 103 000 0 4323 38 045 0 02 2006 95 000 o 95 000 0 3759 35 714 0 02 2007 88 333 23 973 0 02 2008 80 000 o swo oza 22 741 0 02 2009 63 000
8. es of the outcomes associated with a decision Risk profiles can be considered by the manager when considering a decision along with many other factors such as strategic concerns behavioral issues fit with the organization and so on In the following section using an example we have examined earlier we illustrate how Crystal Ball 2000 CB a widely used Excel Add In that is bundled with this book can be used to improve the PM s understanding of the risks associated with man aging projects Considering Uncertainty in Project Selection Decisions Reconsider the PsychoCeramic Sciences example we solved in the section devoted to finding the discounted cash flows associated with a project Setting this problem up on Excel is straightforward and the earlier solution is shown here for convenience as Table 1 1 We found that the project cleared the barrier of a 13 percent hurdle rate for acceptance The net cash flow over the project s life is just under 400 000 and dis counted at the hurdle rate plus 2 percent annual inflation the net present value of the cash flow is about 18 000 The rate of inflation is shown in a separate column because it is another uncertain variable that should be included in the risk analysis Assume that the expenditures in this example are fixed by contract with an outside vendor so that there is no uncertainty about the outflows there is of course uncer tainty about the inflows Suppose that the estimate
9. ing vari able is the rate of inflation that is included in finding the discount factor We have as sumed a 2 percent rate of inflation with a normal distribution plus or minus 1 percent i e 1 percent represents 3 standard deviations It is important to point out that approaches in which only the most likely esti mate of each variable is used are equivalent to assuming that the input data are known with certainty The major benefit of simulation is that it allows all possible values for each variable to be considered Just as the distribution of possible values for a variable is a better reflection of reality than the single most likely value the dis tribution of outcomes developed by simulation is a better forecast of an uncertain 20 o CHAPTER 1 THE WORLD OF PROJECT MANAGEMENT Table 1 2 Pessimistic Most Likely and Optimistic Estimates for Cash Inflows for PsychoCeramic Sciences Inc 2003 Minimum Most Likely Inflow 95 000 Inflow 35 000 50 000 120 000 2005 2006 2007 100 000 115 000 88 000 105 000 80 000 97 000 Maximum 60 000 136 000 125 000 116 000 108 000 100 000 75 000 90 000 2008 67 000 82 000 2009 51 000 65 000 91 000 73 000 38 000 2009 30 000 35 000 621 000 759 000 future reality than is a forecast of a single outcome In general preci
10. jects While there are actions that may be taken to reduce the uncertainty no actions of a PM can ever eliminate it Therefore in today s turbulent business environment effective decision making is pred icated on an ability to manage the ambiguity that arises while we operate in a world characterized by uncertain information One approach that is particularly useful in helping us understand the implications of uncertain information is risk analysis The essence of risk analysis is to make estimates or assumptions about the probability distributions associated with key parameters and variables and to use analytic decision models or Monte Carlo simulation models based on these distributions to evaluate the desirability of certain managerial decisions Real world problems are usually large enough that the use of analytic models is very difficult and time consuming With modern computer software simulation is not difficult A mathematical model of the situation is constructed and a simulation is run to de termine the model s outcomes under various scenarios The model is run or replicated repeatedly starting from a different point each time based on random choices of values from the probability distributions of the input variables Outputs of the model are used to construct statistical distributions of items of interest to decision makers such as costs profits completion dates or return on investment These distributions are the risk pro fil
11. lows with a triangular distribution Given the information in Table 1 2 the process of defining the assumption cells and entering the pessimistic and optimistic data is straightforward and involves six steps 847 000 1 Click on cell B7 to identify it as the relevant assumption cell 2 Select the menu option Cell at the top of the screen 3 From the dropdown menu that appears select Define Assumption CB s Distribu tion Gallery is now displayed as shown in Figure 1 6 Note it is important that the It is generally helpful for the reader to work the problem as we explain it If Crystal Ball has been installed on your computer but is not running select Tools and then Add ins from Excel s menu Next click on the CB checkbox and select OK If the CB Add In has not been installed on your computer consult your Excel manual and the CD ROM that accompanies this book to install it iT 1 6 CONFRONTING UNCERTAINTY THE MANAGEMENT OF RISK e 21 Cell C6 Distribution Gallery Figure 1 6 Crystal Ball 2000 Distri bution Gallery cell being defined as an assumption cell contain a numeric value If the cell is empty or contains a label an error message will be displayed during this step f 4 CB allows you to choose from a wide variety of probability distributions Double l click on the Triangular box to select it I 5 CB s Triangular Distribution dialog box is displayed as in Figure 1 7 In the L
12. mal Distribution STRESS ee PE Inflation Rate 1 6 CONFRONTING UNCERTAINTY THE MANAGEMENT OF RISK e 23 CB is an add in to Excel simply using Excel s copy and paste commands will not work Rather CB s own copy and paste commands must be used to copy the informa tion contained in both assumption and forecast cells The following steps are required 1 Place the cursor on cell G5 2 Enter the command Cell then click on Copy Data 3 Highlight the range G6 G14 4 Enter the command Cell then Paste Data Note that the year 2009 has two cash inflows both occurring at the end of the year Because we don t want to generate two different rates of inflation for 2009 the value generated in cell G14 will be used for both 2009 entries In cell G15 simply enter G14 Now we consider the forecast or outcome cell In this example we wish to find the net present value of the cash flows we have estimated The process of defining a forecast cell involves five steps 1 Click on the cell F17 to identify it as containing an outcome that interests us 2 Select the menu option Cell at the top of the screen 3 From the dropdown menu that appears select Define Forecast 4 CB s Define Forecast dialog box is now displayed In the Forecast Name textbox enter a descriptive name such as Net Present Value of Project Then enter a descrip tive label such as Dollars in the Units textbox 5 Click OK There is only one Fo
13. nefits another or a new proj ect does a better job of supporting the goals or any of a variety of similar reasons The steps in this process generally follow those described in Longman Sandahl and Speir 1999 and Englund and Graham 2000 Step 1 Establish a Project Council The main purpose of the project council is to establish and articulate a strategic direc tion for projects The council will also be responsible for allocating funds to those proj ects that support the organization s goals and controlling the allocation of resources and skills to the projects In addition to senior management other appropriate members of the project council include project managers of major projects the head of the Project Management Office if one exists particularly relevant general managers that is those 18 e CHAPTER 1 THE WORLD OF PROJECT MANAGEMENT B gt CHAPTER 1 THE WORLD OF PROJECT MANAGEMENT 1 6 CONFRONTING UNCERTAINTY THE MANAGEMENT OF RISK As we argue throughout this book effective project management requires an ability to deal with uncertainty The time required to complete a project the availability and costs of key resources the timing of solutions to technological problems a wide variety of macroeconomic variables the whims of a client the actions taken by competitors even the likelihood that the output of a project will perform as expected all these ex emplify the uncertainties encountered when managing pro
14. recast cell in this example but in other situations there may be several Use the same five steps to define each of them When you have completed all entries what was Table 1 1 is now changed and ap pears as Table 1 3 We are ready to simulate CB randomly selects a value for each assumption cell based on the probability distributions which we specified and then calculates the net present value of the cell values selected By repeating this process many times we can get a sense of the distribution of possible outcomes To simulate the model you have constructed 1000 times select the Run menu item from the toolbar at the top of the page In the dropdown box that appears select Run Preferences In the Run Preferences dialog box that appears enter 1 000 in the Maxi mum Number of Trials textbox and then click OK To perform the simulation select the Run menu item again and then Run from the dropdown menu CB summarizes the results of the simulation in the form of a frequency chart that changes as the simula tions are executed See the results of one such run in Figure 1 9 The frequency chart in Figure 1 9 is sometimes referred to as a risk profile While in this particular case our best guess of the NPV for this project would be perhaps 11 000 we see that there is considerable uncertainty associated with the project For example the frequency diagram shows the project could yield a NPV below 9 000 At the same time we see that this sam
15. se forecasts will be precisely wrong Using CB to run a Monte Carlo simulation requires us to define two types of cells in the Excel spreadsheet The cells that contain variables or parameters that we make as sumptions about are defined as assumption cells For the PsychoCeramic Sciences case these are the cells in Table 1 1 columns B and G the inflows and the rate of inflation respectively As noted above we assume that the rate of inflation is normally distrib uted with a mean of 2 percent and a standard deviation of 33 percent Likewise we as sume that yearly inflows can be modeled with a triangular distribution The cells that contain the outcomes or results we are interested in forecasting are called forecast cells In PsychoCeramic s case we want to predict the NPV of the project Hence cell F17 in Table 1 1 is defined as a forecast cell Each forecast cell typically con tains a formula that is dependent on one or more of the assumption cells Simulations may have many assumption and forecast cells but they must have at least one of each Before proceeding open Crystal Ball and make an Excel spreadsheet copy of Table 1 1 To illustrate the process of defining an assumption cell consider cell B7 the cash inflow estimate for 2002 We can see from Table 1 2 that the minimum expected cash inflow is 35 000 the most likely cash flow is 50 000 and the maximum is 60 000 Also remember that we decided to model all these f
16. that the mean of the distribution is its center point Also recall that the mean plus or minus three standard deviations includes 99 percent of the data The normal distribution dialog box Figure 1 8 calls for the distribution s mean and its standard deviation The mean will be 0 02 2 percent for all cells The standard deviation will be 0033 one third of 1 percent Note that Figure 1 8 displays only the first two decimal places of the standard deviation The actual standard deviation of 0033 is used by the program As you enter this data you will note that the distribution will show a mean of 2 percent and a range from 1 percent to 3 percent Notice that there are two cash inflows for the year 2000 but one of those occurs at the beginning of the year and the other at the end of the year The entry at the beginning of the year is not discounted so there is no need for an entry in G4 Some versions of CB insist on an entry however so go ahead and enter 2 percent with zero standard devia tion Move on to cell G5 in the Assumption Name textbox for the cell G5 enter Infla tion Rate Then enter 02 in the Mean textbox and 0033 in the Std Dev textbox While the rate of inflation could be entered in a similar fashion for the following years a more ef ficient approach is to copy the assumption cell G5 to G6 G14 Because the inflation rate is now a variable we must change the formulae for column E as noted in Table 1 3 Since Cell G5 Nor
Download Pdf Manuals
Related Search