Swimming in Sea …of Projects? Get data out of them.

Overseeing a large program that has over 100 projects? This situation is actually a great opportunity because with that many projects, there are a lot of data that you can dig into.

The amount of data that these projects contain can be overwhelming but if you can reframe your thinking, they can be a potential source of insights. When I first started working at this company that had 150+ projects, not only did I have to learn the company’s culture, I also had to quickly get up to speed on the way this particular program worked. The best way to do that was to do some slicing and dicing to see what kind of picture comes up. I believe that by first looking at the project data instead of diving into EACs helped me get a grasp on the characteristics of this program much more quickly and thus my learning curve was shorter compared to the other project controllers.

[divider]Possible data to look at are:[/divider]

• Largest project and smallest project and the range

• List of projects over running and list of projects under running

• 10 largest projects and the associated project managers

• List of project managers and the number of projects they hold

• Monthly revenue run rate

At the time, one of the more interesting descriptive statistical data was the ranges of contract value. When I first pulled up this data, most of the contract value clustered around the $10K mark and only a handful of $500K and maybe one or two $1 million project. So maybe 130 projects were around $10K, the other 10 projects spread from $20K to $50K and then the rest at above $100K. The structure of the program worked against us: think about the constant setting up and closing the WBS codes, the forecasting, the billing, the collecting cash.

 

As an analogy that I used at the time, pretend you have two teams, two troughs of water, and two empty receptacles. The goal of the two teams is to collect the water from the troughs and bring the water to the empty receptacles within a specified timeframe. Team one gets a bucket and team two gets a cup. Now think of the water as cash and the bucket and cup as invoices. Who is going to be able to collect the water (cash) the fastest without loss of water along the way?

Posed this way, this is a no brainer, you want to use the bucket. Same principle applies to projects: you want to have larger projects, not only for revenue purposes but for invoicing and collection purposes. Yet, a lot of project managers will go for small projects because the smaller projects are deemed easier to sell.

Anyway, after two to three years, the structure of the program shifted: we began selling more $500K projects and less $10K projects. The clustering began to move to the $100K – $500K range. While managing larger projects may or may not be easier, the revenue captured was larger and I believe it began to be easier to invoice and collect.

Examples histograms- not real data:

 

 

 

 

 

 

 

[divider]How to capture your clusters[/divider]

If you can, use the histogram method. You might have to set up your Excel to pull in the Analysis Toolpak, so talk to your administrator to pull in the Analysis Toolpak or, if you are doing this at home, pull it in yourself (File-Options-AddIns). Once you have it, you can find the histogram under Data-Data Analysis-Histogram. You will have to instruct on where the data is located and the bins (or ranges) you want to see.

Now, my data had both active and inactive projects and I wanted to look at only active projects, so the histogram did not work for me. I used an array formula to pull the information. So, the formula for the first bin at $10,000 looked something like the following:

{=COUNT(IF((‘Project List’!$S$5:$S$353<=$B34)*(‘Project List’!$AB$5:$AB$353=”n”),’Project List’!$S$5:$S$353))}

‘Project List’ was where the data was located, column S contained the contract value, B34 was the bin set at $10,000 and column AB denoted whether the project was active (“n”) or inactive. This formula counts up the number of projects with contract value at or below $10,000.

The second bin at $20,000 had the following formula:

{=COUNT(IF((‘Project List’!$S$5:$S$353<=$B35)*(‘Project List’!$AB$5:$AB$353=”n”),’Project List’!$S$5:$S$353))-C34}

This time B35 is $20,000. Note the addition of -C34. That is because the first part is counting all active projects with contract value at or below $20,000; however, I only want values greater than $10,000 and less than or equal to $20,000. C34 refers to the formula for the first bin and therefore subtracts out all projects with contract value $10,000 or less.

The third bin at $30,000 had the next formula:

{=COUNT(IF((‘Project List’!$S$5:$S$353<=$B36)*(‘Project List’!$AB$5:$AB$353=”n”),’Project List’!$S$5:$S$353))-SUM($C$34:C35)}

Now you see instead of just -C34 but the summation of C34 and C35 because, again, the first part counts up all of the projects with contract value $30,000 or less.

The rest of the bins will have the same formula structure as that of bin 3 so you can copy the formula for the remainder of the bins.