Optimization Using Solver

  • Sustaining vs. Strategic

    Optimization-based problem solving is definitely a data science (used in management science, supply chain, and operations), but it's based on an entirely different problem from the regression-based prediction problem in a prior chapter (e.g. predict who will purchase a bike). Regression-based prediction involves making an educated prediction (not an exact answer) based on historical data. On the other hand, optimization problem solving gives you an "exact" answer that is only valid to the extent that you accurately model a set of contraints. Follow along with the remainder of the chapter to learn how to make "perfect" decisions.

  • Linear Programming

    Linear programming is a powerful quantitative tool used by operations managers to solve problems in the most optimal way given constraints. What do we mean by optimal ? In a business, the overall goal is to make as much money as possible with as little waste (and as little cost) possible.

    Some common optimization problems that linear programming can solve include the following:

    1. Determining optimal production schedules (minimize cost)
    2. Determining the best set of worker-job assignments (minimize cost)
    3. Determining the optimal mix of products in a factory (maximize profit)

    Before we dive into using MS Excel Solver to solve linear programming problems, we should have an understanding of the three key components of a linear programming model:

    1. Objective Function - The thing we want to maximize or minimize.and put into Solver
    2. Decision Variables - What we change to maximize or minimize the objective fucntion
    3. Constraints - Rules placed on the decision variables that limit how they change

    Earlier, we mentioned that in a business we want to maximize profit and minimize cost. For example, suppose we have a Donut Shop called Mark's Donuts. We want to maximize our profit . Mark sells three types of donuts for a profit of $0.05, $0.30, and $0.49, respectively.

    How can we model the total profit using a mathematical expression?

    Before we attempt to model total profit as an expression, let's define some variables to represent the quantity of each different type of donuts that Mark sells.

    Looking at the table, we can see that we use H to represent the quantity of donut holes Mark sells, R to represent the quantity of regular donuts Mark sells, and S to represent the quantity of special donuts that Mark sells. These variables are referred to as our Decision Variables . We decide how many donuts of each type to make to maximize profit.

    Functional Areas

    How does this help us come up with a mathematical expression for profit? Let's ask this question: If Mark sells 10 donut holes, how much profit will he make? We get $0.05 profit for each donut hole Mark sells, so $0.05 * 10 = $0.50.

    That's our mathematical equation . Now, if we want to model that as a mathematical expression , so we can easily calculate the profit for any number of donut holes sold, how do we do that?

    Profit From Donut Holes = 0.05 * H.H represents the quantity of donut holes sold and 0.05 is the profit per donut hole.

    Now we know how to make our Objective Function! The total profit from ALL types of donuts can be modeled in the following way:

    Total Profit = 0.05 * H + 0.30 * R + 0.49 * S

    We can insert the quantities for each donut type we sell and instantly know what our total profit is.

    Let's ask a different question. We know we always want to maximize profit. What is the best way to maximize profit for Mark's Donut Shop?

    If you said make as many of each type of donut as possible, you would be correct. But let's think about that -- wouldn't it be great if we really could make INFINITY of each type of donut? Well, yes, we would have infinite profit.

    What's holding us back from maximizing in this way? Well, it takes labor to make donuts, and the donuts actually have to be sold in order to profit off of all of them. It might be hard to find people that want to work 24/7 making donuts for you as well as people that need to buy donuts constantly.

    These issues, labor and demand, are Constraints. They define the bounds of the Objective Function . Think about it -- without the Constraints, the Objective Function would be what we call in the math world Unbounded . Infinite profit. Or, in the minimization case, NEGATIVE cost!

    We have the following information about demand: (1) We expect to sell between 150 and 250 donut holes, (2) between 100 and 200 regular donuts, and (3) between 50 and 150 special donuts (inclusive).

    Let's model our demand constraints mathematically.

    150 <= H <= 250

    100 <= R <= 200

    50 <= S <= 150

    We also have the following information about labor: (1) Donut holes require 0.02 hours of labor, (2) regular donuts require 0.05 hours of labor, and (3) special donuts require 0.07 hours of labor. Also, we only have 20 hours of labor available. Let's model our labor constraints mathematically. First, how do we model the total hours required to make all three types of donuts? Similar to our objective function:

    Total Hours = 0.02 * H + 0.05 * R + 0.07 * S

    We also know, however, that our labor hours cannot exceed 20 (inclusive). Hence:

    (0.02 * H + 0.05 * R + 0.07 * S) <= 20

    Now, we have our Objective Function, our Decision Variables, and our Constraints defined. If we really want to be sticklers, we can also make constraints that say that we can't have negative quantities of donuts. For example, H >= 0 and so on. Let's put the whole LP together!

    Maximize:

    0.05 * H + 0.30 * R + 0.49 * S

    Subject to:

    150 <= H <= 250

    100 <= R <= 200

    50 <= S <= 150

    (0.02 * H + 0.05 * R + 0.07 * S) <= 20

    Now we're ready to solve the LP in Excel Solver.

  • Tutorial

    Time to try this out. Download the Excel file below and follow along with the video tutorialFollow along in the video: