Original Post Date: Tuesday, July 3, 2012

Introduction

@RISK and Crystal Ball are two Excel based applications that allow users to perform uncertainty, sensitivity or risk analysis on data contained in Excel spreadsheets. The analysis can be performed using various techniques including Monte Carlo and Latin Hypercube.  Many TruePlanning users are interested in performing this type of analysis on the models they create in TruePlanning or may even have requirements to perform this type of analysis on their estimates.  

In response to this desire, PRICE Systems L.L.C. has created two Excel based solutions that allow users to easily leverage the analytical power provided by Crystal Ball or @RISK against any TruePlanning project. These solutions allow users to specify the specific inputs and outputs they would like to examine. As the solutions are based in Excel, all of the configuration and reporting features contained in Crystal Ball or @RISK are available for use in these solutions.

There is one solution for use with @RISK, called “TP_AtRisk_Integration_12_1.xlsm”, and one solution for use with Crystal Ball, called “TP_CrystalBall_Integration_12_1.xlsm”. Both solutions were created in Excel 2007 and need to be used in Excel 2007 or later versions.  The @RISK solution requires @RISK to be installed and licensed to work, and the Crystal Ball solution requires Crystal Ball to be installed and licensed to work.

@RISK Solution

The following are the steps needed to set up and use the PRICE built TruePlanning/@RISK solution.


  1. Launch @RISK. This will result in Excel launching with the “@RISK” ribbon enabled.
  2. Open the “TP_AtRisk_Integration_12_1.xlsm” Excel file.

Note that the solution is a “Macro enabled” Excel file.Macros must be enabled for the solution to work. Many users need to enable macros on a file by file basis and will be prompted when the file is opened.

3. Notice that there are two ribbons beyond the standard Excel ribbons: @RISK and TruePlanning

 4. The @RISK menu provides users access to @RISK features including configuring the settings for simulations, defining distributions and outputs, as well as generating reports. These options will be discussed further along in the blog. 

5. The TruePlanning menu provides users access to the behaviors needed to integrate TruePlanning and @RISK including opening projects, defining targeted inputs and activities.

6. Open the @RISK file that contains the simulation settings to be used.  Click the “File” button in the @RISK ribbon and select the appropriate *.rsk file.

Users unfamiliar with @RISK should note that @RISK is an Excel based application, but it maintains a simulation setting file that is independent of the Excel files used to store the data targeted for analysis. This allows one @RISK configuration file to be used across multiple Excel files.

First time users will not have a properly configured *.rsk file and will need to create one. The only setting required by this solution is to set a macro to be executed with each run. Perform the following steps to create, set up and save a properly configured *.rsk file:


   1. In the @RISK ribbon click the “Simulation Settings” button.

   2. Select the “Macros” tab in the simulation settings dialog.
   3. Select the check box for “Before Each Iteration’s Recalc”.
   4. Enter “ReCalc” in the Macro Name text box for “Before Each Iteration’s Recalc”.
   5. The dialog should look as follows:



1.      6. Users can click the “Save as Default” check box. This will default all runs of @RISK to be configured properly for the  TruePlanning solution. If @RISK is only going to be used with the TruePlanning solution, this would be a good idea. If other Excel files are going to be used, it is better to leave this selection unchecked.  If the check box is left unchecked, users will need to load a saved *.rsk file with the proper settings before running @RISK against a TruePlanning project.

2      7. Click the “OK” button. The Simulation Settings dialog will go away.

  8. Click the “File” Menu on the @RISK ribbon:

 

 

a.       In the drop down menu that appears, select “Save @RISK file”. When prompted, provide a name and a location for the *.rsk file to be saved.

b.      The next time the TruePlanning/@RISK solution is used, start by opening the file that was just saved. To do this, use the above “File” button on the @RISK menu and select “Open @RISK file”, then locate and select the file that was saved in the above steps.

c.       Now @RISK is configured properly for the TruePlanning/@RISK solution. (This is the hardest part and only needs to be performed once.)

 

