You will often have in a spreadsheet model one or more arrays of cells that are modeling the random or uncertain nature of a variable over time, for example:
Volume of imports
Price of commodities
Growth of a bacterial population
We provide quite a number of different time series example models in ModelAssist. The formulae used may involve quite complex and esoteric equations, which means it is difficult for the modeler and the reviewer to see how the modeled time series behaves. We offer a number of techniques to check the model behavior when you have access to the model itself, but we also need to be able to produce a plot to include in reports that gives a feel for the modeled time series.
The links to the Summary Chart software specific models are provided here:
Crystal Ball offers a very nice summary report that is easily created and customized. You need to take the following steps to create the figure below:
After running a simulation with multiple forecasts that are related to each other, select "Run" >> "Open Trend Chart";
Click "Choose Forecasts" and select the forecasts you are interested in;
If you click "Chart Prefs", you can select the certainty bands you would like Crystal Ball to display, plus you have several other chart options;
In this plot we see that the model has an upwardly trending 'y' shown by the yellow mean line, and increasing uncertainty of the interest rate shown by the expanding distance between the percentiles.
You may prefer to plot your own form of summary chart in Excel after exporting the required output statistics from Crystal Ball into Excel (select "Run" >> "Extract Data" >> Forecast Values" >> "OK"). That turns out to be rather a frustrating exercise in Excel, so you can use Epix Analytics's Summary Chart for doing that.
@RISK offers a very nice summary report that is easily created and customized if you name the cell array as a single output:
In this plot we see that the model has an upwardly trending interest rate shown by the yellow mean line, and increasing uncertainty of the interest rate shown by the expanding distance between the percentiles.
@RISK offers the option of plotting bounds around the mean that are either percentiles (as shown above) or standard deviations, or a combination. We advise that you avoid using standard deviations, unless they are of particular interest for some technical reason, because a spread of say 1 standard deviation around the mean will encompass a varying percentage of the distribution depending on its form. That means that there is no consistent probability interpretation attached to mean +/- x standard deviations.
You may prefer to plot your own form of summary chart in Excel after exporting the required output statistics from @RISK into Excel. That turns out to be rather a frustrating exercise, so you can use Epix Analytics’s Summary Chart for doing that.
An alternative to the summary plot is a Tukey or box plot:
A Tukey plot is more commonly used to represent variations between data sets, but it does have the possibility of including more information than summary plots. A word of cautions: the extreme generated values from a simulation can vary enormously between simulations with different random number seeds, which means they are not usually values to be relied upon. This file provides you with a tempelate: Tukey Box Plot to produce the graph above.
The links to the Asphalt software specific models are provided here: