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.
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:
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.
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
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.