7. Select the TruePlanning Activities to be loaded for each type of Cost Object.

   1. Click the “Select Activities” button in the TruePlanning ribbon.  The following dialog will be displayed: 

   2. Select the type of Cost Object in the “Cost Objects” drop down.

   3. The “Activity” drop down will dynamically list the Activities for the type of Cost Object in the “Cost Objects” drop down.

   4. Click the “Select Activity” button to add the currently selected Activity to the list of selected Activities.
   5. To remove a selected Activity, click on it in the “Selected Activities” list and click the “Remove Activities” button. Multiple  Activities can be removed at the same time.
   6. Click the “OK” button to close the dialog. At this time users will be asked if they would like to reload the project. If they click “Yes”, the project will be reloaded and will display the new list of selected Activities.

8. Select TruePlanning inputs to be loaded for each type of Cost Object.

   1. Click the “Select Inputs” button in the TruePlanning ribbon. The following dialog will be displayed:

   2. Select the type of Cost Object in the “Cost Objects” drop down.

   3.The “Input” drop down will dynamically list the Inputs for the type of Cost Object in the “Cost Objects” drop down.

   4. Click the “Add Inputs” button to add the currently selected Input to the list of selected Inputs.

   5.  To remove a selected Input, click on it in the “Selected Inputs” list and click the “Remove Input(s)” button.

   6. Click the “Exit” button to close the dialog. At this time user will be asked if they would like to reload the project. If they click “Yes”, the project will be reloaded and will display the new list of selected Inputs.

9.  Click the “Exit” button to close the dialog. At this time user will be asked if they would like to reload the project. If they click “Yes”, the project will be reloaded and will display the new list of selected Inputs.

 


10. The above dialog allows users to select the project to be used. It also contains two options for configuring the analysis:

1.      1. The “Use Default Distribution” check box. If the check box is not selected, inputs will be added to the Excel sheet, but @RISK distributions will not be created for the inputs. Users will need to define their own distributions.  If the check box is selected, then the solution will provide a default distribution for every input created when the project is opened. The distribution created will be a triangular distribution that sets the following values:

a.       Pessimistic: 50% of the current input’s value

b.      Most Likely: The current input’s value

c.       Optimistic: 150% of the current input’s value

 

2.      2. The “Cost” and “Labor” radio buttons allow users to select if hours or cost will be targeted in the @RISK outputs. When a project is loaded, TruePlanning Cost Objects and selected Activities are always defined as @RISK outputs.

3.      3. Select the project to be opened.

4.      4. Click the “OK” button and the project will be loaded.

 

11. Once the project is loaded users should see the following:

12. The following columns will be created:

1.       Cost Object Name: This column will contain the name of the Cost Object as it appears in the TruePlanning PBS.

2.       Cost Object Type: This column will contain the type of the Cost Object. For rows representing Activities, it will contain the name of the Activity. It is possible in TruePlanning to give Activities unique names and those names will be listed. Activities will be listed in the order they appear in the TruePlanning PBS. If Activities are given a custom name, that name will appear, but the Activity will be in the list of Activities based on its original name.

3.       Labor or Cost: Described in a later section.

4.       Input Name: The name of the Input. These columns will be empty for rows representing Cost Objects and Activities.

5.       Value: Described in a later section.

6.       Unit: This column indicates if the Cost Object or Activity output is in hours or cost. For Inputs, it provides the unit of the input where appropriate.

 

13. Labor or Cost column. All Cost Objects in the select project will be loaded with either their Estimated Cost or their Labor Requirement (total number of hours of work to be performed for the Cost Object) depending on the option that was selected when the project was opened. In the above example, Hours was selected.  Each value in this column will be designated as an @RISK output. Each output will be given a specific name. For Cost Objects, the name will be the order of the Cost Object in the PBS followed by the name given to the Cost Object in the PBS. For Activities, the name will be the Cost Object name that the Activity belongs to followed by the order of the Activity in relation to the other Activities of the same Cost Object and then the name of the Activity.

Cost Object Output:

 

Activity Output:

 

14. Value column. For each selected type of Input, a row will be created for each occurrence of that Input in the TruePlanning project. If the “Use Default Distribution” option was selected when the project was opened, the Value column will contain an @RISK formula defining that value as an input to the @RISK simulation. As described above, the default distribution type will be a triangular distribution using 50% and 150% of the current value of the input.

