1. Starting with the model from Case 2.2, you should replace all of the inputs with probability distributions. Specifically, based on their knowledge of the new product and the market, Jim and Catherine have suggested using the distributions with the parameters listed in the file C10_04.xlsx. Note that the most likely values for the triangular distributions and the means for the normal distributions are the values from Case 2.2.

2. Jim and Catherine are again interested in adjusting the 0-1 variables from Case 2.2 so that they can examine different scenarios. However, they realize that the number of combinations of the two 0-1 variables they have control of, low-end versus high-end and mildly aggressive marketing versus very aggressive, is a small number, 2×2 = 4. Therefore, they want you to use a RISKSIMTABLE function, with an index from 1 to 4, so that a single @RISK run with 4 simulations can be made. You will have to use lookup functions so that the two 0-1 variables change appropriately as the index of RISKSIMTABLE varies from 1 to 4. As for the third 0-1 variable, whether the competition will introduce a competing product, they want you to model this probabilistically, using a RISKBERNOULLI function with parameter 0.3. This simply indicates that there is a 30% chance of a competing product.

3. Once you have the model set up correctly, you should designate the NPV of eTech sales as an @RISK output cell. You should also designate @RISK output ranges for each of the annual cash flow series requested in Case 2.2: net revenues from existing products, marketing costs for the ePlayerX, and so on. Then you should run @RISK with at least 1000 iterations for each of 4 simulations. In your memo to management, you can decide which results appear most interesting and should be reported. However, Jim and Catherine are especially interested in which of the uncertain inputs have the most effect on the bottom line, NPV of eTech sales, so you should definitely include one or more tornado graphs. Of course, they also want some help in deciding which of the four simulations—that is, which of the four possible eTech strategies—is best in terms of the NPV output.

4. Although Jim and Catherine are pleased with the simulation model, a big improvement over the deterministic model from Chapter 2, they still think something is missing: correlations. They have estimated correlations between several inputs, also listed in the file C10_04.xlsx, and they would like you to run the analysis again with these correlations included. Does this make any difference in the results, or was this extra complexity unnecessary?

This is an extension of Case 2.2 from Chapter 2, so you should read that case first. It asks you to develop a spreadsheet model for Jim Simons, VP for Production at eTech, and Catherine Dolans, VP for Marketing, so that they can better understand the implications of their decisions regarding the company’s new product ePlayerX. The model is supposed to contain 0-1 variables that Jim and Catherine can adjust to see (1) the effects of the ePlayerX product type (low-end or high-end), (2) the eTech marketing strategy for the ePlayerX (mildly aggressive or very aggressive), and (3) whether another company introduces a competing product to the market. The model from Case 2.2 has provided Jim and Catherine plenty of insight, but they both agree that it lacks a very important ingredient: uncertainty. Therefore, they have asked you to introduce uncertainty explicitly into the model and to quantify its effects. Specifically, you have been asked to do the following and then write your findings in a memo to management.