A Solution for Developing the Algorithm of Transferring Cost Estimation Data to CPM

Fifth International Conference on Construction in the 21st Century (CITC-V)

“Collaboration and Integration in Engineering, Management and Technology”

May 20-22, 2009, Istanbul, Turkey

A Solution for Developing the Algorithm of Transferring Cost Estimation Data to a CPM Program via Construction Management ERP Software

Ahmet Avinal
A Professional on Construction Management, Lecturer, University IKU, YTU, İstanbul, Turkey

This email address is being protected from spambots. You need JavaScript enabled to view it. , This email address is being protected from spambots. You need JavaScript enabled to view it.

Abstract

The main cause for poor execution of “Work Schedules” for construction projects is mainly related with the requirement of heavy data input on the CPM (Critical Path Method) software and the difficulties in data updates for each change on the project. We may summarize the primary cause for inadequate planning as being incapacity of access to the data.

As a matter of fact, the required data for CPM programs is covered in the cost estimation information of the construction work. The transfer of data entries to the CPM software through an algorithm and the right tool (software) shall make it easy to overcome the needless burden of data entry, which will assist the “Planning Professional” to focus on his/her main duties.

A cost estimation report takes place on every construction project, defining the work in quantities and the cost. On a cost estimation report, each production is defined with its item number, definition, quantity, unit price, location (detailed bill of quantity – BOQ )

In order to have a clear cost estimation report, a need for classifying production items may come out, based on their similar characteristics and their related building information.

Each production item of the estimation should take place as a resource belonging to related activities of the CPM schedule containing both cost and location information. Otherwise the project schedule is incomplete and most probably insufficient.

In other words; the data combined to develop the scope of the construction should be the same as the input of the CPM software. Only the formats may vary.

- The total cost information related to the activities should match the monetary amount of the scope of construction.

- If the productions are assigned to the activities as primary resources, the quantity and cost values belonging to these activities should match the cost and quantity amount of productions composing the scope of works.

In order to ensure keeping the same exact data, despite in different formats, both on CPM software and cost estimation, the quest to come up with an algorithm for this transfer has activated the practice and formed the solution exclusive for construction business.

Keywords
Construction management, CPM software, Primavera, ERP (Enterprise Resource Planning) Systems on construction management.

1. Introduction

When it comes to the management of the construction projects, the most significant part is proper planning. To be in a position to claim efficient execution of planning on a construction project, it is required to assume that;

- All crew members and the supervisors of the construction project teams receive the daily or weekly work orders from the CPM software.

- Regular updates to the CPM software are accomplished via information of executed works on the project or works in progress on site.

We should separate the expectations related to the functionality level and developing process of CPM software, as pre-ERP systems period and post-ERP systems era. The expectation had always been too high from CPM software during the times when there were no data sharing and easy access to the information.

During Pre-ERP period, CPM software systems had been considered as a solution for processes such as “Budgeting, Creating Cash in-Cash out Tables, Resource distribution, procurement orders and etc.”

Substantially, CPM software is solely a planning and management tool to assure the proper execution of work by reporting the scope of timing and priority for activities and productions to the related units. The coverage for timing of activities and production is also a need for ERP systems. By means of including the timing information to the known facts such as quantities and financial issues, it is possible to create an allowance for proper execution of processes such as “Budgeting, Cash in - Cash out Tables, Resource Distribution, and Procurement Orders” .

In this day, exclusively on construction practices, it is out of the question to separate the CPM software and solutions from the ERP systems, a “Construction Data Management” system.

2. Sample Project

The illustrated schedule is controlled over the Primavera software version 3.00. Based on this fact, instructions are made considering the data structure of Primavera software. You may download the Primavera P3 back-up file for the sample project over the internet via the link http://www.avinal.com/smpl.prx and activate “SMPL” project by the “Restore” command.

The processes described in this bulletin are the data that has been integrated in ERP systems’ modules. In order to help you to keep up with the comments and behold the essential data regarding the solution, an “Excel” file has been created by transferring the data to different sheets. You may download the file from the internet via the link http://www.avinal.com/smpl.rar

2.1. Sample Project Details

This sample, reviewed in accordance to the ERP system which is exclusive for construction business, consists of 94 Blocks and 2000 housings.

Containing 740 various production items, this scope is composed of 32850 rows, taking into consideration 9750 rows for productions of each Block and the BoQ information (location information of productions based on story levels of the blocks).

- Sample: Cost Estimation Report, 9750 rows (smpl.xls, sheet “BOQ”)

- Sample: Cost Estimation Report detailed BOQ, 32850 rows (smpl.xls, sheet “Detailed BOQ”)

- Sample: Production Items (smpl.xls, sheet “Resources”)

Taking place as production items on scope of works, 740 items transformed into 740 resources located in the Resource Library of the CPM software (Primavera). 9750 rows of data which were involved in the scope of works as monetary and quantity values morphed into 12000 activities on the schedule of works. The 32850 rows of information included as detailed BOQ values on scope of works, are expressed with their monetary and quantity values once more for the detailing of resources for activities on CPM software.

-Sample: 12000 activities (smpl.xls, sheet “Activity Analysis”)

None of the data input had been assigned manually on the Primavera file namely SMPL. Essential data required for proper monitoring of a CPM schedule is covered in the section “Data that is needed to be identified on CPM Software”. These ten thousands of data had been gathered from the various other modules of ERP system by filtering through a suitable algorithm. Speaking of data entries, the time to reorganize a Primavera file like this one does not exceed 25 or 30 minutes.

On this file, people in charge of planning, only made judgments for activity durations and activity dependencies and created links between activities. In other words, they simply accomplished their professional liability.

During the heavy traffic of works, creating a CPM file apart from an ERP system and updating the file following each change seems highly impossible. Data such as actual completion progress (AS: Actual Start, AF: Actual Finish) for activities and their related resources of a Primavera file is created through the information that was transferred to Primavera database from the ERP system.

Figure 1: An Overview on the Scope of “Construction Data Management”

If we separate a construction projects’ phases as seen on the figure above and such as mentioned below:

Feasibility – Preparation – Planning – Execution – Realization – Measurement and Evaluation – Result and Feedback

We can easily notice that during each phase of a construction project, each one has its specific database with an algorithm for operation (or each one should have a database like this).

ERP software which is designed exclusively for construction business should be consisted of modules and different work-specific databases. The whole database framework (modules) should include the data related to their contents, along with a proper algorithm and a structure which are convenient for the functionality.

When necessary, they should be able to report the data in accordance with the functional purposes.

One of the fundamental characteristics of ERP software is the capability of providing access to the data input on each module where necessary. When taking into consideration of this principal, it is quite possible to end up with questions such as:

- What are the exact data needed by a CPM software which is considered as the main control unit for Construction Management System?

- Data existing on the other modules of the system or not?

- In which form the data taking part in another module should be transferred to CPM module and in which way transferred data should be processed on CPM module.

The purpose of this paper includes the following;

* The data required to take part on the CPM software is already included in the scope of works for the project.

* Data belonging to this scope can easily be transferred to the CPM software through a convenient algorithm despite having a different format even in minutes.

* Whole coverage for changes and updates for a schedule of a project which is in progress can be provided by the other modules integrated in the ERP system.

3. Data Needed to be Identified on the CPM Software

The main data entries to be determined for a new project are as follows;

1. Activity ID
2. Activity description
3. Activity codes
4. Resource library definitions
5. Cost values for resources
6. Assigning resources to the activities along with their quantity amount
7. Assigning resources to the activities along with their cost values
8. Creating the WBS (Workbreak Down Structure)
9. Assigning personnel in charge for technical works to each activity
10. Assigning personnel in charge for production to each activity

Following the completion of data input, we may summarize the works that should be done by the Planning Engineer are as follows:

11. Determining the original durations for each activity
12. Creating links and dependencies between the activities

Now we shall review the 9750 rows example scope of works file which was transferred to MS Excel format (smpl.xls, sheet “BoQ”). For starters it is needed to be clarified *that* which column has what type of information and the roots of the data (the original primary input of the data to which module?)

Item Number and Description: This one is the production code and the general description for that production. Usually this information is included in the Unit Price Catalog or takes place in the Analysis Database as resources defining each production unit along with the content and unit price of the related item.

