How to Create a Capacity Planning Graph in Excel?

5 min readApr 18, 2018

We are continuing our column on the different tools that will help you improve your project management practices with Excel.

Today, we are going to focus on capacity planning graphs and how this prediction tool can help you better manage your projects.

What Is a Capacity Planning Graph?

Also called workload plan, a capacity planning graph is a visual representation of your available capacity.

It’s a project management tool that mitigates your planning risks and helps you improve your forecasting by:

Giving you better insights, so you can identify whether your teams have available time to support new projects
Determining whether your resources’ workloads are balanced
Estimating the impact of your planning on your team’s workload in real time

Why Use Capacity Planning Graphs?

Planning can be tricky because you can’t predict the future and you have no control over how things are going to unravel. Capacity planning graphs can help you analyse these tricky situations, which arise pretty often, for example when you want to :

Ensure billable resources are working at full capacity
Anticipate and justify future HR needs
Anticipate times of over- and under-capacity
Help identify initiatives that must be prioritised
Compare your teams’ workload

There are many use cases for capacity graphs, but here are two specific examples that will help you get the gist of it.

Determine Whether or Not Your Team Can Support New Projects

When your company is working on several projects in parallel, it’s not easy to determine if you can commit to new projects.

To create a capacity planning graph, you will have a worksheet containing your different departments and the planned capacity for each of the departments for a certain period of time.

In this example, we have presented different departments of an organisation, each of these departments has its own projects to complete. Your objective as a project manager could be to determine through the capacity planning graph whether the R&D department can take on a new project in the coming weeks.

As you can see, from February till mid March the R&D department is under capacity (the capacity graph hovers around the 80% line).

This implies that they can work on new initiatives. However, from the end of March till April, the planned capacity for the R&D department exceeds 100%. Taking on a new project at this point could generate risks and jeopardise ongoing projects.

That’s very valuable information! But you don’t need to stop there.

As a project manager, you want to make your planning as efficient as possible, which is why you should consider moving around some initiatives and prioritising others. Creating synergies between different departments would be the way to go.

For example, if you take a look at the table for the Operations department, you can see that their capacity is below 100% from February to April. Lending a helping hand to the R&D department by temporarily sharing your resources will allow you to support new projects (assuming these resources are trained to do the tasks).

Predict Your Future Human Resource Needs

Capacity planning graphs can also help you manage the unexpected.

By anticipating times of over & under capacity, you’ll be able to monitor your team’s planned efforts to predict your future HR needs, prioritise your initiatives and shorten periods of inactivity.

In this example, the graph displays the planned capacity for specific teams of an organisation. We chose to allocate the capacity over a period of one year.

Your objective as a human resource manager is to optimise your team’s workload and ensure that their planned efforts do not exceed their availability. This will allow you to identify broad recruitment trends and allow you to gain a long-term view of your teams’ workload.

As you can see, Team A, D & E have periods of over-capacity and under-capacity that fluctuate depending on the month of the year.

But, Team B is regularly in over-capacity during the whole year. This means that your teams’ planned efforts exceed their availability. That’s not going to go well.

In this situation, you should consider prioritising your projects to be able to focus on the right initiatives, hiring new talents to reduce your employees’ workload and sharing your resources to shorten periods of inactivity.

As mentioned in the previous example, it is important to create synergies between departments. Temporarily sharing your resources with Team C would be the best move because you won’t have to spend the time and the money recruiting and training others.

Since they are under-capacity throughout the year, their planned efforts do not exceed their availability meaning Team C still has available time to plan for.

In the long run, circumstances allowing, you might consider permanently shifting some resources from Team C to Team B.

How to create a Capacity planning graph

Now that you understand the purpose of capacity planning graphs, here’s how to actually create one.

Important: The planned capacity of a person corresponds to the ratio between the numbers of hours that are planned for him/her compared to his/her total hours of availability. Here is how you calculate planned capacity:

Note: Microsoft Excel version 2011 was used for this article. If you are using a different version of Excel, or Google Sheets, the process remains the same.

First Example

Step 1

Once you have calculated your planned capacity, highlight your data table then click on “Charts” in your toolbar, then click on “Line”.

Step 2

A graph will appear, right click on the graph and then “Select data”. Go to “Series” and remove all departments except for the R&D department and Total Capacity (those are the ones we used in our example). In this example, we want to determine whether the R&D department can support another project. Then click on OK. Now you have your capacity planning graph.