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.

We will be using the Resource Capacity Planner Excel Template in this exercise.
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)

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

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

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

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

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

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).

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.

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.

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


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.

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.

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.

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

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.