Overview of steps:
- Enter Settings
- Enter the Capacity of each Resource along with their availabilities
- Enter vacation and overtime
- Enter Demand (tasks) data for each task under different projects
- View Dashboard, Calendar and the Project/Resource view of demand
- Address any over-utilization and under-utilization
Step 1: Enter Settings
In the Settings sheet, we will enter the basic settings for this planner.
We will set the planning period by entering a Start Date and End Date. The template allows a maximum of up to 2-year planning period.
Enter Planning Period Dates
List of unique roles and Project Categories
You can enter the list of unique roles that are needed for various projects and the project categories, for which the resource planner shall be used.
Please note that the template supports a maximum of 25 unique roles.
The template uses the term ‘Resource’. The most commonly used resource in most scenarios is the human resource (can be called as Employee). But a machine could be a resource too. Hence instead of using ‘Employee’, the term ‘resource’ is generally used. In here, you can enter the list of resources and their role.
Also, here you can enter the list of Projects and their categories.
Since most companies have holidays, it is important that we factor that in when we calculate capacity. We can enter our company holidays and the template will set the availability of resources to 0 by default on those days. Each holiday must be entered individually.
Enter List of Holidays
If a resource works during a holiday, we can enter that as overtime in the VACATION_OVERTIME sheet.
Step 2: Enter Resources’ Capacity
Now, we move to the Capacity sheet in the template.
We must enter the resources, provide start and end dates of availability, available hours for each of their work day.
Resources can be hired anytime and similarly resources may leave a company anytime. The template will assign 0 availability before start date and after end date.
In the above example we have entered the capacity details of each of the resource ( as entered in Settings) and we have not entered any details for SUN/SAT which will be accounted by the template as weekend (day-off).
Once the capacity details are entered the template automatically calculates the total Capacity for the entered date range.
The Demand column will get updated once we enter demand data.
Please note: The columns in Green are automatically calculated so do not delete or alter them.
Step 3: Enter Vacation and Overtime
Though we have entered standard availability above, we know that employees may take vacation or sometimes work overtime.
We can enter any vacation or overtime taken by resources in the VACATION_OVERTIME sheet.
Point to note in Vacation/Overtime:
- Overtime is positive to capacity and Vacation is negative to capacity.
- In the above example Davis does "Overtime" for a period of 11 days.
- For the overtime to impact the capacity on non working days, please ensure to assign Capacity as 0 on Non-Working Days. For ex., SAT / SUN (as shown in below image)
Step 4: Enter Demand data
In this template, demand is entered for each task in a Project in the Demand sheet.
Required fields: Task, Project and the Role that is needed to fulfill the given task; the start and end date for that particular task and the Demand for each task.
Once there is a resource available to meet the demand, assign the resource in the "Resource" column.
The resources are a list of items in a drop-down based on the Role assigned.
The automatically calculated columns are in Green which includes the total demand, the assigned demand and project category.
The template is built in such a way that, in the Demand sheet itself you can get a warning of mismatched demand and assigned demand. That is, if the demand is less than the assigned demand, the template highlights the same for you.
Please do not delete or modify the calculated columns as they may break the functionality of the template.
Once we complete entering the demand, we are done with data entry.
Step 5: View Dashboard
Dashboard sheet shows the summary of demand that is assigned and the capacity' utilization in two sheets, respectively.
The Demand Assignment Dashboard:
Get a quick overview of the assigned and the unassigned project and project categories.
This dashboard helps in identifying which projects are yet unassigned and the roles that are unassigned too.
Assigned demand is those demand who have a resource assigned to them and are available to fulfil (in the Demand sheet)
The second sheet is the capacity utilization dashboard:
Get to see the number of resources and roles with Deficit and Surplus capacity and take immediate actions to ensure a smooth flow in the work.
Use this sheet to identify under-utilization and over-utilization in your project assignments.
Why is this important?
By looking at the trade off in the surplus and deficit capacities, you can take corrective measures to ensure proper resource allocation to meet unassigned demand.
This way, the Dashboard will aid you in achieving close to 100% optimal resource utilization and to meet a 100% demand.
Hence it is important to view utilization at role and/or resource level.
This calendar, provides you with an overview based on either at the Role or the Resource level (dimensions).
The calendar is very flexible you can choose to view it for either Capacity, Demand or Capacity-Demand, Utilization% making it easy for you to track.
You have the flexibility to choose the time dimension ranging from Daily to Annually.
You can filter the Dashboard and Calendar based on Resources and (or) Roles to drill-down further in your analysis.
Similar to the calendar, get a project level overview of the demand allocation. This visual shows, for a chosen time dimension (from Daily to Annually) the Projects and the list of resources currently on these projects.
What if there is a need to have a similar Resource-level view? We have you covered! The Resource View gives you just that.
The Resource and the Project view, both have a set of slicers to enable to you drill-down on each of the visual to a granular level.
Please note: To remove any filters applied on the Project / Resource view before going back to the Dashboard or Calendar sheets.
Step 6: Addressing Over-utilization and Under-Utilization
In order to address the over-utilization and under-utilization, we must do one or more of the following:
- Change Capacity
- Adjust standard availability of resources in the Capacity sheet.
- Increase (to increase capacity) or Decrease (to reduce capacity) available hours
- Add overtime (to increase capacity) or vacation time (to reduce capacity)
- Change Demand
- Change the demand hours if possible. This would reduce the demand and allow the capacity to fulfill the demand.
- Change Allocation/Assignment
- Change assignment of tasks (demand) to a different resource.