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

1st BLOCK

B

BLOCK B

20

2nd BLOCK

C

BLOCK C

30

3rd BLOCK

D

BLOCK D

40

4th BLOCK

E

BLOCK E

50

5th BLOCK

F

BLOCK F

60

6th BLOCK

G

BLOCK G

70

7th BLOCK

H

BLOCK H

80

8th BLOCK

I

BLOCK I

90

9th BLOCK

J

BLOCK J

A0

10th BLOCK

K

BLOCK K

B0

11th BLOCK

L

BLOCK L

C0

12th 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 7th and 8th 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.