Y/TC-3290

Implementing Complex Costing Models for Schedules

Mark E. Plemmons Technical Computing Engineering & Technology Y-12 National Security Complex

paper to be presented at the Association for the Advancement of Cost Engineering (AACE) International 47th Annual Meeting, June 23-25,2003

Prepared by the Y-12 National Security Complex P.O. Box 2009, Oak Ridge, Tennessee 37831-8096 Managed by BWXT Y-12 L.L.C. for the U. S. DEPARTMENT OF ENERGY under contract DE-AC05-00OR22800

Disclaimer

This work of authorship and those incorporated herein were prepared by Contractor as accounts of work sponsored by an agency of the United States Government. Neither the United States Government nor any agency thereof, nor Contractor, nor any of their employees, makes any warranty, express or implied, or assumes any legal liability or responsibility for the accuracy, completeness, use made, or usefulness of any information, apparatus, product, or process disclosed, or represents that its use would not infringe privately owned rights. Reference herein to any specific commercial product, process, or service by trade name, trademark, manufacturer, or otherwise, does not necessarily constitute or imply its endorsement, recommendation, or favoring by the United States Government or any agency or Contractor thereof. The views and opinions of authors expressed herein do not necessarily state or reflect those of the United States Government or any agency or Contractor thereof.

This document has been authored by a subcontractor of the U. S. Government under contract DE-AC05-00OR-22800. Accordingly, the U. S. Government retains a paid-up, nonexclusive, irrevocable, worldwide license to publish or reproduce the published form of this contribution, prepare derivative works, distribute copies to the public, and perform publicly and display publicly, or allow others to do so, for U. S. Government purposes.

Implementing Complex Costing Models for Schedules

Abstract

Tools can be developed to use corporate accounting system data and complex costing algorithms to mimic the cost calculations of an accounting system. This capability can then be used to calculate the estimated cost of resources defined in schedules, providing an accurate and efficient "what if" capability for users.

Although the tool used for this case study interfaces with Primavera Project Planner® (P3) schedules, the same methodology can be applied to other commercial off-the-shelf scheduling or estimating software packages.

In addition to meeting the primary goal of accurately determining unit rates and associated planned cost for resources, additional features can be added to increase the program's functionality to increase the user's productivity. In addition, the tool's client server architecture can enable its use for other software. Examples are provided.

Introduction

Determining the cost associated with resource-loaded schedules is usually a matter of loading the resource unit rates into scheduling software and allowing the software to calculate the budgeted cost values for the resources assigned to each schedule activity. When the corporate cost model is too complex for this approach, a commercial costing package often can be utilized. However, there are instances in which a custom solution is required.

Such is the case with the government facility where I work. SAP is used for the corporate accounting system. The rate for a given resource depends on a multitude of factors, including fiscal year, overhead key (based on location of work and type of funding), and SAP planning version. To complicate matters, a single project can have more than one instance of each of these factors.

Primavera Project Planner® (P3) 3.1 is used to develop resource-loaded schedules. A significant step toward integrating P3 with the accounting system was deciding to use SAP cost elements and "activity types" as resource codes for scheduling. In addition, SAP charge numbers were associated with schedule activities using activity code fields.

While P3 provided some cost-escalation capability for defined resources, it was unsuitable for the needs of the company. In addition, a different method of spreading resources was required to accommodate the company business months, which differed from calendar months.

Import P3 schedule into Cobra®

Create an Excel crosswalk file to map P3 WBS, SAP project, and SAP charge numbers

In Access, import the Cobra® data, read the crosswalk file, and create a file compatible with the SAP import routine

Import the file into SAP, run a transaction to apply indirect cost against the planned resources, and run a report to view the results

Figure 1: Previous Process

An alternative method of calculating resource costs was put into place using Cobra®, a cost program by WST Corporation. In addition to computing budgeted cost and having the capability to spread resources using business months, the cost program was to be used for performance reporting. However, importing large P3 schedules into Cobra® was quite time-consuming, often taking well over an hour. More importantly, it was determined that the software could not support the number of overhead keys in use by the company.

It was then decided that Cobra® would be used to import data from P3, but the resource data would be loaded into SAP for calculating planned cost. In addition, performance reporting would be done in SAP instead of Cobra®, eliminating the need to download actual quantities and cost from SAP. A multi-step process was defined to import P3 resources into SAP for pricing (Figure 1.)

While this process produced accurate planned cost for schedules, two problems remained. The first involved the excessive time required to load the resource quantities into SAP, which often took hours. The second problem was that the resource spreading algorithm used by Cobra® was different than the one that was needed.

System Design

To overcome these limitations, a new utility program was designed. The program provided the capability to use data exported from SAP and schedule resources from P3 to accurately calculate the planned cost for a project. The interface was designed to be seamless for P3 users, minimizing workflow disruption.

Most of the SAP data needed was already available in the corporate data warehouse, so initial system design was based on that data source. Ultimately, however, a direct data feed from SAP was implemented to provide additional capability and support more users.

Because schedules were not the only system tools and reports that required resource rates, the system was functionally separated: the workstation component (called "P3Bud") interfaced with P3 schedules, and the server component (called "Schd") calculated the resource rates. The benefits of implementing the system as two components grew increasingly clear as development progressed.

