This section is dedicated to explaining the function of each item on the Scenarios ribbon within Microsoft Excel. The Scenarios tab should appear after installation, complete with the following items. If you have questions or feedback about the user interface of Scenarios, contact Invrea here.
This text box is used to set the number of scenarios that will be generated. Using more scenarios guarantees more accurate and detailed simulations, but also requires more computation time. The default setting is 1,000 scenarios; we recommend using a low number of scenarios (around 10,000) while building a model, and moving to higher numbers of scenarios (100,000 or higher) when presenting results. Currently, Scenarios' max setting is 10 million scenarios. For more discussion of how many scenarios to generate, look here. When viewing scenarios, this box will display the number of scenarios that were generated, and cannot be changed; it will also display the number of scenarios that are currently being displayed on the graph.
This label displays the cell whose values are currently being studied. If Scenarios is run with a cell or range of cells selected, this range of cells will be listed here. After Scenarios is finished generating scenarios, the likely values of the current cell(s) being explored will be graphed on the right side of the screen.
When Scenarios is run, it generates the selected number of scenarios for all cells in the spreadsheet. This means that after running, if you wish to look at possible values for another cell or range of cells, you don't have to cancel and run Scenarios again - you can simply click Explore selection, and the values of the currently-selected cell(s) will be plotted.
Pressing this button runs Scenarios, with the number of scenarios specified in the Scenarios box. After scenarios have been generated, the cell that is currently selected will be plotted on the right.
After Scenarios has been run, scenarios have finished generating, and the plot of the explored cells has been drawn, the Stop button will become available. This will exit Scenarios' plotter and return to normal Excel operation. Because you should not edit the contents of a cell while running Scenarios, you should click this if you wish to change a spreadsheet, and then re-generate scenarios.
When this button is pressed, a new scenario is generated and displayed, using the assumptions that are specified on the given spreadsheet. This button has the same effect as simply pressing F9. Note that the scenarios generated when you do this do not account for data - they reflect only a spreadsheet's assumptions, not its actuals. In order to account for data, you must generate scenarios using the Run button. Note also that the Next and Previous buttons do not work for the Scenarios assumptions
RANDBETWEEN, as these are actually Excel functions that operate independently of Invrea Scenarios.
When this button is pressed, Scenarios goes back to the previous scenario, and displays that. This menu button is the opposite of the Next button, and has the same effect as pressing Shift+F9. The Next and Previous buttons are unavailable while exploring scenarios after pressing the Run button, and will re-appear after Stop is pressed. Note also that the Next and Previous buttons do not work for the Scenarios assumptions
RANDBETWEEN, as these are actually Excel functions that operate independently of Invrea Scenarios.
Clicking this button guides the user through the process of adding a Scenarios assumption to the currently selected cell. Assumptions can only be added or removed when scenarios are not being generated or viewed. A similar menu can be reached by either right-clicking and selecting Add assumption, or by selecting Insert function from the Formulas tab, and selecting the Scenarios special group of functions. Adding assumptions can be performed on one cell, or on a group of selected cells, where the parameters of the assumption are separated by commas.
Clicking this button guides the user through the process of adding an actual to the currently selected cell. Actuals are pieces of data that automatically test and refine the user's assumptions when Scenarios is run. Actuals can only be added and removed when scenarios are not being generated or viewed. Using this menu, actuals can be added for more than one cell at once by separating the parameters of the actual using commas. The same menu can be reached by right-clicking a cell and selecting Record actual. Instead of going through this menu, an actual can also be added by copying a data point, right-clicking a cell with a Scenarios assumption in it, and selecting Paste actual.
This feature allows the user to convert models from standard risk-analysis plugins into Scenarios models. If the user opens a workbook and Scenarios finds commands from the risk-analysis plugin @Risk, this button will appear, which converts the @Risk model into a Scenarios model. This converter is not a complete converter - it primarily works for the non-industrial version of @Risk, and there is no Scenarios equivalent for certain @Risk commands. However, if the converter fails, it does so gracefully, notifying the user of the commands that could not be converted.
This button is only available when Scenarios is not being run. The Highlight assumptions button serves three related purposes. First, when Highlight assumptions is enabled, cells that contain Scenarios functions will be highlighted in yellow or red; we'll return to what these colours mean in a second. This is immediately useful for, in a large spreadsheet, locating where its Scenarios assumptions are located.
Second, running Highlight assumptions allows you to examine the effect of your assumptions while building a model. After Highlight assumptions has been run, selecting a highlighted cell will plot a rough approximation of the assumption that is made about this cell's values, along with some brief statistics describing this cell's assumed values. This feature is provided in order to give a quick way to view the impact of certain assumptions.
Third, the Highlight assumptions functionality allows you to quickly check whether your assumptions are well-reflected in the actual data contained within the spreadsheet. Cells containing
ACTUAL statements are highlighted in yellow or red, depending on whether the assumptions made throughout the spreadsheet make these data points likely or unlikely. When these cells are selected, a plot is drawn. This plot contains two elements: first, in blue, the model's predictions, based on the encoded assumptions, about what the observed data point is likely to be. Second, in red, the actual value observed. If these two are too far apart, the cell containing the
ACTUAL will be highlighted in red, and a warning message will be shown.
What this warning means is a little subtle. Just because a data point is unlikely does not mean a spreadsheet model is wrong - sometimes, running Scenarios will show you that a perfectly consistent and accurate answer can still be reached. In short, sometimes even exceedingly unlikely events do happen. However, you may wish to consider whether your assumptions might realistically be improved to better match unlikely data points. And if observed data points are extremely unlikely, running Scenarios will fail entirely. This happens if every scenario is deemed impossible taking into account the observed data, in which case learning is not possible. For more information on this result, see here. If this occurs, then Highlight assumptions should help locate the cell(s) that contain the impossible data points.
If Scenarios is run while Highlight assumptions is enabled, Highlight assumptions will be automatically disabled.
When Scenarios assumptions are included within cells, these cells are recalculated constantly in accordance with natural Excel operation, such as when a new cell is added to the sheet. You may find that this becomes annoying while in the middle of entering a new model. To disable this automatic recalculation, select Lock random cells. Upon enabling this option, random cells will be fixed to a central value, and will no longer re-evaluate while entering new values, saving the spreadsheet, or hitting F9.
Specifically, random cells will all be locked to their median values. This feature is also useful in order to find out what the median predictions of a given model are. To unlock cells so that they take new values every time the spreadsheet is recalculated, select Unlock random cells again.
If Scenarios is run while Lock random cells is enabled, cells will automatically be unlocked; the same holds for Highlight assumptions. Note that 'locking' Scenarios does not apply to models using
BAYESLINEST, which cannot currently be locked. Models using
RANDBETWEEN also cannot be locked, as these are native Excel functions whose evaluation Invrea Scenarios cannot control.
If, while scrolling through generated scenarios, you find one that requires further study, or you wish to present or share the scenarios you've generated, click Save scenario. This will allow you to specify a location where a spreadsheet containing all this scenario's values will be saved, along with the plot of the currently-explored cell(s) on the right. Note: This function is only available if the spreadsheet in question has been saved already. If Scenarios is run on a spreadsheet that has not been saved anywhere on the user's computer, Save scenario will be disabled.
After Scenarios has finished generating scenarios and the plot has been drawn on the right, the scenario scroller will appear. This is a scroll bar titled Scroll through scenarios. A 'scenario', in Invrea's parlance, is a set of values of the entire spreadsheet. Therefore, while scrolling through scenarios, you will see the values of the random cells - those that contain Scenarios assumptions - change, as they are different in each scenario. The Toggle scroller button enables or disables this scroll bar.
This crucial label lists the mean, or expected value, of the cell being explored (which cell is being explored is listed in the Exploring cell(s) label). The expected value of a cell is its average over all generated scenarios, taking into account how likely each of the cell's possible values is. If multiple cells are selected, then this label lists the average of the expected values for each cell.
This label lists the standard deviation of the cell being explored (which cell is being explored is listed in the Exploring cell(s) label). If two cells are being explored, this label changes to showing the correlation coefficient between the two cells; if multiple cells are being explored, it changes again to show the average trend of the cells.
The checkbox Likely scenarios only can be selected after Scenarios has finished generating and plotting scenarios. This checkbox, which defaults to checked, limits the plotted scenarios, and the scenarios accessible from the scroller, to the most likely group of scenarios. Often, removing exceedingly unlikely events makes plots of the generated scenarios easier to digest and explain. However, if you wish to view unlikely scenarios such as tail events - so-called 'black swans' - then this can be accomplished by unchecking the Likely scenarios only box. The plot will be redrawn, and the scenario scroller refreshed, with the new unlikely scenarios added in.
The Summary statistics button is available after Scenarios has finished generating and plotting scenarios, for studying univariate or bivariate descriptive statistics if either one or two cells is being explored (Summary statistics for multiple cells at once are not yet available.) Clicking this button generates a window that contains some basic statistics describing the value of the cell being explored, over all the scenarios that were run. The statistics that this window contains are computed by looking at the cell’s value in all the scenarios generated, taking into account the individual probability of each scenario.
Specifically, the Summary statistics window - for only one cell - contains the following statistics describing the value of the cell being explored: the mean, the median, the standard deviation, the skewness, the excess kurtosis, and a confidence interval calculator. Using the Significance box, a confidence for any desired level can be constructed; and using the Type box, the type of confidence interval selected can be desired. For example, entering 95% significance, and choosing a two-sided interval, generates an interval that the true value of the cell being explored can be assumed to lie in with 95% probability. Entering 90% significance, and choosing a left-sided interval, generates an upper bound that the true value of the cell being explored is less than with 90% probability.
(Technically, these intervals are Bayesian credible intervals and not frequentist confidence intervals, as they are created using distributions which have learned from data, but the term 'confidence interval' is more commonly used and frequently is misused to refer to credible intervals, so we choose to use it here.)
The bivariate Summary statistics window, which comes up when exploring scenarios for two cells at once, shows univariate statistics (those discussed above) for each cell, and in addition shows a range of bivariate statistics describing the relationships between the cells. The correlation coefficient describes the linear relationship between the two variables; the coefficient of determination is the square of the correlation coefficient; the Kolmogorov-Smirnov statistic is a measure of the distance between the two probability distributions; and the Kolmogorov-Smirnov significance is the significance level at which a Kolmogorov-Smirnov two-sample test would conclude that these two probability distributions are different (which is uniformly distributed under the null hypothesis that the two probability distributions are the same).
Invrea Scenarios generates scenarios by sending Excel formulas into a process running alongside Excel that we call the inference engine. This inference engine quickly performs most Excel calculations, but if user-defined macros or less-common Excel functions are used, the Scenarios inference engine evaluates these by sending these commands to Excel to be evaluated. This is a slow process, and so functions that are not implemented on Scenarios' inference engine should be avoided. When warnings are enabled, Scenarios warns the user during inference of any functions that are not sped up in this way. Warnings are enabled by default; press this button to toggle warnings.
When this button is clicked, if the user has Internet access, a connection is made with Invrea's website to see whether a new version of Scenarios is available. If there is, a download link is provided.
The About button pops up a dialog box containing the current version number of Scenarios, a link to where you can file a bug report, and a link to this online documentation.