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