Imagine you’re faced with a purchasing problem… You need to buy some inventory that comes in multiple sizes. Bigger sizes come at a discount, which can save you money. Buy too much, it will be wasted, and that won’t save you money at all. This is a problem that would be very challenging to solve with basic IF statements and arithmetic formulas. Fortunately, Excel has an official Plug-in called Solver that can do amazing things to solve optimization problems like this. Let’s walk through an introduction to Solver and work through an example…

The Problem

In this scenario, medicine is sold in 3 different size vials: 50mg, 150mg, and 450mg. The bigger sizes come at a discount price to the smaller ones. The prices are $6.41, $11.80, and $26.75, respectively. The challenge is to build a spreadsheet that will calculate the lowest cost combination of medicine vials, based on what a patient needs.

Installing Solver

Before we can start working on this problem, we need to make sure Solver is installed in Excel.

Click on the File menu and click on the Options button.

Choose the Add-ins section on the left, and go down to the Manage: drop-down menu. Make sure Excel Add-ins is selected and click Go…

In the Add-Ins dialog box that opens, check the box next to Solver and click OK.

To make sure that it has installed properly, look in the Data menu tab at the far right and find the Solver button. If it is there, we’re ready to build our spreadsheet.

Laying Out the Problem

We need to lay out the medicine vial options with a table of information. It needs to show all the variables of the problem:

• Dose of medicine in each vial
• Price of each vial
• Quantity of vials for each size
• Total Dose for each size (Dose x Quantity)
• Cost for each size (Price x Quantity)
• Totals for Dose and Cost
• Amount of medicine needed by the Patient

A sample layout is below:

The formulas for the Dose and Cost columns, as well as the Totals are as follows:

Setting Up Solver

Now that we have the basic layout designed, we can open Solver. Click on the Data menu tab and click on Solver on the far right.

The Solver Parameters dialog box will open up. This is Command Central for the Solver plug-in, where it accepts all the settings it needs to work through the problem. We’ll walk through the setting step-by-step…

Set Objective is where you input the number you are trying to optimize. In our case we are trying to optimize the Total Cost so we select cell E5.

To: is where we specify whether we want to Maximize the objective, Minimize the objective, or make it as close as possible to the Value Of the input. In our case, we are trying to Min the objective.

By Changing Variable Cells: is where we can specify the conditions that we want to change to reach our objective. In this case, the cells we need to change are the Quantity cells in C2:C4.

Subject to the Constraints: is where we set the rules of the game. You can specify operands like “=” (equal to), “>=” (greater than or equal to), etc. You can also specify that a number must be an integer or a binary. In this case, we  need two conditions. The first is that our Quantity values must be integers (we can’t buy half of a vial). The second is that the Total Dose (D5) must be greater than (or equal to) the dose the patient needs (E7).

Make Unconstrained Variables Non-Negative makes sure that variables like Quantity don’t go negative when Solver tries to find a solution. A solution of -3 50mg vials and 1 150mg vial might be cheaper (negative, actually), but it’s not an option at most stores I shop at. Make sure this option is checked.

Select a Solving Method: is where we choose the technique that Excel uses to solve the problem. When the solution working with integers (like our positive, integer vial quantities), Simplex LP is the best, fastest way to solve problems.

Running Solver and Checking the Output

Double-check that all the settings match the snapshot above, and then click Solve.

Solver will run through many combinations of numbers for the quantities of vials until it finds the optimal combination. When it does, it will make bell sound and bring up a Solver Results dialog box.

The key phrase you are looking for is:

Solver has found a solution. All Constraints and optimality conditions are satisfied.

If all is well, choose Keep Solver Solution and click OK.

Solver will change the quantities of vials to suit the solution and show you the final cost for the combination!

It is possible to build much more complex scenarios that Solver can work through. It is a great way to approach problems with many variables that would be difficult or time-consuming to solve by hand or with formulas in Excel. Look for more Solver tutorials in the future!

To Download the Sample Worksheet, Click Here…