Resource Allocation in Excel (Create with Quick Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

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.


Step 1: Inserting 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 Productions.
  • 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: Estimating 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.

Using SUMPRODUCT function calculating used materials in resource allocation model

  • After that, drag the Fill Handle icon to copy the formula up to 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: Calculating 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 in the cell.

=G6-H6

  • Then, press Enter.

Using formula to calculate remaining inventory

  • Now, drag the Fill Handle icon to copy the formula up to 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 in 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 up to cell F15.

  • Afterward, to evaluate the total profit, define cell B16 as the Total Profit.
  • Then, write down the following formula in 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 Toolpak, 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

Read More: How to Do Portfolio Optimization Using Excel Solver


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 an Excel sheet.

Moreover, you can use our Excel spreadsheet and modify the sheet according to your institution’s requirements. To create 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

Read More: How to Use Excel Solver for Linear Programming


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. To make 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 the 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 chose 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.

Read More: Example with Excel Solver to Minimize Cost


Download Practice Workbook

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


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.

Keep learning new methods and keep growing!


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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