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.
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:
All the operations in this article are accomplished by using the Microsoft Office 365 application.
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.
Read More: How to Create Financial Planning Calculator in Excel
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.
- 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.
Read More: How to Calculate Optimal Product Mix in Excel (with Easy Steps)
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.
- 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.
Read More: Example with Excel Solver to Minimize Cost
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.
- 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.
- 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.
Read More: Some Practical Examples with Excel Solver
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.
Read More: How to Use Solver in Excel (with Detailed Steps)
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:
Read More: How to Use Excel Solver to Determine Which Projects Should Be Undertaken
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:
- 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.
- 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.
- 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.
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!
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…