Quantity: The information calculated on the “Measurement Module” and derived from this module.

Unit and Unit Price: Unit price of the related production. It has been created on the Analysis database.

Group: On this project the descriptions under the Group Column defines the type of each building.

Sub-Group: On this project the numbers under the Sub-Group columns identify the sequence of each block.

Table 1: Group and Sub-Group

Group	Description	Sub-Group	Description
A	BLOCK A	10	1^st BLOCK
B	BLOCK B	20	2^nd BLOCK
C	BLOCK C	30	3^rd BLOCK
D	BLOCK D	40	4^th BLOCK
E	BLOCK E	50	5^th BLOCK
F	BLOCK F	60	6^th BLOCK
G	BLOCK G	70	7^th BLOCK
H	BLOCK H	80	8^th BLOCK
I	BLOCK I	90	9^th BLOCK
J	BLOCK J	A0	10^th BLOCK
K	BLOCK K	B0	11^th BLOCK
L	BLOCK L	C0	12^th BLOCK
M	BLOCK M
P	CAR PARK AREA

Table 2: Job Classes

Code	Description	Code	Description	Code	Description
01	FOUNDATION WORKS	12	WALL FINISHES	41	SEWAGE WATER PIPING
02	CONRETE WORKS	13	DOORS	42	STORM WATER PIPING
03	BACKFILLING WORKS	14	FAÇADE WORKS	43	SANITARY SYSTEMS
04	MASONRY WORKS	15	HAND-RAILS WORKS	44	HEATING SYSTEMS
05	WATER INSULATION	16	PVC COATING	46	PLUMBING
06	HEAT INSULATION	18	PARQUET WORKS	50	EARTHING (GALVANISED)
07	SCREED WORKS	19	INSTALLATIONS	51	EARTHING SYSTEM
08	PLASTERING	20	REMOVAL OF FORMWORKS	52	PIPING FOR ELECTRICAL SYSTEMS
09	GYPSUM BOARDS	21	ELEVATOR ORDERS	54	POWER PANEL
10	ROOF COVERING	22	INSTALLATION (ELEVATORS)	55	ELEC. INSTALLATIONS & TESTING
11	FLOOR FINISHES	40	M & E WORKS

Code: Defines the similar production groups of the scope.

Table 3: BOQ Location Codes

Location	Description	Location	Description	Location	Description
000	Foundation	500	Level 5	BB0	Level 11
010	Over the Foundation	510	Columns Level 5	BK1	Columns Level 11
030	Basement Columns and Shear Walls	600	Level 6	CC0	Level 12
050	Basement Slabs	610	Columns Level 6	CK1	Columns Level 12
100	Level 1	700	Level 7	EL0	FAÇADE
110	Columns Level 1	710	Columns Level 7	MM0	Stairs & Ladders
200	Level 2	800	Level 8	MM1	Stairs Level +4.45 m.
210	Columns Level 2	810	Columns Level 8	ORT	Common Areas of Usage
300	Level 3	900	Level 9	T10	ROOF
310	Columns Level 3	910	Columns Level 9	T20	TERRACE
400	Level 4	AA0	Level 10
410	Columns Level 4	AK1	Columns Level 10

Location: The definition of this BOQ location code which is used to determine the location information of the production is as follows:

The terms for creating the data that is needed to be identified on CPM software via ERP system are as follows:

1. Activity ID:

For Activity ID numbering process, we use a formula that contains the content of data mentioned above on each table and prefer the order as follows:

“Group + Sub-Group (1 Character Set) + Code + Location (2 Characters Set)

We end up with Activity ID numbers such as A10000, A10203, A10210, A10402. People who are aware of the structure of numbering mentioned above will not face difficulties to comprehend the activities with the following definitions:

A10000,    Block A1 Earth Works, Foundation
A10210,    Block A1 Concrete Works, Basement Columns and Shear Walls
A20210,   Block A2 Concrete Works, (for slabs) Level 1
E20402,    Block A2 Masonry Works Level 2

Apparently, using only a single function we created 12270 Activity ID numbers belonging to the scope of a construction project consisting of 9917 rows and 94 blocks.

Note: This arrangement may vary according to different construction projects. There is no such rule that limits the structure of Activity ID numbering.

