- Excel Spreadsheet (Excel 2007+): PlannerTuts_BOQ_Pay_Item.xlsx
P6 Schedule and BOQ challenges
A contract’s Bill of Quantities is always meant to be more details than the schedule, at least in theory. In many cases, the planner and the estimator are not working in perfect coordination and you may end with two different level of details for the same project. In case you need some background, check out Bill of Quantities explained.
Let’s assume you are assigned to a new project that the company has just won. After opening the schedule you will notice that although the bill of quantities and the schedule total budget are the same, the level of the details is different – and the budget was loaded manually from the bill of quantities without any formal relationship between the activity ID, and the pay item.
There are many approaches to handle this situation. One of them is to keep manually loading the hours into the schedule, knowing there’s a risk that the next planner will have to do the same guess work as you have done before. Or, you can add extra information that will make your life easier later, when the project receives all those updated BOQ.
We’ll show you this last approach with the help of Excel and some great formulas for manipulating the data. We’ll start with our BOQ, our P6 schedule, do some manipulations in Excel and eventually get it all back into P6.
Preparing The Data
Unfortunately, the first step is the most tedious one and there is no easy short cut. First, we’ll create a new User Defined Field in Primavera called “Bill Item No.”. Add the column to your layout so we can add data to that field. Remember, in this situation we already have a schedule built.
So basically it is a guess works; you print the bill of quantities and you compare the same WBS and the activity descriptions and the assigned budget hours and you start filling in values for the column “Bill Item No.”
There are three types of relations.
- One to One: one activity represents one pay item on the BOQ.
- One to Multiple: one activity is the summary of many pay items. For example, all culverts of different sizes are grouped under one activity – “Culverts Construction.”
- Multiple to One: in many cases the BOQ has the total quantities but in the schedule you need to split the work into more detailed sections.
Transforming the data in the spreadsheet
Copying P6 Activity Data to Sheet 1
In P6, adjust your columns so that only “Activity ID” and “bill Item No” are on-screen. Make sure there is no grouping (grouping set to <none>).
Highlight all rows and copy the two columns – Activity ID, and the Bill of item.
Paste them into columns A and B on Sheet 1 in the Excel spreadsheet. The formulas on Sheet 1 will do some calculations on the new data.
Any Bill Item No cells with multiple items (like Activity A2650 above with values 31,32,33,34,35) cannot be used in Excel as such. So we need to split these Bill Item No out to multiple rows. Notice the delimiter used here is a comma. We used Excel formulas found here. In column C we find the position of the comma in the cell, then we use mid() to extract the value of the Bill Item. As an alternative, you can always use the excel option “Text to Columns” to have the same result.
On Sheet 2, we use a formula to UNPIVOT the data from Sheet 1. The formulas do all of the work automatically. It means we transpose the pay item from row to column, the formula is courtesy of Dick Kusleika,
Moving on to Sheet 3
Copy the two columns “Activity ID” and “bill Item No” from Sheet 2 and paste them on Sheet 3 as values, filter the blank values from bill item No and delete those row that have blank values for Bill Item No. The column repeated will show how many times the pay item was repeated, the value of budget hours is taken from the tab BOQ divided by the number of times the item was repeated.
Notice now, that we have created a proper data mode between Activity ID and Bill Item No.
Moving on to Sheet 4
A pivot table will summarize the data from sheet 3 by activity_ID.
Voila, you now have the budget hours per activity ID and you can now import back your Budget into P6.
You can do get the data back into Primavera P6 by exporting your resource assignments from P6, then perform a look up to bring in the values from sheet 4. If you need help on using lookups, you can always read this post for help on using vlookup in excel.
It is very important to document the links between the schedule and external data, as it will be much easier later when the quantities change and you need to sync your schedule.
Things for you to try:
- The formula to unpivot data is awesome, combine it with 2D lookup and you have power data manipulation tool in your spreadsheet.