How to do monthly team capacity (hours) planning?

In this article, we will be taking up monthly capacity planning for a team.

SCENARIO

We are responsible for planning our team’s capacity every month. We need to have a realistic view of how much work can be done by our team next month, taking into account the availability of each of our team members during the month. This will allow us to commit to our leadership on deliverables’ scope and if needed ask for more resource or to reduce the deliverables.

Resource Capacity Planner Excel Template - Monthly Team Capacity Planning — Resource Capacity Planner Excel Template – Monthly Team Capacity Planning

We will be using the Resource Capacity Planner Excel Template in this exercise.

VIDEO DEMO

BASIC SETUP

We will be planning for the month of March 2016. We have demand data at the daily level. The template can handle daily level data for 52 days. (Limit for weekly or monthly level is 1 year)

Resource Capacity Planner Excel template - Input Planning Period — Resource Capacity Planner Excel template – Input Planning Period

In our organization, all Saturdays and Sundays are weekends. In this case study, we are planning in number of hours of capacity/demand.

Resource Capacity Planner Excel Template - Input Weekends — Resource Capacity Planner Excel Template – Input Weekends

We have 2 company holidays (Mar 15th, and Mar 24th).

Resource Capacity Planner Excel Template - Input Holidays — Resource Capacity Planner Excel Template – Input Holidays

We have 3 skill groups in our team: Project Manager, Developer and Tester.

Resource Capacity Planner Excel Template - Input Skill Groups — Resource Capacity Planner Excel Template – Input Skill Groups

Next, we will enter the standard availability of our team members in the RESOURCES sheet.

Resource Capacity Planner Excel Template - Input Resources — Resource Capacity Planner Excel Template – Input Resources

In the VACATION_OVERTIME sheet, we will enter vacation planned by our team members.

Resource Capacity Planner Excel Template - Input Vacation — Resource Capacity Planner Excel Template – Input Vacation

CAPACITY CALCULATION

Now that we have entered the availability data, the template will do its work to calculate the overall capacity of our team. As shown in the SUMMARY sheet, we have 612 total hours and the cost of that is 24120 (assuming cost per working hour).

Resource Capacity Planner Excel Template - Overall Capacity — Resource Capacity Planner Excel Template – Overall Capacity

Since our team has three different skill sets, it is very important to emphasize more on capacity calculation by skill group, as shown in the charts below.

Resource Capacity Planner Excel Template - Capacity by Skill Group — Resource Capacity Planner Excel Template – Capacity by Skill Group

The template is able to factor in their standard availability, company holidays, vacation and overtime data to calculate their true availability for the month. This information allows you to present the realistic capacity of your team to the leadership.

CAPACITY VS DEMAND

Now, let’s move on to the second phase where we have to evaluate our capacity against demand (how much work needs to be done). Let’s take a simple scenario where our demand is 8 hours of Project Manager skill, 10 hours of Developer skill and 8 hours of Tester skill per working day.

The template allows you to enter varying hours per day as well.

Resource Capacity Planner Excel Template - Input Demand — Resource Capacity Planner Excel Template – Input Demand

Now, it’s time to go back to our SUMMARY sheet where we can see the capacity vs demand.

Resource Capacity Planner Excel Template - Capacity vs Demand by Skill Group — Resource Capacity Planner Excel Template – Capacity vs Demand by Skill Group

Resource Capacity Planner Excel Template - Capacity Surplus Deficit by Skill Group — Resource Capacity Planner Excel Template – Capacity Surplus Deficit by Skill Group

These two charts clearly tell us that we are just meeting the demand for ‘Project Manager’ skill while we have some surplus in the other two skill groups. We have to remember that this is cumulative for the entire month. So, if our goal is to achieve the demand somehow by the end of the month, then we should be okay. However, in some business scenarios, meeting cumulative demand goal is not enough. We need to be able to meet demand every working day. To understand this better, let’s go to the SURPLUS_DEFICIT sheet.

Resource Capacity Planner Excel Template - Surplus Deficit Capacity by day — Resource Capacity Planner Excel Template – Surplus Deficit Capacity by day

This sheet shows the daily surplus/deficit. We see that we have surplus on some days but we also see deficit on a couple of days. Assuming demand cannot be reduced, we have to increase the capacity in order for us to meet the demand. Let’s see some of the ways to do that.

1. Increase Standard Availability
We can increase the standard availability of resources. For example, employee who is scheduled to work 4 hours every weekday can be scheduled for 8 hours every weekday. However, this will increase the capacity throughout the month and may result in high excess capacity.

2. Over-Time from existing employees
Tester 2 is taking vacation on Mar 8th and we have deficit of 4 hours. We can have Tester 1 do overtime for 4 hours on that day.

Resource Capacity Planner Excel Template - OverTime — Resource Capacity Planner Excel Template – OverTime

3. Adding new employee
Since both Developers are out on Mar 11th, and we have a deficit of 10 hours, we can add a temporary employee for just Mar 11th. We can do so by adding in the RESOURCES sheet as shown below.

Resource Capacity Planner Excel Template - Add new Employee — Resource Capacity Planner Excel Template – Add new Employee

Now, when we go back to see the SURPLUS_DEFICIT sheet, there is no deficit anymore.

Resource Capacity Planner Excel Template - Surplus Deficit Capacity by day - Updated — Resource Capacity Planner Excel Template – Surplus Deficit Capacity by day – Updated

The level of surplus or deficit that is acceptable varies by organization as it depends on the cost of having surplus or deficit. You can work with the capacity/demand data as needed to arrive at an acceptable plan for your organization. This template provides flexibility to input capacity/demand information.

We looked at daily level planning in this example, but you could also plan for weekly or monthly level for up to 1 year. (limit for daily planning is 52 days). The template also allows creating an employee report on availability.

For more details about the template’s features, please visit product page.