Resource Capacity Planner – Excel Template – Support
This page will host supporting information for the Resource Capacity Planner Excel Template which can be used to plan resource capacity, compare versus demand and identify over-utilization and under-utilization.
To know the features of this template, please visit the product page.
The latest version (v2) was published in July 2018.
The following are the support articles to help you get started with using the template. If there are any questions not addressed here, please drop a comment and I will add article/video demo about that topic.
- Please see this article on Important Tips before using the template
- Do not edit calculated cells
- Input Data is always visible & can be edited easily
- Backup by saving file regularly
- Refresh Calculations when Pivot Tables are used
- This template uses Excel tables for data entry. If you are not very familiar with Excel tables, please see this article on How to enter data using Excel Tables?
- What is a Table?
- Why do we use Tables?
- Components of a Table
- 3 Basic Data Entry Operations
- 5 Essential Tips to avoid mistakes
- Step by Step User Guide
- Step by step user guide to Resource Capacity Planner Excel template.
- Learn how to enter input data on capacity and demand.
- Find how to use the Capacity planning Dashboard and Calendar to identify over-utilization and under-utilization.
- Learn how to address over-utilization and under-utilization to improve the plan
If you have any questions on the template, please post them in the comments section below. I will respond as soon as I can. If it will benefit others, I will also publish a blog post and video answering your question.
175 Comments
I have sent an email to your support team regarding an issue I see with the template. I am able to get the correct calculations on the Dashboard tab when using DAILY demand tasks but unable to get the correct calculations when entering WEEKLY or MONTHLY tasks in the Demand sheet. Please advise.
How do I convert hours into percentage utilization? I want to track demand, capacity, dashboard and calendar in percentage instead.
Thanks for your question.
Unfortunately, there is no built-in automatic way to calculate percentage in the current template. We would have to copy the capacity and demand values separately and put into a new sheet, and calculate the %.
For an automatic way, we need to develop a new sheet. The formulas in the Calendar sheet can be used and modified to show capacity and demand separately. Then, new formula to calculate percentage can be created.
Please let us know if any questions.
Best wishes.
Can I get it?
FYI, I want to capture capacity and demand by percentage and calculate utilization by percentage.
Hello
We are not accepting any customized projects now due to a heavy workload.
Thanks
Hi,
I am doing Monthly Planning. in DEMAND Tab i enter start date as 1-jan-2020 and hours as 170h it means for Jan each day it should take it as 8.5h. But in CALENDAR Tab for that particular resource for 1-jan-2020 allocated hours is showing as 170h and rest of Jan days it is showing 8.5h. How to update demand monthly?
Best Regards.
Thanks for purchasing our template.
Can you please share your file along with the list of issues to contact@indzara.com.
Best wishes
The template has the ability to show the capacity and demand of a resource within the same project. But I need to be able to compare the total capacity of a resource with respect to different projects. that is, from 2008 annual capacity hours of the resource, how many hours/cost of demand does it have in each assigned project? can I do this with this template?
Hello, I have multiple resources and multiple projects that they will be assigned to so I am unclear of the best method for entering the data for capacity and demand. Can someone advise the following:
Capacity : should I only enter the hours of availability of a full workday or the hours they are just available to work on the specific projects?
Demand : I am entering the tasks
Dashboard : I am only seeing the surplus…not the deficits
Need to understand the best methodology to ensure I am getting the right output…thanks
Thanks for purchasing.
1. In the Capacity sheet, you will have to enter the hours a resource is available to work on a specific project using a specific skill for each weekday during a period (between Start and End Date). Please see examples in https://indzara.com/2018/07/resource-capacity-planner-excel-template-step-by-step-user-guide/
2. Same with Demand sheet. Here we have to enter specific demand. Example – On Apr 4, 2020, 6 hours of development work should be done by Resource A as part of Project One.
3. Dashboard: if the capacity entered is greater than demand, then only surplus will display.
Please review the guide in https://indzara.com/2018/07/resource-capacity-planner-excel-template-step-by-step-user-guide/
If there are further questions, please email me (support@indzara.com) the file and questions. We will address them as soon as we can.
Best wishes.
Resource Capacity Planner template has a default of 100 resources. I need to increase this default to 200 resources. What do I need to do?
Please email support@indzara.com for a version that supports 200 resources. The file is large and could be slower. But I will be glad to share. Please email with order number.
Thanks & Best wishes.
Any way to convert this from individual resource (person) to resource type (Dept)?
we have teams of each skill. for us its not practical to assign individuals to each project/task we are looking to assign work to a department and based on the head count determine our capacity constraints for that type of work.
instead of assigning to each individual?
Thanks for your interest.
I have not tested that input option so far. But logically it should still work. Are you expecting any different output than what is currently available in the dashboard? If you would like to try the template and find that it does not meet your needs please let us know and we will issue a full refund. Please email support@indzara.com if you have any questions or need assistance.
Best wishes.
I have a very similar need to that of Jerry and need to know whether I have enough headcount with a given skill to fulfil demand.
However, having purchased and played with the spreadsheet, it seems like you have to assign a named resource to a Demand for it to register.
Thinking about it logically, it looks like the Demand worksheet is being used to state both demand and fulfilment.
It could maybe be modelled differently? e.g. split into
A) Demand i.e. demand for a given skill on a given project over a given period
B) Fulfilment – the named resource that will fulfil the given demand
Thanks for your feedback.
We will try to incorporate something in the similar lines in our future releases.
Best wishes
Resource Capacity Planner template:
The Capacity Vz Demand dashboard does not show data for projects or resources. I did several refreshes after entering data in the Capacity and Demand sheets, but no data is shown on dashboards. I have followed all the steps in the guideline and other resources listed in this support section. Is it possible for me to upload my file for you to take a look at it?
Thanks
Thanks for using our template.
Please email your file with the list of issues to contact@indzara.com
Best wishes
I have a resource (John) that is schedule to work 3 hours a day (five days) on a given project (Project 1) for a specific length of time (start date and end date). When I enter the “Task” (Task 1) for John (associated to Project 1) it comes back as a deficit showing on the due date. Because there isn’t an actual “start date” to Task 1 it does not evenly distribute the workload when I view it on the “Calendar” sheet filtered for “Resource” and “Hourly”. It shows as a deficit of time on the “Date Due” not accounting for the time allowed to complete the task.
How can this be resolved because it not projecting accurate deficit? Thanks
Thanks for using our template.
Please share the file along with the list of issues to contact@indzara.com
Best wishes
I’m working on the Vacation/OT page. My resources are all on many projects. I do not understand why I must repeat my entries for every project the person is on. A resource is going to be out. So all 10 assigned projects should understand the employee is unavailable those days. How can I do this with one entry per person per vacation? It’s just like the holidays! You don’t make us add a holiday for every project. The person simply isn’t available for scheduling.
“Important:
If you have assigned a resource to multiple skills and/or projects, you will have to enter vacation or overtime separately for each combination. This allows more flexibility in capacity planning as you can enter overtime only for a certain project instead of both projects (assuming resource assigned to 2 projects).
Thanks for your feedback.
In your scenario, when the employee is completely on Vacation, I understand that this would result in multiple entries. There are some other cases where an employee is only taking off from one project on a day but will work on other projects. Or the employee may do overtime on a specific project but not on others. There are different possible scenarios. As I tried to consolidate all such entries into one table for all vacation/overtime, partial and full, I had to make that compromise. Otherwise, I would have to have separate tables (Full Vacation, Project Specific Vacation, Overtime tracking) to do different scenarios that may become more complex with more sheets and so on. Holidays are considered like full time off for all employees in organization. That’s why they don’t have this restriction.
I am fully agreeing with you that this results in more data entries in scenarios like yours. I hope that my explanation clarifies the need to balance different scenarios as well as complexity/simplicity. I will continue to explore better methods of solving this problem in future as the software and computing power advances.
Please let me know your thoughts.
Thanks & Best wishes.
I am going to say that this is hardly ever the case. Resources take vacation from their duties, not a project. I too would like the Vacation time to be calculated against the resources total capacity instead of per project.