I was recently asked for guidance on how to set-up a resource demand / supply management for a PMO. The case in question was starting with no resource planning tools and wanted to be able to assess what projects could be delivered in parallel.
Below is a high level guide to build a simple resource planning process to support project delivery.
1. Establish the supply / resource pool
You need to capture details of the resources available (supply). If you are doing this manually you might want to group resources into generic skill sets i.e. project manager, business analysts, tester.
If the resources are in multiple locations, you should add a field to capture location.
On assumption they are all going to be with the organisation for 12 months you can straight line capacity over 12 months. For example, if you have 10 project managers, you would place 10 FTE against project managers for Jan, Feb, Mar, etc.
This will then provide a high level view of supply on a month on month basis.
2. Establish demand
Get each project to complete a 12 month demand profile by generic resource (not just straight line).
The template should be the same format as the one used to create the supply baseline.
When you have received the submissions for all of the projects, you should be able to build an aggregated view of demand on a month by month basis.
3. Compare demand to supply
Take the demand summary by resource type and location and compare against the supply baseline. This will allow for the quick identification of where demand exceeds supply. You then can work with the appropriate projects to see if you can smooth demand.
4. Monthly Refresh
On a monthly basis, get all of the projects to provide updated demand templates based on project progress. Establish a process to capture changes in the supply baseline (new joiners, leavers, etc). Then on a monthly basis, compare demand to supply and identify contentions.
The manual approach makes using Excel a sensible option. However, for a more automated version you may be able to use MS Project or look to invest in a resource management tool.
To save time you may want to think about buying a template set as opposed to building them yourselves in Excel.
Obviously this is a generic approach. You will need to consider what is needed in your organisation before making a decision.