BUA 345 - Lecture 24
Linear and Integer Programming
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.
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