To access the Btrieve files that P3 uses for schedule data, the Open Database Connectivity (ODBC) application programming interface (API) for database access by Microsoft® and the Btrieve ODBC driver from Primavera were used. The development language for both components was Microsoft® Visual Foxpro. Microsoft® Distributed Component Object Model (DCOM) methodology was used to communicate between the components. This allowed the workstation component to issue remote procedure calls to the server component, giving the application several advantages. The advantages included having a single server application ("Schd") running on a centralized server for several front-end ("P3Bud") applications; having a single location for rate table information; and accessing files locally instead of over the network, which dramatically increased the bandwidth for both front and server applications.

Implementation

Configuration on computers used by schedulers included installing the ODBC driver, defining the directories where schedule data are located as ODBC data sources, and configuring P3 to launch the front end component (P3Bud) from the P3 tool bar. Adding the component to the tool bar was accomplished easily by modifying P3's initialization file, p3.ini.

Before launching the application, the user updated the files used by the ODBC driver by selecting "update data dictionary" from the P3 tool bar. The user then starts the workstation application by selecting the toolbar option specified in the p3.ini file.

Upon execution, the P3Bud application presents the user with a window for preferences to be defined. Selectable preferences include filter criteria, planning version, and whether to save the calculated budgeted cost values or just display them on a report.

After preference selection is complete, the application reads the schedule data using the ODBC API. The program then initiates a connection with the server (Schd) component. For each resource in the schedule, a rate is determined by the server rate calculator and returned to the workstation (P3Bud) component, where planned cost is determined. To be consistent with the P3 interface, the standard P3 reporting program (PrmLook.exe) is used to show the calculation log file.

In addition to P3Bud, additional interfaces to the server component were designed and implemented for other tools. The tools included a web-based resource rate calculator, a web-based report showing internal time charges and the associated cost, and projected overhead costs for remaining commitments on projects.

Results

Because SAP data is used and the indirect cost multipliers are applied using the same method SAP uses to apply them, the planned cost calculated by the new tool were very close to the official planned cost by SAP after the data was transferred from P3 to SAP. Variance between the value calculated by P3Bud and the value from SAP is well below 1%, and usually within .001%. This exactness allows schedulers to be confident of the planned cost associated with what-if schedules prior to loading into SAP. This ability is particularly important when the proposed funding level for a project is fixed and the scheduler is tasked with determining the scope that can be accomplished for the available funding.

The budgeted cost calculation performed by this tool is not only accurate but fast as well. A large resource-loaded schedule containing 1,000 activities and 4,000 resources can be "priced" in less than 5 minutes. This is a dramatic improvement over the previous process, which had at times taken hours.

Since its implementation, the tool has been configured for use by dozens of schedulers. It is currently being used by more than 70 individuals, and is executed more than 1,000 times each month. There was some initial concern that the server component might become overtaxed with so many heavy users. However, monitoring its use has determined that there is seldom simultaneous use by more than one user, and at no time have more than three people used it at any given time. This workload is not a problem for the server, although it, no doubt, slows the calculation speed a bit for when more than one user runs it concurrently.

Enhancements and Expansion

As with any in-house tool, additional features were identified and requested, and some have been implemented. These included the following:

P3Bud: prices P3 schedules

ShowTime: web report that shows projected cost of internal time charges

RateCalc: web report that calculates the loaded rate for a resource

RemCom: calculates and stores projected overhead for remaining commitments for various reports

Estimating software: develops project cost estimates

Figure 2: Clients for Schd

Identifying assessment planned cost from SAP. This type of cost is not directly related to project execution but is simply assessed against projects. Examples include the cost of electricity, telephones, and maintenance. The planned cost is entered manually into SAP and downloaded for use by P3Bud so that total planned cost can be shown for a project.
Modifying the tool to use multiple ways to spread resource quantities. Front-end loaded, bell-shaped, and other spreads defined for a Primavera schedule are implemented accurately across business months.
Creating tab-delimited files containing resource spreads and percent-complete information for direct upload into SAP. The values are summarized by SAP charge number based on the schedule code field containing the charge number values. Implementing this feature allowed Cobra® finally to be retired from use.
Using the resources on the target (baseline) schedule instead of the current schedule to weight the percent-complete values when summarizing by charge number.
Developing the capability to do "snapshots" of the data used for rate calculation. This feature was requested by the Estimating organization, whose in-house estimating program had been modified to use the same server component the schedulers used. The estimators require the ability to rework estimates from previous years using the rates in place at the time, thus the need for data snapshots. The estimating software became a new client for the Schd server component (Figure 2.)
P3Bud usage for classified (secret) projects. A separate network exists for the company to support classified projects. For this network, there is no data warehouse from which SAP data could be obtained. To implement P3Bud, a separate data feed directly from SAP was created. Each day, a process executes in SAP to update the data files containing the information Schd requires. The data is located in a folder accessible to authorized users on Microsoft® Windows-based computers. A corresponding process executes on the Windows side to read the updated files and convert them to the format needed by Schd. An update methodology is used to keep the data available to Schd users during the short update process. This improved method of obtaining data was transferred to the unclassified network so that a single system design could be maintained for both networks.

Conclusion

Implementation of this tool has resulted in a significant efficiency gain for many schedulers. It has led to the use of a single, accurate rate-calculation tool for several existing and new corporate applications, minimizing code duplication and the need to determine why different reports contain values that do not match. It also has incorporated new and useful functionality, such as providing a simple method of loading planned resources into SAP and the capability to snapshot rate information for use at a later date.

This tool illustrates the advantages and benefits that can be realized by developing in-house data applications to meet specific internal needs. In this case study, the result has been the development of an application that increases project control staff productivity and provides meaningful what-if capability for an organization with complex costing rules.