Some solutions for A06 - Due Saturday 2/14 at midnight This...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Due Saturday 2/14 at midnight This assignment is worth 20 points. It must be done in your pre-assigned team. Do the following problems from the text (H&H) 5.6 5.9 5.11 5.13 Please submit work in ONE Excel file. Name your file A06_TeamName. (TeamName is shown in Group Pages folder.) Examples: A06_Blue A06_Maize A06_Red. One submission per team. In the upper left hand corner of the first tab of the Excel file: List the name(s) of each team member. List the percent contributions of each team member. Submissions without names or percent contributions will be scored. However, individual scores will not be assign idual scores will not be assigned until percent contributions are received. 10% reduction per day overdue. be2cae54ba178b8afc30b43f91c103707694798e.xlsx 10/06/2009 5.6 Unit Profit Resource Milk Sugar Cream Ken and Larry, Inc. Chocolate $1.00 Vanilla $0.90 Banana $0.95 Used 180 150 60 Available 200 150 60 Total Profit $341.25 Range Name CreamSupply CreamUsed Gallons_Produced MilkSupply MilkUsed SugarSupply SugarUsed Total_Profit Unit_Profit Cells G9 E9 B12:D12 G7 E7 G8 E8 G12 B4:D4 Resources Used per Gallon Produced 0.45 0.5 0.4 0.5 0.4 0.4 0.1 0.15 0.2 Chocolate 0 Vanilla 300 Banana 75 <= <= <= Gallons Produced Parts a - f See Sensitivity Report 5.6 Microsoft Excel 12.0 Sensitivity Report Worksheet: [Book2.xlsx]5.6 Report Created: 2/13/2009 12:45:22 PM Objective Cell (Max) Cell Name Total_Profit Gallons Produced Total Profit Decision Variable Cells Cell $B$12 $C$12 $D$12 Constraints Cell Name CreamUsed CreamUsed MilkUsed MilkUsed SugarUsed SugarUsed Final Value Name Gallons Produced Chocolate Gallons Produced Vanilla Gallons Produced Banana Final Value This sensitivity report is equivalent to the report given Final Value 341.25 Reduced Objective Cost Coefficient 0 0 1 300 0 0.9 75 0 0.95 Allowable Increase 0.04 0.05 0.02 Shadow Constraint Price R.H. Side 60 1 60 180 0 200 150 1.88 150 Allowable Increase 15 1.00E+030 10 a. b. Optimal solution is weekly production of 0 gallons of chocolate; 300 gallons of vanilla; and 75 gallons of banana Profit for banana changes to $1.00; i.e. PB= $1.00, an increase of $.05, beyond the allowable increase. Optima TotalProfit will increase by at least 75x($.021428). If PB were changed to $.92, a decrease of $.03, the current solution remains optimal. TotalProfit will decrease c. d. The supply of cream is decreased by 3 gallons; less than the allowable decrease of 3.75 gallons. The current s product mix will change in ways unpredictable from the information given. TotalProfit will decrease by (3 gal x $ e. An additional 15 pounds of sugar can be purchased for $15. Should the purchase be made (and why)? Sugar i are available, and 150 pounds/week are being used. The shadow price of sugar is $1.875/pound and remains up to and including 10 pounds/week (Allowable Increase). The first 10 extra pounds of sugar will generate Tota Since the first 10 extra pounds of sugar would increase TotalProfit by more than its cost of $15, BUY THE EXT We cannot predict the new optimal product mix from the information given; it would be necessary to re-solve th 15 pounds of sugar. f. At the optimal product mix, the milk constraint reads: MILK USED = .45*(0) + .5*(300) + .4*(75) = 180 < 200 = There is slack in the supply of milk, so increased supply would be worthless, i.e. the shadow price of milk is $0. for any increases, however large; i.e. the Allowable Increase is unbounded [arbitrarily large]. Similarly, decreas TotalProfit because such a reduction would only decrease the available slack of 20. Thus, the shadow price of up to the total slack. That is, the Allowable Decrease is 20 gallons of milk per week. port is equivalent to the report given in the problem, but the row order is changed. Allowable Decrease 1.00E+030 0.01 0.05 Allowable Decrease 3.75 20 30 of vanilla; and 75 gallons of banana. TotalProfit = $341.25 per week. yond the allowable increase. Optimal solution changes. s optimal. TotalProfit will decrease by ($.03/gal x 75 gal/week) = $2.25 per week. rease of 3.75 gallons. The current shadow price remains valid. The optimal otalProfit will decrease by (3 gal x $1/gal) = $3.00 per week. rchase be made (and why)? Sugar is a binding resource: 150 pounds/week ugar is $1.875/pound and remains valid for increases in the supply of sugar pounds of sugar will generate TotalProfit increases of $18.75 per week. than its cost of $15, BUY THE EXTRA SUGAR. it would be necessary to re-solve the revised problem, incorporating the extra ) + .5*(300) + .4*(75) = 180 < 200 = MILK SUPPLY. s, i.e. the shadow price of milk is $0. That shadow price remains valid [arbitrarily large]. Similarly, decreasing the milk supply will not harm the ck of 20. Thus, the shadow price of $0 remains valid for any decrease, 5.9 Range B5:G13 contains live model. All others are images. Unit profit $2 $5 Resource 1 2 Level a Unit profit Resource Usage per Unit A1 A2 1 2 1 3 6 $2 2 $5 Used 10 12 <= <= Profit Available 10 12 $22.00 b Available Resource 1 5 6 7 8 9 10 11 12 13 14 15 Resource 1 2 Level Unit profit Resource Usage per Unit A1 A2 1 2 1 3 6 $2 2 $5 Available 10 12 <= <= Profit Available 10 12 $22.00 Shadow price = $23-$22 = $1 Resource Usage per Unit A1 A2 1 2 1 3 9 1 A1 6 0 0 0 0 3 6 9 12 12 12 12 A2 2 2.5 3 3.5 4 3 2 1 0 0 0 0 Profit $22.00 $12.50 $15.00 $17.50 $20.00 $21.00 $22.00 $23.00 $24.00 $24.00 $24.00 $24.00 Resource 1 2 Level Available 11 12 <= <= Profit Available 11 12 $23.00 Allowable range for the $1 shadow price to remain valid is roughly: 8 to 12 c Unit profit $2 $5 d Available Resource 2 6 7 8 9 10 11 12 13 14 15 16 17 18 Resource 1 2 Level Unit profit Resource Usage per Unit A1 A2 1 2 1 3 6 $2 2 $5 Available 10 12 <= <= Profit Available 10 12 $22.00 Shadow price = $23-$22 = $1 Unit profit $2 $5 A1 6 6 7 8 9 10 8 6 4 2 0 0 0 0 A2 2 0 0 0 0 0 1 2 3 4 5 5 5 5 Profit $22.00 $12.00 $14.00 $16.00 $18.00 $20.00 $21.00 $22.00 $23.00 $24.00 $25.00 $25.00 $25.00 $25.00 Resource 1 2 Level Resource Usage per Unit A1 A2 1 2 1 3 4 3 Used 10 13 <= <= Profit Available 10 13 e $23.00 Allowable range for the $1 shadow price to remain valid is roughly: 9 to 14 Next tab Microsoft Excel 12.0 Sensitivity Report Worksheet: [Book1.xlsx]5.9 Report Created: 2/13/2009 9:36:25 PM Objective Cell (Max) Cell Name Final Value $G$13 Profit Available 22 Decision Variable Cells Cell $C$13 $D$13 Constraints Cell $E$10 $E$11 Name Used Used Final Value 10 12 Shadow Price 1 1 Constraint R.H. Side 10 12 Allowable Increase 2 3 Allowable Decrease 2 2 Name Level A1 Level A2 Final Value 6 2 Reduced Cost 0 0 Objective Coefficient 2 5 Allowable Increase 0.5 1 Allowable Decrease 0.33 1 Resource 1 2 Allowable range Lower Upper 8 12 10 15 5.11 Resource usage per Per unit of activity Resource Resource A1 A2 Available 1 1 0 4 2 1 3 15 3 2 1 10 Unit profit $3 $2 Optimal solution: OV* = $17.00 Resource 1 shadow price = $ a A2 5 R esou rce 3: 2 A1 + 1 A2 = 10 R esou rce 1: 1 A1 + 0 A2 = 4 A2 5 R esou rce 2: 4 1 A1 + 3 A2 = 15 4 R e s o u r c e 2 : 1 .0 0 A 1 + 3 .0 0 A 2 = 3 3 2 P a y o ff: 3 A1 + 2 A2 = 17 2 1 1 0 0 1 4) 4 15 10 2 3 4 A1 0 0 1 O p t im a l D e c is io n s ( A 1 ,A 2 ) : ( 3 , R esou rce 1: 1A 1 + 0A 2 <= R esou rce 2: R esource 3: 1A 1 + 2A 1 + 3A 2 <= 1A 2 <= O p t im a l D e c is io n s ( A 1 ,A 2 ) : ( 3 .0 0 , 4 .0 0 ) R e s o u r c e 1 : 1 .0 0 A 1 + 0 .0 0 A 2 < = 5 .0 0 R e s o u r c e 2 : 1 .0 0 A 1 + 3 .0 0 A 2 < = 1 5 .0 0 R e s o u r c e 3 : 2 .0 0 A 1 + 1 .0 0 A 2 < = 1 0 .0 0 Resource 2 constraint boundary moves to the NE, but the optimal corner point stays at the intersection of the Resource 2 & 3 constraint boundaries. The new OV** = $17.20, so the shadow price is OV**-OV* = $17.20 - $17.00 = $.20 Resource 2 shadow price = $17.20 - $17.00 = $0.20 R e s o u r c e 3 : 2 .0 0 A 1 + 1 .0 0 A 2 = 1 0 .0 0 P a y o f f : 3 .0 0 A 1 + 2 .0 0 A 2 = 1 7 .2 0 A2 6 R e s o u r c e 1 : 1 .0 0 A 1 + 0 .0 0 A 2 = 4 .0 0 Resource 3 constraint bounda at the intersection of the Reso so the shadow price is OV**-O Resource 3 shadow price = $ A2 6 P ay o ff: 3 5 R e s o u r c e 2 : 1 .0 0 A 1 + 3 .0 0 A 2 = 1 6 .0 0 4 4 5 R e s o u r c e 2 : 1 .0 0 A 1 + 3 .0 0 A 2 3 3 2 2 1 1 0 0 1 2 3 4 A1 0 0 1 O p t im a l D e c is io n s ( A 1 ,A 2 ) : ( 2 .8 0 , 4 .4 0 ) R e s o u r c e 1 : 1 .0 0 A 1 + 0 .0 0 A 2 < = 4 .0 0 R e s o u r c e 2 : 1 .0 0 A 1 + 3 .0 0 A 2 < = 1 6 .0 0 R e s o u r c e 3 : 2 .0 0 A 1 + 1 .0 0 A 2 < = 1 0 .0 0 O p t im a l D e c is io n s ( A 1 ,A 2 ) : ( 3 .6 0 , 3 .8 0 ) R e s o u r c e 1 : 1 .0 0 A 1 + 0 .0 0 A 2 < = 4 .0 0 R e s o u r c e 2 : 1 .0 0 A 1 + 3 .0 0 A 2 < = 1 5 .0 0 R e s o u r c e 3 : 2 .0 0 A 1 + 1 .0 0 A 2 < = 1 1 .0 0 0 0 1 2 3 4 A1 0 0 1 O p t im a l D e c is io n s ( A 1 ,A 2 ) : ( 2 .8 0 , 4 .4 0 ) R e s o u r c e 1 : 1 .0 0 A 1 + 0 .0 0 A 2 < = 4 .0 0 R e s o u r c e 2 : 1 .0 0 A 1 + 3 .0 0 A 2 < = 1 6 .0 0 R e s o u r c e 3 : 2 .0 0 A 1 + 1 .0 0 A 2 < = 1 0 .0 0 O p t im a l D e c is io n s ( A 1 ,A 2 ) : ( 3 .6 0 , 3 .8 0 ) R e s o u r c e 1 : 1 .0 0 A 1 + 0 .0 0 A 2 < = 4 .0 0 R e s o u r c e 2 : 1 .0 0 A 1 + 3 .0 0 A 2 < = 1 5 .0 0 R e s o u r c e 3 : 2 .0 0 A 1 + 1 .0 0 A 2 < = 1 1 .0 0 Active model c Unit profit A1 $3 A2 $2 Image of model solved for 16 units of res A1 Unit profit $3 Resource usage per Per unit of activity Resource A1 1 1 2 1 3 2 Produced 2.8 Resource usage per Per unit of activity Resource A1 A2 1 1 0 2 1 3 3 2 1 Produced 3 4 Used #NAME? #NAME? #NAME? <= <= <= Profit Resource Available 4 15 10 #NAME? Shadow price of resource 2 = $17.20 - $ Image of model solved for 5 units of resource 1 A1 A2 Unit profit $3 $2 Resource usage per Per unit of activity Resource A1 A2 1 1 0 2 1 3 3 2 1 Produced 3 4 Image of model solved for 11 units of res A1 Unit profit $3 Resource usage per Per unit of activity Resource A1 1 1 2 1 3 2 Produced 3.6 Used 3 15 10 <= <= <= Profit Resource Available 5 15 10 $17.00 Shadow price of resource 1 = $17.00 - $17.00 = $0 d Resource 1 0 1 2 3 4 5 6 7 8 9 10 A1 3 0 1 2 3 3 3 3 3 3 3 3 A2 4 5 4.67 4.33 4.00 4.00 4.00 4.00 4.00 4.00 4.00 4.00 Profit #REF! 10.00 12.33 14.67 17.00 17.00 17.00 17.00 17.00 17.00 17.00 17.00 Shadow price of resource 3 = $18.40 - $ Resource 2 2.33 2.33 2.33 0.00 0.00 0.00 0.00 0.00 0.00 0.00 11 12 13 14 15 16 17 18 19 20 21 A1 3 3.8 3.6 3.4 3.2 3 2.8 2.6 2.4 2.2 2 1.8 Shadow price stays at $0 down to 3 Resource 1 constraint boundary moves to the right, but optimal corner point does not change. Therefore, OV* = 17 is unchanged, and shadow price = 17-17 = 0. Resource 1 shadow price = $17.00 - $17.00 = $0.00 R e s o u r c e 3 : 2 .0 0 A 1 + 1 .0 0 A 2 = 1 0 .0 0 A2 5 R e s o u r c e 2 : 1 .0 0 A 1 + 3 .0 0 A 2 = 1 5 .0 0 4 R e s o u r c e 1 : 1 .0 0 A 1 + 0 .0 0 A 2 = 5 .0 0 3 2 P a y o f f : 3 .0 0 A 1 + 2 .0 0 A 2 = 1 7 .0 0 1 0 0 1 2 3 4 5 A1 O p t im a l D e c is io n s ( A 1 ,A 2 ) : ( 3 .0 0 , 4 .0 0 ) R e s o u r c e 1 : 1 .0 0 A 1 + 0 .0 0 A 2 < = 5 .0 0 R e s o u r c e 2 : 1 .0 0 A 1 + 3 .0 0 A 2 < = 1 5 .0 0 R e s o u r c e 3 : 2 .0 0 A 1 + 1 .0 0 A 2 < = 1 0 .0 0 Resource 3 constraint boundary moves to the NE, but the optimal corner point stays at the intersection of the Resource 2 & 3 constraint boundaries. The new OV** = $18.40, so the shadow price is OV**-OV* = $18.40 - $17.00 = $1.40 Resource 3 shadow price = $18.40 - $17.00 = $1.40 R e s o u r c e 3 : 2 .0 0 A 1 + 1 .0 0 A 2 = 1 1 .0 0 A2 6 P a y o f f : 3 .0 0 A 1 + 2 .0 0 A 2 = 1 8 .4 0 5 R e s o u r c e 2 : 1 .0 0 A 1 + 3 .0 0 A 2 = 1 5 .0 0 4 R e s o u r c e 1 : 1 .0 0 A 1 + 0 .0 0 A 2 = 4 .0 0 3 2 1 0 0 1 2 3 4 A1 O p t im a l D e c is io n s ( A 1 ,A 2 ) : ( 3 .6 0 , 3 .8 0 ) R e s o u r c e 1 : 1 .0 0 A 1 + 0 .0 0 A 2 < = 4 .0 0 R e s o u r c e 2 : 1 .0 0 A 1 + 3 .0 0 A 2 < = 1 5 .0 0 R e s o u r c e 3 : 2 .0 0 A 1 + 1 .0 0 A 2 < = 1 1 .0 0 0 0 1 2 3 4 A1 O p t im a l D e c is io n s ( A 1 ,A 2 ) : ( 3 .6 0 , 3 .8 0 ) R e s o u r c e 1 : 1 .0 0 A 1 + 0 .0 0 A 2 < = 4 .0 0 R e s o u r c e 2 : 1 .0 0 A 1 + 3 .0 0 A 2 < = 1 5 .0 0 R e s o u r c e 3 : 2 .0 0 A 1 + 1 .0 0 A 2 < = 1 1 .0 0 odel solved for 16 units of resource 2 A2 $2 Resource usage per Per unit of activity A2 0 3 1 4.4 Used 2.8 16 10 <= <= <= Profit Resource Available 4 16 10 $17.20 ice of resource 2 = $17.20 - $17.00 = $0.20 odel solved for 11 units of resource 3 A2 $2 Resource usage per Per unit of activity A2 0 3 1 3.8 Used 3.6 15 11 <= <= <= Profit Resource Available 4 15 11 $18.40 ice of resource 3 = $18.40 - $17.00 = $1.40 A2 Profit 4 #REF! 2.4 16.20 2.8 16.40 3.2 16.60 3.6 16.80 4 17.00 4.4 17.20 4.8 17.40 5.2 17.60 5.6 17.80 6 18.00 6.4 18.20 Resource 3 0.20 0.20 0.20 0.20 0.20 0.20 0.20 0.20 0.20 0.20 6 7 8 9 10 11 12 13 14 15 16 Shadow price stays at $.20 over this range. A1 3 0.6 1.2 1.8 2.4 3 3.6 4 4 4 4 4 A2 Profit 4.00 #REF! 4.80 11.40 4.60 12.80 4.40 14.20 4.20 15.60 4.00 17.00 3.80 18.40 3.67 19.33 3.67 19.33 3.67 19.33 3.67 19.33 3.67 19.33 1.40 1.40 1.40 1.40 1.40 0.93 0.00 0.00 0.00 0.00 Range for $1.40 shadow price. Microsoft Excel 12.0 Sensitivity Report Worksheet: [Book3]5.11 Report Created: 2/13/2009 12:54:39 PM Objective Cell (Max) Cell Name Profit Profit Available Decision Variable Cells Cell Name $C$66 Produced A1 $D$66 Produced A2 Constraints Cell Name $E$62 Used $E$63 Used $E$64 Used f Final Value 3 15 10 Shadow Price Constraint R.H. Side 0 4 0.2 15 1.4 10 Allowable Increase 1.00E+030 15 1.67 Allowable Decrease 1 5 5 Final Value 3 4 Reduced Cost Objective Coefficient 0 3 0 2 Allowable Increase 1 7 Allowable Decrease 2.33 0.5 Final Value 17 The shadow price of a resource is the rate of change of the optimal objective function value with respect to the amo For example, one more unit of resource 3 will increase the optimal profit by $1.40, from $17.00 to $18.40. If the cost of an additional unit of resource 3 were less than its shadow price, adding the unit would increase total pro e Valid range for shadow price Low High 3 ### 10 30 5 11.67 lue with respect to the amount of resource available. 7.00 to $18.40. nit would increase total profit since Δprofit = shadow price - cost of additional resource. A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 B C D E F G H I J K Super Grain Corp. Advertising-Mix Problem Exposures per Ad (thousands) Ad Budget Planning Budget TV Spots 1,300 Magazine Ads 600 SS Ads 500 Budget Spent #NAME? #NAME? Total Reached #NAME? #VALUE! Budget Available 4,000 1,000 Minimum Acceptable 5 5 Required Amount 1,490 Total Exposures (thousands) 0 Range Name BudgetAvailable BudgetSpent CostPerAd CouponRedemptionPerAd ExposuresPerAd MaxTVSpots MinimumAcceptable NumberOfAds NumberReachedPerAd RequiredAmount TotalExposures TotalReached TotalRedeemed TVSpots Cells H7:H8 F7:F8 C7:E8 C15:E15 C4:E4 C21 H11:H12 C19:E19 C11:E12 H15 H19 F11:F12 F15 C19 300 90 Cost per Ad ($thousands) 150 100 30 40 <= <= Young Children Parents of Young Children Number Reached per Ad (millions) 1.2 0.1 0 0.5 0.2 0.2 TV Spots 0 Magazine Ads 40 SS Ads 120 >= >= Coupon Redemption per Ad ($thousands) Total Redeemed 0 = Number of Ads Maximum TV Spots TV Spots 3 <= 5 Magazine Ads 14 SS Ads 7.75 Range name not used in model Microsoft Excel 12.0 Sensitivity Report Worksheet: [Book4]Revised Super Grain Report Created: 2/13/2009 1:01:33 PM Objective Cell (Max) Cell Name TotalExposures Number of Ads (thousands) Decision Variable Cells Cell TVSpots $D$19 $E$19 Constraints Cell $F$7 $F$8 $F$11 $F$12 TotalRedeemed Name Ad Budget Budget Spent Planning Budget Budget Spent Young Children Total Reached Parents of Young Children Total Reached TotalRedeemed Final Value 3,775 1,000 5 5.85 1,490 Shadow Price 0 35 -1575.76 0 -8 Constraint R.H. Side 4000 1000 5 5 1490 Name TVSpots Number of Ads Magazine Ads Number of Ads SS Ads Final Value 3 14 7.75 Reduced Cost Objective Coefficient 0 1300 0 600 0 500 Final Value 16175 a b c d e f g Increase in total exposures per a $1,000 increase in ad budget: 0 The shadow price in a is valid for any increase in ad budget since the allowable increase is infinite. Increase in total exposures per a $1,000 increase in planning budget: The shadow price in a is valid for increase in planning budget no greater than Simultaneous $100 increases in ad and planning budgets might or might not change the optimal so Add $100 to the planning budget and total exposures would increase by at least 85*35 = Subtract $100 from the ad budget and total exposures would decrease by 100*0 = Allowable Increase 1040 1.00E+030 577.78 Allowable Decrease 1.00E+030 192.59 1.00E+030 Allowable Increase 1.00E+030 22.5 1.32 0.85 385 Allowable Decrease 225 85 0.45 1.00E+030 90 Simultaneous Increases 100 100 % 0% 444% 444% lowable increase is infinite. 35.455 (000s) 22.500 ($000s) t not change the optimal solution; the 100%-rule sum is 444%. at least 85*35 = y 100*0 = 797.727 (000s) 0.000 no change ...
View Full Document

This note was uploaded on 04/21/2009 for the course BUS 338W-1 taught by Professor White during the Winter '09 term at University of Michigan-Dearborn.

Ask a homework question - tutors are online