Original Post Date: Tuesday, July 3, 2012 

Introduction

A cost estimation model needs to be fed data, and it is only as good as data used to create it. Frequently the data needed to ‘feed’ the cost model comes from a variety of sources including engineers, subject matter experts or other individuals not directly building the cost model. Just asking these experts to stick their finger in the air and take a guess isn’t always the best approach. Using the COM enabled Data Input Form Excel solution that comes with TruePlanning can help users obtain the data needed to complete cost estimate models in a way that significantly reduces the effort to collect and enter the data into TruePlanning.

Solution Overview

The solution is based in Excel and is saved as a macro enabled file (*.xlsm).   The solution is made up of sheets that represent domain specific forms and each form (or sheet) contains parameters specific to a single domain. I.E. software development estimating parameters are collected on one sheet, while software COTS estimating parameters are on another sheet. The parameters are generic in nature, but easily translate to TruePlanning inputs. There is a one to one correlation between forms and TruePlanning Cost Objects.

There are four major ways to use the COM enabled Data Input Forms:

1.       Paper Forms: The forms can be printed and presented for completion.

2.       Electronic Forms:  The Excel solution can be sent to users and users can enter data directly in the sheets that represent the forms.

3.       Automated Creation of Excel Electronic Form Workbook from TruePlanning Project: The solution can represent a TruePlanning project estimate as a set of data forms that represent the PBS.

4.       Automated Creation of TruePlanning Project from Electronic Forms:  The solution can take existing forms and using the TruePlanning COM API, create a TruePlanning project estimate.

 

Content

The Data Input Forms Excel solution contains forms that are product agnostic. This is a requirement for use in some organizations. That said, the estimation parameters map to the inputs of TruePlanning Cost Objects. Each form maps directly to a TruePlanning Cost Object. The Cost Objects included in the Data Input Forms Excel solution are:

System

Assembly

Software Component

Software COTS

Hardware Component

Hardware COTS

Call Center

Enterprise Software

Facility

IT Enterprise

Network Device

Peripheral Device

Server

Training

Workstation

 

For each form there is a second sheet that contains instructions on the estimation parameters. These instructions will help users understand what data needs to be provided for each parameter. Where appropriate the instruction sheets contain look up tables to help users.

Each estimation parameter is numbered, and the instruction sheets have numbers that correspond to the estimation parameters so users can identify which instructions belong to which estimation parameters.

Form Example:

 

Instruction Sheet Example:

 


Usage

The following sections provide details on the different ways to use the Data Input Forms Excel solution.

Printing

Users can simply use the print functionality in Excel to print the sheets to be distributed for data collection. The Data Inputs Forms Excel solution is provided with an empty template copy of each type of form. It is also possible to enter some data in the forms in Excel and then print the form partially completed.

Excel Entry

Users who do not have TruePlanning can be provided a copy of the Data Input Forms Excel solution and asked to enter data directly into the forms.  It is recommended that copies of the forms be made in the Excel files before entering data so there is always a pristine copy of the form available.

Automated Creation of Excel Electronic Form Workbook from TruePlanning Project

This is a feature of the Data Input Forms Excel solution that is based on the TruePlanning COM API. Therefore users need to have TruePlanning installed to use this feature. To use this feature click the “Open TruePlanning” button on the TruePlanning ribbon in the Excel menu.

 

 

After clicking the button, users are prompted to select the project to be exported into the Data Input Forms Excel solution.  This feature works through the TruePlanning COM API. Excel macros interrogate the selected TruePlanning project and identify each Cost Object in the selected TruePlanning project who’s type matches that of a form in the Data Input Forms Excel solution.

For each Cost Object found in the selected project, a new sheet is created in the Excel solution and the input data contained in TruePlanning for that Cost Object is entered into the new Excel form. So, if a project that contained a System, an Assembly, a Hardware Component, and a Software Component were selected, the following image would depict the new forms that would be created. The sheets that are created will be named in the following format:

{PBS #}. {Cost Object Name}

So if in the above example, the top Cost Object below the System Folder (the System Folder is not brought into the Excel Solution) was named “System”, you would see the Excel sheet with the name:

2. System


Each of the forms would be filled with the inputs found in the corresponding TruePlanning Cost Object.

 

The top most estimate parameter in all the sheets is the “Product Breakdown Structure Number”. When a project is loaded into the  Data Input Forms Excel solution, this parameter will be filled in automatically. The first part is a WBS type number that represents the Cost Object’s order in the PBS. This is a value that is created by the Excel solution and does not exist in TruePlanning. In addition to the WBS style number there is the Order number and the depths (or indent) number. These pieces of data are also provided automatically. The Order number will always match the number in the Excel Sheet name. The Depth number represents the level of indenture from the System Folder, where the System Folder has a depth of 1. Therefore all direct children of the System Folder will have a depth of 2.

 

Once a project has been loaded into the Data Input Forms Excel solution, the forms can be printed or the entire Excel solution can be sent to individuals so input data can be provided.

 

Creation of TruePlanning Estimation Project from Electronic Forms

This is a feature of the Data Input Forms Excel solution that is based on the TruePlanning COM API. Therefore users need to have TruePlanning installed to use this feature. Just as it sounds, this feature allows users to create TruePlanning project estimates using the data contained on the Data Input Forms. Excel solution. For even small projects, this can represent a huge reduction in effort getting data into TruePlanning.

The TruePlanning project will be based on the forms that are found in the Excel solution. To do this each sheet that is to become a TruePlanning Cost Object must identify its location in the TruePlanning Product Breakdown Structure (PBS). There is a data entry line in each form called: Product Breakdown Structure Number. This number should have two values: the “Order #:” and “Depth”.  The “Order#:” defines the form’s absolute order in the TruePlanning PBS, regardless of depth (or indenture from the System Folder). The “Depth:” parameter defines parent/child relationships.

Once users have set up the forms so the PBS is set up as they want it, they can press the “Push To TruePlanning” button in the TruePlanning ribbon in Excel. They will be asked for a name to give to the new project to be created in TruePlanning, and then a TruePlanning project will be created using the PBS defined in the Excel sheets and entering the inputs contained in the Excel sheets.

Users can then open TruePlanning and view their newly created project.

Generally, given the ease of creating PBS’s in TruePlanning, most users will start with a PBS in TruePlanning, and then pull that PBS to the Data Input Forms Excel solution. This will create copies of the forms for the PBS and guarantee the PBS ordering is set up properly when the project is pushed back into TruePlanning.

Also, because data can flow back and forth, it is easy to use the Excel solution throughout iterations of the TruePlanning estimate project. Existing inputs can be pulled into Excel, updated by an expert, and then pushed back.

Summary

Using the Data Input Forms Excel solution can help users save considerable time and effort collecting and entering data. The PBS can be created in TruePlanning where working with the PBS is easy. Then the estimation project can be exported to the Excel solution and the forms can be provided to users for completion. Because the inputs stored in the TruePlanning estimation project are transferred to the Excel solution, it is possible to perform data collection iteratively.

Please feel free to contact PRICE Systems L.L.C. support regarding any questions about the Data Input Forms Excel solution that arise. PRICE support can be reached at: http://www.pricesystems.com/support/contactinfo.asp

Or call 1.800.43.PRICE.