BUA 345 - Lecture 23

Introduction to Unconstrained Optimization

Author

Penelope Pooler Eisenbies

Published

April 7, 2025

Housekeeping

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

In-class Polling (Session ID: bua345s25)

Lecture 23 In-class Exercises - Q1

Session ID: bua345s25

Review Question from Lecture 22:

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

Session ID: bua345s25

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*} \]

Good News! Excel Solver does this algebra for you!

Setting up the Solver

Lecture 23 In-class Exercises

NOT on PointSolutions

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

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.

For Question 6 you will use the solver to determine the price that will maximize profit.

Question 6. 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/16.
Including today, there are six lectures and engagement questions remaining.

To submit an Engagement Question or Comment about material from Lecture 23: Submit it by midnight today (day of lecture).

--- title: "BUA 345 - Lecture 23" subtitle: "Introduction to Unconstrained Optimization" author: "Penelope Pooler Eisenbies" date: last-modified lightbox: true toc: true toc-depth: 3 toc-location: left toc-title: "Table of Contents" toc-expand: 1 format: html: code-line-numbers: true code-fold: true code-tools: true execute: echo: fenced --- ## Housekeeping ```{r setup, echo=FALSE, warning=F, message=F, include=F} #| include: false # this line specifies options for default options for all R Chunks knitr::opts_chunk$set(echo=F) # suppress scientific notation options(scipen=100) # install helper package that loads and installs other packages, if needed if (!require("pacman")) install.packages("pacman", repos = "http://lib.stat.cmu.edu/R/CRAN/") # install and load required packages pacman::p_load(pacman,tidyverse, magrittr, olsrr,gridExtra, knitr, viridis, png, kableExtra) # verify packages # p_loaded() ``` ### 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 ::: fragment **In-class Polling (Session ID: bua345s25)** ::: ## ### Lecture 23 In-class Exercises - Q1 ***Session ID: bua345s25*** **Review Question from Lecture 22:** :::::: columns ::: {.column width="58%"} 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)? <br> Round answer to closest whole mile. ::: ::: {.column width="2%"} ::: ::: {.column width="40%"} ![](img/l22_bmw_pic.png){fig-align="center"} ::: :::::: ## ### Review of Models from Previous Lecture ![](img/l23_review_nl_models.png){fig-align="center" height="100%"} ## ### Using Non-linear Models for Optimization ![](img/l23_optimizing.png){fig-align="center" height="100%"} ## ### Installing and Accessing the Excel Solver (Windows) ![](img/l23_Solver_Install_Steps.png){fig-align="center" height="100%"} ## :::::: columns ::: {.column width="28%"} ### Excel Solver This Excel Solver can be used for optimization queries that we will cover today and in the next lecture. ::: ::: {.column width="2%"} ::: ::: {.column width="70%"} ![](img/L23_ES6_Solver_popup.png){fig-align="center" height="100%"} ::: :::::: ## ### Optimizing BMW Fuel Economy :::::::: columns :::: {.column width="48%"} - 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: ::: fragment $$ Y = -3.0714 + 1.306X – 0.0115X^2 $$ ::: :::: ::: {.column width="2%"} ::: :::: {.column width="50%"} ![](img/l22_bmw_pic.png){fig-align="center" height="1.5in"} ::: fragment ![](img/l23_bmw_plot_w_model.png){fig-align="center" height="4in"} ::: :::: :::::::: ## ### Optimizing by Trial and Error ![](img/l23_bmw_trial_and_error.png){fig-align="center" height="100%"} ## ### Using the Excel Solver ![](img/l23_optimizing_bmw_with_solver.png){fig-align="center" height="100%"} ## ### Why use the Solver? ![](img/l23_what_solver_does.png){fig-align="center" height="100%"} ## ### A Very Different Vehicle :::::::: columns :::: {.column width="48%"} - 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. ::: fragment $$ Y = 1.58X – 0.017X^2 $$ ::: :::: ::: {.column width="2%"} ::: :::: {.column width="50%"} ![](img/l23_chevy_pics.png){fig-align="center" height="2in"} ::: fragment ![](img/l23_chevy_plot.png){fig-align="center" height="4in"} ::: :::: :::::::: ## ### Trial and Error for the Chevy Pickup ![](img/l23_chevy_trial_and_error.png){fig-align="center" height="100%"} ## ### Lecture 23 In-class Exercises Q2-Q3 :::::: columns ::: {.column width="48%"} ***Session ID: bua345s25*** **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. ::: ::: {.column width="2%"} ::: ::: {.column width="50%"} ![](img/L23_ES6_Solver_popup.png){fig-align="center" height="100%"} ::: :::::: ## ::::::::: columns ::::: {.column width="48%"} ### 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 ::: fragment $$ Y = 2486 – 15.61X $$ ::: - Also the wholesale purchase price is \$75 so profit per bottle is: ::: fragment $$ Profit = Retail Price - $75 $$ ::: ::::: ::: {.column width="2%"} ::: :::: {.column width="50%"} ![](img/l23_chanel_pic.png){fig-align="center" height="1.75in"} ::: fragment ![](img/l23_chanel_plot.png){fig-align="center" height="4in"} ::: :::: ::::::::: ## ### Using Excel to Optimize price ![](img/l23_optimizing_price_2_equations.png){fig-align="center" height="100%"} ## ### The Underlying Optimization Equation ::::::::: columns ::::: {.column width="48%"} Observed Weekly Demand: X = Retail Price (set by store) Y = Weekly Demand ::: fragment $Y = 2486 – 15.61X$ $Profit = X - \$75$ ::: <br> - Weekly Net Profit: ::: fragment $$ \begin{align*} &= Demand \times Profit \\ &= (2486 – 15.61X) \times (X – 75) \\ &= -186450 + 3656.75X -15.61X^2 \end{align*} $$ ::: - Good News! Excel Solver does this algebra for you! ::::: ::: {.column width="2%"} ::: :::: {.column width="50%"} ![](img/l23_chanel_pic.png){fig-align="center" height="1.75in"} ::: fragment ![](img/l23_chanel_polyplot.png){fig-align="center" height="4in"} ::: :::: ::::::::: ## ### Setting up the Solver ![](img/l23_optimizing_price_solver_setup.png){fig-align="center" height="100%"} ## ### Lecture 23 In-class Exercises **NOT on PointSolutions** 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 **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. <br> **For Question 6 you will use the solver to determine the price that will maximize profit.** **Question 6.** 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 ![](img/l23_a_more_complex_question.png){fig-align="center" height="100%"} ## ### 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/16. - **Including today, there are six lectures and engagement questions remaining.** ::: fragment **To submit an Engagement Question or Comment about material from Lecture 23:** Submit it by midnight today (day of lecture). :::