Blog

How to create a Resource Plan in 5 minutes with Microsoft Excel 2007 Pivot Tables

Getting an overview of your resource demand can be a complex thing. If you do not have a tool that can make an aggregated overview you have to calculate this by yourself. If your resource demand change over time, you will be forced to do this exercise all over again. This can be quite time consuming.

But no need to worry, Microsoft Excel 2007 can do the job for you. I will show you how to create a quick overview of your resource demand based on monthly aggregations. By using a Pivot table you can create a small Microsoft Excel 2007 application that will get the job done. Should your resource demand change, the application will recalculate in a split second!

But before we begin here are a few definitions:

  • The resource demand is the sum of (unallocated) resources that the project requires. This might be budget, staff or material. However for this example I will refer to staff.
  • The Resource Plan is an overview of what exact resources (in this case staff) the project needs and how much of the resources (might be expressed as hours, days, amount etc.) the project requires.
  • The Work Plan is an overview of what tasks the resources will manage and when.

Everything set? Here we go!

Step 1. Open Microsoft Excel 2007.

Step 2. Create a spreadsheet for the Work Plan.

The work plan should contain the following columns:

  • Task name
  • Start date
  • Finish date
  • Month
  • Effort Hours
  • Columns for each resource your have in your team (in this example I have a column for John, Jane, Peter, Elizabeth and George)

The spreadsheet should look like the screenshot below.

pivot1

An example of the work plan you just created

Task name is the name of the task that the resource has to carry out. Start and Finish dates are the time range for the tasks. I like to manage on specific dates instead of entire months or weeks, because this gives me a greater sense for the timing of the project and a higher granularity, but this is of course pretty individual. For this example these two columns are not important though. The application will work anyway. Month however is very important. In this column you specify in which month the task will be done. Effort Hours is the sum of all work hours that you allocate on each resource on each task.

Now you have your initial framework for your work planning.

Step 3. Now create a second spreadsheet by right clicking on the spreadsheet pane in the bottom of the screen and select “Insert…”. Name the new spreadsheet “Resource Plan”.

pivot2

This is the spreadsheet pane in Microsoft Excel 2011 where you can switch between spreadsheets

You now have the two spreadsheets you need for the application. You can name the original one “Work Plan” like I did.

Step 4. In the “Resource Plan” insert a new Pivot table by clicking on the “Insert” tab in the top of the screen and click on “Pivot table”. Leave the mouse cursor in the “Table/Range” field in the pop up window that shows. Go to the “Work Plan” spreadsheet” and select the work plan columns that you’ve created in step 2.

pivot3

This is the window where you choose what data the Pivot Table should use

The input in the field looks like this: ‘Work Plan’!$A$2:$J$14, whereas “Work Plan” is a reference to the spreadsheet and the “!$A$2:$J$14″ is a reference to the cells that you have selected. Click “Ok” when you are ready.

Step 5. Now you have to set up the Pivot table for the right reporting.

If your “PivotTable Field List” is not showing in the right side of the screen, left click on the Pivot table and click the “PivotTable Field List” button in the tabs in the top of the screen.

Now do the following:

  1. Check and drag “Month” to “Column Labels”
  2. Check and drag “Values” from “Values” to “Row Labels”
  3. Check and drag each resource to “Values”. Left click on each, select “Value Field Settings…” and choose “Sum” in the “Summarize value field by” list and name each resource “Sum of John”, “Sum of Jane” etc.

The “PivotTable Field List” should now look like this:

pivot4

This is how you should configure the Pivot Table can calculate the resource demand and reflect it in the Resource Plan

Now the setup is complete and you are ready to implement the Resource Plan application in you project.

Your blank Pivot table will look like this.

pivot5

This is an example of the blank Resource Plan that will finally be populated with the resource demand

Step 6. Go to the “Work plan” spreadsheet again.

Step 7.  Start building your detailed work plan.

Submit task names, start dates, finish dates, what month the task is being done, total effort hours and distribute the effort hours on each resource name.

I have prepared an example of a work break down structure in my pseudo plan below. Take a look at:

pivot6

This is an example of how to build a work break down structure in the detailed Work Plan

Step 8. Now go to the “Resource Plan” spreadsheet and right click on the Pivot Table and select “Refresh…”.

This action will refresh the Pivot table with the data that you have submitted to the “Work Plan” spreadsheet. And there you go! A Resource Plan calculated by a Microsoft Excel 2007 Pivot table.

The result should look like this:

pivot7

An example of how the “Resource Plan” spreadsheet should look like after you have submitted your detailed work plan

Now you are able to build an overview of your resource demand in a given month at any moment. Should your resource demand change simply hit “Refresh” to recalculate the Pivot table to generate a new Resource Plan

source: pmblog.net

Posted in: Excel Planning

Leave a Comment (0) ↓

Leave a Comment