by Ed Merriman
| January 13, 2016
This document provides instructions on using a TruePlanning / Crystal Ball Solution that was created for a specific PRICE customer. The solution is based in a macro enabled Excel file. It is an enhancement of the generic TruePlanning / Crystal Ball solution and has been created to allow the customer’s personnel too quickly and easily setup and run analysis on TruePlanning projects using Crystal Ball. The customer had very specific requirements regarding the distributions to be used in uncertainty analysis and this tool was developed to allow them to use set the distributions they desired with minimal effort.
The customer was working on a large estimation effort that required uncertainty to be run against many TruePlanning projects. Each project had unique values that needed to be applied to the distributions, but the core distributions were based on specific formulas. The addition of a dictionary for each project was created to support this variation in data between projects.
The solution was designed to be very configurable but also includes ‘presets’ which allow users to configure sets of distributions and apply them with a single click. This feature, combined with the dictionaries had two benefits. First it allowed users to run uncertainties with the correct distributions with minimal effort. Second, it provided confidence that each uncertainty analysis was being run with the same distributions each time. This saved the customer significant time when performing the analysis.
This solution only targets the Hardware Component Cost Object, but it wouldn’t take a significant effort to include other Cost Objects in a solution similar to this solution.
The following software packages are required to use the solution:
1. Microsoft Office, version 2010 32-bit or 2013 32-bit
2. PRICE’s TruePlanning 14.2
3. Crystal Ball version 22.214.171.124.000 (32-bit)
1. Launch Crystal Ball. This will result in Excel opening with the Crystal Ball ribbon.
2. Open a copy of the TruePlanning / Crystal Ball Solution in the instance of Excel that opened when Crystal Ball was launched.
3. Click on the TruePlanning Ribbon
4. Create a dictionary by clicking the “Create Dictionary” button. Note: If the dictionary to be used already exists, this step can be skipped.
5. Provide a name for the Excel sheet that will contain the information and provide the path to the TruePlanning *.tpprj file that contains the TruePlanning project to be analyzed. Click “OK”.
6. If prompted, select the appropriate connection name. The default connection name is “(local)”. It is unlikely that users will be prompted.
7. A sheet with the name provided in the above dialog will have been created and populated with the PBS from the selected TruePlanning project. Additionally 5 columns are created to represent the 5 inputs that can be set up as Crystal Ball Assumptions.
8. On this sheet, growth or contingency values can be entered for specific PBS elements. They will be used later in the setup of Crystal Balls’ assumption distributions.
9. Click the “Open Project” button on the TruePlanning ribbon.
10. The Setup Run dialog is launched.
11. The Setup Run dialog allows users to configure up to 5 inputs from the Hardware Component Cost Object to be used as assumptions in the Crystal Ball analysis.
12. To setup an input six attributes must be set:
a. Name (pulldown)
b. Optimistic/Min formula (pulldown)
c. Likely/Mid formula (pulldown)
d. Pessimistic/Max formula (pulldown)
e. Provided Growth (text box)
f. Use Dictionary Growth (checkbox)
13. Name: The name pulldown contains a list of inputs for the Hardware Component. Select one. If the blank entry in the pulldown is selected, that input will not be part of the Crystal Ball analysis.
a. Weight of Structure
b. Weight of Electronics
c. Percent of New Structure
d. Percent of New Electronics
e. Engineering Complexity
f. Manufacturing Complexity for Structure
g. Manufacturing Complexity for Electronics
14. Optimistic / Likely / Pessimistic: These pulldowns allow the user to select the equation to be used for analysis. The current list of equations is:
a. Blank (Means no equation will be used. This is only appropriate when there is no input selected)
b. 80% of CBE
c. 90% of CBE
d. 95% of CBE
f. 102% of CBE
g. 105% of CBE
h. 110% of CBE
i. 120% of CBE
j. CBE * (1 + Growth)
k. Likely * 1.3
l. CBE * 1.3
m. (CBE + (CBE*Cont))*1.3
15. Provided Growth: This is a value that will be used in any formula that uses a growth or contingency value if the option to use data from the dictionary sheet is not selected. The value provided for growth will be used for all PBS elements that receive a Crystal Ball assumption.
16. Use Dictionary Growth: If checked, values for formulas that use growth or contingency will come from the selected dictionary sheet.
17. Presets: This group box contains four radio buttons that allow users to quickly configure the setup to one of four commonly used setups.
18. Use Dictionary: This pulldown lists all sheets in the current Excel file that contain dictionary information. Users MUST select a dictionary that matches the PBS of TruePlanning project to be used in the analysis.
19. Click “OK” to complete the setup process. This will result in the user being queried to supply a path to a TruePlanning *.tpprj file that contains the TruePlanning project to be used.
20. When the setup is complete, the Excel sheet “Sheet 1” will contain the PBS of the targeted TruePlanning project with the select inputs set up as Crystal Ball assumptions and all Cost Objects with an indent of three or less set up as Crystal Ball forecasts.
21. Users can now run the Crystal Ball analysis as is appropriate. Use of Crystal Ball is beyond the scope of this document.
There is a feature in the TruePlanning / Crystal Ball that was built specifically to support the most recent SOMA effort. The “Process CB Results” button will consume a Forecast report created by Crystal Ball after an analysis run and put the 50% and 70% results into the “output-cr” sheet. NOTE: this feature has expectations on the shape of the PBS used in the analysis as well as specific naming conventions. Details on this feature can be provided by PRICE upon request.