Building a Dynamic Resource Allocation Tool in Google Sheets

Image by Editor | Ideogram
 

Project success mostly depends on the proper resource allocation. A dynamic resource allocation tool in Google Sheets will help you track resource availability, assign tasks, and manage timelines in real time. In this article, we will build a dynamic resource allocation tool in Google Sheets.

Let’s consider a dummy project to build a dynamic resource allocation tool.

Step 1: Prepare the Resource Datasets

Let’s create three tabs to manage the project name, tasks, date, availability, etc.

• Input: List the task details, date, and required resources.
• Resources: List all resource names, their roles, and availability.
• Dashboard: You can visualize resource allocation status and availability.

Input Sheet/Tab:

Step 2: Calculate Assigned Hours and Availability

Calculate Assigned Hours for Each Resource:

You can use Google Sheets functions to calculate the total hours assigned to each resource.

• Select cell D2 of the Resources sheet and insert the following formula.

Formula:

=SUMIF(Input!C2:C5, A2, Input!F2:F5)

This formula returns the assigned hours from the Input sheet.

• Input’!C2:C5: Resource column of Input sheet where the resource name is listed.
• A2: The cell containing the resource’s name.
• ‘Input’!F2:F5: Hours Required column of Input sheet containing the total hours required for each task.

Calculate Availability for Each Resource:

From the total hours and assigned hours you can calculate the availability percentage.

• Select cell E2 of the Resources sheet and insert the following formula.

Formula:

=1 - (D2 / C2)

This formula calculates the availability percentage of remaining hours.

Step 3: Add Conditional Formatting to Highlight Availability

• Select the Availability column from the Resources tab.
• Go to the Format tab >> select Conditional Formatting.
• Set the following rules: If Availability < 0:
  • In the Format Rules >> select Less than >> insert 0 as values.
  • Select the color red to indicate over-allocation.

• Set the following rules: If Availability < 0.2:
  • In the Format Rules >> select Less than >> insert 0 as values.
  • Select the color orange to indicate over-allocation.

Emily Koper is fully occupied that’s why it is marked as orange.

Step 4: Create a Dashboard View

You can create a dashboard to visualize the available resources and allocated resources.

• Select cell C2 of the Dashboard sheet and insert the following formula.

Formula:

=ARRAYFORMULA(IF(Resources!E2:E4 < 0, "Over-Allocated", IF(Resources!E2:E4 < 0.2, "Limited", "Available")))

This formula will return the status of the resources.

• If availability is negative, it will return the status as Over-Allocated.
• If availability is less than 20%, return the status as Limited.
• Otherwise, mark the status as Available.

Step 5. Using Gantt Chart for Visualization

You can create a Gantt Chart to visualize the project task progress over duration.

• Go to the Input tab where tasks are listed.
• Add two new columns for Start Day and Task Duration:

Start Day: Calculate the day number relative to the start of the project.

• Select cell G2 of the Input sheet and insert the following formula.

Formula:

=D2-MIN($D$2:$D$5)

This formula returns the earliest start date in your tasks.

Task Duration: Calculate the task duration in days.

• Select cell H2 of the Input sheet and insert the following formula.

Formula:

=E2-D2+1

This formula returns the task duration.

Create the Gantt Chart:

• Select the Start Day and Task Duration columns.
• Go to the Insert tab >> select Chart.
• In the Chart Editor >> select Stacked Bar Chart.

Format the Gantt Chart:

• Click on the Start Day series >> Set its color to White to make it transparent to hide it.
• Only Task Duration bars are visible.

Final Output:

Conclusion

By following the above steps, you can create a dynamic resource allocation tool. Resource allocation tool helps to assign and manage project tasks efficiently. Customize your tool with Google Sheets advanced options. You can update and customize the tool based on your needs.