David's Primavera Stuff

If you can read this, This page is still under construction!


For twenty years I have maintained that P6 is a better cost/planning tool than a planning tool. Yet very few of my clients use it for cost.

Why?

  • P6 is not multi currency. The so-called currency conversion rates only offer a single conversion rate for each currency, which is sad.
 
  • There is no ‘Cost’ window. The data for the Activities window and the others, comes from a single table. Nice and easy, and fantastic pivot speed. Cost comes from two tables – the Resource Assignment table and the Expenses table. Frankly, this omission is just lazy and incomplete. You cannot for example, produce a normalised Layout by Cost Account. Sigh.

  • There is (well, in my usual industries) a small army of vintage spreadsheet jockeys, aka Cost Engineers, whose livelihood depends on their single-user spreadsheets. They are very happy doing things the way they have always been done.

  • Cost and Planning are separate departments/disciplines/personnel. This is a category error; they are both Project Controls


Where does cost come from in p6?

And what about the Cost Account Dictionary?


The Cost Account Dictionary is an Enterprise level dictionary. In common with almost all dictionaries in P6, it is not Project specific. It offers a twenty-five-level hierarchy, and each code value can be twenty characters long. It supports multiple roots, so you could make a Project specific segment if required. Until version 21 of P6, this was the only code structure whose minimum granularity was not the Activity, i.e., an activity can have more than one Cost Account associated with it. The new ‘Resource Assignment Codes’, introduced for users who do not understand how the cost account dictionary works (sarcasm) also supports a code structure granularity smaller that the individual Activity. I am still investigating if there is anything you can do with a Resource Assignment code, that you cannot do with a Cost Account.

Each resource assignment and expense can have a cost account associated with it.

I am considering here an activity which normally ‘planning’ would ignore. There is very little to ‘plan’ about this, we are seeing basically very high fixed daily costs, with a wee bit of procurement. This is the sort of ‘spreadsheet data’ which is usually only the preserve of costing rather than planning – but which in my not very humble opinion, is better dealt with in P6 not Excel.

The example I will use below has an activity that costs £2m. The daily costs are known. The duration is not. We have a fixed daily cost, and a lump sum. Earned Value is probably ‘0/100’ – we might abandon the well and ‘Earn’ nothing.

What ‘size’ should activities be? My answer is that an activity in P6 should map to a unit-of-estimate/approval in the estimate and approval system. In my hypothetical P6 user, that could be £2m for the type of project outlined below, but the same P6 user could also have ten thousand £2k jobs which are construction or maintenance. I shall consider that end of the spectrum later. There is no such thing as too much detail in P6, just insufficiently clever ways of summarizing it. We must have the closest possible mapping from estimate to activity.

Here is the example cost accounts dictionary for this text:






Note that drilling costs has three sub-accounts, one for each of the Joint Venture partners, who share the cost. This is required since several wells will be drilled that have different partner share percentages.

This screen:



Shows us that there are three resource assignments associated with the single ‘Big Boy Three’ drilling rig, each of which has a different Cost Account. Other wells in the same campaign may have different partner allocations.

Here is the same data in the Resource Spreadsheet view:



Grouped by Activity ID, three resource assignments of the same resource make up the day rate of $100,000.

NOTE the unique key for the Resource Assignment table is Activity ID, Resource ID and Cost Account. Updating ‘under the hood’ with Excel or anything else needs to understand this.

There are many practical reasons that a single activity needs to accrue costs to more than one account. While Labour, Non-labour and Material (much more about material costs later) resources aggregate to separate sub-totals, that is never really enough. Consumable materials vs Capital acquisitions, Base scope vs Growth, Own labour vs outsourced. I am sure you can quickly imagine more reasons for this.

But now that we have Resource Assignment Codes, available from version 21 (20?) onwards, could we not use those instead of the Cost Account dictionary? Like the Cost Account dictionary each of these codes is multi-root, and a 25-level hierarchy. But, a Cost Account from the single dictionary can be applied to a Resource Assignment record AND an Expense. This is the only coding structure in P6 that can do this. It is a curse and a blessing.

Duration Type, Resources and Expenses.


A major restriction in the relationship between multiple resources and their parent activity is that the Activity’s Duration Type controls the algebra of (Duration* Units per time Period = Total units) for all of the Resource Assignments. If an Activity’s Duration doubles, does the total units for every Resource Assignment double, or does the Units per Time Period half? The challenge is, of course, that sometimes an Activity has ‘per diem’ AND ‘lump sum’ costs.

Two solutions.

  1. Expenses are always ‘lump sum’, so we could use, say, ‘Fixed Units/Time’ for the duration type when all of the resources are ‘per diem’, and have the lump sum costs, perhaps materials, as expenses. This works well, and Resources as ‘per diem’ Materials as ‘lump sum’ is common. Note that expenses are not ‘Enterprise’ and are Project Specific. There are no ‘limits’ as there are with resources. The price is not dictionary based (there is no dictionary!). There are issues associated with reporting, which we will cover later.
  2. A resource with a specific duration. For example, we have a material resource that ‘happens’ on day one only of our above drilling Activity. This is horrible to progress, so we better go through it…..(I just need a wee therapeutic moment to remind myself how the old P3 allowed ‘Resource Barcharts which would have been really nice here to visualise what is going on. We could see when the materials are accounted for graphically) In the example we now have here:







