Resource Allocation Model in Excel Using Solver

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

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.

Allocating Resources in Excel using Solver

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.

Allocating Resources in Excel using Solver

Solver determined how to use the resources to maximize the total profit of a company.

Download Working File

allocating-resources.xlsx

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.


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 here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

2 Comments
  1. Reply
    Van Thai Bui September 29, 2016 at 3:32 PM

    I think we need one more constraint is:
    $B$11:$F$11 = interger
    Thank you very much!

  2. Reply
    Jorge Gonzalez August 28, 2017 at 10:18 PM

    You should reference “Excel 2013 Bible” by J. Walkenbach if you are going to take the examples from that book…

    Leave a reply