Resource Allocation in Excel (Create with Quick Steps)

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


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:

resource allocation excel

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

Insert necessary data in dataset

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

Input All Required Data to Create Resource Allocation Model

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

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

  • Press Enter.

Using SUMPRODUCT function calculating used materials in resource allocation model

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

Estimate Amounts of Used Inventory to design resource allocation in Excel

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.

Using formula to calculate remaining inventory

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

  • You will get the result.

Calculate Remaining Inventory in Resource Allocation Model

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.

Evaluating Individual Project Profit in Resource Allocation File

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

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

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

Launching Solver ToolPak to Get Max Profit

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

Setting suitable properties to get the maximum profit in Excel

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

Adding suitable constraints in resource allocation model for max profit

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

All required options for getting value in resource allocation sheet

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

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

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:

Resource Allocation for a Project in Excel


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:

Resource Management in Excel Sheet


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.

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

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

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

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

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

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

Create Resource Allocation Gantt Chart in Excel

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

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

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

ExcelDemy
Logo