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.
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.
Kelloo includes powerful resource analytics and reporting.
