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:

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.

Use the Solver ToolPak to check the maximum profit amount and the corresponding book amount.

  • 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

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.

Resource Management in Excel Sheet

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:

=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

 

Read More: Example with Excel Solver to Minimize Cost


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

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…

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo