November 22, 2022
General
6 minutes
Iryna Viter

How to Build a Capacity Model Without Using Excel

Looking to create a capacity planning model for your business, but don't know where to start? Learn how to do it in our 6-minute guide to capacity modeling.

Excel errors are rampant. The more data you feed it — the higher the error rate will be.

In a study on Excel use, a representative from the University of Hawaii found that 88% of spreadsheets contained errors. Some of them were mechanical, logical, or caused by omission. But it all boils down to one thing — Excel is not reliable for large data sets and when you are calculating capacity, a single error can mean wrong expectations.

When doing capacity modelling, you need to pull all the relevant threads together and embrace the art of visualizing workload vs capacity to see how much your team can realistically accomplish.

But considering just how flawed Excel is, can you find better ways to build your staff capacity planning model?

In this article, we're going to look at the concept of this model, the values it brings, and the steps you can take to get the most out of it.

get better at capacity planning

What is a capacity planning model?

A capacity planning model is a (visual) representation of confirmed workload vs capacity — whether you're looking at a team, an individual role, or even a skill in your pool of resources.

There are three different ways you can take when building your capacity planning model template:

  1. Lag strategy. As the name suggests, this is a reactive model where you increase capacity only after you spot increased demand and confirm that your existing resources are not enough.
  2. Lead strategy. Unlike the previous strategy, this one is proactive. It requires you to increase capacity by forecasting that the demand will increase (provided you have reliable data suggesting that.
  3. Match strategy. Last but not least, this strategy will require you to constantly monitor workload vs capacity to spot discrepancies and fix them in real time.

Why create a capacity planning model?

The purpose of the capacity model is to help you get a realistic understanding of what your capacity is, what the demand is like, as well as if and how your current resource pool can accommodate the latter without getting overworked or being left on standby.

Without a capacity model, you're flying blind. Not knowing how much work you can fit into a given period of time, you can't make smart decisions about where to invest time and resources. Having visibility into the available capacity helps with everything from recruiting to onboarding, and from project planning to prioritization.

Capacity planning allows you to

  • See and plan ahead of time any upcoming workloads
  • Scope out tasks/projects as they come in
  • Prevent scheduling mistakes early on

What goes into a capacity model?

All things considered, capacity planning model is pretty complex, especially for IT organizations. There are lots of moving parts in it, you need to keep your capacity agile and dynamic — which can turn into a Herculean task if you are limited to Excel only.

Before you look into how to build a capacity planning model, consider the items you will need to add there:

  • Projects and timelines — what kind of deliverables will your teams have and when are they due?
  • People, skills, roles — what resources do you have available, what is their availability like, and what expertise do they bring to the table?
  • Hours on each project — how many hours does each project require and how many hours do your people have planned out already? This point will also help you understand your business capacity overall.

What's wrong with capacity planning in Excel?

In short, building your capacity planning model in excel is a risky business because you can easily get tangled in all the data or get blind-sided by some numbers that can slip through the cracks.

Here are some other limitations you are sure to run into when building your workforce capacity planning model using spreadsheets:

  • No granular insights into available skills and capacity
  • No way to monitor and account for people's time off
  • When large data sets are involved, your Excel will often crash
  • Only one person can be editing it at a time

Best practices for building capacity models

Building a project management capacity planning model and creating capacity reports in general is never an easy job. But there are sure ways you can make it more organized and effective.

Here are some tried and tested practices.

  1. Check your capacity. What is you current capacity like? How many hours do your team members have in confirmed workload and how many do they still have free? Don't forget to account for potential vacation time and general time off!
  2. Analyze demand. What is business demand like at the moment? Do you expect an increase, a decrease, or stable sailing in the number of projects you will have in the upcoming months?
  3. Forecast capacity needs. Plan potential capacity adjustment needs based on the data you collected from the first two steps. If you see the demand growing but capacity going down — time to hire more or do some resource/ project prioritization. If you see an opposite trend, restrain from hiring new people as they will most probably be often left on standby.
  4. Identify resource prioritization needs. If some projects are more urgent or important than others, work out a strategy how you would prioritize resources, especially if you have some limitations there.
  5. Pinpoint bottlenecks. What could go wrong, if anything? What limitations do you expect to potentially hinder progress in the future? Perhaps there is a resource that is in high demand but is already fully booked? Perhaps you have several projects due at the same time and are not sure if your team can make it?

Thinking ahead and calculating your further steps before you actually need to take them is the way to avoid all the dangers of capacity planning and project management in general.

What's next?

Now that you're convinced just how inefficient capacity planning model can turn out in Excel, here's how Runn, an advanced resource management software, can help you master capacity management from A to Z.

Suppose you have a design project coming up and need to look into the capacity of your resources to see if the project is feasible with what you currently have. If not, you can either push the delivery date so it fits your team's availability or do some hiring to start the project as planned.

But here's how you get to that decision using Runn's capacity charts.

In the chart below, you can easily see the capacity vs confirmed workload for your Team Orange in the second week of January. They have a maximum capacity of 180h. And with their confirmed workload standing at 105h, you can give this team an additional 75h-worth of work.

But what if you need a more granular view into the availability of your resources? You can then filter the chart based on roles, skills, employment type, people, tags, and projects.

capacity model

Your project will be revolving around Figma — do you have enough experts who are good at designing in Figma? If yes, do they have enough capacity left to participate in the new design project?

Go ahead and filter your chart by skills! (see "Skill Figma" as a filter in the visual below).

Once you put the project dates in and filtered your resources by skill, you get a high-level overview of the profiles that match your search. Look at Rihanna Fenty! She knows Figma and starting from December 12th, has 4 hours free.

Want to drill in and look at all the other projects Rihanna is working on to double-check if she makes the best fit with the project you are planning?

She is assigned to one iCar website project for 4 hours per day and seems to have no time off planned, which means she has those 4 hours free!

To sum up

Building a capacity model can give a company the ability to accurately predict and react to shifts in demand, without having to resort to costly, last-minute changes. It can also help identify potential bottlenecks that could cause problems down the line.

It's important to understand that there is no one right way to build a capacity model — there are countless different business models, and countless different ways that each business can make it through the year.

A capacity model is simply a tool that can be customized for any given situation, as long as it can accurately assess a company's current state and estimate its future needs based on the information provided.

Want to see how far Runn can get you with your capacity planning model? Book a demo today!

Enjoy the post? Sign-up below for the latest strategies, stories and product updates from the team at Runn.

You might also like

Try Runn today for free!

Join over 5,000 users worldwide.
Start scheduling in less than 10 minutes.
14 day trial. No credit card required.