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

**Read More: **How to Do Portfolio Optimization Using Excel Solver

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

**Read More: **How to Use Excel Solver for Linear Programming

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

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

**Read More: **Example with Excel Solver to Minimize Cost

**Download Practice Workbook**

Download the practice workbook.

## Related Articles

- How to Use Excel Solver to Rate Sports Team
- How to Use Excel Solver to Determine Which Projects Should Be Undertaken?
- Solving Sequencing Problems Using Excel Solver Solution
- Solving Transportation or Distribution Problems Using Excel Solver
- How to Assign Work Using Evolutionary Solver in Excel
- Solving Equations in Excel

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

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…