数据、模型与决策（运筹学）课后习题和案例答案009 - 图文

7. Finally, Celeste wants ―Narcissism‖ displayed if ―Reflection‖ is displayed. So if the decision variable for ―Reflection‖ is 1, the decision variable for ―Narcissism‖ must also be 1. However, the decision variable for ―Narcissism‖ can still be 1 even if the decision variable for ―Reflection‖ is 0 (L45 ≥ N45).

Cost Constraint The cost of all of the pieces displayed has to be less than or equal to $4 million (C36 ≤ C38).

9-25

The problem formulation in an Excel spreadsheet follows. ABCDEFGHIJKLMNOPQRSTUVWXYZAAAdvances Native American Rights?Computer-Generated Drawing?Advances Environmentalism?Photo-Realistic Painting?Expressional Painting?Wire-Mesh Sculpture?Water-Color Painting?Advances Science?Price ($thousand)Cubist Painting?Other Art Form?Hangs on Wall?111111111111Female Artist?David Lyman?Sits on Floor?Candy Tate?Oil Painting?Rick Rawls?Ash Briggs?Male Artist?Ziggy Lite?Painting?Collage?123456789101112131415161718192021222324252627282930313233343536373839404142434445ArtistColin ZweibellColin ZweibellColin ZweibellRita LoskyRita LoskyRita LoskyRita LoskyNorm MarsonNorm MarsonCandy TateCandy TateRobert BayerRobert BayerDavid LymanDavid LymanAngie OldmanAngie OldmanAngie OldmanRick RawlsRick RawlsRick RawlsBill ReynoldsBill ReynoldsBear CantonBear CantonBear CantonHelen RowHelen RowZiggy LiteZiggy LiteAsh BriggsAsh BriggsAsh BriggsAsh BriggsPiecePerfection300Burden250The Great Equalizer125Chaos Reigns400Who Has Control?500Domestication400Innocence550Aging Earth700Wasted Resources575Serenity200Calm before the Storm225Void150Sun150Storefront Window850Harley750Consumerism400Reflection175Trojan Victory450Rick500Rick II500Rick III500Beyond650Pioneers650Wisdom250Superior Powers350Living Land450Study of a Violin400Study of a Fruit Bowl400My Namesake300Narcissism300All That Glitters50The Rock50Winding Road50Dreams Come True50Total3,950<=Budget4,0001111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111213>=>=>=<=<=111420111111111111111111111111161>=>=>=>=>=1111111111111110511114=4111=112=2110>=>=<=111111111510001100001111001010001001000100111115Wire Mesh SculptureComputer-Generated DrawingPaintingsDavid Lyman PiecesFemale Artist Pieces\1110151>=0.50.5timesComputer-Generated Drawing>=0.330.33timesWire Mesh Sculpture<=102timesOther Art Forms=1Rick Rawls Pieces>=50.5timesMale Artist Pieces>=0\

36 BCDTotal=SUMPRODUCT(C2:C35,Include?)=SUMPRODUCT(D2:D35,Include?)

AA36=SUM(Include?) 404142434445

KLWire Mesh Sculpture=E36Computer-Generated Drawing=F36Paintings=L36David Lyman Pieces=P36Female Artist Pieces=S36\lection\M>=>=<==>=>=N=O40*F36=O41*E36=O42*M36=Q36=O44*T36=AA31O=1/SUM(F2:F35)=1/SUM(E2:E35)2Rick Rawls Pieces0.5\PQRtimesComputer-Generated DrawingtimesWire Mesh SculpturetimesOther Art FormstimesMale Artist PiecesInclude? 9-26

Range NameBudgetInclude?TotalPiecesTotalPriceCellsC38AA2:AA35AA36C36

In the optimal solution, 15 pieces are displayed at a cost of $3.95 million. The following pieces are displayed:

1. ―The Great Equalizer‖ by Colin Zweibell 2. ―Chaos Reigns‖ by Rita Losky

3. ―Wasted Resources‖ by Norm Marson 4. ―Serenity‖ by Candy Tate

5. ―Calm Before the Storm‖ by Candy Tate

6. ―Void‖ by Robert Bayer (or ―Sun‖ by Robert Bayer) 7. ―Harley‖ by David Lyman 8. ―Reflection‖ by Angie Oldman 9. ―Rick III‖ by Rick Rawls 10. ―Wisdom‖ by Bear Canton

11. ―Study of a Fruit Bowl‖ by Helen Row (or ―Study of a Violin‖) 12. ―All That Glitters‖ by Ash Briggs 13. ―The Rock‖ by Ash Briggs 14. ―Winding Road‖ by Ash Briggs

15. ―Dreams Come True‖ by Ash Briggs

b) The formulation of this problem is the same as the formulation in part (a) except that

the objective function from part (a) now becomes a constraint and the cost constraint from part (a) now becomes the objective function. Thus, we have the new constraint that we need to select 20 or more pieces to display in the exhibit. We also have the new objective to minimize the cost of the exhibit.

The new formulation of the problem in an Excel follows.

9-27

ABCDEFGHIJKLMNOPQRSTUVWXYZAAAdvances Native American Rights?Computer-Generated Drawing?Advances Environmentalism?Photo-Realistic Painting?Expressional Painting?Wire-Mesh Sculpture?Water-Color Painting?Advances Science?Price ($thousand)Cubist Painting?Other Art Form?Hangs on Wall?Female Artist?David Lyman?Sits on Floor?Candy Tate?Oil Painting?Rick Rawls?Ash Briggs?Male Artist?Ziggy Lite?Painting?Collage?

123456789101112131415161718192021222324252627282930313233343536373839404142434445ArtistColin ZweibellColin ZweibellColin ZweibellRita LoskyRita LoskyRita LoskyRita LoskyNorm MarsonNorm MarsonCandy TateCandy TateRobert BayerRobert BayerDavid LymanDavid LymanAngie OldmanAngie OldmanAngie OldmanRick RawlsRick RawlsRick RawlsBill ReynoldsBill ReynoldsBear CantonBear CantonBear CantonHelen RowHelen RowZiggy LiteZiggy LiteAsh BriggsAsh BriggsAsh BriggsAsh BriggsPiecePerfection300Burden250The Great Equalizer125Chaos Reigns400Who Has Control?500Domestication400Innocence550Aging Earth700Wasted Resources575Serenity200Calm before the Storm225Void150Sun150Storefront Window850Harley750Consumerism400Reflection175Trojan Victory450Rick500Rick II500Rick III500Beyond650Pioneers650Wisdom250Superior Powers350Living Land450Study of a Violin400Study of a Fruit Bowl400My Namesake300Narcissism300All That Glitters50The Rock50Winding Road50Dreams Come True50Total5,45011111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111416>=>=>=<=<=11142011111111111111111111111111112162>=>=>=>=>=1111111111111112811114=4111=132=2110>=>=<=111111111713111101001111101010001001001100111120>=20Wire Mesh SculptureComputer-Generated DrawingPaintingsDavid Lyman PiecesFemale Artist Pieces\3112171>=0.50.5timesComputer-Generated Drawing>=1.000.33timesWire Mesh Sculpture<=162timesOther Art Forms=1Rick Rawls Pieces>=6.50.5timesMale Artist Pieces>=0\Include? In the optimal solution, exactly 20 pieces are displayed at a cost of $5.45 million –

$1.45 million more than Ash decided to allocate in part (a). All pieces from part (a) are displayed in addition to the following five new pieces:

1. ―Perfection‖ by Colin Zweibell 2. ―Burden‖ by Colin Zweibell 3. ―Domestication‖ by Rita Losky 4. ―Sun‖ (or ―Void‖) by Robert Bayer

5. ―Study of a Violin‖ (or ―Study of a Fruit Bowl‖) by Helen Row

9-28