There is a fourth resource record. ‘Materials’ is a general-purpose Resource ID with a type of ‘Material’ which is priced at £1.00 per unit. We could, if we were crazy, have the entire SAP materials catalogue as individual resources properly priced, but life is short. Note that I have encoded the Materials resource with a resource duration of one, such that it should be fully ‘actualised’ after the first day if we use ‘auto compute actuals’. ‘Auto compute actuals’ is an excellent choice for this activity, because there are no variable costs in this example. Every day we will use exactly one drilling rig, and the materials will be accounted for on day one.

BUT

I have always struggled with Resource Assignments that have a duration. I am never confident when progress is applied that it will work, and that it can be ‘un-done’. My choice is to use Expenses:




Note the ‘Accrual type’ of ‘Start of Activity’ – we actualise the whole cost on period one of the Activity.

Progressing

This activity does not conform to our usual progress measurement methodology. We do not count how much work is done, but merely how many days have passed. If seven days have elapsed, we will have accrued seven days of costs. This is a rare opportunity to use P6’s ‘Update Progress’ facility, whereby we can apply ‘perfect’ progress to selected activities, under the ‘Tools’ menu:




This is like moving the Data Date over the selected Activities and progresses them as if CPI and SPI =1

The result is this:



All of the Expenses are actualised, 20% of the Resources are Actualised.

However, we might also have some ‘real’ progress information, for example we might be told that there is still 30 days to go on this job. The Activity has (of course!) a duration type of Physical, so we can change the remaining duration without changing anything – except the Remaining Cost. Which is exactly as we require:




A very rare shout-out for ‘Cost % complete’!

Now I shall look at the other end of the job size spectrum, and consider a small corrective maintenance job. There might be a great many of these. The characteristics that interest us here are;

• The primary consideration about the ‘size’ of the job, is the manhour estimate, not as above, the duration.
• Progress is measured as ‘how much work is done’ not ‘how much time has passed’
• Expenditure comes from timesheet information, not days-elapsed
• Resources are NOT ‘auto-compute-actuals’
• Resources have a limit, expressing their availability – they are shared across activities AND projects.
• Duration type is ‘Fixed Duration and Units’, %complete type is Physical
• The Calculations tab at the Project level has ‘Add Actual to remaining’ checked, and ‘Recalculate Units and Cost when Duration % complete changes’ unchecked


Our activity is a repair on a pipe. There are 40 manhours of pipefitter time, and there is scaffolding and certification to do. The project we are in is Maintenance, one of many projects in our ‘Whole Asset’ portfolio.



All three resources share the same Cost Account – Corrective Maintenance. These same resources could be applied against a different cost account, for example ‘Preventive Maintenance’ or ‘June 2022 outage’. If Inspectors are ‘own labour’ and other trades ‘contract’ this could be expressed here.

The Remaining Units/Time value for Pipefitter is 20 hours per day. This is clearly more than one Pipefitter. How many Pipefitters is a great question, but is answered at length elsewhere in this website.

You don’t need to know a lot about construction, to understand that the scaffolding needs done first. In many environments scaffolding is so critical, that it is a separate activity. It is also pretty obvious that inspection occurs at the end of the activity, and is occasionally also a separate activity. However, this is an article about cost, not micro-scheduling. Granulating cost down to specific days or even hours is just not required, weekly in real time would be a minor miracle in the spreadsheet driven world. Therefore, we shall for this example decide that daily resource allocation is a ‘foreman’ job – not a ‘Primavera’ job! Our target here is a weekly granulation of Baseline, Current Budget, Actual, Earned and Forecast cost.

In a perfect world, my idealised Foreman would finish the day by reporting % complete on the dozen jobs their squad worked on that day, and once a week approves timesheets, which record would include the Activity ID of the jobs worked on. Dream on. Primavera allows you to book timesheet hours to the Resource assignment (perfect) the Activity ID, The WBS or the Project. In this example we shall assume that the timesheet system knows the trade that is booking the hours, the project being booked to, and the WBS, but not the Activity.

Here we see our ‘Bucket’ Activity for all of the Actual costs associated with this branch of the WBS:



It requires to have a Resource Assignment record for each resource that is used in the ‘real’ Activities, but has no budget associated with it.

The Fix Pipe activity is now completed. The timesheet data has been added to our ‘Dummy’ Activity. I have added the Cost Performance field, which is correct for the whole WBS;



Of course, the CPI at WBS level is less than perfect. But much better than no figure at all, and a great incentive to get more detailed timesheets.

