15.071 | Spring 2017 | Graduate

The Analytics Edge

9 Integer Optimization

9.1 Welcome to Unit 9

9.2 Sports Scheduling: An Introduction to Integer Optimization

9.3 eHarmony: Maximizing the Probability of Love

9.4 Operating Room Scheduling: Making Hospitals Run Smoothly (Recitation)

9.5 Assignment 9

Back: 8.5 Assignment 8

Video 1: The Goal of eHarmony

The slides from all videos in this lecture can be downloaded here: Maximizing the Probability of Love (PDF).

Even’ Star Organic Farm Revisited

Last week in the “Even’ Star Organic Farm” optional homework problem, we formulated and solved the problem faced by Brett Grohsgal, the founder of the organic farm in southern Maryland. This week, we’ll use integer optimization to improve the formulation and model some new decisions faced by Brett. We’ll be using the spreadsheet EvenStarFarmRevisited (ODS) for LibreOffice or OpenOffice, and EvenStarFarmRevisited (XLSX) for Microsoft Excel.

Problem 1.1 - Adjusting the Formulation

Last week, we saw that Brett has to pay an entry fee for each channel that he participates in. He could instead choose to not participate in a certain channel, and therefore not have to pay the entry fee. This week, we’ll model this choice using binary variables to see if we can increase Brett’s profit.

Add three new decision variables to your model: one for whether or not Brett participates in the restaurant channel, one for whether or not he participates in the farmers’ market channel, and one for whether or not he participates in the CSA channel. (HINT: It will be easy to input your model into Solver if you add these decision variables in the row right below the current decision variable table.)

If Brett chooses not to participate in a channel, two things need to change in our model: (1) he does not have to pay the fixed cost to enter that channel, and (2) he can’t sell any cases in that channel.

To remove the fixed cost, take a look at the objective function equation. You should see three terms each subtracting the fixed cost value from the total profit for one of the channels. Multiply each of these terms by the corresponding binary variable you just defined for that channel. Now, if the binary variable is equal to 1, Brett pays the fixed cost, but if the binary variable is equal to 0, Brett does not pay the fixed cost.

Problem 1.4 - Adjusting the Formulation

How many total cases of produce does Brett sell in the restaurant channel?

Exercise 4

Numerical Response

Explanation

The sum of the decision variable values for the restaurant channel is 897, which is the total number of cases sold through this channel.

Answers will be marked correct within a 1% tolerance.

Problem 2.1 - Sensitivity Analysis

To maximize his profit, we saw in the optimal solution that Brett should not enter the Farmers’ Market channel. However, Brett feels that having a booth at the farmers’ market increases his visibility in the community, and is important to his business. Which of the following actions could he take to try to make the farmers’ market channel more profitable so that it is worth re-entering? Select all that apply.

Exercise 5

He could increase his prices.

He could reduce the entry cost.

He could reduce the variable costs.

He could buy a bigger truck to increase the number of cases he can sell.

Explanation

Increasing his prices and/or reducing the entry cost for the farmers’ market channel will increase his profits. Reducing the variables costs will not help since they are currently zero, and buying a bigger truck will not help since he currently does not even want to use the truck he has.

Problem 2.2 - Sensitivity Analysis

In LibreOffice, which of the following adjustments makes Brett enter the farmers’ market channel in the optimal solution? Select all that apply.

Exercise 6

Increasing his prices at the farmers’ market by 10%

Increasing his prices at the farmers’ market by 25%

Reducing his entry cost to $5,000.00 Reducing his entry cost to$3,000.00

Explanation

If you increase the prices at the farmers’ market by 10% and resolve the model, the solution does not change. However, if you increase the prices by 25%, the solution changes and it becomes profitable to enter the farmers’ market channel. Reducing the entry cost to either $5,000 or$3,000 does not change the solution.

Problem 2.3 - Sensitivity Analysis

Suppose that Brett finds it easier to increase his prices than to reduce his entry cost, so he decides to increase his prices in the farmers’ market by 25%. Make this adjustment in LibreOffice, and re-solve the model (remember to change any other values back to their original values if you have adjusted them to answer any previous questions).

What is the objective value in the new solution?

Exercise 7

Numerical Response

Explanation

After increasing the prices at the farmers’ market and resolving the model, the objective value changes to \$54,726.68.

Problem 2.4 - Sensitivity Analysis

Which types of produce does he sell at the farmers’ market? Select all that apply.

Exercise 8

Tomatoes (large)

Tomatoes (small)

Watermelon

Okra

Basil

Cucumbers

Sweet Potatoes

Winter Squash

Explanation

At the farmers’ market, Brett should sell large tomatoes, sweet potatoes, and winter squash.

Problem 2.5 - Sensitivity Analysis

Which channels does Brett enter now? Select all that apply.

Exercise 9

Restaurants

CSA

Farmers’ Market

Explanation

The binary variables for CSA and the farmers’ market are equal to 1, so he enters these two channels.

Problem 2.6 - Sensitivity Analysis

The decision variables for the number of cases can take fractional values. It seems impractical for Brett to sell a fractional number of cases of any produce at the farmers’ market, and he would prefer to always sell an integer number of cases. Restrict the cases decision variables to be integer, and resolve the model. Does the objective value change?

Note that you might need to set the time limit in Solver for this problem. Remember that you can set the time limit by opening up the Solver, and then clicking on Options. If you are using Excel, you want to set “Max Time” to 100. If you are using OpenOffice or LibreOffice, you want to check that “Solving time limit” says 100. If not, click on it and hit “Edit”. Change it to 100 and click Okay.

Exercise 10

Yes

No

Explanation

No, the objective value does not change. Note that your solution might have changed slightly, so Brett might have had to make some adjustments to his case allocation.

In many situations, the decision variables should take integer values, but we often don’t need to add the integer restriction (the solution gives integer values naturally). If this is the case, it is better to not include the integer restriction, since it is a simpler model. However, in some cases, like this one, we needed to add the integer restriction to our model.

Acknowledgements: This problem is based on the case study “Introducing Integer Modeling with Excel Solver” by Dessislava Pachamanova, INFORMS Transactions on Education 7(1), p.88-98, 2006.

Course Info

Spring 2017
Learning Resource Types
Lecture Videos
Lecture Notes
Problem Sets with Solutions