Wine in Your Knapsack? Jon M. Conrad, Miguel I. Gomez and Alberto J. - - PowerPoint PPT Presentation

Wine in Your Knapsack?

Jon M. Conrad, Miguel I. Gomez and Alberto J. Lamadrid

5th Annual Conference American Association of Wine Economists Free University of Bozen-Bolzano, Bolzano, June 23rd 2011

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 1 / 28

Outline

1

Motivation

2

Problem Formulation Background Objective Multiple Solutions Different Cultivars Multiple cultivars, multiple solutions

3

Data Descriptive Detailed information

4

Results No multiple Purchases Multiple purchases

5

Conclusions

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 2 / 28

Motivation

Outline

1

Motivation

2

Problem Formulation Background Objective Multiple Solutions Different Cultivars Multiple cultivars, multiple solutions

3

Data Descriptive Detailed information

4

Results No multiple Purchases Multiple purchases

5

Conclusions

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 3 / 28

Motivation

Motivation

How do you optimally build a wine cellar?

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 4 / 28

Problem Formulation

Outline

1

Motivation

2

Problem Formulation Background Objective Multiple Solutions Different Cultivars Multiple cultivars, multiple solutions

3

Data Descriptive Detailed information

4

Results No multiple Purchases Multiple purchases

5

Conclusions

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 5 / 28

Problem Formulation Background

Background

Prototype OR problem max

Xj u = n

• j=1

uj × Xj Subject to

n

• j=1

wj × Xj ≤ c Xj ∈ {0, 1}, j = 1, 2, . . . , n Operation, and changes, in electrical systems

Prototype binary problem [Martello and Toth.(1990)]. NP-hard problem [Kellerer, Pferschy, and Pisinger(2004)]. Computing solution using Mixed Integer Programming (MIP) [Srisuwannapa and Charnsethikul(2007)].

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 6 / 28

Problem Formulation Objective

The Wine Selection Problems

Open selection problem max

Xj r = n

• j=1

rj × Xj Subject to

n

• j=1

pj × Xj ≤ B Xj ∈ {0, 1}, j = 1, 2, . . . , n = 64 (1) Adding quantity constraints, e.g. n

j=1 Xj = 30

max

Xj r = n

• j=1

rj × Xj Subject to

n

• j=1

pj × Xj ≤ B

n

• j=1

Xj = 30 (2)

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 7 / 28

Problem Formulation Objective

The Wine Selection Problems

Open selection problem max

Xj r = n

• j=1

rj × Xj Subject to

n

• j=1

pj × Xj ≤ B Xj ∈ {0, 1}, j = 1, 2, . . . , n = 64 (1) Adding quantity constraints, e.g. n

j=1 Xj = 30

max

Xj r = n

• j=1

rj × Xj Subject to

n

• j=1

pj × Xj ≤ B

n

• j=1

Xj = 30 (2)

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 7 / 28

Problem Formulation Objective

The Wine Selection Problems

Open selection problem max

Xj r = n

• j=1

rj × Xj Subject to

n

• j=1

pj × Xj ≤ B Xj ∈ {0, 1}, j = 1, 2, . . . , n = 64 (1) Adding quantity constraints, e.g. n

j=1 Xj = 30

max

Xj r = n

• j=1

rj × Xj Subject to

n

• j=1

pj × Xj ≤ B

n

• j=1

Xj = 30 (2)

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 7 / 28

Problem Formulation Objective

The Wine Selection Problems

Open selection problem max

Xj r = n

• j=1

rj × Xj Subject to

n

• j=1

pj × Xj ≤ B Xj ∈ {0, 1}, j = 1, 2, . . . , n = 64 (1) Adding quantity constraints, e.g. n

j=1 Xj = 30

max

Xj r = n

• j=1

rj × Xj Subject to

n

• j=1

pj × Xj ≤ B

n

• j=1

Xj = 30 (2)

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 7 / 28

Problem Formulation Multiple Solutions

