# Create a Resource Allocation Model in Excel – 5 Steps

## 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:

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

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

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

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

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

## Step 3 – Calculating the Remaining Inventory

• Select I5.
• Enter the following formula in the cell.

`=G6-H6`

• Press Enter.

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

You will see the result.

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

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

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

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

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

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

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

## 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

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

## 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:

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

• Press Enter.

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

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

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

Download Practice Workbook

Download the practice workbook.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

2 Comments
1. I think we need one more constraint is:
\$B\$11:\$F\$11 = interger
Thank you very much!

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

Advanced Excel Exercises with Solutions PDF