BUA 345 - Lecture 24
Linear and Integer Programming
Housekeeping
April 16th - Guest Speaker
Steven Davis from DLA Piper will speak for 15 minutes.
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
Lecture 24 In-class Exercises - Q1
Poll Everywhere - My User Name: penelopepoolereisenbies685
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
Poll Everywhere - My User Name: penelopepoolereisenbies685
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.
527 L Whole 20 L 2% 23 L Skim
527 L Whole 20 L 2% 13 L Skim
517 L Whole 20 L 2% 23 L Skim
522 L Whole 20 L 2% 18 L Skim
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.
Cookie Company Details and Questions
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
Cookie Supply and Demand has Expanded
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
Poll Everywhere - My User Name: penelopepoolereisenbies685
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 might ask you to evaluate a proposed solution by listing poros and cons.
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 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:
Preparation for Lecture 27 (4/21)
Together we will be creating a basick stock forecasting dashboard.
The most basic way to publish a dashboard is using Rpubs
- For class on Tuesday, 4/21, at minimum everyone will need to create a free RPubs account.
A more advanced, more professional (more time-consuming) way is using GitHub
If you want to use GitHub (OPTIONAL), I can get you started, and I will create videos and assist you with follow up questions.
Those interested in publishing on GitHub, please create free account on GitHub, and download the following to your laptop:
Key Points from Today
The examples shown here use a linear combination or equation to find the value in multiple variables that optimize Y, our objective.
Y can be
- maximized (Milk production example)
- minimized (Cookie shipping costs example)
- Set to a specific goal value
Additional constraints can be added iteratively if first solution does not meet all considerations.
Important to understand concepts and which method to use when (See previous slide).
HW 9 is due on 4/15.
Including today, there are five lectures and engagement questions remaining.
To submit an Engagement Question or Comment about material from Lecture 24: Submit it by midnight today (day of lecture).