Housekeeping

Today’s plan 📋

  • What is Linear Programming

  • Installing and Using Excel Solver (Review Demo)

  • Example 1 – Milk Production

  • One more complication – Integers (countable things)

  • Example 2 – Shipping Cookies

  • What you should know

In-class Polling (Session ID: bua345s25)

💥 Lecture 24 In-class Exercises - Q1 💥

Session ID: bua345s25

Review Question from Lecture 23:

Recall the Bleu de Chanel data from Lecture 23. A store executive worries that sales are slow and wants to lower the price to $95. If the model shown in the plot is correct (wholesale price is $75), what do you tell them?


  • Weekly Net Profit:

\[ \begin{align*} &= Demand \times Profit \\ &= (2486 – 15.61X) \times (X – 75) \\ &= -186450 + 3656.75X -15.61X^2 \end{align*} \]

Installing and Accessing the Excel Solver (Windows)

Excel Solver

This Excel Solver can be used for optimization queries that were covered in the previous lecture and today’s lecture.


Students will also use the solver in Supply Chain coursework.

Example 1 – Milk Production

  • Your farm in Upstate NY owns two herds of cows:

    • One herd of Holstein cows
    • One herd of Ayrshire cows

  • The two herds differ with respect to how many liters per day each herd can produce and the percent Milk-Fat in the herd’s milk.

  • Here are the production and milk-fat percentage data by herd:

NY Milk Production

Optimization - Step 1

Optimization - Step 2 - Option 1

Optimization - Step 2 - Option 2

Optimization - Step 3 - Setting Up the Solver

Optimization - Step 4 - Specifying Constraints

Optimization - Step 4 - Specifying Final Options

Optimization - Step 5 - A Possible Solution

Optimization - Step 6 - Adding One More Constraint

💥 Lecture 24 In-class Exercises - Q2 💥

In this example there is more than one optimal solution.

Excel seemed to pick the simplest one until we add an additional constraint.

What if our farm’s retail outlets requests 20 Liters of 2% Milk?

Which solutions below meet the criteria of 560 total Liters, 21.28 Liters of milk-fat and matches our optimal profit of $177.52?

Use the Excel Solver and update the relevant constriant to determine the correct choice.


A. 527 L Whole 20 L 2% 23 L Skim

B. 527 L Whole 20 L 2% 13 L Skim

C. 517 L Whole 20 L 2% 23 L Skim

D. 522 L Whole 20 L 2% 18 L Skim

E. 520 L Whole 20 L 2% 20 L Skim

Example 2 - Cookies - Linear Integer Programming

  • In the previous example, our product calculations were for milk, a liquid.

  • Milk production estimates were continuous and can be decimal values.

  • In contrast, some products like cookies, bagels, cars, etc., are DISCRETE.

  • No one wants to buy half a cookie or half a package of cookies.

  • This example describes an upstate NY cookie company with production plants in two locations:

    • Syracuse, NY
    • Albany NY

  • The company distributes their holiday cookies to stores in three locations:

    • New York, NY (New York City)
    • Boston, MA
    • Philadelphia, PA

  • The company wants to know how many cookies they should ship from each plant to each store location to MINIMIZE shipping costs.

Costs, Supply, and Demand

Cookies - Examining the Geography Costs

Cookies - Setting objective Cells - Part 1

Cookies - Setting objective Cells - Part 2

Cookies - Objectives and Variables in the Solver

Cookies - Specifiying Constraints in the Solver

Cookies - Examine the Solution

In the cookie example, production has increased at the Albany plant and so has demand in New York and Boston.

The Solver solution for these updated Supply and Demand constraints can be found below and in the supplementary Excel file.

💥 Lecture 24 In-class Exercises - Q3 💥

Based on what you know of the geography, do you think this is the only correct solution or might it be possible to constrain the solution further to minimize transportation distances?

What constraint might you suggest to minimize transportation distances?

As an interactive lecture question, any coherent answer with correct spelling, punctuation, and grammar will receive full credit.

On the Final Exam, I will expect a coherent sentence that demonstrates understanding.

Other ways to Solve These Equations

  • Linear equations and constraints can be written as lines of code in R or Python.

    • In R you can use the nloptr package.

  • The costs and the supply and demand values can be specified as inputs into the function in R.

  • Output values would be the same as shown in Excel.

  • Linear equations and constraints can be written as lines of code in R or Python.

    • In R you can use the nloptr package.

  • The costs and the supply and demand values can be specified as inputs into the function in R.

  • Output values would be the same as shown in Excel.

What You Should Know from This Week’s Lectures

  • What is linear programming (optimization) as opposed to non-linear optimization.

  • What is meant by a constraint and how to describe each constraint in a sentence.

    • Describing constraints and equations is an important communication skill; also helpful for coding.

  • How to use the Excel Solver (HW 9 and HW 10 will include solver questions).

  • Which solver method is appropriate for which problem:

    • Non-linear smooth relationship (Lecture 23): Use GRG Nonlinear
    • One (or more) linear relationships or equations (Lecture 24): Use LP Simplex
    • Non-smooth or less well-defined relationships (Supply Chain course): Use Evolutionary

  • From Excel Documentation: