GRAPHICAL TOOLS IN SIMETAR FOR ANALYZING SIMULATION RESULTS

Simetar© provides nine graphics tools for displaying the results of stochastic simulations and for analysis of data. These graphics tools utilize the charting capabilities of Excel so all charts and graphs can be edited and enhanced using standard Excel options and tools. Simetar© charts and graphs are developed using standard menus that allow the user to easily specify the data, titles, and labels for charts that are used frequently for simulation.

Any series of numbers can be graphed on an X-Y axis as a line graph using this option in Simetar©. The icon to access line graphs is Develop line chart with and without labels on data points. The Line Graph menu (Figure 1) requires that you specify the values for the X axis (such as, years) and the Y values (such as, prices). Labels for these variables are optional and are entered in the Y and X-Axis Label boxes. The Chart Title is optional. You may include a label in the first cells indicated for each X variable, if you select the box for Series Labels in First Cell.

 Develop line chart with and without labels on data points.

Figure 1. Line Graph Dialog Box.

The line graph can have more than one line by using the Add Y’s button and indicating multiple Y series in the Select Y-Axis Range, one at a time. Once the graph is drawn by Excel, it can be edited using Excel chart commands. An example line graph is presented in Step 1 of DemoSimetar-Ana. Errors in developing line graphs can occur if the data to graph are in rows when the default Columns options is selected.

The line graph function can add labels to the points on line graphs. For example, a price/quantity chart can be developed with year labels on the individual data points to show years when structural changes took place. To use this option indicate the column or row of labels in the Data Labels box, being sure to have the same number of labels as there are data points.

Cumulative distribution function (CDF) graphs of individual or multiple variables (simulated values) can be developed using the Simetar© function. CDF graphs are initiated by selecting the Cumulative distribution function chart icon. Identify the variables to graph by highlighting the column(s), after first clicking in the Select Range to Graph box (Figure 2). Include names in the first cell of the variable range you highlight, so the chart will have names for the individual lines. (Be sure the variable names begin with a letter.) The chart can be placed on the current worksheet or in a new chart sheet. Use Excel’s chart commands to change the scale for the X axis and to make changes to the title.

 Cumulative distribution function chart

Figure 2. CDF Graph Dialog Box.

CDF graphs developed using Simetar© are dynamic so when the values referenced for the chart change, the CDF graph is instantly updated by Excel. This feature is particularly useful for simulation. Each time the simulation results are updated in SimData, the CDF graphs will be updated. The CDF graph option is useful for comparing simulated values of a random variable to the variable’s raw data. This is possible in Simetar© even though the two series have a different number of observations. Two examples of CDFs are provided in the DemoSimetar-Ana workbook. A single CDF is developed for a simulated net returns distribution in Step 4. A chart with the CDFs for a 5 scenario analysis is demonstrated in Step 5.

Probability distribution function (PDF) graphs of individual or multiple variables can be estimated using the Probability density function chart icon. Identify the variables to include in the PDF graph by selecting the variables in the Select Range to Graph box and the Add button if the variables are not in continuous columns (or rows) (Figure 3). The PDF graph function uses Kernel estimators to smooth the data rather than just using line segments to connect the dots. Eight different Kernels are available to develop the PDF graphs:

 Probability density function chart

• Gaussian
• Cosinus
• Triangle
• Triweight
• Epanechnikov
• Quartic
• Cauchy
• Double Exponent
Figure 3. PDF Graph Dialog Box.

Once the graph is drawn you can change the Kernel by editing the output range in the worksheet.

If the data series have names in the first cell indicate this on the menu, if not unselect the Labels in First Cell option. Multiple PDFs can appear on the same axis so the simulated values and their historical values can both be graphed on the same axis. This feature is possible because the data series being graphed do not have to be the same lengths.

PDF graphs developed using Simetar© are dynamic and when the values in the Selected Range to Graph, change the graph is instantly updated. This feature is useful when displaying simulation results using PDFs. The mean of the variables in a PDF is included automatically. Confidence intervals at the alpha equal 5 percent level, can be added by changing the Alpha equal 0.9 to 0.10 in the seventh row of the PDF Graph output table. The title can be changed by editing the first line of the PDF Graph output. An example of a PDF graph is provided in Step 6 of DemoSimetar-Ana.

Histograms of individual variables (simulated output) can be developed using the Simetar© menu. The histogramHistogram of alternative stochastic scenarios icon activates this option. Indicate the variable to graph by clicking the Select Range to Graph box in the dialog box (Figure 4) and highlighting the variable in the worksheet. Specify the Number of Bins (intervals) and select OK. The more bins the smoother the histogram. The maximum number of bins is the number of observations minus one. Experiment with the number of bins to find the number which best suits the data. An added feature of the histogram option in Simetar© is to display data as a cumulative distribution with the bins growing in height from zero to one as the X value gets large. The simulated results for net returns were charted two ways, as PDF and CDF histograms in Step 7 for DemoSimetar-Ana.

 Histogram of alternative stochastic scenarios

Figure 4. Histogram Dialog Box.

A Fan Graph consists of multiple lines in the Y axis for multiple scenarios (or multiple years for one variable) graphed in the X axis. The variables graphed in the X axis can be successive years for any simulated output variable that Simetar© collected data for during simulation. Alternatively, the variables graphed on the X axis can be the same simulated variable but for different scenarios or years. The purpose of a Fan Graph is to show the effect of risk on a variable over time or across scenarios.

A Fan Graph showing the simulated mean and percentiles or confidence interval lines about the mean can be developed using the Fan graph of alternative stochastic scenarios icon in Simetar©. The range of variables to be graphed on the X axis must be specified in the Select Ranges to Graph box (Figure 5). The variables (scenarios) must be specified in the order they are to appear in the graph. For example, if the graph is for 10 years of a probabilistic forecast, specify the 10 variables across the, say, 500 iterations as the selected range to graph. If the variables are not contiguous, they can be specified one at a time using the Add box.

 Fan graph of alternative stochastic scenarios

Figure 5. Fan Graph Dialog Box.

The Fan Graph dialog box (Figure 5) provides boxes to specify up to six percentile or confidence lines about the mean. The individual lines to add to the Fan Graph must be specified as fractions, such as 0.05 and 0.95 would result in a graph with 3 lines: the mean, the 5 percentile and the 95 percentile lines. Once the Fan Graph has been developed, you can dynamically change the graph by editing the percentile values in the output table. For example, if the 5% and 95% lines need to be changed to 1% and 99%, simply change the 0.05 to 0.01 and the 0.95 to 0.99 in the Fan Graph output table. Changing the percentile causes Simetar© to re-draw the graph. See Step 8 in DemoSimetar-Ana for an example of a fan graph.

The StopLight chart compares the target probabilities for one or more scenarios and is activated by selecting theDevelop a stoplight chart for comparing risk alternatives icon. The user must specify two values: a Lower Target and an Upper Target for the StopLight and the alternative scenarios to compare (Figure 6). The StopLight procedure calculates the probabilities of: (a) exceeding the upper target, (b) being less than the lower target, and (c) observing values between the targets. Like a stop light the three ranges are assigned colors of red (less than the lower target value), yellow (between the targets), and green (exceeding the upper target value). The complete results of comparing 5 scenarios in Step 9 are included as worksheet StopLight1 in DemoSimetar-Ana.

 Develop a stoplight chart for comparing risk alternatives

Figure 6. StopLight Dialog Box.

A summary table with the probabilities for each of the three ranges is prepared in the StopLight worksheet. A bar chart of the probabilities for the three ranges in the StopLight probabilities table is included as well. The lower and upper target values for the StopLight table can be changed after the chart is developed so the analyst can watch their effect on the probabilities in real time. Also, the output worksheet contains the sorted values for the scenarios with color coded cells (red, yellow, and green) for visual comparison of simulated scenarios. The StopLight procedure works well for comparing Simetar© simulation results generated for multiple scenarios.

Three types of probability plots can be generated by selecting the probability plot icon . A sample of the probability plot dialog box is depicted in Figure 7 where a Normal Probability Plot was developed for a simulated series. The probability plot function also develops Quantile–Quantile (or Q–Q) Plots and Probability–Probability (or P–P) Plots. See Step 14 of DemoSimetar-Ana for an example of all three types of probability plots.

 Probability plot charts

Figure 7. Normality Plot Dialog Box.

The Normal Plot is a method for checking how close to normal a random variable is distributed. A Normal Plot compares the ordered data themselves to the standard normal distribution’s percentiles. If a variable

is normally distributed the sorted data values will be entirely on a straight line with the only deviations from the line due to sampling error. The Normal Plot is also called the Normal Quantile Plot in some texts.

A Quantile-Quantile (Q-Q) Plot can be used to compare two distributions where the quantiles of two distributions are plotted against each other. If the two random variables have the same distribution, their paired observations lie on a 45° line. If the two random variables are in the same family of distributions, their paired observations tend to be linear although they may not lie on the 45° line.

A P-P Plot is also used to compare the shapes or distributions of two random variables. A P-P Plot consists of a graph of the percentiles for the sorted values of two variables graphed on one axis. If the two random variables have the same distribution (shape) the observations for a P-P Plot will be on a 45° line.

Box plots of one or more variables can be prepared by selecting the Box plot chart icon and filling in the information requested for a Box Plot. The Box Plot dialog box (Figure 8) indicates the information required for this function. An example of Box Plots for comparing five scenarios is provided in Step 13 of DemoSimetar-Ana.

 Box plot chart

Figure 8. Box Plot Dialog Box.

The Box Plot is a quartile summary of a random variable in graphical form that indicates whether a variable is skewed to the left or right. The names and values of the Box Plot are best defined in a chart:

As indicated in the sample chart above, 50 percent of the observed values fall within the box (25th to 75th quartile). If the distribution is skewed to the right then the bottom line segment is longer than the top line segment, and vice versa if the distribution is skewed left. Values that lie outside the extreme lines are likely to be outliers. The median and mean will show up as one line for symmetrical distributions.

A scatter matrix of multiple univariate data series can be created using the scatter matrix icon Scatter matrix plot. The scatter matrix is an array of individual graphs of several univariate data series. Each series is plotted against each of the other series, one at a time, like a correlation matrix. The graphs show the linear relationships between individual series and can be useful in multiple regression to determine collinearity. See Step 31 in DemoSimetar-Mat for an example.

 Scatter matrix plot