Managing 100+ Projects

 [divider]I once had 150+ projects…[/divider]

When I joined this team, they had a lot of project work. At the time, they were mostly in the $10K range, with very few in the $1 million range. All project reporting and analysis were done by spreadsheets. So if you had 150 projects, you had 150 spreadsheets.

One of the key problems with this business unit was that the project controllers could not keep up with the work. Because of the sheer number of projects, they were reduced to producing status reports for only the larger projects, if they even had the chance to do that. Some never got around to even producing a status report for one project.

If you find yourself in this position where you lack time to report on project results due to the sheer number of projects, it may be time to rethink your process. If you are producing your project reports one project at a time, you will never get your work done. There is another way to do it and it involves getting Excel to “automate” the work.

First you need to move from reporting on a single project to reporting on all of the projects at once. Instead of downloading data for one project, download data for all of your projects.

Then set up Excel to manage your data via pivot table.

For my set of projects, I had to combine contract data with financial data. Unfortunately, the contract data came out of the contract database whereas the financial data came out of SAP (later it was an in house project financial management software). In this kind of situation, a key will have to be developed such that a common identifier exists between the two different data sources. The key will be a way of connecting two sets of data. In my situation, the key was the project number.

At the time, I created a pivot table out of the SAP data and applied the key to match the contract data against the SAP data in the pivot table, so that project manager’s name, project end date and project contract value were visible, side by side with the financial status.

To aid in clarity of layout, conditional formatting was applied so that grand totals stood out and any over runs were highlighted.

Finally, a filtering system was set up so that project managers could filter by their name and pull up their projects.

With this kind of Excel system set up, you should be able to produce status on all of your projects in maybe two to three hours, rather than days .