Swap-Out and Swap-In @RISK Functions

An @RISK model contains one or more custom spreadsheet functions, e.g. the functions RiskNormal (an input distribution) and RiskOutput (an output). While these functions are necessary to design a model and run simulations, they can cause difficulties when the spreadsheet is opened on another machine that doesn’t have @RISK installed. In those scenarios, the @RISK functions will all return #NAME? errors (Figure 1, right).

Swap-Out @RISK

The Swap-Out @RISK command allows all the @RISK functions in a spreadsheet to be temporarily replaced (i.e. “swapped-out”) with fixed values. This makes it easy to then share the model spreadsheet with those who do not have @RISK. Others can then view and make changes normally to the spreadsheet in this swapped-out state. Later, the @RISK functions can be restored (i.e. “swapped back in”). @RISK even gracefully handles cases where major changes are made while a model is in the swapped-out state.

To swap-out a model, select the Swap-Out @RISK command on the Utilities menu. The Swap-Out window (Figure 2, right) will open, containing the options that control how the swap process functions. The Swap-Out window contains two preference groups:

• Distribution Function Replacement - These options control with which fixed value to replace distribution functions if those functions don’t have a RiskStatic property function.
• Options - Options for other components that should be included or excluded when swapping out @RISK functions.

Distribution Function Replacement

When @RISK functions are removed during a swap out, any cell that contains an @RISK function will have the @RISK function removed and a static value inserted. The options in the Distribution Function Replacement group control what value is used to replace any distribution functions (input distributions or time series models). Additionally, the swap out process will replace other @RISK functions - Outputs, Statistical Functions, and other miscellaneous function - by replacing the function with the calculated value of the cell at the time the swap was performed.

The options available are:

• The "expected value" of a distribution function is its mean; in the rare cases where a distribution function has no mean, the mode will be used.

• Modified Expected Value - Display the modified expected value in the cell; modified expected values are the expected value of the probability distribution rounded to the nearest integer, for use with discrete distribution functions where an integer value may be required.
• Exact Expected Value - Display the exact, unrounded expected value of the probability distribution.
• Mode - Display the mode of the probability distribution.
• Percentile - Display the selected percentile value of the probability distribution.
• 

  Figure 3 - Percentile Settings

  When 'Percentile' is selected from the pull-down menu, the 'Percentile' field (Figure 3, right) will be made active; select a percentile value from the new pull-down menu, or enter a value manually.

Other Options

The Options group settings control what other components are included in the workbook when the swap out process is run. The first three options include additional configurations that can be accessed by clicking the More button ( ). The group contains the following configurations:

Include Thumbnails

Thumbnails are small graphs that appear in an Excel comment field; they allow non-@RISK users to see graphs. The Thumbnail Settings window (Figure 4, right) contains additional options for controlling which cells should have thumbnails added, how large the graphs should be, and whether or not to add legends to the graphs.

Color Function Cells

This option causes the cells that contain @RISK functions to be colored, based on their contents, as part of the swap-out procedure. The specific colors used, and how those colors should be applied to the spreadsheet can be controlled from the Color Cells Preference window.

Add Summary Sheets

By adding a summary sheets to a model, additional worksheets are added to the swapped-out model; these sheets contain additional information about the model and any simulation results that have been generated.

Save Simulation Data in Workbook

This option causes @RISK to embed the last run simulation results directly in the swapped-out workbook. These results are not useful to non-@RISK users who are viewing or editing the model, but it may be desirable to keep the data for later, when the model is swapped back in. See Swap-In @RISK for more information.

Swap-In @RISK

After a model has been swapped out and all the @RISK functions removed, most of the @RISK ribbon will be disabled and new @RISK functions cannot be added to the spreadsheet. However, a new icon will appear on the ribbon that allows the functions to be swapped back into the model (Figure 5, right).

The Swap-In @RISK command opens the Swap-In window (Figure 6, below), which controls how @RISK will report changes that will be made to the spreadsheet prior to inserting @RISK functions back in; this allows changes to be reviewed and edited before they are applied. In most cases, the Swap-In procedure can be considered to be automatic, as @RISK captures all changes to static values that were made when functions were swapped out. It also handles moved formulas and inserted rows and columns automatically. However, if formulas or static values where @RISK functions were previously located are changed or deleted in a swapped-out model, previewing those changes can be beneficial. Thus, the default option for the Swap-In dialog is to only preview cells that have had either their static value or formulas changed while swapped-out.

The options available in the Swap-In window are:

• All - All changes to be made to the model are reported, regardless if changes were made or not to the spreadsheet while it was swapped-out.
• Only Where Formulas and Static Values Were Modified – Only cells where formulas or static values were changed while the model was in the swapped-out state will be previewed. (This is the default.)
• Only Where Formulas Were Modified – Only cells where formula were changed while the model was in the swapped-out state will be previewed.
• None – No changes should be previewed.

When 'OK' is clicked, the swap in process will open the Review Model Changes window (see below), if appropriate.

Review Model Changes

Any previewed changes will show up in the Review Model Changes window (Figure 7, right). This window is organized as nested elements with individual cells listed under their worksheet, and worksheets listed under their workbook. It lists each of the cells in the workbook that will be modified by the swap-in process and for each item, there is a choice of four possible functions to swap back in (in some cases, two or more of these functions may be the same).

The options available are:

• Original (Before Swap) - The original function in the cell before anything was swapped out.
• Original (After Swap) - The function or value initially put into the cell by the swap out procedure.
• Current - The function or value that is currently in the cell.
• Recommended – The function that @RISK determines is the most appropriate to swap back in.
• Other - Any value can be entered - it can be left blank - to replace the field's contents.

Review Model Changes Command Buttons

The Review Model Changes window includes command buttons that allow for bulk application of actions, as well as exporting the model change information:

• Help - Open help resources (online or local, based on @RISK settings); see Help Button for more information.
• Settings/Actions - Window-specific settings and actions. The Review Model Changes options are:
• • Apply to All - Apply the selected action to all elements.
  • Collapse - Collapse the entire view.
  • Expand - Expand the entire view.
  • Find - Open the Find panel at the top of the window to enable searching the list of elements.

• Export - Commands for exporting the current table of data. The options available are:
• • Copy Data - Copy the Review Model Changes as a table of data to the clipboard. The selection radio button status will be pasted as TRUE/FALSE text values.
  • Copy Data with Headers - Copy the Review Model Changes as a table of data to the clipboard, including the window's column headers. The selection radio button status will be pasted as TRUE/FALSE text values.
  • Print Table - Open the Print dialog to send the contents of the window to a printer.
  • Save as PDF File - Open the Export dialog to save the contents of the window to a PDF file.
  • Export to Excel - Open the Export to Excel Options window (Figure 9, above right) to export the contents of the window to Excel, either to a new workbook or to a new worksheet in the active workbook.