Resource allocation means a proper distribution of our existing inventories to make the maximum amount of profit, In this article, we are going to demonstrate the step-by-step procedure for designing a resource allocation model in the Excel spreadsheet. If you are also curious about it, download our practice workbook and follow us.

This article is part of my series: **Excel Solver Guide (Easy and Step by Step)**.

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

## What Is Resource Allocation Model In Excel?

The process of resource management, or the actions necessary to prepare, assemble, and nurture your project team, includes the step of resource allocation, and the model which states those are called the resource allocation model.

A resource allocation model specifically involves:

- Identifying the skill sets needed to finish project tasks for various projects.
- 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 your project schedule.

## Step-by-Step Procedure to Design Resource Allocation Model in Excel

To demonstrate the procedure, we consider the production process of **four** books of any publication. After completing all the steps our resource allocation model will look like the image shown below:

**📚 Note:**

All the operations in this article are accomplished by using the **Microsoft** **Office 365** application.

**Read More:** **How to Use Solver in Excel (Solving Linear Programming Problems)!**

### Step 1: Input All Required Data

In the first step, we will input all the required data associated with our production. The steps are described below:

- First of all, create a layout for inputting the dataset in the range of cells
**B4:F14**. Here, input your running projects’ names row-wise and insert the required materials list column-wise.

- Now, input all the necessary data corresponding to the production in the datasheet.

- Then, allot two rows to define the
**Unit Profit**and**Number of Production**. - Finally, input the desired amount of them for the corresponding books.

- Our first step is completed.

Thus, we can say that we have finished the first step of creating a resource allocation model in the Excel spreadsheet.

### Step 2: Estimate Amounts of Used Inventory

In this step, we will calculate the amounts of used inventories during our desired amount of production. The process is given as follows:

- At first, select cell
**H6**. - Now, write down the following formula using
**the SUMPRODUCT function**to get the used amount of the material. Make sure that you insert the**Absolute Cell Reference**sign for the range of cells**C14:F14**.

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

- Press
**Enter**.

- After that,
**drag**the**Fill Handle**icon to copy the formula upto cell**H12**.

- You will get the amounts of inventories used during the production.

Hence, we can say that we have completed the second step to create a resource allocation model in the Excel spreadsheet.

### Step 3: Calculate Remaining Inventory

Now, we are going to estimate the remaining inventories of our raw materials. The procedure is explained below:

- First, select cell
**I5**. - After that, write down the following formula into the cell.

`=G6-H6`

- Then, press
**Enter**.

- Now,
**drag**the**Fill Handle**icon to copy the formula upto cell**I12**.

- You will get the result.

Hence, we can say that we have accomplished the third step to create a resource allocation model in the Excel spreadsheet.

### Step 4: Evaluating Profit

In the following steps, we are going to calculate the profit from an individual book and the overall profit from all of them. The estimation process is shown below step-by-step:

- Firstly, define cell
**B15**as**Profit**for getting the individual profit from a book. - For that, select cell
**C15**and write down the following formula into the cell.

`=C13*C14`

- Press
**Enter**.

- Now,
**drag**the**Fill Handle**icon to your right to copy the formula upto cell**F15**.

- Afterward, to evaluate the total profit, define cell
**B16**as**Total Profit**. - Then, wrote down the following formula into the cell using
**the SUM function**.

`=SUM(C15:F15)`

- Similarly, press
**Enter**.

- You will get the total profit value.

So, we can say that we have completed the fourth step to create a resource allocation model in the Excel spreadsheet.

### Step 5: Applying Solver Analysis Toolpak for Maximum Profit

If you look at our resource allocation model, you will figure out that some of our remaining inventory values are negative. That means if we want to produce according to our desire, we have to buy extra raw materials for production.

Now, we will use the Excel built-in **Solver ToolPak** to check the maximum profit amount and corresponding book amount for every case. The steps are shown below:

- In the beginning, go to the
**Data**tab and click on the**Solver**command from the**Analysis**group. If you don’t have this data tool-pak, enable it from the**Excel Options**.

- As a result, the
**Solver Parameters**dialog box will appear. - Now, in the
**Set Objective**option, select cell**C16**. - Then, choose the
**Max**option. - After that, define the
**By Changing Variables Cells**field as**C14:F14**. - Next, we will add some constraints. You may remember that our remaining inventory value cannot be less than
**zero (0)**. To add the constraints, click on**Add**.

