Resource Planning

It is essential in a project management environment to have a good handle on the availability of your resources. At the lowest level, a project manager may be concerned only with one project. Will the assigned resources get the job done when promised?

The problem with being a project manager is that you (in most cases) don’t directly control your project team members. They usually have an administrative resource manager. This person controls their assignments, gives them their evals, and manages their “day job.” This person can also pull the resource off your project and onto another. This happens more in an environment where there is no attempt made to plan the allocation of resources across all projects. As a project manager, you are limited to complaining that your project is higher priority, that you really need this person, and that your bonus depends on getting the project done on time (none of which are convincing arguments to resource managers).

Sometimes, the same effect is achieved through a more insidious means. The resource manager assigns the person to your project. Perhaps your company has a policy of allocating people to projects no more than 75% of the time. This allows each person 2 hours per day to answer the phone, write emails, go to the bathroom, and do their day job. Then, at some point, while your project is underway, the same resource manager (innocently) assigns the same person to another project, also at 75% of their time. Performance on both projects gradually slips as the resource (being the good corporate citizen that they are) tries to please three masters. How can we prevent this from happening? We can’t, but we can take steps to ensure that the organization doesn’t undertake more projects than it has the capacity to do so.

Here are the steps we took to plan the use of our resources, and the delivery of our projects. We used simple Excel graphs to show the supply and demand on a team’s time. A major assumption of these graphs is that all members of a team are interchangeable. This analysis could be performed at a higher level, but its utility would be limited. It could be performed for each role on a team, but it would be harder to maintain.

1. Determine how many hours each team has available to work on projects.

2080 Hours in a work year
- 240 Hours off (vac, sick, holidays)
-200 Hours doing admin things
1640 Hours available to work on projects
x 5 Number of people on team
8200 Hours per year to work on projects
/ 12
683 Hours per month to work on projects

2. Build a data table for each team.

The table contains a row for each month and a column for each project. The table is populated with the hours required of each team (Resource Group in MS Project) by each project. The table will look something like this.





3. Build the graphs.

Graph each table, using a consistent color across all graphs for the same project. This makes it easy to find the project. A graph will look like this. Note the red arrow (manually placed) that shows the capacity of the team in any given month).




4. Analyze the graphs

Having all graphs in front of you allows you to determine several things. Can your organization support another project? Should you bring in a contactor? Should you shelf a project and resume it when you have more organizational capacity? What impact will a project started in January have on IT when it hits that department in April?









Updating the graphs each quarter with data from the latest Project schedules allows you to see a rolling twelve month window into the organization.

If you have any questions, ask via a comment.

Distributing Project Reports without MS Project

The implementation of the project management discipline can come in all shapes and sizes. Rarely does an organization have the resources to train all possible team members or give them all the tools they will need. MS Project often appears on the computers of project managers and essential technical leads, and only sometimes on the desk of the occasional team member (hence the need for differentiation between Project Server and Project Web Access).

The rest of this post takes place at a company where there were only two Project Managers in the PMO. These Project Managers were PMP’s who provided project management mentoring, training, and support to Project Leads. Project Leads were business managers who were ultimately responsible for the successful deployment of their projects. As such, they were not experts in project management or scheduling and relied on help from the Project Managers. At the same time, it was the Project Leads who called the meetings, resolved the issues, and wrote the status reports.

To ensure that all team members were fully informed about what was expected of them (and because the company didn’t upgrade from MS Project 2000 to MS Project 2007), I developed a way to keep team members informed of their upcoming tasks, and keep Project Leaders aware of project details, especially in advance of the monthly report-out to the company President. This report was developed in Excel and used a combination of Project filters and export maps to produce its reports. The report was run just after the status meeting and consisted of six tabs. It was easy to develop, easy to read, and effective in its use.

1. CREATE THE FILTERS

The reports rely on filters and export maps, and the first step is to create the filters.

For the above filter, the questions in the Value field will prompt the user for input when the filter is applied. The input dates will provide the dates inbetween which the work will have been completed.

For the above filter, the questions in the Value field will prompt the user for input when the filter is applied. The input dates will provide the dates inbetween which the work is scheduled to be completed.

For the above filter, the question in the Value field will prompt the user for input when the filter is applied. The input date will provide the date by which current tasks should be started.

2. CREATING THE EXPORT MAPS

The next step is to create the export maps. This can be done via Save As a File Type of HTML and then “New Map.” On the Options tab, check Task Mapping, and on the Task Mapping tab, give the Destination HTML Table a meaningful title, and then link the Export Map to the Export Filter named below. Lastly, map the MS Project fields to a similarly-named HTML Table Field.

Export Map: Critical Tasks
Filter: Critical Tasks

Export Map: Completed Work Between Two Dates
Filter: Completed Work between Dates

Export Map: Finish Between Dates
Filter: Finish Between Dates

Export Map: Late Tasks in Progress
Filter: Late Tasks in Progress

Export Map: Status Report
Filter: Status Report

3. RUNNING THE REPORTS

The next step is to run the reports. The best way that I’ve found to export valuable Project data, but still in an Excel format, is to Save As File Type of HTML. Before I go any further, I set my computer to open HTML files in Excel. To run the individual reports, I Save As File Type HTML, pick an export map, and enter any parameters when prompted. Once I’ve run the 6 reports, saving them all as HTML files, I open MS Excel. I then open the individual HTML files, which will open in Excel. I can then copy the needed data into the Excel shell which I had previously formatted.

1. Critical tasks – all the uncompleted tasks on the critical path

There are no parameters for this report, as it exports all uncompleted tasks that are on the critical path.

2. Done – all tasks completed before this status meeting extending back to the previous status meeting

This report prompts the user to enter the date of the previous status meeting and the date of the status meeting just ended. It tells the reader what tasks were completed in the most recent time period.

3. Due – all tasks due to be completed before the next status meeting

This report prompts the user for the date of the next status meeting. It tells the reader what tasks are scheduled to be completed before that next status meeting.

4. Late – all tasks that were supposed to be completed by the status meeting just ended

This report prompts the user for the date of the most recent status meeting. It lists all the tasks that should have been completed by then.

5. Next – the tasks scheduled to be finished after the next status meeting but before the one after that

This report prompts the user for two dates which frame a time period between the next status meeting and the meeting after that. It tells the reader what tasks are scheduled to be finished in the next time frame, giving a look forward.

6. Status – all tasks scheduled to be worked before the next status meeting (consisted of all open tasks that were either late or started but due in the future)

This report prompts the user for the date of the next status meeting. It tells the reader all the tasks that must be worked on before that meeting. This list includes all late tasks, all tasks scheduled to be finished before the next meeting, or all tasks that should have started before the next meeting. In other words, everything that the team should be working on, regardless of when it finishes.

4. DISTRIBUTING THE REPORTS

The Project Lead gets an Excel Workbook which contains 6 tabs for the 6 reports listed above. This gives the Project Lead enough information to answer questions from company executives.The Project Team Members get a Workbook which contains only the Status Report. This tells team members what they need to focus on in the next period.

Here is what one of the reports looks like in Excel:

These reports allow the Project Managers to distribute data without requiring that team members have an MS Project license, or that they know how to read a project schedule.

If you have any questions, feel free to do so via a comment.