In this article, you will learn how to build a resource allocation model in Excel using the solver. Our motto is always making the best use of available resources with the highest output.
This article is part of my series: Excel Solver Guide (Easy and Step by Step).
Allocating Resources in Excel using Solver
We’re going to solve a problem that is ideal for Solver. Ideal problems for Solver involve optimizing the volumes of individual production units that depend on varying amounts of fixed resources. The following figure shows a simplified example of a toy company.
This toy company makes five different toys. These toys use six different materials in varying amounts. For example, Toy E uses 3 units of red paint, 0 unit of blue paint, 2 units of white paint, 1 unit of plastic, and 5 units of wood, and 3 units of glue. Column G shows the current inventory of each type of material. Row 10 shows the unit profit for each toy.
The number of toys to make is shown in the cell range B11: F11. These are the values that Solver will determine. These are changing cells. The goal of this example is to determine how to allocate the resources to maximize the total profit (cell B13). In other words, Solver determines how many units of each toy to make to maximize profit. The constraints in this example are relatively simple:
- The company has to ensure that production doesn’t use more resources that are available. This can be achieved by specifying that each cell in column I is greater than or equal to zero. $I$4>=0, $I$5>=0, $I$6>=0, $I$7>=0, $I$8>=0, $I$9>=0
- The company also must ensure that the quantities produced aren’t negative. This can be accomplished by specifying the Make Unconstrained Variables Non-Negative option.
Use the following steps to set up the Solver Parameters dialog box. This dialog box appears when you choose Data ➪ Analysis ➪ Solver.
Fill Set Objective field with this value: $B$13.
Select the radio button of the Max option in To control.
Select cell $B$11 to $F$11 to fill the field By Changing Variable Cells. This field will show then $B$11:$F$11.
Add constraints one by one. The constraints are: $I$4>=0, $I$5>=0, $I$6>=0, $I$7>=0, $I$8>=0, $I$9>=0. These constraints will be shown in the Subject to the Constraints field. Click this article to know more about adding constraints.
Select the Make Unconstrained Variables Non-Negative check box.
Select Simplex LP from the Select a Solving Method drop-down list.
Now click the Solve button. You will find the Solver Results dialog box shown in the following figure. Click OK, you are done with your result.
The following figure shows the results that are produced by Solver. It shows the product mix that generates $12,365 in profit and uses all resources in their entirety, except for glue.
Download Working File
Happy Excelling 🙂
Do you know any other ways of making a resource allocation model with an Excel solver? Please put your thought in the comment box.
This example is taken from “Excel 2013 Bible” by J. Walkenbach.