Help with Optimization and Decision Support Modeling for Business HW4

Module 6 Practice Problems OSCM 471/571 Optimization and Decision Support Modeling for Business

1 / 15

Study Materials: Lecture Note 6: Nonlinear Programming Model

1. (Airline Ticket Pricing Model) Business travelers tend to be less price sensitive than leisure

travelers. Knowing this, airlines have discovered that extra profit can be generated by using separate pricing for these two types of customers. For example, airlines often charge more for a midweek flight (mostly business travelers) than for travel that includes a Saturday-night stay (mostly leisure travelers). Suppose an airline has estimated demand vs. price for midweek travel (mostly business travelers) and for travel that includes a Saturday-night stay (mostly leisure travelers) as shown in the table below. This flight is served by a Boeing 777 with capacity for 300 travelers. The fixed cost of operating the flight is $30,000. The variable cost per passenger (for food and fuel) is $30.

Demand Price Midweek Saturday-night Stay Total $200 150 465 615 $300 105 210 315 $400 82 127 209 $500 63 82 145 $600 49 60 109 $700 35 45 80 $800 27 37 64

a. One function that can used to estimate demand (D) as a function of price (P) is a linear

demand function, where D = a – bP. For positive values of a and b, this will give lower demand when the price is higher. However, a nonlinear demand function usually can provide a better fit to the data. For example, one such function is a constant elasticity demand function, where D = aPb. For positive values of a and negative values of b, this also will give lower demand when the price is higher. Graph the above data and use the Add Trendline feature of Excel to find the constant elasticity demand function that best fits the data in the above table for midweek demand, Saturday-night stay demand, and total demand. Answer:

Module 6 Practice Problems OSCM 471/571 Optimization and Decision Support Modeling for Business

2 / 15

b. For this part, assume that the airline charges a single price to all customers. Using the demand function for total demand determined in part a, formulate and solve a nonlinear programming model in a spreadsheet to determine what the price should be so as to achieve the highest profit for the airline. Answer: The decision to be made is how much to charge for the tickets. Therefore, define a changing cell TicketPrice (C12). Based on part a, the demand for tickets is Demand = a * (TicketPrice)b = 3,287,816 * TicketPrice-1.62. The goal is to maximize profit. The profit is based on the ticket price, variable cost, demand, and fixed cost as follows: Profit = (TicketPrice – VariableCost) * Demand – Fixed Cost. This formula is entered into Profit (C16). The Solver information and solved spreadsheet is shown below.

Module 6 Practice Problems OSCM 471/571 Optimization and Decision Support Modeling for Business

3 / 15

c. Now assume that the airline charges separate prices for midweek and Saturday-night stay tickets. Using the two demand functions for midweek and Saturday-night stay tickets determined in part a, formulate and solve a nonlinear programming spreadsheet model to determine what the prices of the two types of tickets should be so as to maximize the profit for the airline. Answer: The solution to part c is similar to part b. There are now two decisions: (1) the price for midweek and (2) the price for Saturday-night stay tickets. Based on part a, the demand for midweek tickets is Demand = 102,922* TicketPrice–1.21. The demand for Saturday-night stay tickets is 7,490,107* TicketPrice–1.83.The solved spreadsheet is shown below.

d. How much extra profit can the airline achieve by charging higher prices for midweek tickets than for Saturday-night stay tickets? Answer: With higher prices for midweek tickets, the profit is $58,457. With uniform pricing, the profit is $54,501. The extra profit with higher prices for midweek tickets is $3,956.

Module 6 Practice Problems OSCM 471/571 Optimization and Decision Support Modeling for Business

4 / 15

2. Consider the following three cases for how the profit from an activity varies with the level of the activity.

a. For each case, draw the profit graph by plotting the profits for the various levels of the activity and then drawing a smooth curve through the points by hand. Answer:

Case 1

Case 2

Case 3

$0

$5

$10

$15

$20

$25

0 1 2 3 4 5

Level of Activity

Profit

$0

$10

$20

$30

$40

$50

0 1 2 3 4 5

Level of Activity

Profit

$0

$1

$2

$3

$4

$5

$6

$7

0 1 2 3 4 5

Level of Activity

Profit

Module 6 Practice Problems OSCM 471/571 Optimization and Decision Support Modeling for Business

5 / 15

b. For each case, indicate whether the activity has decreasing marginal returns, increasing marginal returns, or neither. Answer: Case 1: decreasing marginal returns Case 2: increasing marginal returns Case 3: neither increasing nor decreasing marginal returns

c. How would your answers in part b change if the graphs plotted in part a were cost graphs instead of profit graphs? Answer: Case 1: increasing marginal returns Case 2: decreasing marginal returns Case 3: neither increasing nor decreasing marginal returns

d. For each case, use Excel’s curve fitting method to (1) obtain a nonlinear formula with a quadratic form for the profit graph and then (2) construct the graph. For any case where the activity has neither decreasing marginal returns nor increasing marginal returns, comment on how good a fit is provided by using a quadratic form. Answer:

Case 1

Case 2

y = -x2 + 10x

$0

$5

$10

$15

$20

$25

0 1 2 3 4 5

Level of Activity

Profit

y = x2 + 5x

$0

$10

$20

$30

$40

$50

0 1 2 3 4 5

Level of Activity

Profit

Module 6 Practice Problems OSCM 471/571 Optimization and Decision Support Modeling for Business

6 / 15

Case 3

The quadratic form does not seem to be a very good fit.

y = -0.1786x2 + 1.7214x + 1.5

$0

$1

$2

$3

$4

$5

$6

$7

0 1 2 3 4 5

Level of Activity

Profit

Module 6 Practice Problems OSCM 471/571 Optimization and Decision Support Modeling for Business

7 / 15

3. Jim Matthews, vice president for marketing of the J. R. Nickel Company, is planning advertising campaigns for two unrelated products. These two campaigns need to use some of the same resources. Therefore, Jim knows that his decisions on the levels of the two campaigns need to be made jointly after considering these resource constraints. In particular, letting x 1 and x 2 denote the levels of campaigns 1 and 2, respectively, these constraints are 4𝑥 + 𝑥 ≤ 20 and 𝑥 + 4𝑥 ≤ 20.

In facing these decisions, Jim is well aware that there is a point of diminishing returns when raising the level of an advertising campaign too far. At that point, the cost of additional advertising becomes larger than the increase in net revenue (excluding advertising costs) generated by the advertising. After careful analysis, he and his staff estimate that the net profit from the first product (including advertising costs) when conducting the first campaign at level x 1 would be 3𝑥 − ( 𝑥 − 1) in millions of dollars. The corresponding estimate for the second product is 3𝑥 − ( 𝑥 − 2) .

Letting P be total net profit, this analysis led to the following nonlinear programming model for determining the levels of the two advertising campaigns:

Maximize 𝑃 = 3𝑥 − (𝑥 − 1) + 3𝑥 − (𝑥 − 2) Subject to 4𝑥 + 𝑥 ≤ 20 𝑥 + 4𝑥 ≤ 20 And 𝑥 ≥ 0, 𝑥 ≥ 0 a. Construct tables to show the profit data for each product when the level of its

advertising campaign is 𝑥 = 0, 1, 2, 2.5, 3, 4, 5 (for the first product) or 𝑥 =

0, 1, 2, 3, 3.5, 4, 5 (for the second product). Answer:

Module 6 Practice Problems OSCM 471/571 Optimization and Decision Support Modeling for Business

8 / 15

Profit data for product 1:

Production

Rate

Net Profit ($millions)

Incremental Net Profit ($millions)

0 -1 — 1 3 4 2 5 2

2.5 5.25 .25 3 5 -.25 4 3 -2 5 -1 -4 x1 3x1 - (x1 -1)2

Profit data for product 2:

Production

Rate

Net Profit ($millions)

Incremental Net Profit ($millions)

0 -4 — 1 2 6 2 6 4 3 8 2

3.5 8.25 .25 4 8 -.25 5 6 -2 x2 3x2 - (x2 - 2)2

b. Use these profit data to draw rough-hand a smooth profit graph for each product.

(Note that these profit graphs start at negative values when 𝑥 = 0 or 𝑥 = 0 because the products would lose money if there is no advertising to support them.)

c. On the profit graph for the first product, draw an approximation of this profit graph by inserting a dashed-line segment between the profit at 𝑥 = 0 and 𝑥 = 2 , between the profit at 𝑥 = 2 and 𝑥 = 4, and between the profit at 𝑥 = 4 and 𝑥 = 5. Then do the same on the profit graph for the second product with 𝑥 =

0, 2, 4, 5. Answer (b&c):

Module 6 Practice Problems OSCM 471/571 Optimization and Decision Support Modeling for Business

9 / 15

The profit graph for product 1 is shown below.

The profit graph for product 2 is shown below.

d. Use separable programming with the approximation of the profit graphs obtained in

part c to formulate an approximate linear programming model on a spreadsheet for Jim Matthews’s problem. Then solve this model. What does this solution say the levels of the advertising campaigns should be? What would the total net profit from the two products be? Answer:

Production of product 1

Profit ($millions)

5

1 2 3 4 5

4

3

1

-1

2

Production of product 2

Profit ($millions)

10

1 2 3 4 5

8

6

4

2

-2

-4

Module 6 Practice Problems OSCM 471/571 Optimization and Decision Support Modeling for Business

10 / 15

Advertising campaign 1 should be at level 2 and advertising campaign 2 should be at level 4. The total net profit will be $13 million ($18 million plus the negative $5 million at x1 = 0 and x2 = 0).

e. Repeat parts c and d except using 𝑥 = 0, 2, 2.5, 3, 5 and 𝑥 = 0, 3, 3.5, 4, 5 for the

approximations of the profit graphs in part c. (These particular approximations actually lead to the exact optimal solution for Jim Matthews’s problem.) Answer: The revised approximated profit graph for product 1 is shown below.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