Second Stage Solution

If multiple solutions, run a second stage. min

Xj E = n

• j=1

pj × Xj Subject to

n

• j=1

rj × Xj ≥ r ∗

n

• j=1

Xj = 30 Xj ∈ {0, 1}, j = 1, 2, . . . , n = 64 (3)

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 8 / 28

Problem Formulation Different Cultivars

Extending to different cultivars

Choosing more than one wine cultivar (e.g. Cabernet Sauvignon, Pinot, Zinfandel) max

Xj r = m

• k=1

nk

• j=1

rj,k × Xj,k Subject to

m

• k=1

nk

• j=1

pj,k × Xj,k ≤ B

nk

• j=1

Xj,k ≥ Ck = 10 Xj,k ∈ {0, 1}, j = 1, 2, . . . , nk, k = 1, 2, 3 = m (4)

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 9 / 28

Problem Formulation Multiple cultivars, multiple solutions

Multiple solutions, multiple cultivars

Run a second stage for the case of multiple solutions. min

Xj E = m

• k=1

nk

• j=1

pj,k × Xj,k Subject to

m

• k=1

nk

• j=1

rj,k × Xj,k ≥ r ∗

nk

• j=1

Xj,k ≥ Ck = 10, Xj,k ∈ {0, 1}, j = 1, 2, . . . , nk, k = 1, 2, 3 = m (5)

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 10 / 28

Problem Formulation Multiple cultivars, multiple solutions

Finding all solutions

The pseudo-algorithm to identify multiple solutions is as follows

1: i ← 1; 2: Do first run without constraints on past solutions. 3: i ← i + 1; 4: repeat 5:

Create dynamic set to store solutions (cont). This is implemented as a matrix with increasing number of columns in each iteration (dimensions P.c × cont, where P.c is the number of solutions for all classes, cont is the number of solutions found -initially 1).

6:

Store first solution in the first column of the matrix. The implementation places each integer solution per class as a stacked column vector.

7:

for all consecutive runs, i > 1 do

8:

add a constraint such that the squared difference between each element of the new solution and each element of all past solutions is greater than or equal to one. This is equivalent to state that there must be at least one position of the integer solution vector (1’s and 0’s) that differs.

9:

Store the new solution found in the set of past solutions (cont)

10:

i ← i + 1;

11:

end for

12: until i = Niter, run-time specified by user.

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 11 / 28

Data

Outline

1

Motivation

2

Problem Formulation Background Objective Multiple Solutions Different Cultivars Multiple cultivars, multiple solutions

3

Data Descriptive Detailed information

4

Results No multiple Purchases Multiple purchases

5

Conclusions

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 12 / 28

Data Descriptive

Descriptive prices and ranks for the wines

Example Wide range of variances for prices...

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 13 / 28

Data Detailed information

Some detail on information

General descriptives Cabernet Pinot Zinfandel Overall Average of rank 90.71 88.60 90.34 89.99 Average of price 106.41 73.23 44.11 84.14

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 14 / 28

Data Detailed information

... And the further detail

Type Rank Cabernet Pinot Zinfandel Grand Total 84.5 Average Price ($) 58.86 47.11 32.11 49.23 Max Price ($) 220.00 95.00 55.00 220.00 Min Price ($) 10.00 19.00 18.00 10.00 92.5 Average Price ($) 102.83 86.34 48.22 86.90 Max Price ($) 484.50 370.00 140.00 484.50 Min Price ($) 27.00 25.00 24.00 24.00 97.5 Average Price ($) 350.70 411.25

• 360.79

Max Price ($) 830.00 490.00

• 830.00

Min Price ($) 75.00 332.50

• 75.00

98 Average Price ($)

• 75.00

75.00 Max Price ($)

• 75.00

75.00 Min Price ($)

• 75.00

