1. Starting with the spreadsheet model from Case 2.1 (or starting over, if you like), develop a spreadsheet model that lets Ewing see the implications of any set of approved projects. You should again use 0-1 values to indicate which products are approved. All of the capital expenditures in Table 2.2 should be replaced with triangular distributions, all independent of one another. The values in the table should be the most likely values, and each minimum and maximum for the distribution should be, respectively, 15% below and 30% above the most likely value. The NPVs should also be modeled with triangular distributions, using the values in the table as most likely values. However, their minimum and maximum values should be, respectively, 20% below and 15% above the most likely value. Cliff’s thinking is that costs and NPVs both tend to be on the optimistic side. Hence, he thinks it is best to have right skewness in the cost distributions and left skewness in the NPV distributions.

2. Choose at least three sets of 0-1 values that look promising in terms of total NPV and satisfying the various constraints: (1) the total three-year budget of $10 billion shouldn’t be exceeded; (2) no single-year budget of $4 billion should be exceeded; and (3) each functional area should have at least one project approved. Then use a RISKSIMTABLE function, along with lookup functions, so that each of these sets of 0-1 values can be simulated in a single @RISK run. The number of simulations will equal the number of sets of 0-1 values you want to try.

3. The results for each set of 0-1 values should include the distribution of total NPV and the probability that each budget is satisfied. For example, the results should include the fraction of iterations where total capital expenditures for the three year period are no greater than $10 billion.

4. (Optional) Use RISKOptimizer to find the optimal set of 0-1 values. Set it up so that the objective is to maximize the mean total NPV. There should be a probability constraint on each budget: the probability that capital expenditures are within the budget should be at least 0.95. There will also be the usual (non-probabilistic) constraints that each functional area should have at least one project approved. 5. Write your results in a concise memo to management. 

This is an extension of Case 2.1 from Chapter 2, so you should read that case first. It asks you to develop a spreadsheet model, using a 0-1 variable for each potential project, so that Cliff Erland, Manager for Project Development, can easily see the implications of approving any set of projects. Cliff would now like you to consider uncertainty in project expenditures and NPVs. The values given in Table 2.2 should now be considered best guesses, and probability distributions should be substituted for these. Cliff provides you with the following guidelines.