15. When projects are loaded without the “Use Default Distribution” option enabled, the Value column will not contain any @RISK distribution formulas and users will need to define their own distributions. Distributions can be defined using the @RISK “Define Distribution” button on the @RISK ribbon. The @RISK documentation can provide guidance on how to do this.  

16. If users do not want certain input distributions or outputs to be part of the analysis, they can remove the formulas in the cells or delete entire rows. I.e., if a user only wants to run the analysis against the total cost, but vary multiple TruePlanning Inputs, they could delete the contents from all of the cells in the Labor row except for the System Folder cell. Then the only output would be the total for the project. 

17. Once the TruePlanning project has been loaded and the input distributions and outputs have been setup, @RISK can be run against the TruePlanning project.  The @RISK documentation can provide detailed guidance on how to set up a simulation, but to launch the simulation, simply click the “Start Simulation” button on the @RISK ribbon.

18. Once the simulation has completed, use the @RISK reporting features to view the results of the simulation. Again @RISK documentation can provide guidance on this.


Crystal Ball

The following are the steps needed to set up and use the PRICE built TruePlanning/Crystal Ball solution.

  1. Launch Excel. Crystal Ball is a COM based add-in to Excel and should launch when Excel is launched.  
  1. Open the “TP_CrystalBall_Integration_12_1.xlsm” Excel file.

Note that the solution is a “Macro enabled” Excel file.Macros must be enabled for the solution to work. Many users need to enable macros on a file by file basis and will be prompted when the file is opened.

3. Notice that there are two ribbons beyond the standard Excel ribbons: Crystal Ball and TruePlanning

4. The Crystal Ball menu provides users access to the Crystal Ball features including configuring the settings for simulations, defining assumptions and forecasts, as well as generating reports. These options will be discussed further along in the blog. 

 

5. The TruePlanning menu provides users access to the behaviors needed to integrate TruePlanning and Crystal Ball including opening projects, defining targeted inputs and activities.  

 

       6. Select the TruePlanning Activities to be loaded for each type of Cost Object.

1.       Click the “Select Activities” button in the TruePlanning ribbon.  The following dialog will be displayed:

2.       Select the type of Cost Object in the “Cost Objects” drop down.

3.       The “Activity” drop down will dynamically list the Activities for the type of Cost Object in the “Cost Objects” drop down.

4.       Click the “Select Activity” button to add the currently selected Activity to the list of selected Activities.

5.       To remove a selected Activity, click on it in the “Selected Activities” list and click the “Remove Activities” button. Multiple Activities can be removed at the same time.

6.    Click the “OK” button to close the dialog. At this time users will be asked if they would like to reload the project. If they click “Yes”, the project will be reloaded and will display the new list of selected Activities.

 

7.       Select TruePlanning inputs to be loaded for each type of Cost Object.   

a.       Click the “Select Inputs” button in the TruePlanning ribbon. The following dialog will be displayed:


b.      Select the type of Cost Object in the “Cost Objects” drop down.

c.       The “Input” drop down will dynamically list the Inputs for the type of Cost Object in the “Cost Objects” drop down.

d.      Click the “Add Inputs” button to add the currently selected Input to the list of selected Inputs.

e.      To remove a selected Input, click on it in the “Selected Inputs” list and click the “Remove Input(s)” button.

f.        Click the “Exit” button to close the dialog. At this time, the user will be asked if they would like to reload the project. If they click “Yes”, the project will be reloaded and will display the new list of selected Inputs.

 8. Load a TruePlanning project by selecting the “Open Project” button in the TruePlanning menu.  A project selection dialog will be displayed.  

9.The above dialog allows users to select the project to be used. It also contains two options for configuring the analysis:

1.       The “Use Default Distribution” check box. If the check box is not selected, inputs will be added to the Excel sheet, but Crystal Ball assumptions will not be created for the inputs. Users will need to define their own assumptions.  If the check box is selected, then the solution will provide a default assumptions for every input created when the project is opened. The assumption created will be a triangular distribution that sets the following values:

1.    Pessimistic: 50% of the current input’s value