A B C D E F Unit Profit ($millions) Campaign 1 Campaign 2

0 to 2 3 5 2 to 4 -1 1 4 to 5 -4 -2

Total Resource Used Available

Resource 1 4 1 12 <= 20 Resource 2 1 4 18 <= 20

Campaign 1 Campaign 2 Campaign 1 Campaign 2 0 to 2 2 2 <= 2 2 2 to 4 0 2 <= 2 2 4 to 5 0 0 <= 1 1 Total 2 4

Total Profit ($millions)

18

Maximum

Resource Used per Unit

Production of product 1

Profit ($millions)

5

1 2 3 4 5

4

3

1

-1

2

Module 6 Practice Problems OSCM 471/571 Optimization and Decision Support Modeling for Business

11 / 15

The profit graph for product 2 is shown below.

The linear programming model is then as follows:

Advertising campaign for product 1 should be at level 2.5 and the advertising campaign for product 2 should be at level 3.5. The total net profit will be $13.5 million ($18.5 million plus the negative $5 million at x1 = 0 and x2 = 0).

f. Use Excel and Solver to formulate and solve the original nonlinear programming model directly. Compare with the answers obtained after completing part e. Answer:

Production of product 2

Profit ($millions)

10

1 2 3 4 5

8

6

4

2

-2

-4

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

A B C D E F G Unit Profit ($millions) Campaign 1 Campaign 2

0 to 2 3 4 0 to 3 2 to 2.5 0.5 0.5 3 to 3.5 2.5 to 3 -0.5 -0.5 3.5 to 4

3 to 5 -3 -2 4 to 5

Total Resource Used Available

Resource 1 4 1 13.5 <= 20 Resource 2 1 4 16.5 <= 20

Campaign 1 Campaign 2 Campaign 1 Campaign 2 0 to 2 2 3 <= 2 3 0 to 3

2 to 2.5 0.5 0.5 <= 0.5 0.5 3 to 3.5 2.5 to 3 0 0 <= 0.5 0.5 3.5 to 4

3 to 5 0 0 <= 2 1 4 to 5 Total 2.5 3.5

Total Profit ($millions)

18.5

Maximum

Resource Used per Unit

Module 6 Practice Problems OSCM 471/571 Optimization and Decision Support Modeling for Business

12 / 15

This solution is the same as the one in part e.

g. Use calculus to find the value of 𝑥 that maximizes 3𝑥 − (𝑥 − 1) , the net profit

from the first product. Also use calculus to find the value of x 2 that maximizes 3𝑥 −

(𝑥 − 2) , the net profit from the second product. Show that these values satisfy the constraints for the nonlinear programming model. Then compare these values with the answers obtained in parts e and f. Answer:

The derivatives of the functions equal zero at their maximum value. The derivative of 3x1 - (x1 -1)2 is 3- 2(x1 - 1)which equals zero when x1 = 2.5.

The derivative of 3x2 - (x2 - 2)2 is 3 - 2(x2 - 2) which equals zero when x2 = 3.5.

These values also satisfy the constraints: 4x1 + x2 = 4(2.5) + 3.5 =13.5 £ 20,

x1 + 4x2 = 2.5 + 4(3.5) =16.5 £ 20.

This is the same solution that was found in parts e and f.

1 2 3 4 5 6 7 8 9 10

A B C D E F Unit Profit Campaign 1 Campaign 2

Net Profit 3X1 - (X1 - 1)2 3X2 - (x2-2)2

Total Resource Used Available

Resource 1 4 1 13.5 <= 20 Resource 2 1 4 16.5 <= 20

Total Profit Campaign 1 Campaign 2 ($millions)

Level 2.5 3.5 13.50

Used Per Unit Produced

Module 6 Practice Problems OSCM 471/571 Optimization and Decision Support Modeling for Business

13 / 15

4. Consider the following nonlinear programming problem. Maximize 𝑃𝑟𝑜𝑓𝑖𝑡 = 𝑥 − 13𝑥 + 59𝑥 − 107𝑥 + 61𝑥 subject to

0 ≤ 𝑥 ≤ 5

a. Formulate this problem in a spreadsheet and then use the Nonlinear Solver and the Multistart feature to solve this problem. Answer:

b. Use Evolutionary Solver to solve this problem. Answer:

Module 6 Practice Problems OSCM 471/571 Optimization and Decision Support Modeling for Business

14 / 15

5. Reconsider the portfolio optimization problem considered in Section 8.5, where the goal was to select the portfolio that beat the market for the largest number of quarters over the last six years.

a. Use Evolutionary Solver to instead find a portfolio that did not lose money in the

largest number of quarters. Answer: Answers may vary since Evolutionary Solver is based on randomness.

b. Use Evolutionary Solver to instead find a portfolio that yielded a return of at least 10 percent for the largest number of quarters. Answer:

Module 6 Practice Problems OSCM 471/571 Optimization and Decision Support Modeling for Business

15 / 15

Answers may vary since Evolutionary Solver is based on randomness.