75.00 Average Price across all ranks ($) 106.41 73.23 44.11 84.14 Max Price across all ranks ($) 830.00 490.00 140.00 830.00 Min Price across all ranks ($) 10.00 19.00 18.00 10.00 Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 15 / 28

Results

Outline

1

Motivation

2

Problem Formulation Background Objective Multiple Solutions Different Cultivars Multiple cultivars, multiple solutions

3

Data Descriptive Detailed information

4

Results No multiple Purchases Multiple purchases

5

Conclusions

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 16 / 28

Results No multiple Purchases

Solution, Single cultivar (2003 Zinfandel)

Five solutions found. Sum of rankings r ∗ = 2, 896 32 bottles selected. Solutions differ from one another by one bottle of equivalent price and rating. 31 wines in common for all solutions. Changes in: Hartford Vineyard Zinfandel, Hartford’s Fanucchi Wood Road Vineyard, Hartford’s Dina’s Vineyard, Robert Biale’s Monte Rosso and Robert Biale’s Aldo’ Vineyard.

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 17 / 28

Results No multiple Purchases

Description of solution to (5)

Type Rank Cabernet Pinot Zinfandel Grand Total 84.5 Number 5.00 7.00 8.00 20.00 Min Price ($) 10.00 19.00 18.00 10.00 Average Price ($) 20.80 26.57 25.13 24.55 Max Price ($) 27.00 30.00 28.00 30.00 Expenditure ($) 104.00 186.00 201.00 491.00

• Exp. Percentage

50.00 70.00 47.06 54.05 92.5 Number 5.00 3.00 9.00 17.00 Min Price ($) 27.00 25.00 24.00 24.00 Average Price ($) 31.60 29.00 29.33 29.94

• Max. Price ($)

36.00 32.00 33.00 36.00 Expenditure ($) 158.00 87.00 264.00 509.00

• Exp. Percentage

50.00 30.00 52.94 45.95 Total Number 10.00 10.00 17.00 37.00 Total Min Price ($) 10.00 19.00 18.00 10.00 Total Average Price ($) 26.20 27.30 27.35 27.03 Total Max Price ($) 36.00 32.00 33.00 36.00 Total Expenditure ($) 262.00 273.00 465.00 1000.00 Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 18 / 28

Results No multiple Purchases

Graphic Characteristics of Solution 5

10 20 30 40 50 60 70 80

Number Min Price ($) Average Price ($) Max Price ($) Exp. Percentage Number Min Price ($) Average Price ($) Max Price ($) Exp. Percentage Total Number Total Min Price ($) Total Average Price ($) Total Max Price ($) 84.5 92.5 all

Cabernet Pinot Zinfandel Grand Total Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 19 / 28

Results No multiple Purchases

Solution without quantity constraints

type r Cabernet Pinot Zinfandel Grand Total 84.5 Number 5.00 6.00 9.00 20.00 Min Price ($) 10.00 19.00 18.00 10.00 Average Price ($) 20.80 26.00 25.56 24.50 Max Price ($) 27.00 29.00 29.00 29.00 Expenditure ($) 104.00 156.00 230.00 490.00

• Exp. Percentage

62.50 66.67 45.00 54.05 92.5 Number 3.00 3.00 11.00 17.00 Min Price ($) 27.00 25.00 24.00 24.00 Average Price ($) 29.00 29.00 30.09 29.71

• Max. Price ($)

33.00 32.00 34.00 34.00 Expenditure ($) 87.00 87.00 331.00 505.00

• Exp. Percentage

37.50 33.33 55.00 45.95 Total Number 8.00 9.00 20.00 37.00 Total Min Price ($) 10.00 19.00 18.00 10.00 Total Average Price ($) 23.88 27.00 28.05 26.89 Total Max Price ($) 33.00 32.00 34.00 34.00 Total Expenditure ($) 191.00 243.00 561.00 995.00 Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 20 / 28

Results No multiple Purchases

Graphic Characteristics of Solution 5, No restrictions

10 20 30 40 50 60 70 80

