Helping CAMs with Manual Forecasting
A universal problem in the world of project controls is getting good, reliable data back from those otherwise engaged in actually executing the project plan.
This challenge can have a devastating impact on the accuracy of the schedule status and Earned Value Management (EVM) metrics when team members fail to provide good feedback. There’s a raft of reasons, excuses, for their prevarications that anyone who has been involved in project controls will have heard many times over.
One of the most common objections is “We don’t have time to provide all this information; we have to actually build this widget”.
This leaves the project controls team to find ways to make the collection of data easier and quicker for the control account managers or whoever is charged with providing that status.
First, some education is always a good idea, particularly if the project controls processes are fairly new to the organization. Or perhaps, a new contract has brought with it the need for more sophisticated reporting such as earned value metrics. Once the team learns that, while there is an initial planning requirement and reporting overhead, this system will provide them with useful information. This can help them justify and manage head count, stay on track, head off issues, and better manage risks. They will soon be pulling on the project controls team for data, as much as providing it.
In an Earned Value Management System (EVMS), the Control Account Managers (CAMs) are key resources in helping keep the high standard of earned value metrics flowing to the project team. Their role is to liaise with the team members on the ground and the project controls team to make sure that the right data is flowing in each direction.
And yes, of course I’m oversimplifying it here. But the CAM is critical, and when I’m in the Cobra seat, I want to do everything I can to make the CAMs live easier.
Manual Forecasting Using Deltek Cobra and Spreadsheets
One way to do this is to provide them data in a format that they are comfortable with and can easily manipulate. What better platform than a spreadsheet for doing manual forecasting.
Cue the Resource Assignment Export and Import feature in Deltek Cobra’s Integration ribbon. For years I’ve been successful in helping CAMs provide forecast ETC adjustments back to the Cobra system using this tool.
As a rule CAMs don’t work directly in the Deltek Cobra system: rather, they are usually quite happy with the ubiquitous spreadsheet. The Assignment Export spreadsheet provides a type of control account plan (CAP) which is easy to understand, and as a bonus can be edited and read back into Deltek Cobra to update resource assignment ETCs. The first step for the Cobra operator is to identify any control accounts that have an issue with their Estimate At Completion (EAC) values.
Variance At Completion (VAC)
As a Cobra operator you can easily see when a control account’s EAC is drifting too far from the Budget At Completion (BAC) by opening the Variance At Completion (VAC) field in the CAWP pane.
This means you can notify the CAM and send them an Assignment Export report with some instructions about how to make an adjustment to their ETC values to correct the negative VAC. If you have defined thresholds in your WBS for the project, you will see Red fields in the VAC column. These will need some attention.
Here’s what the steps might look like in this example scenario.
Let’s imagine that you have just gone through the first monthly reporting process for the project. All status has been posted, actual costs loaded, progress and forecast calculated, and draft format reports sent out.
You open the project and see if the forecast calculation has pushed any EAC values beyond their thresholds.
In this example we can see that the Mobilization control account is at $-7635.45. For whatever reason the EAC has climbed above the BAC by this amount.
We need to give the CAM as heads up that the EAC has climbed over the BAC, and we should adjust the ETC values in order to bring this number down to within range of the BAC before we post the final CPR Format reports. Let’s run the Assignment Export report and email it to the CAM< in this case Andrew Jackson.
In the Integration ribbon, select the Assignments Export button.
In the Assignment Export dialog we verify the project name and then click forward to the Resource Assignments dialog. We can select Andrew’s control account using the ellipse button to access the Control Account Lookup dialog.
Tip: You can right-click on the column headers in the Control Account Lookup dialog and add the CAM field. This will help you identify a particular CAMs items when you are dealing with much larger projects than the one in this example.
The next step is to select the forecast class we wish to manually adjust. To do this we click on the Class: field and select Manual Forecast from the resulting list.
For the Result options we will choose the First Result radio button. This has the advantage of outputting the first result in the Resource’s calculations list, which will be HOURS for labor, and DIRECT for non-labor, materials and such.
From here we click the Next button, give the xlsx file a name, and click Finish to run the report.
In this very simple example, we can see that the Engineer has 244.44 estimated hours, and our Logistics Manager 97.78 estimated hours for the remaining period of the Mobilization work package.
We email this spreadsheet to the CAM with a note about the VAC – it looks something like this:
There is a variance of negative $7635.45 on your Mobilization CA, rolled up from the Mobilization WP. Please take a look at the attached Jackson CAP.xlsx file to see the remaining ETC hours for the resources and, if practical, reduce their ETCs to reduce or eliminate the VAC.
Your friendly Cobra EV Analyst
After a brief chat with the two resources in question, the CAM learns that they packed in more hours than planned last period. Hence the overspending and high EAC. However, they both got way more done than planned and report that they will only need to book 140 hours for the Engineer and 80 hours for the Logistics Manager for the remainder of the Mobilization work package.
Using this manual forecasting method, the CAM enters these new values in the remaining period of the work package, saves the changes, and sends the file back to project controls for import.
This process is very simple. In Deltek Cobra we click on the Import Assignments button back in the Integration ribbon.
In the Import Location dialog of the Assignments Import wizard, locate the updated .XLSX file and click Next.
At this point, Deltek Cobra runs a validation process to ensure that the spreadsheet doesn’t contain any invalid data, where perhaps the CAM inadvertently included his weekly grocery shopping list in the spreadsheet.
Assuming this in not the case, and the data is all good, a green checkmark appears.
You can proceed with the import by clicking Next.
Upon completion of the Import, you can now see the updated EAC values and the now positive VAC column, which is displaying a green background in the Mobilization cell.
And for the benefit of those who are curious to know what happens when a CAM does inadvertently type their shopping list into the Assignments spreadsheet:
For the record ‘BE code’ is an old term for Cobra Resources. It stems back to when Resources were called ‘Budget Elements’ back in the old FoxPro days of Cobra 4.7 and earlier.
This is just one suggested way to give CAMs an easy way using manual forecasting, to provide some ETC values to help them keep their EACs in range of the BAC. It may be worth having a short ‘brown bag’ meeting to show the CAMs what they can and cannot edit in the Assignment spreadsheet if you choose to use this as a manual ETC tracking option.
To help with this, you can check out my earlier article Dos and Don’ts for the Assignment Export Import Spreadsheet. This will give them some comfort as to what they can do in the spreadsheet and why it is important to manage their ETCs when WPs start to go negative VAC on them.