I've initiated into a PM role where there is a requirement to view project health of around 30-40 projects. This includes handling resource allocation requests. Can anyone please advise on using of an excel sheet template, if this is the right approach and maybe a sample sheet. Thanks
At one of my previous companies I replaced a 100+ slide PowerPoint deck with a simple excel spreadsheet. This was for a division that shipped close to a million units a quarter across a number of product lines.
It was highly successful. We had one tab for new projects, one tab for sustaining projects and one for field issues. My format included columns for key aspects of the projects (Software, Hardware, Packaging, etc.) so a specific area could be flagged without the whole program being flagged. Major milestone dates were then listed, which could then be flagged with a color, based on status.
At one point we were tracking over thirty different projects with this spreadsheet. The ease of use, updating and easy to digest information made it very popular. Vice presidents would print it out and put it on their wall every week when it was updated.
As for the template, this is highly context sensitive. I started out with little more than the project name and the top four milestones. I then started using it with teams and stakeholders and over the next few weeks tweaked it based on feedback. I didn't put in everyone's feedback. Instead I focused on requests made multiple times and that were not one offs.
So to wrap up, yes it's useful and start with a very simple template, grow it from there though direct use.
Some other ideas:
For each project,
- number of open risk items (registered into the risks database), maybe a computed number from risk system (risk probability * risk cost / project total cost, etc)
- number of critical issues (or an history-chart of the number of issues, to see the trends) (issues should be non-trivial ones, that is worth taking action on a decided level (your level, your higher level, or your lower level)
- number of manhours budgeted and used (if it's more than budgeted, there might be need for re-baselining the resources or taking a better look at the issues, or updating the schedule)
- number of releases of the project team (percentage, or XXX/YYY style) until the upcoming milestone (release can be anything, documents, technical drawings, software units, etc, depending on the scale of the project).
Coloring should be GREEN or RED, and not YELLOW, according to Takashi Tanaka (see his youtube conferences to understand why Yellow is discouraged).