Number Min Price ($) Average Price ($) Max Price ($) Exp. Percentage Number Min Price ($) Average Price ($) Max Price ($) Exp. Percentage Total Number Total Min Price ($) Total Average Price ($) Total Max Price ($) 84.5 92.5 all

Cabernet Pinot Zinfandel Grand Total Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 21 / 28

Results Multiple purchases

Allow for multiple purchases of the same bottle

max

bj

u =

n

• j=1

rj × bj Subject to

n

• j=1

pj × bj ≤ B bj = 0, 1, . . . , bj,max (6)

Transform rank to utility of each bottle:

1

A power function with a given preference parameter: u(bj) = (rj ∗ bj)γ, 0 ≤ γ ≤ 1 For γ = 1, utility is linear in rating.

2

A logarithmic transformation with standardization for repeated purchases of wine j: u(bj) = αjlog(bj × rj 100 + 1)

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 22 / 28

Results Multiple purchases

Marginal utility for multiple purchases

78 80 82 84 86 88 90 92 94 96 98

1st 2nd 3rd

Marginal Rating Bottle of Wine

Marginal Utility

t1 t2 t3

Preference parameter αj scales the rating so that the lowest-rated first bottle of wine is weakly preferred to the second bottle of the highest-rated wine. αj can be calibrated according to the preferences of the wine selector.

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 23 / 28

Results Multiple purchases

Solutions for Integer Problem, Zinfandel

If transformation is Linear → More than 50 solutions with.

1

38 bottles of wine

2

14 different wines chosen

3

Expenditure of $998

4

11 wines selected 3 times in all optimal solutions.

5

Rating equally divided between 84.5 and 92.5.

If transformation is Log →, two solutions with:

1

38 bottles chosen

2

Expenditure of $1000

3

7 brands chosen 3 times, 4 brands chosen two times

4

Difference between the solutions due to an exchange between equivalent bottles: Carlisle Winery Zinfandel Fava Ranch and Carlisle Winery Zinfandel Tom Feeney Ranch.

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 24 / 28

Results Multiple purchases

Integer problem power transformation

If transformation is Power function (γ = 0.8) → 24 solutions with

1

35 bottles chosen

2

Expenditure of $1000

3

• nly 2 bottles repeatedly chosen (3 times)

Relaxing the three repetitions constraint, γ = 0.8 → 40 solutions with:

1

37 bottles chosen

2

Expenditure of $1000

3

Only one bottles repeatedly chosen

4

Preference for variety

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 25 / 28

Conclusions

Outline

1

Motivation

2

Problem Formulation Background Objective Multiple Solutions Different Cultivars Multiple cultivars, multiple solutions

3

Data Descriptive Detailed information

4

Results No multiple Purchases Multiple purchases

5

Conclusions

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 26 / 28

Conclusions

Conclusions Advancement of MIP’s optimal selection fro NP hard problems. Initial problem: zins provide higher rank per dollar than cabs or pinots. (20 zins, 8 cabs and 9 pinots). log utility transformation, allowing the purchase of multiple bottles of the same wine, 20 different brands chosen, with 11 multiple bottle purchases (zins). Value of connoisseur for ‘breaking’ ties.

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 27 / 28

Conclusions

Thank you ajl259@cornell.edu

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 28 / 28

Conclusions

Kellerer, H., U. Pferschy, and D. Pisinger. 2004. Knapsack Problems. Springer, Berlin, Germany. Martello, S., and P. Toth. 1990. Knapsack Problems: Algorithms and Computer Implementations (Wiley-Interscience Series in Discrete Mathematics and Optimization), E. Chichester, ed. John Wiley & Sons. Srisuwannapa, C., and P. Charnsethikul. 2007. “An Exact Algorithm for the Unbounded Knapsack Problem with Minimizing Maximum Processing Time.” Journal of Computer Science 3:138–143.

Conrad et al. (Cornell University) Wine in your Knapsack? June 2011 28 / 28