III. Plans Based on Uncertain Inputs: Functions of Random Variables Most planning under uncertainty involves more complicated processes than the game-board spinners and dice discussed earlier. Typically spreadsheet models of real-world situations have many uncertainties, filtering through many formulas, resulting in a number of critical outputs of interest. Mathematicians refer to something with numeric inputs and outputs as a function. When some of the inputs are uncertain, it is called a function of random variables. However, few people understand this term in the workplace, so I recommend the term "spreadsheet with uncertain inputs". Come to think of it, virtually no one is certain about the numbers they plug into their models, so I guess all spreadsheets are functions of random variables. In this section we will examine a simple business plan with uncertain inputs. Monte Carlo simulation will allow us to make important observations not visible to the naked eye. An e-Commerce Business Imagine that you have come up with a new e-Commerce service for which you can charge $1. There is no marginal cost of providing the service. However, a server facility must be constructed, which will require capital. Specifically, $0.80 of up-front investment is required for each unit of monthly capacity. Sadly the new service is a fad expected to be popular for only one month. Demand for your one month of business is uncertain, but your best guess is 2,000,000 transactions, with a 95% confidence interval of between 1,000,000 and 3,000,000 transactions. Therefore, planning for the average case, you intend to set up a facility with a capacity of 2,000,000 transactions. This will require an investment of $1,600,000. This is reflected in the spreadsheet PLAN.xls shown on the right. Note that there are three types of numeric input cells in this model. The Inputs: Decisions: Numbers you can decide. Certainties: Things you know in advance but can't decide Uncertainties: Things you don't know in advance and can't decide The average demand (2,000,000) has been plugged into cell B8, which results in a profit of $400,000 in cell B16. Before proceeding to Tutorial 3a, try Puzzle 3. PUZZLE 3: Imagine repeating this business situation thousands of times, while choosing demand randomly with a mean of 2,000,000 and a 95% confidence interval of ± 1,000,000. Then $400,000 is: A reasonable estimate of the average profit you would expect. A reasonable estimate of the average profit but misses the potential variation in profit. None of the above (specify) Tutorial 3a - Simulating Profit of the e-Commerce Plan Make sure that XLSim is loaded before proceeding. Open the file PLAN.xls and investigate the formulas, which are annotated on the right side of the sheet. We will model uncertain demand as a normal distribution. Since the average or best guess in this case is 2,000,000, that will be the mean of the distribution. Because we are 95% confident that demand is within 1,000,000 either side of 2,000,000, we will use a standard deviation of 500,000 (see section II.) Because this model has been previously set up you can simply use the Simulate Thaw command to reactivate the random number generator in cell B8. However, to accomplish this from scratch as you will need to do with your own models, place the cursor in cell B8 and click on the function wizard . The function menu should appear as shown below. Select Statistical from the left hand menu and gen_Normal from the right hand menu as shown, then click OK. Fill in a mean of 2000000 and standard deviation of 500000 then click OK. Your worksheet should appear as shown below. Press the F9 key a few times to make sure the random number generator is working. This is a great way to get an intuitive feeling of the uncertainty in inherent in your business plan. Invoke the Simulate, Simulation command. The Simulation Settings dialog box will appear. Click the Add Outputs button. Specify cell B16 as the output cell and "Profit" as the output name as shown below, then click OK.  Specify the number of trials on the Simulation Settings box, then click OK. The statistics of the simulation appear in Simstats.xls. This indicates that the average profit is only around $200,000, roughly half the profit of $400,000 associated with the average demand of 2,000,000 units. What happened? The histogram helps explain what is going on. Create a histogram of profit with 5 bins and zero decimals as shown  below. This clearly shows that if demand were to deviate below the average of 2,000,000, that profit would decrease below $400,000. On the other hand, if demand were to deviate above the average of 2,000,000 there is no corresponding increase in profit because of the limited capacity of your server. Thus any demand of 2,000,000 or more results in a profit of exactly $400,000. This is evident as the tall bar at the right of the histogram. What's the point? The average demand of 2,000,000 resulted in a profit that was NOT the average profit. The answer to Puzzle 3 is c, $400,000 is the maximum possible profit, not the average. More generally we can say that: Average values of uncertain inputs don't always* result in average outputs. This is the strong form of The Flaw of Averages In the example above the average output was lower than that associated with the average input, but there are examples where it goes the other way. This is subject of what is known by statisticians as Jensen's Inequality. * The only time that average inputs are guaranteed to result in average outputs is when the spreadsheet model is linear in all uncertain variables. Even if you know what linear means, you would be hard pressed to tell if a large spreadsheet model had this property. The best advice is to steer clear of average inputs, or point estimates, as they are called euphemistically. Take a look at the cumulative graph of profit as shown below. Notice that there is just over a 20% chance of losing money. Also, there is a 5% chance of losing about $400,000. That is, the Value at Risk at 5% is $400,000. This can also be confirmed in the percentiles section of Simstats.xls. Save the file with the gen_Normal formula in the demand cell, as you will use it in the next tutorial. A Sobering Example of the Flaw of Averages taken from Dr. Savage's INSIGHT.xla. Consider the state of a drunk, wandering around on a busy highway. His average position is the centerline, so........ (Illustration from "INSIGHT.xla: Business Analysis Software for Microsoft Excel," 1st edition, by S.L. Savage, copyright 1998. Reproduced with permission of Brooks/Cole Publishing, a division of International Thomson Publishing. FAX 800-730-2215.)   e-Commerce example with Optimal Investment In the plan discussed above, the profit based on average demand was $400,000, while the average profit turned out to be only about $200,000. Furthermore there was a 5% chance of losing $400,000. This hardly looks as attractive as it did at first. But can we do better? The answer is yes. By experimenting with different amounts to invest we will find that there is an optimal strategy. In tutorial 3b you will use parameterized simulation to run 5 different experiments for different investment amounts. Before proceeding to Tutorial 3b, try Puzzle 4. PUZZLE 4: Previously you planned to invest $1,600,000 because this provided a capacity equal to average demand. Should you increase or decrease your investment, or leave it the same? Tutorial 3b - Using Parameterized Simulation to Optimize Your Investment Make sure that your simulation software is loaded and retrieve Plan.xls. Press the F9 key a few times to check that the random number generator in cell B8 is functioning. Next we will place 5 investment amounts to test in blank cells, for example, F5:F9. Try $1,000,000, $1,200,000, $1,400,000, $1,600,000 and $1,800,000. Invoke the Simulate, Parameterized Sim command. The Parameterized Simulation Settings dialog box will appear. Fill in the appropriate values as shown below. B16, Profit, is the output cell. Specify the number of trials. The Seed, discussed in more detail below should be set to any positive integer. B4, the amount invested is the parameter to be experimented with. The suggested Parameter Name is Investment. The Parameter Values are in F5:F9. Click OK. The simulation results shown below will vary depending on what seed you used. If you used the number 2 and 1000 trials, you should get identical results. Notice that the maximum average profit of $255,000 occurs at an investment of $1,200,000. This is more than 25% better than the average profit associated with the original investment of $1,600,000. But this is less than half the story as you will see in the next step. Invoke the Simulate, Common Graphs command. Click Select All on the Outputs tab so that your graph will include all the possible investment levels. Then move to the Series tab and click the Series Graph button. You should see the graph below. The pink line shows the average profit for each investment level, while the dark blue line shows the 95th percentile. But the big news is the yellow line that shows the 5th percentile, or equivalently the 5% Value at Risk. This shows that at the original investment level of $1,600,000 there was a 5% chance of losing over $400,000. By reducing the investment to $1,200,000 we not only increased average profit, we reduced the 5% VaR to less than $50,000! Look back at the Percentiles section of the Statistics results sheet to see the VaR values at different levels for each of the investments. Summary When the average of an uncertain number is plugged into a spreadsheet model, it may not result in the average output of the model. I call this the strong form of the flaw of averages. See chapter 3 of INSIGHT.xla for additional examples. The effect of potential managerial decisions on the distribution of outputs may often be determined by running a parameterized simulation.

Introduction

Section I Uncertain Numbers: Random Variables

Section II Combining Uncertain Numbers: Diversification

Section III Plans Based on Uncertain Inputs: Functions of Random Variables

Section IV Uncertain Numbers that Depend on Other Uncertain Numbers: Statistical Dependence

Command Reference

References

Acknowledge- ments