2. Activity Description

Using a similar formula for definition process as follows;

Code Description + Location Information

A10000,    EARTH WORKS, Foundation
A10210,    CONCRETE WORKS, Basement Columns and Shear Walls
A20210,    CONCRETE WORKS, Level 1 (Slabs)
E20402,    MASONRY WORKS, Level 2

We can create all of the rows for activity definitions.

3. Activity Codes

If we check the structure for Activity ID Codes on Primavera file, we realize the following;

SUBP field contains a 2 characters set and it is constructed by obtaining 1 character set from Group and Sub-Group information.

IMLT field contains a 2 characters set and it addresses the 2 characters of CODE field.
KAT field contains a 2 characters set and it obtains the first 2 character of the MAHAL field.

4. Creating a Resource Library on Primavera

5. Defining Cost Values for Resources

On the sample Primavera file in Resource library which is located under the “Data-Resources” section, we may easily notice that 740 productions of the scope are taking places as resources along with their item numbers, descriptions and unit prices. This data is sent from the related ERP module.

6. Assigning Resources to the Activities along with their Quantities

7. Assigning Resources to the Activities along with their Cost Values

On this project, activity analyses are created using primary resources (production items) and the quantities and cost values of productions are assigned to the resources. By means these activity analyses of the productions it was possible to assign the secondary resources (such as quantity and cost values for materials and labor) to the activities if necessary or needed.

If you check the “RES” and “COST” values for more than 12000 activities of the Primavera sample file you may easily realize that the information belonging to these resources such as DURATION, EARLY START, EARLY FINISH and PERCENTAGE COMPLETE are also processed and assigned. This additional info was derived from other modules of ERP and was not input manually.

8. Creating the WBS (Work Breakdown Structure)

On this project, Group + Sub-Group formula is chosen for the WBS If you observe the definitions on Primavera sample file, under the “Data – WBS” tab, it is obvious that these information are arranged from the ERP content along with the Planning Structure.

9. Assigning related personnel in charge for technical works to each activity

10. Assigning related personnel in charge for production to each activity

In order to talk about planning execution of a construction project, “Work Orders” have to be derived from the schedule and the reports should be handed to the related technical personnel in charge (TECH) and the personnel in charge for production (LABR) in case of any need.

On a construction project, it is pretty clear that each production items or each block to be constructed has a responsible team assigned. This information can be processed under the Scope (BoQ) module. We are able to notice that scope file with 9907 rows contains the related information under (TECH) and (LABR) columns. These codes may represent a single personnel or a sub-contractor that is assigned to the execution of works. And these data is also transferred to Primavera from ERP system.

You may also find the (TECH) and (LABR) information under the 7^th and 8^th rows of the Activity Codes tab (that includes activity details) of the sample Primavera file.

By this means it is possible to create daily and weekly work order reports regarding to these personnel from both Primavera and ERP content on relevant dates.

4. Conclusion

This paper is a genuine work. Works defined and developed here and similar notions have always been the fundamental principals provided the existence of an ERP system, which is designed by me that is specific to the construction business.

It is well known that the main purpose on ERP systems is to manage the construction activities integrated into one another.

In respect of the functionality of a construction firm, it is essential to evaluate the data regarding to all phases of a construction project on the same database. In order to provide this, the whole data such as drawings, quantity measurements, analyses, unit prices, tender documents, progress payments, cost accountancy data, fixtures, timesheets, stock info, procurement procedures, payment schedules, work schedules (Primavera), budgeting, cash flow charts and profit-loss tables should take place and evaluate on an ERP system and the transferring of data should be done over the ERP system on convenient formats to the related modules.

Occasionally, in the organization structure of the firms, these data may be in charge of various units and personnel and belong to the content of various software systems. There is information which is essential to be gathered from architectural software solutions, accountancy software systems, scheduling CPM software; tender documentation software and quantity register books. Required data should be transferred through a suitable algorithm and data management and monitoring should be provided. There is no control over the data when this circumstance is not met. So you don’t have control over the out come neither.

This work, based on construction management facts, emphasize the outcome that CPM software does not make sense at all by itself. The principal function of CPM software is to calculate the magnitude of timing.