2.    Most Likely: The current input’s value

3.    Optimistic: 150% of the current input’s value

2.    The “Cost” and “Labor” radio buttons allow users to select if hours or cost will be targeted in the Crystal Ball forecasts. When a project is loaded, TruePlanning Cost Objects and selected Activities are always defined as Crystal Ball forecasts.

3.    Select the project to be opened.

4.    Click the “OK” button and the project will be loaded.

 

10. Once the project is loaded users should see the following:

11. The following columns will be created

1.       Cost Object Name: This column will contain the name of the Cost Object as it appears in the TruePlanning PBS.

2.       Cost Object Type: This column will contain the type of the Cost Object. For rows representing Activities, it will contain the name of the Activity. It is possible in TruePlanning to give Activities unique names and those names will be listed. Activities will be listed in the order they appear in the TruePlanning PBS. If Activities are given a custom name, that name will appear, but the Activity will be in the list of Activities based on its original name.

3.       Labor or Cost: Described in a later section.

4.       Input Name: The name of the Input. These columns will be empty for rows representing Cost Objects and Activities.

5.       Value: Described in a later section.

6.       Unit: This column indicates if the Cost Object or Activity forecast is in hours or cost. For Inputs, it provides the unit of the input where appropriate.

 

12. Labor or Cost column. All Cost Objects in the select project will be loaded with either their Estimated Cost or their Labor Requirement (total number of hours of work to be performed for the Cost Object) depending on the option that was selected when the project was opened. In the above example, Cost was selected.  Each value in this column will be designated as a Crystal Ball forecast. Each forecast will be given a specific name. For Cost Objects, the name will be the order of the Cost Object in the PBS followed by the name given to the Cost Object in the PBS. For Activities, the name will be the Cost Object name that the Activity belongs to followed by the order of the Activity in relation to the other Activities of the same Cost Object and then the name of the Activity.  To see the details about a forecast, select a cell that contains a forecast and then select the “Define Forecast” button on the Crystal Ball ribbon.

 

Cost Object Output:

 

Activity Output:

 

13. Value column. For each selected type of Input, a row will be created for each occurrence of that Input in the TruePlanning project. If the “Use Default Distribution” option was selected when the project was opened, the Value column will contain a Crystal Ball assumption defining that value as an input to the Crystal Ball simulation.  As described above, the default distribution type will be a triangular distribution using 50% and 150% of the current value of the input. To view the details of an assumption, select the cell that contains the assumption, and then click the “Define Assumption” button in the Crystal Ball ribbon.

 


14. When projects are loaded without the “Use Default Distribution” option enabled, the Value column will not contain any Crystal Ball assumptions and users will need to define their own assumptions. Assumptions can be defined using the Crystal Ball “Define Assumption” button on the Crystal Ball ribbon. The Crystal Ball documentation can provide guidance on how to do this

 

15. If users do not want certain assumptions or forecasts to be part of the analysis, they can remove the formulas in individual cells or delete entire rows. In Crystal Ball, users can remove assumptions and forecasts for specific cells by right clicking on the cell, selecting the Crystal Ball menu, then selecting the “Clear Cell Data” menu option.  So, for example, if a user only wants to run the analysis against the total cost of the project, but vary multiple TruePlanning Inputs, they could clear the contents from all of the cells in the Labor/Cost row except for the System Folder cell. Then the only forecast would be the total cost for the project. 

 

16. Once the TruePlanning project has been loaded and the assumptions and forecasts have been setup, Crystal Ball can be run against the TruePlanning project.  The Crystal Ball documentation can provide detailed guidance on how to set up a simulation, but to launch the simulation, simply click the “Start” button on the Crystal Ball ribbon.

 

17. Once the simulation has completed, use the Crystal Ball reporting features to view the results of the simulation. Again Crystal Ball documentation can provide guidance on this.

 

Summary

Producing a high quality cost estimate is about predicting the future, and the future contains uncertainty and risk. @RISK and Crystal Ball provide proven mechanisms for understanding that risk. By using solutions that allow users to leverage the power of these tools against TruePlanning cost estimates, TruePlanning users can gain even greater confidence and insight into the cost models they have created.