Using Solver to Optimize Solutions to Costing Problems in Excel

Intro Solver LeadImagine 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.

File Options

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…

Manage Excel AddIns

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

AddIns Menu

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.

Data Solver

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:

Page Layout

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

Page Layout Formulas

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.

Data Solver

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…

Solver Parameters

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.

Solver Results

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!

Solver Page Output

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…

 

Get the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts ThumbnailAndrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

5 thoughts on “Using Solver to Optimize Solutions to Costing Problems in Excel

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

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

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

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *