Quick Navigation

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 **Max**imize the objective, **Min**imize 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…

Thanks so much for this. Do you have a solution for being able to run this same solver equation over multiple rows (multiple objective cells) ?

Hi Kyla!

I’m glad this was able to help! Multiple objectives aren’t possible with Solver using this method, but there is a way to cheat… If you’re trying to minimize all the costs, you can minimize the SUM of each patient’s costs and get multiple optimizations that way. Take a look at the attached image to see how I set up an example. You might be able to build one that is more streamlined to include additional rows. Let me know what you come up with!

Good luck,

Andrew

Hi Andrew,

Thanks for the tutorial I am beginning to understand it at this basic level. However, the problem I wish to solve is to identify to lowest OVERALL cost for a tendering exercise consisting of say 50 contracts, where bids are received from multiple bidders. Some bidders will bid for all contracts some for just a few, some bidders can accept all contracts they have bid for and some will have a capacity constraint and can only accept say 8 contracts out of the 15 they have bid for. Oh, and some bidders will offer discounts if they win a certain number of contracts.

Can the lowest overall cost be calculated with Solver or is the problem too complex? There are a lot of variables, and believe me when trying to work through this makes ones head hurt!

Kind regards,

Mark

Hi Mark

Good morning.

Actually I am now facing this very problem that you faced couple of years back. Did you manage to solve it in excel solver?

Grateful if you could let me know.

Kind regards.

Anil Jhuboo

From Mauritius

I use the SUP function in excel 2013 it was working well but at the end of the table is no longer give me the results, the cell still empty.

i cant download the sample file

Thanks!

It worked perfectly for me.

Hi

I’m trying to calculate the cost of the man-hours for service rendered. I have tried by converting the man-hours to man-days and calculating the cost using this formula ((D7-INT(D7))*24)*$960, I get the answer.

$960 is man-day cost which is $120*8.

Would you be able to help me by advising which formula to use if I want to calculate the operational cost without having to convert the man-hours to man-days?