In a sense, simulation does for uncertainty what the light bulb does for darkness. It does not eliminate uncertainty, but illuminates it. And just as light bulbs require a dependable standardized source of electricity, simulations require a trustworthy and believable source of probability distributions. In the previous sections of this tutorial, we have described how to generate the appropriate distributions for each particular example. This is a bit like teaching you how to generate different voltages of electricity for different kinds of light bulbs. Most people just want to flip on the switch. With this analogy in mind, the field of Probability Management is evolving to do for probability what the power grid does for electricity. It allows people with a knowledge of probability to generate distributions for use by a larger audience. Further, it enables simulation results to be consolidated across organizations much like numbers in a consolidated financial statement. To get a quick overview of Probability Management click here.
In the late 90's I coined the term Blitzogram to describe an interactive histogram driven by lightning fast simulation, which would yield probabilistic results before the user's finger had left the <Enter> key. It took several years before a combination of computer speed and software advances made this a reality. In 2006, Frontline Systems introduced the Risk Solver: the first commercially available software to bring interactive simulation to the spreadsheet. This truly was a new light bulb for illuminating uncertainty. In 2008, AnalyCorp, in conjunction with Oracle Corp., SAS Institute, and Frontline Systems, developed the DIST data type which encapsulates a thousand or more Monte Carlo samples into a single XML string. This provides a compact and modular approach to storing probability distributions. When properly generated, they are additive, in the sense that if DISTX and DISTY represent uncertain numbers X, and Y, then DISTX+DISTY represents the sum of X and Y. For more details see ProbabilityManagement.org. XLSim 3.0 supports interactive simulation in Excel using DISTs, as does Frontline Systems more general and powerful Risk Solver. DISTs may be shared between users of both of these applications, playing the role of a standard current for the power grid. To get a quick feel for this new simulation environment, load XLSim 3 then open Portfolio D.xls It makes use of some special add-in functions to perform interactive simulation in Excel. These functions are described further below, but for now lets play.
This interactive simulation describes the famous risk return model of Harry Markowitz, which ushered in Modern Portfolio Theory in the 1950s.You must activate the model with the Activate button 
. This extracts the DST formulas from comment fields, whereupon, the model will interactively simulate on every re-calc.To return the model to the inactive state, use the Deactivate button 
, whereupon the DST formulas will again be stored as comments, with the last numerical values pasted into the cells. In general, models should be deactivated before being migrated to another computer, whereupon they may be reactivated and saved. Note, for those familiar with XLSim 2 and earlier, Activate and Deactivate correspond to the Thaw and Freeze commands.
The main elements of the model are as follows:
1. The Distribution of future relative asset values are stored as DISTs of 1,000 Monte Carlo trials in cells D5:J5. Double click in one of these cells to see its contents as shown below (in Excel 2003 and earlier you merely need to place the cursor in the cell). The header of the DIST has human readable information, while the trials, which follow the ">" symbol, require computer interpretation. Note that the Return of an asset is its future relative value minus 100%. That is, if the value in the future were 106% of today's value, the return would be 6%.
2. The Portfolio Weights in cells E8:J8 may be changed to reflect the desired proportion of each asset in the portfolio. Note that if the sum of these is less than 100%, the remainder stays in Cash in cell D8. If the sum is greater than 100%, you will have a negative value for cash, corresponding to a loan or some other type of leverage. Each time one of these numbers is changed, you are instantly running a 1,000 trial Monte Carlo simulation. All 1,000 samples of each of the Asset DISTs are run through the model, yielding the distribution of future values of the portfolio as an output DIST, as described next.
3. The Portfolio DIST in cell B11 is the output of the model. It is calculated by =DstSumProduct(Portfolio,Assets,"Portfolio Value"), where Portfolio refers to the range D8:J8, and Assets to the range D5:J5. The final argument in quotes is an optional name for the DIST. You will see the elements of the Portfolio DIST change through interactive simulation as you change the Portfolio Weights.
4. The Risk/Return graph interactively displays the risk (measured as variance), the average or mean return of the original assets (blue dots), and the chosen portfolio (green dot). The yellow line is the efficient frontier, displaying the lowest possible risk for each level of average return. The calculations are based on the Variance and Average of the corresponding DISTs, which are calculated as Var(Dst(DIST)) and Average(Dst(DIST)) respectively.
5. The Blitzogram displays the distribution of returns of the chosen portfolio. The calculations are stored in columns R and S.
6. The Blitzometer is an alternate display of the distribution of portfolio returns. It allows the user to focus on a few key aspects of the distribution. The settings to the right are described below.
In the discussion below any cell in column D, such as D4, is assumed to contain a DIST, whereas a cell in column N contains a number.
The =Dst(DIST) function converts a DIST (distribution string as described above) to an Excel range.
=Dst(D4) will return the first number in the DIST in cell D4. Entered as an array function in cells N1:N1000 as {=Dst(D4)} it will return the first 1,000 elements of the DIST.
=Average(Dst(D4)) returns the average value of the DIST, where =Average is the standard Excel function. =Sum(Dst(D4)) returns the sum of the elements of the DIST, and so on for most Excel fomulas when the arguments are not ranges such as A1:B5. Range arguments will be discussed below.
DstCreate has two functions:
Formulas using range arguments require special Dst formulas, which will also be generated automatically using the 
button.
For example, =SUM(D1:D4) will be transformed into =DstSum(D1:D4), and will return a DIST whose elements are the sum of the elements of the four input DISTs.
button.Write a normal Excel function with DISTs as arguments, then while leaving the cursor in that cell click . The formula will be appropriately transformed.
For example =Average(D4) will be transformed to =Average(Dst(D4)). =D1+D2 will be transformed to {=DstCreate(Dst(D1))+Dst(D2))}.
For a complete list of how formulas are transformed using the button, click here.
Traditional Simulation III covers a traditional simulation application of XLSim to illuminate the Flaw of Averages in an e-Commerce setting. This tutorial will show you how to create an interactive version of this analysis using the DIST data type. For more detail of this example, see Traditional Simulation III.
Open the file PLAN.xls and investigate the formulas, which are discussed in Traditional Simulation III.
1. 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 Activate command
to reactivate the random number generator in cell B8.
We start by making a DIST to model demand. Invoke the Simulate, Simulation command. The Simulation Settings dialog box will appear. Note the new field, DIST Origin, in which you can make comments about who generated the DIST, what it signifies, etc.
2. Click Add Outputs. For the Cells field, choose the cell you wish to simulate (Demand, in this case). For the Names field, you can choose a cell with a descriptive name for the cell you are simulating, or type in a name yourself. For DIST Type, select Single, for single precision, then click OK to go back to Simulation Settings.
3. Specify 1000 trials on the Simulation Settings box, then click OK.
4. The statistics of the simulation appear in Simstats.xls. Note that unlike before, we now have a cell that contains the DIST of the simulation for demand we just performed.
5. Select the cell containing the DIST (the cell below DEMAND and one to the right of DIST). Type CTRL+C to copy this cell, go back to Plan.xls, click on the cell for Demand (B8), then type CTRL+V to paste the DIST into that cell. Your spreadsheet should now look like this:
XLSim 3 uses special formulas that recognize DISTs. They begin with "Dst", and are listed in the Command Reference. The two most important of these are =DST(Dist) and =DstCreate(Array). In its standard form, =DST(Dist) returns the first trial of the DIST. In its array form, {=Dst(Dist)}, it returns all the trials of a DIST. =DstCreate(Array) returns a DIST of that Array. You do not need to learn how to use array formulas in Excel at this point because XLSim will do it for you automatically when needed, but it is an Excel skill worth developing on your own eventually.
Updating a spreadsheet to work with DSTs is extremely easy to do with XL Sim 3. On the DIST toolbar, the button can be used to update all the formulas an output cell depends on. The following steps show how easy it is to update PLAN.xls to work with DISTs once we have the demand DIST from part (a).
1. Replace the existing cell for demand, B8, with the DIST for demand created in part (a).
2. Highlight the output cell. In this case, that is PROFIT, cell B16.
3. On the DIST toolbar, click the DIST Update button
4. Instantly you will notice the cells for Sales, Revenue, and Profit have all been converted to DISTs, and your spreadsheet should look similar to this now:
5. By updating the formula in the profit cell, all the formulas that are involved in creating the Profit cell are updated as well. Let's take a look at how the formulas changed with a single click:
| Cell Name | Cell Location | Old Formula | Updated Formula |
|---|---|---|---|
| Profit | B16 | =IF(DEMAND<=CAPACITY,DEMAND,CAPACITY) | {=DstCreate(IF(Dst(B8)<=B6, Dst(B8), B6),"=IF(B8 SmallerThan =B6, B8, B6)")} |
| Revenue | B14 | SALES*PRICE | {=DstCreate(Dst(B12)*B10,"=B12*B10")} |
| Sales | B12 | REVENUE - INVESTMENT | {=DstCreate(Dst(B14)-B4,"=B14-B4")} |
1. As you can see, the DISTs extend way out to the right. For aesthetic reasons it is useful to truncate the display by typing a blank just to the right of the DIST cell. Put the cursor in cell C8, press the space bar, then press <Enter> and you will have hidden all but the beginning of the Demand DIST. Do this in cells C12, C14, and C16 as well.
2. In cell B19, we will display the average profit. Enter "Average Profit" in B18, and =Average(Dst(PROFIT)) in cell B19. It should display $200,496.
3. Next we will create a Blitzogram of Profit. Place the cursor in cell B16, which contains the DIST of profit, then click the Blitzogram button, 
. A dialog box will appear. Click in cell B21, or any other empty cell and click OK
When the graph appears, drag it and expand it. The model should appear as shown below.
4. Interact with the model by changing the investment or the price per unit. After each keystroke, 1,000 trials are run, yielding a new average profit and Blitzogram. Note that the bin values for the Blitzogram in B23:33 will change dynamically as you experiment with the model. It is often useful to freeze the bins to get a better comparative view of the distribution as you interact with the model. To do this, copy cells B23:33, and use Paste Special Values to copy the values over the original formulas.
5. Experiment with the Blitzometer, by clicking in cell B16 and clicking 
. For more on the Blitzometer see the command reference.
Load and activate Plan D Param.xls as shown below.This file uses the standard Excel Data Table to rerun the model for each of six investment levels. The line graph shows the average profit and high and low percentiles of profit for each level. Note that investing less than $1,600,000 results in both higher average profit and less downside risk. Experiment with the price in cell B10, and the model will automatically run a 1,000 trial simulation for each investment level. You may also change the percentiles in cell G3 and H3.
