BUA 345 - Lecture 23
Introduction to Unconstrained Optimization
Housekeeping
April 16th - Guest Speaker
Steven Davis from DLA Piper will speak for 15 minutes.
Today’s plan
Quick review of non-linear models
Installing and Using Excel Solver (Demo)
Example 1 - BMW
Example 2 - Chevy Colorado
Example 3 - Chanel
Brief example of more complex unconstrained optimization question
Lecture 23 In-class Exercises - Q1
Poll Everywhere - My User Name: penelopepoolereisenbies685
Review Question from Lecture 21:
Recall the BMW data discussed in Lecture 22. Use the Trendline option in Excel to find the polynomial model equation.
What is the estimated miles per gallon (MPG) for a BMW at an average speed of 60 miles per hour (MPH)?
Round answer to closest whole mile.
Review of Models from Previous Lecture
Using Non-linear Models for Optimization
Installing and Accessing the Excel Solver (Windows)
Excel Solver
This Excel Solver can be used for optimization queries that we will cover today and in the next lecture.
Optimizing BMW Fuel Economy
As part of new sales campaign for BMW, we want to determine the optimal speed (MPH) to maximize fuel economy (MPG) of the BMW 430i.
Recall that we have a small data set examining average fuel economy at 8 different speeds.
We have already determined that the best model to fit these data is polynomial:
\[ Y = -3.0714 + 1.306X – 0.0115X^2 \]
Optimizing by Trial and Error
Using the Excel Solver
Why use the Solver?
A Very Different Vehicle
A Chevy pickup is very different from a BMW
How different is the optimal speed for this larger, heavier, more practical vehicle?
We have determined that the best model to fit these data is polynomial model with no intercept.
\[ Y = 1.58X – 0.017X^2 \]
Trial and Error for the Chevy Pickup
Lecture 23 In-class Exercises Q2-Q3
Poll Everywhere - My User Name: penelopepoolereisenbies685
Question 2. Use the solver to find the optimal miles per hours for the Chevy Colorado that maximizes fuel economy. Round your answer to once decimal place.
\[ Y = 1.58X – 0.017X^2 \]
Question 3. A new truck model has a different fuel efficiency curve:
\[ Y = 1.9X – 0.031X^2 \]
What would be the optimal speed to maximize fuel efficiency of this model? Round your answer to once decimal place.
Optimizing Price
As the buyer for large upscale department store you observe the following linear relationship. As price increases, demand decreases:
- X = Retail Price (set by store)
- Y = Weekly Demand
\[ Y = 2486 – 15.61X \]
- Also the wholesale purchase price is $75 so profit per bottle is:
\[ Profit = Retail Price - $75 \]
Using Excel to Optimize price
The Underlying Optimization Equation
Observed Weekly Demand:
X = Retail Price (set by store)
Y = Weekly Demand
\(Y = 2486 – 15.61X\)
\(Profit = X - \$75\)
- Weekly Net Profit:
\[ \begin{align*} &= Demand \times Profit \\ &= (2486 – 15.61X) \times (X – 75) \\ &= -186450 + 3656.75X -15.61X^2 \end{align*} \]
- Excel Solver does this algebra for you!
Setting up the Solver
Lecture 23 In-class Exercises
NOT on Poll Everywhere
This question is just to check that you have setup your equations and solver correctly.
If the wholesale price is maintained at $75, what is the optimal sales price to maximize profit?
- Rounding down to a whole dollar amount, you should get $117.00
Lecture 23 In-class Exercises Q4-Q6
Poll Everywhere - My User Name: penelopepoolereisenbies685
You do not use the solver for Questions 4 and 5.
Question 4. Due to overproduction, the wholesale price of Bleu de Chanel briefly decreases to $70. Under these conditions, what will profit be if retail price is $110? Round answer to a whole dollar amount.
Question 5. Due to tariffs, the wholesale price of Bleu de Chanel increases to $80. Under these conditions, what will profit be if retail price is increased to $125? Round answer to a whole dollar amount.
Question 6. Use the solver to determine the price that will maximize profit.**
Due to tariffs, the wholesale price of Bleu de Chanel increases to $80. Under these conditions, what price will maximize profits? Round answer to a whole dollar amount.
Supply Chain Questions Can Be Complex
Key Points from Today
The optimization examples shown here use an estimated non-linear relationship to find the value in one variable (X) that maximizes the value of the other variable (Y).
These three examples simplified to polynomial equation. This method could also be used for Exponential, Power, Logarithmic, or more complex relationships.
Important to understand:
what unconstrained optimization is.
how to find X value(s) that maximize(s) Y.
what the Excel Solver can do:
Excel solver is used in Supply Chain courses
Can also be done by writing functions in R (or Python)
HW 9 is now available and due on 4/15.
To submit an Engagement Question or Comment about material from Lecture 23: Submit it by midnight today (day of lecture).