Micro-rant. One of the huge problems of separating Cost and Planning into two separate systems, is that inevitably the granularity of detail differs in both. The above example is typical of the ‘fudge’ that is required to join them up together again.


Rates


Every resource in P6 has five different rates:



To see them, you right click on the data-row underneath ‘Effective date’. These can be renamed in the ‘Admin Categories’ dialogue.


Every Project has a default Rate Table:



Every Resource Assignment can overwrite this, there are three ‘Rate Sources’



The Resource dictionary has five rates, the Roles dictionary has five rates, and Override lets you enter a specific one-off price. The Rate Type lets you enter which table of rates from either the Resource or the Role dictionary is being used for this assignment. ‘Global Change’ allows very quick manipulation, it is very easy to say ‘make all Electrical resources use Price/Unit5’

Price change on date

All Resources and Roles can have any number of price changes:



Here we see that the price of an hour of pipefitter increases on the 1st of January 2023. And, yes, if an activity using pipefitters slips from this year to next because of the schedule, its price automatically changes.

Cost vs Value


Sigh. Once upon a time, in the distant past of the 20th Century, a sensational piece of project management software called ‘Primavera SureTrak’ which cost £299.00, allowed each resource to have a cost and a value. The ‘S’ Curves were to die for. But enough nostalgia. Soon, I will be gone, and there will be no-one to remind of how great things were in the Old Days.

Resource prices must be positive, but resource quantities can be negative. Primavera puts brackets round the negative values. So, yes you could have a milestone with negative units:




We see that at the moment we expect a profit of £1,861.00.

Be careful. If you thought Primavera’s graphics were less than ideal, they simply do not work with values below zero on the ‘x’ axis. Somewhere towards the end of this document, there will be detailed instruction on pivoting this data automatically into Excel for the charts everybody wants.

Store Period Performance.


Unless we take advantage of this facility, then all costs are spread using the Resource Assignment’s curve (we have only used the default ‘linear’ so far) between an Activity’s start date and finish date. This works pretty well for activities with a fixed cost per time unit such as our first example. It works very well for short Activities that are started and finished within a single accounting period. Best Practice requires that after every progress update, we store that progress interval’s cost data in what P6 calls a financial calendar. Until very recently, each P6 database allowed only one such calendar, fine for ‘owners’ who can just instruct every project to use the same calendar, a bit of a nightmare for ‘contractors’ who might have one client whose week ends on Friday, and another Sunday.



Above you see such a calendar, created under Admin, Financial Periods. The period name is editable

Working through an example of how this works below…

Here is our ‘Fix Pipe’ example again, note the addition of the Resource Assignment field ‘This Period Actual Units’:




It is this field that we update with this week’s timesheet hours:



The Activity has started, and we are 50% complete. The timesheet hours have been uploaded.

The Financial Calendar associated with this project us chosen at the Project Level:



Under ‘Tools : Store Period performance’, we pick the appropriate week:



We now have the ‘Financial periods’ columns enabled in User Preferences:



And can see the financial period columns, in both the Resource Assignment and Activity’s Layouts:



Yes, the database schema has been dramatically extended. This means we overcome the ‘Smoothed’ ‘S’ curve effect, by preserving every data point. Reconciliation with the General Ledger and its monthly data points is now possible.


Reporting.


There is no ‘Cost’ window, as there is one for, say, Activities. If we only use Resources to describe costs, then we have the Resource Assignments window:



Endless pivots like the one above (by Cost Account) are available. BUT of course, they do not include expenses….

We do have the arcane wonders of ‘Tracking Layouts’



Group and Sort is restricted to WBS, yes, NO Cost Account. Also, these are WBS summary level only reports, you cannot granulate to Activity, let alone Cost Account. Tracking Layouts is a bit of an orphan, now that the Oracle/Primavera product ‘Portfolio Analysis’ as a ‘cheap’ read-only solution to give managers a read-only do-it-yourself reporting tool is long gone. Oh, if any of you ever find a manager who would rather learn a new on-line reporting tool, rather than have a planning engineer send them a suite of PDFs every week, do let me know. I have a bridge to sell them.


So how do we report on Resource costs and Expenses, grouped by Cost Account?


Resources and Expenses have their own window, with all of the Layout options you would expect, except a timescale/spreadsheet view in the expenses window.

Under the Tools pull-down, there are reports.

Usual disappointments apply with the report wizard. A brilliant tool to pivot data from a single P6 table, such as resource assignments, into a spreadsheet whose ‘y’ axis can be and period from hour to year. What if we want Resource Assignments and Expenses? The report wizard will not let you pick both of ‘linked tables’ or ‘time distributed data’.

Excel. No surprises there. The correct route to get P6 data from the database into Excel, is as always, the Report Wizard…


Note. Compared to everybody reading this, I am effectively an Excel Newbie. I know how to get the data into Excel, and then the graduate trainee does the fancy, Excel stuff.
I will eventually return to this point, and insert the scripts and screenshots to extract two spreadsheets with the data you need.