Create a Resource Allocation Model in Excel

What Is a Resource Allocation Model in Excel?

A resource allocation model involves:

Identifying the skill sets needed to finish project tasks.
Calculating how long these tasks will take in hours.
Planning the resource capacity, which entails choosing who will work on what tasks based on availability predictions and the project schedule.

Consider the production process of four books.

This is an overview:

resource allocation excel

Step 1- Entering Data

Create a dataset in B4:F14. Enter project names row-wise and the materials list column-wise.

Enter all necessary data.

Insert necessary data in dataset

Allot two rows to define the Unit Profit and Number of Productions.
Enter the amount for each book.

Input All Required Data to Create Resource Allocation Model

Step 2 – Estimating the Amounts of the Used Inventory

Select H6.
Enter the following formula using the SUMPRODUCT function to get the used amount of the material. Use an Absolute Cell Reference in C14:F14.

=SUMPRODUCT(C6:F6,$C$14:$F$14)

Press Enter.

Using SUMPRODUCT function calculating used materials in resource allocation model

Drag down the Fill Handle to copy the formula to the other cells.

You will see the amounts of inventories used during the production.

Estimate Amounts of Used Inventory to design resource allocation in Excel

Step 3 – Calculating the Remaining Inventory

Select I5.
Enter the following formula in the cell.

=G6-H6

Press Enter.

Using formula to calculate remaining inventory

Drag down the Fill Handle to copy the formula to the other cells.

You will see the result.

Calculate Remaining Inventory in Resource Allocation Model

Step 4 – Evaluating the Profit

Define B15 as Profit to see the profit of a book.
Select C15 and enter the following formula.

=C13*C14

Press Enter.

Evaluating Individual Project Profit in Resource Allocation File

Drag down the Fill Handle to copy the formula to the other cells.

To evaluate the total profit, define B16 as the Total Profit.
Enter the following formula using the SUM function.

=SUM(C15:F15)

Press Enter.

Evaluating Total Profit By SUM Function in Resource Allocation Model In Excel

You will see the total profit value.

Step 5 – Applying the Solver Analysis Toolpak for Maximum Profit

Some of the remaining inventory values are negative. Extra raw materials are needed for production.

Go to the Data tab and click Solver in Analysis. (You can enable it in Excel Options.)

Launching Solver ToolPak to Get Max Profit

In the Solver Parameters dialog box, in Set Objective, select cell C16.
Choose Max.
Define By Changing Variables Cells as C14:F14.
Add constraints. Click Add.

Setting suitable properties to get the maximum profit in Excel

In Add Constraint, select I6 in Cell Reference.
Change the condition operator ‘<=’ to ‘>=’.
In Constraints, enter 0.
Click Add.

Adding suitable constraints in resource allocation model for max profit

Add the same types of constraints to I7:I12.
Click OK.
You will see all constraints in the empty list box.
Check Make Unconstrained Variables Non-Negative.
Set Select a Solving Method to Simplex LP.
Click Solve.

All required options for getting value in resource allocation sheet

The value of C14:F14 will change. The Solver tool-pak will display Solver Result.
Click OK to keep the value.

The value of maximum profit and the amount of production of each book to secure the profit are displayed.

Applying Solver TookPak Analysis for Maximum Profit in Resource Allocation Model in Excel

How to Do Resource Allocation for a Project

Define the project name row-wise. Enter the names of raw materials in the columns. This is the output

Resource Allocation for a Project in Excel

How to Do Resource Management in Excel Sheet

To create a completely new management model, you can follow the procedure of the resource allocation model.

Resource Management in Excel Sheet

How to Create a Resource Allocation Gantt Chart in Excel

You can add a Gantt chart to resource allocation to check the working period of the project. Use the Conditional Formatting feature.

Steps:

Enter the following formula in E4 to get the dates in E4:P4 using the SEQUENCE function.

=SEQUENCE(1,12,DATE(2022,9,1),1)

Press Enter.

Using SEQUENCE function to get the date value in Gantt chart of resource allocation

Select E5:P8.
In the Home tab, click the drop-down arrow of Conditional Formatting in Styles.
Select New Rule.

Launching new rules dialog box to set customize rules for cell formatting

In New Formatting Rule, choose Use a formula to determine which cells to format.
Enter the following formula with the AND function.

=AND(E$4>=$C5,E$4<=$D5)

Click Format.

Set the formatting rule using AND function in resource allocation Gantt chart

In Format Cells, in the Fill tab, choose a background color. Here, Green, Accent 6, Lighter 60%.
Click OK to close the Format Cells dialog box.

Click OK to close the New Formatting Rule dialog box.

The chart will be displayed.

Create Resource Allocation Gantt Chart in Excel

Download Practice Workbook

Download the practice workbook.

Resource Allocation.xlsx

<< Go Back to Excel Solver Examples | Solver in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Van Thai Bui Sep 29, 2016 at 3:32 PM

I think we need one more constraint is:
$B$11:$F$11 = interger
Thank you very much!
Jorge Gonzalez Aug 28, 2017 at 10:18 PM

You should reference “Excel 2013 Bible” by J. Walkenbach if you are going to take the examples from that book…

Create a Resource Allocation Model in Excel – 5 Steps