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 uses of available resources with the highest output.

This article is part of my series: Excel Solver Guide (Easy and Step by Step).

Table of Contents

## 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**.

### Step 1

Fill **Set Objective** field with this value: $B$13.

### Step 2

Select the radio button of the **Max** option in **To** control.

### Step 3

Select cell $B$11 to $F$11 to fill the field **By Changing Variable Cells**. This field will show then $B$11:$F$11.

### Step 4

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.

### Step 5

Select the **Make Unconstrained Variables Non-Negative** check box.

### Step 6

Select **Simplex LP** from the **Select a Solving Method** drop-down list.

### Step 7

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.

**Read More: Minimizing shipping costs in Excel using Solver**

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 🙂

You know any other ways of **making a resource allocation model with Excel solver**? Please put your thought in the comment box.

*This example is taken from “Excel 2013 Bible” by J. Walkenbach.*

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…