Spreadsheets for capacity planning

The ultimate guide to resource capacity planning_chapter_8

Are you using spreadsheets for capacity planning? Here we explore some of the pitfalls of this approach.

Spreadsheets for resource capacity planning

Spreadsheets are a commonly used tool for resource capacity planning and 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 capacity plan.

However if you are betting your resource capacity planning process on a spreadsheet you may want to re-think. We have worked with many customers over the years who started off with a spreadsheet based planning solution but quickly discovered their limitations for resource planning.

Schedule changes are near impossible

A must have feature of any capacity planning tool is the ability to quickly make schedule changes for example move a project forward by 3 months. In spreadsheets this will mean editing and copying formulas. Changes to plans become at best laborious and at worst error prone and can take many hours. Using Kelloo you can easily model different schedule options and see the results in seconds.

Spreadsheets create silos of resource planning data

As your team grows the resource capacity planning function will inevitably be split between different managers. It is quite common that groups of resources in a similar location or with similar skills are resource managed by a particular resource manager.

So spreadsheet based solutions inevitably means many resource planning spreadsheets in use. Trying to create a holistic view of the resource status becomes a monster headache and involves consolidating the data from multiple sources. Using Kelloo, each resource manager can have their own resource plan or they can share an overall master resource plan.

Simple changes to plans are not simple to do

The layout of spreadsheets means that people inevitably end up producing a spreadsheet organized a little like this:

resource_capacity_planning_spreadsheet

Does it look familiar?

It looks really straightforward. A row for each project and within that a row for each skill set (or resource) with grand totals at the bottom. However, if you have used spreadsheets in anger you will see the obvious problems straight away.

Inserting a new project means copying / updating lots of formulas

Inserting a new skill means potentially inserting a new skill set row for each project. Then copying / updating lots of formulas. Then what happens if we want to model holding a project which means making the project have no demand. Do we delete the project row – or over type the project values with zeros? Yes it becomes a nightmare. In Kelloo you can add a new project in seconds, hold an existing project or re-prioritize work and see the impact on your resources. 

Inability to model different planning options

Resource capacity planning is a collaborative process normally undertaken as part of the portfolio planning process. The people involved are typically the PMO, portfolio managers and resource owners. It typically descends into a bun fight as there is rarely one correct outcome. People need to look at the resource options and weigh the pro’s and con’s.

Doing any form of meaningful what if analysis and comparing different options is impossible in spreadsheets. What is needed is a way to model changes and see the results instantly. In Kelloo you can create scenarios which let you model changing things like resource levels and see how they will impact your plans.

Lack of reporting

Spreadsheets do not include any reporting capability and there will be reports you need to produce from your capacity planning data. How are you going to produce them in a spreadsheet? And how are you going to distribute those reports – will you end up emailing around screen prints or even worse emailing multiple copies of the capacity planning spreadsheet. Kelloo has a suite of dedicated resource capacity planning reports which provide you with deep insight into your resource planning data.

If you liked it then share it

Share on facebook
Share on twitter
Share on linkedin

Other things you may like

Report your timeline in Kelloo

Up until now, the timeline in Kelloo was only available as an online view. The latest release introduces a timeline report which lets you download

Spreadsheets for capacity planning

Are you using spreadsheets for capacity planning? Here we explore some of the pitfalls of this approach. Spreadsheets for resource capacity planning Spreadsheets are a