Spreadsheet for resource capacity planning

resource capacity planning spreadsheet

Spreadsheets like Excel or Google Sheets are a commonly used tool for resource capacity planning.

And yes, the appeal is obvious.

Most people have Excel or Google Sheets and they are easy to use and you can quickly put together a basic resource capacity planning template.

However if you are betting your resource capacity planning process on a spreadsheet you may want to re-think.

When to use a spreadsheet for resource capacity planning

We would only recommend using a spreadsheet for resource capacity planning if you tick all these boxes:

  • You have a very small pool of resources (i.e. less than 10).
  • A handful of projects (no more than 5).
  • One person is responsible for all planning.
  • You don’t need any reporting.
  • You are comfortable developing formulas and macros in tools like Excel.

If you don’t meet these criteria then perhaps a dedicated resource capacity planning solution is the way to go. Tools like Kelloo don’t require a huge investment and the payback in terms of better resource utilization and hiring decisions pays for them many times over. You can read our review of different resource management tools here.

Here are some of the main problems we come across when organizations try to use spreadsheets for resource capacity planning.

Capacity planning in a spreadsheet is not always a great idea

#1 Difficult and cumbersome to update

A must have feature of any capacity planning tool is the ability to quickly make schedule changes. For example moving a project forward in time by 3 months.

Sounds simple doesn’t it? Or you may need to change around the resources allocated to a project (which is something you are going to want to do a lot).

But the thing is, doing this in a spreadsheet will mean editing lots of cells and copying formulas. Is this something you really want to be doing? The chances are you will end up spending hours each week updating a resource capacity planning spreadsheet due to the sheer amount of data it will hold.

#2 Spreadsheets create silos of resource capacity planning data

As your team grows the resource capacity planning function will get split between different managers. So using a spreadsheet based solution inevitably means multiple resource capacity planning spreadsheets in use.  I guess you can see where we are going here?

Trying to create an overview of your resource status becomes a monster headache and involves consolidating the data from multiple sources. This is a major opportunity to introduce errors and omissions into your resource capacity planning data.

#3 You probably need to know how to create macros

Macros are used in tools like Excel to automate common processes. Resource capacity planning spreadsheets will have a lot of columns (one per week or month) and many rows (one per resource, team or skill). Updating this data manually is very time consuming, error prone and repetitive. So most people will end up creating macro to help them. 

Kelloo's resource management tools

Resource planning, forecasting and reporting in one place. We help you get the most from your projects and people.

#4 Spreadsheets are static and one dimensional

When resource capacity planning, you need to look at different resource and project options and weigh the pros and cons to be able to make a decision. To do this you need to shift project timelines around, model what happens if you hire more people etc. This is often called what if analysis. And you need to see the result of the changes as you make them. Which you can’t do in a spreadsheet.

In Kelloo you can create scenarios which let you model changing things like resource levels and see how they will impact your plans.

Prioritize projects

Tools like Kelloo include out of the box resource capacity planning features.

#5 Lack of reporting

You will build up a lot of valuable data when resource capacity planning and being able to report, share and visualize the data is key. Tools like Excel let you build charts over your data but this is not easy to do, is time consuming and difficult to share. Whereas most resource capacity planning tools have out of the box resource capacity planning reports you can run straight away.
Resource management reporting

Kelloo includes powerful resource analytics and reporting.

Our latest articles and tips straight to your inbox

If you liked it then share it

Other things you may like

what-if planning in resource management

What if planning and scenario planning in Kelloo

The new what if planning features in Kelloo give you a crystal ball at your fingertips so you can see the future. What if planning shows what will happen if you change project timings, delay projects, hire or fire resources or cancel or commit to new projects.

what if analysis in resource management

What if analysis in resource management

What if analysis is a superpower that lets you see into the future. It helps you understand the impact of project delays, holding or approving new projects and how new hires can impact plans.