- Another small dialog box called
**Add Constraint**will appear. - Now, in the
**Cell Reference**field, select cell**I6**and change the condition operator**‘<=’**to**‘>=’**. - Then, in the
**Constraints**field, write down**0**. - Finally, click
**Add**.

- Similarly, add the same types of constraints for the range of cells
**I7:I12**. - After adding all the constraints, click
**OK**to close this dialog box. - You will get all the constraints in the empty list box.
- After that, check the
**Make Unconstrained Variables Non-Negative**option. - Moreover, set the
**Select a Solving Method**to**Simplex LP**. - Finally, click
**Solve**.

- You will notice the value of the range of cells
**C14:F14**will change. Besides that, the**Solver**tool-pak will show you a message in a new dialog box titled**Solver****Result**, that your calculation process is converged. - Click
**OK**to keep the value.

- Finally, we will get the value of maximum profit and the amount of production of each book to secure the profit.

At last, we can say that we have finished all the steps to create a resource allocation model in the Excel spreadsheet, and we have gotten our desired result.

## How to Do Resource Allocation for a Project

The resource allocation model for several projects is quite similar to our previously described model. To design a resource allocation model for the projects of your organization, you can follow or model.

But, you have to ensure some modification work to this model. First, define the **project’s name row-wise** at the location where we input our **book’s** **name**. Then, place the corresponding **raw materials’** names in the **column** direction. Make the rest of the modifications work according to your requirements. The model will look like the image shown below:

## How to Do Resource Management in Excel Sheet

Resource management among the running projects is a difficult task for every institution. Because predicting the maximum profit amount is a difficult job. Even sometimes, the production of individually more profitable products doesn’t return us the maximum profit due to the high necessity of raw materials with respect to other projects. To avoid this uncertain situation, we can use a resource management model. This is a pretty easy job, and every person can design a resource management model for their organization in the Excel sheet.

Moreover, you can use our Excel spreadsheet and modify the sheet according to your institution’s requirements. For creating a completely new management model, you can follow the step-by-step procedure of our resource allocation model. After completing all the steps, the resource management model will look like the image shown as follows:

## How to Create Resource Allocation Gantt Chart in Excel

We can also add a **Gantt chart** in a resource allocation to check the working period of the project. It will also help us to see the working period of the corresponding projects. As a result, we are able to rearrange resource allocation. For making it, we have to use the **Conditional Formatting** feature of Excel. The steps to create the **Gantt chart** are given as follows:

**📌 Steps: **

- First of all, write down the following formula in cell
**E4**to get the dates in the range of cells**E4:P4**using**the SEQUENCE function**.

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

- Press
**Enter**.

- Now, select the range of cells
**E5:P8**. - In the
**Home**tab, click on the**drop-down arrow**of the**Conditional Formatting**option from the**Styles**group. - Then, select the
**New Rule**option.

- As a result, a small dialog box called
**New Formatting Rule**will appear. - After that, choose the
**Use a formula to determine which cells to format**option. - Write down the following formula into the empty box using
**the AND function**.

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

- Now, click on the
**Format**option.

- As a result, another dialog box called
**Format Cells**will appear. - Then, in the
**Fill**tab, choose your desired background color. For our chart, we choose**Green, Accent 6, Lighter 60%**color. - Click
**OK**to close the**Format Cells**dialog box.

- Again, click
**OK**to close the**New Formatting Rule**dialog box.

- You will see that the chart will be ready, and the cells will show our selected color.

Thus, we can say that our formula worked perfectly, and we were able to create a resource allocation **Gantt chart** in Excel Sheer.

## Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to create a resource allocation model in Excel. Please share any further queries or recommendations with us in the comments section below if you have any other questions or suggestions.

Don’t forget to check our website, **ExcelDemy**, for several Excel-related problems and solutions. Keep learning new methods and keep growing!

**Related Articles**

**GRG Nonlinear Multistart & Excel Evolutionary Solver (Explained)!****Financial Planning with Excel Solver [2 Case Studies]****Use Excel Solver to Determine Which Projects should be Undertaken****Using Excel Solver to Determine the Optimal Product Mixtures****Optimizing an investment portfolio using Excel Solver**

I think we need one more constraint is:

$B$11:$F$11 = interger

Thank you very much!

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