July 20, 2020
In an integrated cost/schedule environment, activities in a schedule are identified with their control account points (that is, control accounts or work packages) in an earned value management system. This integration allows budgets and forecasts to accurately reflect the most current actual and estimated scheduling information.
By Alan Kristynik, PMP
You can create an integrated cost/schedule system between Deltek Cobra (Cobra) for multi-program cost engine for Earned Value Management (EVM) and Microsoft Excel Files.
By linking a Cobra project with a scheduling tool, you can have a system that provides for single data entry. This avoids both the extra effort and the increased risk of error associated with having to enter the data in both the schedule and Cobra. The detailed resource-loaded schedule provides the time-phasing of the budget in Cobra while status data from the schedule generates earned value in Cobra.
The Cobra Integration Wizard also allows you to load code files from your schedule. You may save only the settings you have made for your configuration, or you may synchronize Cobra with your schedule as well.
Share Cost and Schedule Information
When planning to integrate your Cobra project with a resource-loaded schedule, consider the best practice tips below.
- An activity in the schedule should correlate to a work package in Cobra.
- Activity durations should be from six to eight weeks in order to calculate accurate earned value (by collecting actual costs at the cost account level, you can reduce the number of charge numbers). Note: Activities with longer durations can be used if the progress technique of their work packages is Percent Complete. In this case, the Physical Percent Complete entry in the schedule should be based on something that can be measured objectively, such as the number of completed drawings.
- Budget revisions should begin in the schedule as additions or revisions and be updated in Cobra.
Because Cobra supports the milestones progress technique, it is also possible to set up a system where several activities in the schedule generate a single work package with milestone steps in Cobra. In this scenario, the baseline finish dates of the activities in the schedule can be correlated to the milestone steps in the work package. Note, however, that the earned value is calculated as a weighted percentage for all budget elements of the work package.
While Cobra does have the ability to build the schedule and load the budget manually, this process does not promote integration between cost and schedule. The best usage of this feature would be to add the following items into the Cobra project:
- Level of effort and apportioned work packages
- Planning packages maintained in Cobra
- Budget revisions made in Cobra
- Adjusted resource hours updated in the scheduling software
You can then use powerful features in Cobra such as top-down planning to convert these resource loadings into a fully costed budget baseline, and you can update the schedule with data from Cobra.
The Integration wizard is a user-friendly feature that allows you to define how your Cobra project should be created or updated from your schedule. The Integration Wizard also allows you to load code files from your schedule. You may save only the settings you have made for your configuration, or you may synchronize Cobra with your schedule as well.
The following is a list of items that can be loaded from a schedule:
- Code files
- Resource definitions and rates
- Budget
- Code assignments
- Status
- Forecast
Prepare Cobra for Schedule Integration
It is possible to load information such as rates from your schedule using the Integration wizard.
If the information in the schedule is not accurate, you can create these files manually before using the Integration wizard and refer to them during the integration process. You may need to create the following files before the integration process:
- Code Files
- Resource Files
- Rates
- Template Resource Files
- Calendar
Cobra Project
You can create an empty project in Cobra into which to load the schedule data. For example, if your schedule contains cost classes other than current budget (CB), you should create a blank project with all valid cost classes defined before loading the budget.
Note: When loading data into an existing project, delete items from Cobra that are not in the schedule option located on the Action Selection page of the Integration wizard.
If the work package record does not exist, Cobra creates the appropriate progress technique and sets the work package start and finish dates exactly equal to the activity dates. If the work package record exists at the time you load the schedule, Cobra adjusts only the required data. For example, if the activity has slipped and the finish date is greater than the work package finish date, Cobra adjusts the work package finish date, not the work package start date.
Code Files
There are three ways to create code files and breakdown structures:
- You can have the Integration wizard automatically create these files, in which case Cobra determines the structure type from the data in the schedule.
- You can create the empty code files manually before running the Integration wizard. This enables you to specify the structure of your choice. The Integration Wizard then populates the code file with the values found in the schedule.
- You can create and populate the code file before loading the schedule. This enables you to validate the entries in the schedule against the code file.
Note: If you want to use a non-significant breakdown structure in Cobra that is punctuated in the schedule, you must create the non-significant breakdown structure before the integration process. You can then load the codes from the schedule into the code file.
Resource Files
You can roll up resource information by defining the budget element calculations for a higher-level resource. It is possible to roll up resource information by defining the budget calculations for higher-level resource but not for lower-level resource. The lower-level resource requirements can be combined and rolled up to a higher level for costing purposes.
Cobra loads the unit rate from the resource file only if the value for the rate is any value other than 0 or 1. If the rate for the resource is not equal to 0 or 1 and a calculation template is not used, Cobra creates resource assignments with the result of HOURS and DIRECT, where the direct rate comes from the rate defined in the schedule. If the rate for the resource is equal to 0 or 1, Cobra creates the resource assignment with a single result of DIRECT.
Rates
You can load direct rates from the schedule using the Integration Wizard. If you have results other than direct, enter these rates into the rate file before loading budgets or forecasts. For example, if you plan to use a template resource file, you will need to create OVERHEAD and G&A rates.
Template Resource Files
You can define complex burden structures to be used during the load resource definition process in the Integration wizard by creating a template resource file.
Calendar
You can create a calendar in two ways:
- You can have the Integration wizard automatically create the calendar file.
- You can create the calendar file manually before running the Integration wizard. This enables you to adjust period cut-off dates and working hours before the baseline is loaded.
Note: Cobra determines baseline spread by fiscal cut-off dates and working hours.
Data Import Using Files
With the Integration wizard, you can import project data into Cobra using import files.
You can import a Cobra project from a file saved in Excel or text format. This file includes activity information, baseline resources, forecast resources, status, coding, code assignments, and notes. You can import budget, forecast, and status data either individually or during the same import process. You must import notes by themselves and cannot be combined with other actions on the Action Selection page of the Integration wizard. You cannot use this process to import ancillary data such as resources, rates, calendars, and codes. Before importing from files, you must create a Cobra project with the necessary ancillary files assigned.
Import Files for Activity and Resource Assignments
The import file must contain the required columns when importing data from a CSV file or an Excel spreadsheet.
| Field | Type | Required? |
|---|---|---|
| CA1 (Control Account) | Character | Yes |
| CA2 (Control Account) | Character | Yes |
| CA3 (Control Account) | Character | |
| WP (Work Package) | Character | Yes |
| Milestone Step | Character | |
| Milestone Step Weight | Numeric | |
| Description | Character | |
| Baseline Start Date (BSD) | Date | Yes |
| Baseline Finish Date (BFD) | Date | Yes |
| Forecast Start Date (FSD) | Date | Yes |
| Forecast Finish Date (FFD) | Date | Yes |
| Early Start Date (ESD) | Date | |
| Early Finish Date (EFD) | Date | |
| Late Start Date (LSD) | Date | |
| Late Finish Date (LFD) | Date | |
| Progress Technique (EVT) | Character | |
| Units to Do | Numeric | |
| Budget Class | Character | |
| Forecast Class | Character | |
| Resource | Character | Yes |
| From Date | Date | |
| To Date | Date | |
| Curve | Character | |
| Individual <Results> | Character | Yes |
| CAM | Character | |
| Work Package Manager | Character | |
| Control Account Location | Character | |
| Work Package Charge Number | Character | |
| User Character Field (1-5) | Character | |
| User Number Field (1-5) | Numeric | |
| User Date Field (1-5) | Date |
This is a sample format for loading activities and resource assignments:
| WBS | OBS | WP | BSD | BFD | EVT | Resource | Hours | Direct |
|---|---|---|---|---|---|---|---|---|
| 1.1.1 | MGT | 1.1.1.WP01 | 8/15/2020 | 12/31/2020 | A | PMGR | 500 | |
| 1.1.1 | MGT | 1.1.1.WP01 | 8/15/2020 | 12/31/2020 | C | SENG | 250 | |
| 1.1.1 | MGT | 1.1.1.WP01 | 8/15/2020 | 12/31/2020 | C | TECH | 300 | |
| 1.2.1 | ENG | 1.2.1.WP01 | 8/15/2020 | 12/31/2020 | C | TECH | 300 | |
| 1.2.1 | ENG | 1.2.1.WP01 | 8/15/2020 | 12/31/2020 | C | SENG | 400 |
- The import file must contain a new row for each resource assignment of a work package. All results used by the resource calculations are available as columns which can be loaded from the import file.
- Resources are spread across the activity dates as follows:
- Using the From Date and To Date fields, you can specify the exact value to place in each period. The resource value is spread across these dates. For example, if the From Date and To Date are in the middle of the month for a resource, the amount specified is placed in a single period. If the from date and to date span several months, the amount is spread over those periods instead. On the Resource Assignments page of the Integration wizard, there is an option to spread a resource across activity dates. This spreads the amount linearly across the activity dates.
- There is also an option to use curves for resource spreads. The curve name entered in the resource file must match the curve name on the Spread Curves tab of the Application Preferences dialog box.
- Resources with multiple records are summed up based on the key fields and the From Date and To Date. If there are multiple records with different From Date and To Date, Cobra will load each amount over each period specified.
Import Files for Budget and Forecast Information
The import file must contain the required columns when importing data from a CSV file or Excel spreadsheet:
| Field | Type | Required for Baseline? | Required for Forecast? |
|---|---|---|---|
| CA1 * (Control Account) | Character | Yes | Yes |
| CA2 * (Control Account) | Character | Yes | Yes |
| CA3 * (Control Account) | Character | Yes | Yes |
| WP * (Work Package) | Character | Yes | Yes |
| Milestone Step | Character | ||
| Milestone Step Weight | Numeric | ||
| Description | Character | ||
| ID | Character | ||
| Resource | |||
| Individual <Results> | |||
| Baseline Start Date (BSD) | Date | Yes | |
| Baseline Finish Date (BFD) | Date | Yes | |
| Forecast Start Date (FSD) | Date | Yes | |
| Forecast Finish Date (FFD) | Date | Yes | |
| Early Start Date (ESD) | Date | ||
| Early Finish Date (EFD) | Date | ||
| Late Start Date (LSD) | Date | ||
| Late Finish Date (LFD) | Date | ||
| Progress Technique (EVT) | Character | ||
| Units to Do | Numeric | ||
| CAM | Character | ||
| Code 1 * | Character | ||
| Code 2 * | Character | ||
| Code 3 * | Character | ||
| Code 4 * | Character | ||
| Code 5 * | Character | ||
| Code 6 * | Character | ||
| Code 7 * | Character | ||
| Code 8 * | Character | ||
| Code 9 * | Character | ||
| Budget Class | Character | ||
| Forecast Class | Character |
Note: Fields marked by * are displayed as the prompt defined on the Project Properties dialog box. Only the key fields defined on the Project Properties dialog box are required.
- On the Integration tab of the Application Preferences dialog box, there is an option to save temporary integration tables. By saving the temporary tables, you can examine how the CSV or XLS file is interpreted during the import.
- When you select to import resource assignments, you must use the ID field to correlate the activity file to the resource file. When importing from a scheduling system, this field is usually the activity ID. However, you can concatenate the WBS, OBS, and work package to generate a link between the two files.
- When importing control account level codes such as the CAM, the WP code must be blank to indicate a control account level data.
- When importing from a single activity and resource assignment file, take note of the following:
- CA1 and CA2 depend on what has been defined in the project.
- HOURS is generally the base result, but you can also select multiple columns with different results (for example, HOURS, DIRECT, OVERHEAD, GANDA). The Individual <Results> column displays the results as HOURS, DIRECT, OVERHEAD, or GANDA depending on what is defined in the resource calculation file. Only one Result column is required.
- The Apportioned progress technique is not supported for the integration process. If you want to use this progress technique for integration, you must manually select it on the General tab of the Project view.
- When importing progress techniques, use the corresponding progress technique letter code.
| Field | Type | Required for Baseline? | Required for Forecast? |
|---|---|---|---|
| ID | Character | Yes | Yes |
| Resource | Character | Yes | Yes |
| Amount | Numeric | Yes | Yes |
| From Date | Date | ||
| To Date | Date | ||
| Class | Character | ||
| Curve | Character |
The following options identify how resources are spread across the activity dates:
- Using the From Date and To Date fields, you can specify the exact value to place in each period. The resource value is spread across these dates. For example, if the From Date and To Date for a resource are in the middle of the month, the amount specified is placed in a single period. If the From Date and To Date span several months, the amount is spread over those periods instead.
- On the Resource Assignments page of the Integration wizard, there is an option to spread a resource across activity dates. This spreads the amount linearly across the activity dates.
- There is also an option to use curves for resource spreads. The curve name entered in the resource file must match the curve name on the Spread Curves tab of the Application Preferences dialog box.
- Resources with multiple records are summed up based on the key fields and the From Date and To Date. If there are multiple records with different From Date and To Date, Cobra will load each amount over each period specified.
Import File with Data from Multiple Projects
When loading data into Cobra using Integration » File, you can import data for multiple projects from a single file, without loading against a master project.
Cobra will split the data for each individual project and load them during the integration, instead of having to load each project from individually created files.
The following rules apply when loading an import file with multiple projects:
- This only applies to Integration » File
- You can load multiple projects for any of the action selection processes that load project data: Control Account and Work Package, Code Assignments and User Fields, Resource Assignments, or Status.
- On the Project Selection page, select the Defined in File field to load multiple projects from an import file.
- If the Defined in File field is selected, specify a project in the Use field mapping from Project Cobra will use the specified file to determine the number of key fields that can be selected on the Import File Field Mapper page, as well as the default classes that can be used.
Note: If you are loading multiple projects, all the projects must have the same number of Control Account Key fields as the project selected in the Use field mapping from Project field. If the key fields for a project being loaded do not match, Cobra displays an error in the log and skips loading that project.
- You must have update rights to the projects being loaded from the import file. If you do not have update rights to a project being loaded, Cobra skips that project and displays an error in the log.
- Cobra processes each project in sequence and stores log information in a single process log. The log is associated with the project selected in the Use field mapping from Project
- Since each project is loaded separately, the baseline changes are stored in each individual audit log for each project.
Here is an example of an activity file with data from multiple projects:
| Project | WBS | OBS | WP | Description | ID | BSD | BFD | FSD | FFD |
|---|---|---|---|---|---|---|---|---|---|
| PROJ.001 | 1.1.1.1 | 1400 | Completed CA | 1.1.1.11400 | 6/01/2020 | 6/30/2020 | 6/01/2020 | 6/30/2020 | |
| PROJ.001 | 1.1.1.1 | 1400 | WP01 | Completed WP | 1.1.1.11400WP01 | 5/01/2020 | 6/30/2020 | 5/01/2020 | 6/30/2020 |
| PROJ.002 | 1.1.2.1 | 1600 | WP01 | In Progress WP | 1.1.2.11600WP01 | 7/01/2020 | 9/30/2020 | 7/01/2020 | 9/30/2020 |
| PROJ.002 | 1.1.2.1 | 1600 | WP02 | In Progress WP | 1.1.2.11600WP02 | 7/01/2020 | 9/30/2020 | 7/01/2020 | 8/31/2020 |
Summary
A best practice to consider and embrace within an organization deploying Microsoft Excel and Deltek Cobra is to document desktop instructions for the EVM user community to access as needed. Promote and keep “evergreen” as programs and organizations mature in EVM Capability.
This topic is the last of four in my Deltek Cobra cost/schedule data sharing series:
 |
Alan Kristynik, PMP, GWCPM, is a Senior Associate in SMA’s Integrated Program Planning & Controls (IPPC) Practice, and has a Deltek Cobra Master Certification. He has been a Certified Project Management Professional with PMI since 1998, has an active DoD Top Secret clearance, and is a Subject Matter Expert in Earned Value Management Systems (EVMS). |
If you’re building a team and you have positions you can’t fill, you need to use SMA Talent on Demand (TOD®)! With TOD®, you can find experienced talent, such as Alan, matched to your exact needs: