Software Tips
Linking Microsoft® Excel® Models With TruePlanning
TruePlanning offers users the ability to integrate data from their custom Excel models into the TruePlanning framework. Once data from an external model is linked to an input in TruePlanning, it becomes a permanent part of that project. Therefore, if a user wants to share the project with someone, or if the user archives the estimate and needs to make a change in the future, access to the custom Excel model will always be available right within TruePlanning.
Additionally, TruePlanning allows users to tailor results according to the specific reporting needs of their organizations. Users can rename activities and resources so that the results of the custom model and the results in TruePlanning match.
Steps to integrate a custom model
1. Assumption: You have a custom Excel model that calculates production and development hours for testing when given the impact and likelihood of certain software modules. You want to link the results of this custom Excel model into TruePlanning.
2. Create a new TruePlanning project file. Drag a Purchased Service cost object into the Navigation Tree to model the number of hours needed for a service you are purchasing (for example, Quality Assurance).
3. Next, switch to the Detailed view of the Navigation Tree, and rename the activities and resources of the Purchased Service Cost Object so that the results report according to the specifics of your organization. Suppose you need to report out on two different activities within the Quality Assurance department: one for testing development artifacts and the other for testing production artifacts. Rename the first activity, "Quality Assurance Consulting Development," and rename the second activity, "Quality Assurance Consulting Production." Then, rename the resources of both these activities, "Quality Assurance."
4. Now that reporting has been configured, select the Purchased Service cost object, and choose the Input Sheet tab to link your external Excel model.
5. Highlight the Production Labor Hours and Development Labor Hours input fields by holding down the Control key on the keyboard and selecting the inputs.
6. Right-click the highlighted input sheet cells, and select the option for linking to Excel documents.
7. Browse to locate the Excel file you want to link. Select Open.
8. TruePlanning will open the selected Excel file and insert a worksheet named "TruePlanning." This worksheet contains the names of the TruePlanning inputs that have been selected for linkage to Excel.
9. Within the Excel file, go to the worksheet that contains the Production Hours and Development Hours outputs.
10. Link the Production Hours and Development Hours outputs to the "TruePlanning" Excel worksheet by using Excel formulas.
11. Close Excel and when prompted choose to save. The Production and Development outputs of your custom Excel model will be automatically linked to the Production Labor Hours and Development Labor Hours input fields on the TruePlanning Input Sheet.
12. On the Tools menu, click Calculate Now.
13. Select the Results tab. To view activity and resource results, click Activity Name from the View drop-down menu, and click Resource Name from the By drop-down menu. In this example, the activity results have been split across the two different custom activity names, "Quality Assurance Consulting Development" and "Quality Assurance Consulting Production." Additionally, the resource results have been consolidated into the custom Resource Name, Quality Assurance.
14. Now, you have the ability to access the custom Excel model right within TruePlanning. For example, suppose you need to change the Impact and Liklihood within your custom Excel model, and you want these changes reflected in TruePlanning.
15. To edit the Impact and Liklihood of your custom model, switch to the Input Sheet, and click the Excel icon in the Value column.
16. Once your custom Excel model opens, change the Impact and Liklihood values. When you change the Impact and Liklihood values, the calculated Production Hours value will change. Close Excel and when prompted choose to save. The revised Production Hours output of your custom Excel model will be automatically linked to the Production Labor Hours input field on the TruePlanning Input Sheet.
