Importing XLSX Expense Costs into Primavera P6
Microsoft Excel is often used as a platform for the organization of project data, and XLSX data files can import to Primavera P6 Professional. This makes these XLSX Microsoft Excel spreadsheets a suitable intermediary between project team members and Primavera P6 Professional.
In most organizations, only a few chosen personnel are Primavera P6 proficient schedulers. Most project team members are often P6 illiterate. This makes the transmission of team member project data into Primavera P6 cumbersome.
Fortunately, Primavera P6 Professional imports XLSX files, so team members can use Microsoft Excel for the submission of their project related data. Projects often have project specific expense costs. It is possible to submit these expense costs in an Excel format to import and incorporate into the project schedule.
This article demonstrates importing XLSX expense costs project data into Primavera P6 Professional.
In Figure 1 we have our demonstration project.
Figure 1
This is a schedule for the installation of an HVAC air condition system. Note that this schedule is in the planning stage. It has Work Breakdown Structure (WBS) elements and activities, but no relationships at this stage in the scheduling process. We have several activities that we want to assign project specific costs using the expenses feature in P6 Professional.
Expenses are one way to assign a cost item to an activity. They are often used to assign non-resource costs to activities. Unlike resources, expenses are project specific cost items. They are often used in the construction industry to plan equipment costs, subcontractor costs, lump sum costs and other costs that are project specific.
Here’s the process for entering project specific expenses to an XLSX spreadsheet, and then importing this cost data into a Primavera P6 Professional schedule. We begin by first exporting our HVAC schedule to Microsoft Excel. Choose File | Export, Figure 2.
Figure 2
In the Export Format dialog we select ‘Spreadsheet – (XLSX), Figure 3.
Figure 3
Continuing in the Export Type dialog, we choose to export Activities, Activity Relationships, and expenses, Figure 4.
Figure 4
Our schedule at present has no relationships, so we could have unchecked this option. Since most schedules have relationships we went ahead and selected this option. We did, however, elect to not export resources and resource assignments. Moving forward in the Projects to Export dialog we click to export our Air Conditioning System project, Figure 5.
Figure 5
Click next and this brings us to the Select Template dialog. In this dialog we click to modify the Standard Layout, Figure 6.
Figure 6
Note we chose to modify the standard layout for convenience. You may have to start from scratch and elect to add a new template. In the modify template dialog we choose the Activities subject area and go with the options listed in Figure 7.
Figure 7
Next, we choose the Expenses subject area select the options listed in Figure 8.
Figure 8
Continuing, we click next and select the Excel file to copy the project data to, Figure 9.
Figure 9
We confirm the export was successful (Figure 10).
Figure 10
After our Excel spreadsheet is exported, we open it and view the Task tab, Figure 11.
Figure 11
In the TASK tab, we highlight the activity IDs and sort A to Z, Figure 12.
Figure 12
When offered the option, choose to expand the selection for the sort, Figure 12. We then copy and paste this column to the PROJCOST worksheet, Figure 13.
Figure 13
Great! We are now ready to populate our PROJCOST worksheet with the expense costs for our schedule. In Figure 14, we have our PROJCOST worksheet including our activity expense costs. Figure 14
Note that Primavera P6 Professional ignores columns that have and (*) in the (row 2) header. This means that it will not read the Activity Status column or the Price/Unit column. The budgeted units are the number of each expense item.
Observe that we have two pumps, two control valves, and four thermostats, Figure 14. The budgeted cost becomes the price/unit. The budgeted cost in Primavera P6 Professional becomes the Budgeted Units times the Price/Unit. If you enter actual costs you will find that the actual cost column takes precedence over the budgeted cost column for the Price/Unit values.
Now we begin the import process. It’s important to close the file you want to import into Primavera P6 Professional; open data files will not import properly. Moving forward we choose File | Import, Figure 15.
Figure 15
In the import dialog we toggle Spreadsheet – (XLSX) and click next, Figure 16.
Figure 16
We then select the file to import and choose next, Figure 17.
Figure 17
At this juncture you may receive the warning that the user preferences used to create the Excel file could not be found. If this is your case, go ahead and select Yes to continue the import. The import should work fine even with this warning message.
Figure 18
Proceeding, we toggle to import Activities, Activity Relationships, and Expenses, Figure 19.
Figure 19
We then choose to update existing project and import to our currently open schedule, Figure 20.
Figure 20
Finally, we choose Finish, Figure 21.
Figure 21
Our results appear like Figure 22.
Figure 22
Inspecting our results, we consider activity A1050, Install Centrifugal Pumps, and we view the bottom details expenses tab for this activity. You will observe that the Excel worksheet Budgeted Cost column becomes the Price/Unit in Primavera P6 Professional.
In Primavera P6 Professional, the Budgeted Units (2) is multiplied by the Price/Unit ($4000) to provide a Budgeted Expense Cost of $8000 for the pumps. This expense cost is rolled up to the expense cost for the entire project. The Budgeted Expense Cost column adds to the Budgeted Total Cost column, which are equivalent for this demonstration.
Summary
So, importing XLSX expense costs into Primavera P6 is possible. The scheduler may then make updates to this Excel worksheet to include the budgeted expense costs of respective activities. This Excel worksheet may then in turn be imported back into an existing Primavera P6 Professional schedule to provide for the budgeted expense and budgeted total cost of the project.
Thus, team members who are not familiar with Primavera P6, may provide the scheduler project expense cost data Excel worksheets to import into P6. It is therefore good to familiarize oneself with the P6 process of exporting, updating, and importing project data. And the goal in this process is to maintain as much of the layout of the exported worksheet as possible to minimize import issues.