exercises

Microsoft Excel 14.1 Sensitivity ReportWorksheet: [Workbook1]7.5Report Created: 4/26/2015 7:32:30 PM

Variable Cells Final Reduced Objective Allowable Allowable

Cell Name Value Cost Coefficient Increase Decrease$B$4 F 0 -0.00125 6.99 0.00125 1E+030$C$4 C 23.4375 0 7.99 7.99 0.01$D$4 P 59.375 0 5.99 1.335 0.005$E$4 N 18.75 0 8.99 5.9875 0.0025

ConstraintsFinal Shadow Constraint Allowable Allowable

Cell Name Value Price R.H. Side Increase Decrease$B$11 MC F 500 0.7484375 500 150 250$B$12 SC F 300 0.2503125 300 316.66666667 150$B$13 S F 237.5 0 450 1E+030 212.5$B$14 MJ F 350 0.7490625 350 250 150

# of Baskets 0 23.4375 59.375 18.75Contribution $6.99 $7.99 $5.99 $8.99

Profit $711.48

ConstraintsMild Cheddar 500 <= 500Sharp Cheddar 300 <= 300Swiss 237.5 <= 450Monetory Jack 350 <= 350

Four Chees Basket

Cheddars's Basket

Party Box Basket

Nachos Blend Basket

Microsoft Excel 14.1 Sensitivity ReportWorksheet: [Exercises.xlsx]7.6Report Created: 4/26/2015 8:09:03 PM


Cell Name Value Cost Coefficient Increase Decrease$B$2 E 0 -3.5714285714 51 3.5714285714 1E+030$C$2 C 2 0 102 16.666666667 12.5$D$2 P1 0 0 66 37.571428571 1E+030$E$2 P2 0 -37.571428571 66 37.571428571 1E+030$F$2 B 5 0 89 47 12.5


Cell Name Value Price R.H. Side Increase Decrease$B$11 Primrose E 0 11.4285714286 0 3.5 0$B$7 Clay E 130 1.42857142857 130 23.333333333 43.75$B$8 Enamel E 9 0 13 1E+030 4$B$9 Dry Room E 17 0 45 1E+030 28$B$10 Kiln E 23 20.1428571429 23 5.6 3.5

English Currier Primerose 1 Primerose 2 BluetailServices 0 2 0 0 5Contribution $51.00 $102.00 $66.00 $66.00 $89.00

$649.00

ConstraintsClay 130 <= 130Enamel 9 <= 13Dry Room 17 <= 45Kiln 23 <= 23Primrose 0 = 0

Objective Function

E C P1 P2 BServices 0 1.14285714 0 0 6.14285714Contribution 51 102 66 66 89

$663.29

ConstraintsClay 140 <= 140Enamel 8.42857143 <= 13Dry Room 19.5714286 <= 45Kiln 23 <= 23Primrose 0 = 0

Objective Function

14.28571431.42857143

Yes

E C P1 P2 Bservices 0 2 0 0 5Contribution 51 102 66 66 89

$649.00ConstraintsClay 130 <= 130Enamel 9 <= 13Dry Room 17 <= 25Kiln 23 <= 23Primrose 0 = 0

Objective Function



Cell Name Value Cost Coefficient Increase Decrease$B$2 Alloy W 0 -71.935483871 35 71.935483871 1E+030$C$2 Alloy X 903.22580645 0 47 108.55555556 12$D$2 Alloy Y 0 -38.919354839 60 38.919354839 1E+030$E$2 Alloy Z 774.19354839 0 140 48 97.7


Cell Name Value Price R.H. Side Increase Decrease$B$10 A W 477.41935484 0 600 1E+030 122.58064516$B$11 C W 400 315.161290323 400 311.11111111 180.95238095$B$12 M W 800 30.9677419355 800 131.03448276 350

W X Y ZAlloy 0 903.225806 0 774.193548contribution $35.00 $47.00 $60.00 $140.00

Objective Function $150,838.71

Constraints

Aluminium 477.4193548 <= 600Copper 400 <= 400Magnesium 800 <= 800

Microsoft Excel 14.1 Sensitivity ReportWorksheet: [Exercises.xlsx]7.8Report Created: 4/27/2015 9:23:20 AM

Variable CellsFinal Reduced Objective Allowable

Cell Name Value Cost Coefficient Increase$B$2 S 26666.666667 0 0.22 1E+030$C$2 B 18333.333333 0 0.2 0.016$D$2 H 0 -0.1266666667 0.18 0.1266666667$E$2 V 12666.666667 0 0.18 0.0485714286

ConstraintsFinal Shadow Constraint Allowable

Cell Name Value Price R.H. Side Increase$B$9 Carrot S 135000 0 150000 1E+030$B$10 Mashrooms S 80000 0.02266666667 80000 27142.857143$B$11 Green Peper S 135000 0.016 135000 15000$B$12 Brocli S 140000 0.056 140000 15833.333333$B$13 Corn S 86666.666667 0 150000 1E+030

AllowableDecrease

0.0680.056

1E+0300.0133333333

AllowableDecrease

1500080000

10555.55555618333.33333363333.333333

Stir Fry Barbecue Hearty Mushrooms Veggie Crunch# of mixes 26666.6667 18333.3333 0 12666.6667Contribution $0.22 $0.20 $0.18 $0.18

Objective Function $11,813.33

ConstraintsCarrot 135000 <= 150,000Mashrooms 80000 <= 80000Green Peper 135000 <= 135000Brocli 140000 <= 140000Corn 86666.6667 <= 150000


Variable CellsFinal Reduced Objective Allowable Allowable

Cell Name Value Cost Coefficient Increase Decrease$B$9 Arlington Onyx 300 0 20 5 26$C$9 Arlington Treble 100 0 19 2 1$D$9 Arlington Hilton 400 0 22 1 28$E$9 Arlington Dean 0 2 24 1E+030 2$B$10 Binghamton Onyx 0 5 26 1E+030 5$C$10 Binghamton Treble 0 4 24 1E+030 4$D$10 Binghamton Hilton 0 5 28 1E+030 5$E$10 Binghamton Dean 600 0 23 4 1E+030$B$11 Canton Onyx 0 7 33 1E+030 7$C$11 Canton Treble 400 0 25 1 2$D$11 Canton Hilton 0 1 29 1E+030 1$E$11 Canton Dean 0 0 28 2 4


Cell Name Value Price R.H. Side Increase Decrease$B$18 Arlington Onyx 800 -6 800 400 100$B$19 Binghamton Onyx 600 -5 600 0 300$B$20 Canton Onyx 400 0 700 1E+030 300$B$18 Arlington Onyx 800 0 0 800 1E+030$B$19 Binghamton Onyx 600 0 0 600 1E+030$B$20 Canton Onyx 400 0 0 400 1E+030$B$21 Onyx Onyx 300 0 0 300 1E+030$B$22 Treble Onyx 500 0 0 500 1E+030$B$23 Hilton Onyx 400 0 0 400 1E+030$B$24 Dean Onyx 600 0 0 600 1E+030$B$21 Onyx Onyx 300 26 300 100 300$B$22 Treble Onyx 500 25 500 300 400$B$23 Hilton Onyx 400 28 400 100 400$B$24 Dean Onyx 600 28 600 300 0

Onyx Treble Hilton Dean

Arlington $20.00 $19.00 $22.00 $24.00Binghamton $26.00 $24.00 $28.00 $23.00Canton $33.00 $25.00 $29.00 $28.00

Onyx Treble Hilton Dean

Arlington 300 100 400 0 800Binghamton 0 0 0 600 600Canton 0 400 0 0 400

300 500 400 600

objective function $40,500.00

constraintsArlington 800 <= 800Binghamton 600 <= 600Canton 400 <= 700Onyx 300 >= 300Treble 500 >= 500Hilton 400 >= 400Dean 600 >= 600


Variable CellsFinal Reduced Objective Allowable

Cell Name Value Cost Coefficient Increase$B$8 Center 1 Organic Orchard 0 0.4 0.7 1E+030$C$8 Center 1 Fresh & Local 0 2.220446E-16 0.9 1E+030$D$8 Center 1 Healthy Pantry 200 0 0.6 2.220446E-16$E$8 Center 1 Season's Harvest 0 4.440892E-16 1 1E+030$B$9 Center 2 Organic Orchard 0 0.6 1.2 1E+030$C$9 Center 2 Fresh & Local 50 0 1.2 2.220446E-16$D$9 Center 2 Healthy Pantry 200 0 0.9 0.2$E$9 Center 2 Season's Harvest 200 0 1.3 4.440892E-16$B$10 Center 3 Organic Orchard 300 0 1 0.4$C$10 Center 3 Fresh & Local 450 0 1.6 0.1$D$10 Center 3 Healthy Pantry 0 0.2 1.5 1E+030$E$10 Center 3 Season's Harvest 0 0.1 1.8 1E+030

ConstraintsFinal Shadow Constraint Allowable

Cell Name Value Price R.H. Side Increase$B$16 Organic Orchard 200 -0.7 200 200$B$17 Organic Orchard 450 -0.4 450 450$B$18 Organic Orchard 750 0 760 1E+030$B$16 Organic Orchard 200 0 0 200$B$17 Organic Orchard 450 0 0 450$B$18 Organic Orchard 750 0 0 750$B$19 O Organic Orchard 300 0 0 300$B$20 F Organic Orchard 500 0 0 500$B$21 H Organic Orchard 400 0 0 400$B$22 S Organic Orchard 200 0 0 200$B$19 O Organic Orchard 300 1 300 10$B$20 F Organic Orchard 500 1.6 500 10$B$21 H Organic Orchard 400 1.3 400 10$B$22 S Organic Orchard 200 1.7 200 10

AllowableDecrease

0.42.220446E-16

1E+0304.440892E-16

0.60.1

2.220446E-161.7

10.40.20.1

AllowableDecrease

101010

1E+0301E+0301E+0301E+0301E+0301E+0301E+030

300450200200

Center 1 $0.70 $0.90 $0.60 $1.00Center 2 $1.20 $1.20 $0.90 $1.30Center 3 $1.00 $1.60 $1.50 $1.80

Center 1 0 0 200 0 200Center 2 0 50 200 200 450Center 3 300 450 0 0 750

300 500 400 200

$1,640.00

Constraints1 200 <= 2002 450 <= 4503 750 <= 760

300 >= 300

500 >= 500

400 >= 400

200 >= 200

Organic Orchard

Fresh & Local

Healthy Pantry

Season's Harvest

Organic Orchard

Fresh & Local

Healthy Pantry

Season's Harvest

Objectvie function

Organic OrchardFresh & LocalHealthy PantrySeason's Harvest



Cell Name Value Cost Coefficient Increase Decrease$B$8 Amherst 60 0 7 1 9$C$8 Amherst 0 1 5 1E+030 1$D$8 Amherst Customer 110 0 4 0 6$E$8 Amherst 0 1 8 1E+030 1$F$8 Amherst 30 0 8 1 0$B$9 Providence 0 1 10 1E+030 1$C$9 Providence 100 0 6 1 6$D$9 Providence Customer 0 0 6 1E+030 0$E$9 Providence 50 0 9 1 9$F$9 Providence 50 0 10 0 1


Cell Name Value Price R.H. Side Increase Decrease$B$16 Amherst 200 -2 200 50 10$B$17 Providence 200 0 210 1E+030 10$B$18 60 9 60 10 50$B$19 100 6 100 10 100$B$20 110 6 110 10 50$B$21 50 9 50 10 50$B$22 80 10 80 10 50

Plant Customer1 2 3 4 5

Amherst $7.00 $5.00 $4.00 $8.00 $8.00Providence $10.00 $6.00 $6.00 $9.00 $10.00

Plant Customer1 2 3 4 5

Amherst 60 0 110 0 30 200Providence 0 100 0 50 50 200

60 100 110 50 80

$2,650.00

ConstraintsAmherst 200 <= 200Providence 200 >= 210

1 60 >= 602 100 >= 1003 110 >= 1104 50 >= 505 80 >= 80

objective Function



Cell Name Value Cost Coefficient Increase Decrease$B$2 A 4500 0 0.045 0.0565 0.0105$C$2 B 0 -0.01705 0.0562 0.01705 1E+030$D$2 C 0 -0.00525 0.068 0.00525 1E+030$E$2 D 3500 0 0.1015 0.024 0.0105$F$2 E 2000 0 0.206 1E+030 0.048


Cell Name Value Price R.H. Side Increase Decrease$B$10 ARL A 25000 0.02825 25000 3000 7000$B$10 ARL A 25000 0 0 25000 1E+030$B$11 MM A 4500 0 0 4500 1E+030$B$12 GF A 2000 0 0 2000 1E+030$B$13 TI A 10000 0 0 10000 1E+030$B$11 MM A 4500 0 3000 1500 1E+030$B$12 GF A 2000 0.048 2000 1750 1500$B$13 TI A 10000 0.01675 10000 7000 1000

Adams Barney Chilton Dunster Excelsior4500 0 0 3500 2000

Annual Return $0.05 $0.06 $0.07 $0.10 $0.21

Objective Function $969.75

Constraints

Average risk level 25000 <= 25000Money Market 4500 >= 3000Growth Fund 2000 <= 2000Total Investment 10000 = 10000

Month 1 Month 2 Month 3 Month 4 Month 5Trained Employees 0 13.6763101 50.7968615 77.3376328 97.2237735Untrained in production 129.804099854 83.5884278 54.0437662 32.1948407 23.3314719Untrained in training 15.1959001464 41.245057 29.4897459 22.0957119 9.46319796

Objective Function $2,528,448.57

ConstraintsDemand Month 1 129.804099854 >= 100Demand Month 2 100 >= 100Demand Month 3 115 >= 115Demand Month 4 125 >= 125Demand Month 5 140 >= 140Demand Month 6 150 >= 150Trained Employees M1 0 = 0Trained Employees M2 13.6763101318 = 13.6763101Trained Employees M3 50.7968614621 = 50.7968615Trained Employees M4 77.337632779 = 77.3376328Trained Employees M5 97.2237734519 = 97.2237735Trained Employee M6 105.740651616 = 105.740652Untrained Employee M1 145 = 145Untrained Employee M2 124.833484876 = 124.833485Untrained Employee M3 83.5335121531 = 83.5335122Untrained Employee M4 54.290552524 = 54.2905525Untrained Employee M5 32.7946698178 = 32.7946698Untrained Employee M6 23.1112180609 = 23.1112181

Month 6105.74065223.1112181

0



Cell Name Value Cost Coefficient Increase Decrease$B$2 HM 0 6327 1900 1E+030 6327$C$2 HM 9.5678478863 0 1900 6522.6804124 6327$D$2 HM 5.6113872996 0 1900 6522.6804124 6851.808$E$2 HM 26.213494046 0 1900 6786.5428886 6862.584699$F$2 HM 35.497572816 0 1900 6797.216937 8781.2926496$G$2 HM 0 1900 1900 1E+030 1900$B$3 RM 150 0 3300 1E+030 1E+030$C$3 RM 145.5 0 3300 1E+030 6660$D$3 RM 150.22445549 0 3300 1E+030 6660$E$3 RM 151.04853976 0 3300 1E+030 6929.4174757$F$3 RM 171.41990291 0 3300 1E+030 6940.3162409$G$3 RM 200 0 3300 1E+030 8880.7571295$B$4 SM 150 0 3300 0 1E+030$C$4 SM 145.11728608 0 3300 0 6398.6650485$D$4 SM 150 0 3300 1E+030 6398.6650485$E$4 SM 150 0 3300 1E+030 6657.510722$F$4 SM 170 0 3300 1E+030 6667.9818253$G$4 SM 200 0 3300 1E+030 8880.7571295$B$5 TM 0 0 3300 1E+030 0$C$5 TM 0.3827139155 0 3300 163067.01031 0$D$5 TM 0.224455492 0 3300 163067.01031 6660$E$5 TM 1.0485397618 0 3300 169663.57221 6929.4174757$F$5 TM 1.4199029126 0 3300 169930.42342 6940.3162409$G$5 TM 0 0 3300 1E+030 8880.7571295


Cell Name Value Price R.H. Side Increase Decrease$B$10 T7 0 0 0 0 1E+030$B$11 T8 0.3827139155 0 0 10.117286085 0.3827139155$B$12 T9 0.224455492 6398.66504854 0 5.7201440844 0.2336227268$B$13 T10 1.0485397618 6657.51072203 0 26.721549394 1.0913643332$B$14 T11 1.4199029126 6667.98182532 0 36.18556701 1.4778947368$B$15 T12 0 0 0 0 1E+030$B$10 T7 0 0 0 30 0$B$11 T8 0.3827139155 0 0 0.3827139155 1E+030$B$12 T9 0.224455492 0 0 0.224455492 1E+030$B$13 T10 1.0485397618 0 0 1.0485397618 1E+030

$B$14 T11 1.4199029126 0 0 1.4199029126 1E+030$B$15 T12 0 8880.75712949 0 3629719.3263 0$B$16 R7 150 0 0 150 1E+030$B$17 R8 145.5 0 0 145.5 1E+030$B$18 R9 150.22445549 0 0 150.22445549 1E+030$B$19 R10 151.04853976 0 0 151.04853976 1E+030$B$20 R11 171.41990291 0 0 171.41990291 1E+030$B$21 R12 200 0 0 200 1E+030$B$22 ST7 150 0 0 150 1E+030$B$23 ST8 145.5 0 0 145.5 1E+030$B$24 ST9 150.22445549 0 0 150.22445549 1E+030$B$25 ST10 151.04853976 0 0 151.04853976 1E+030$B$26 ST11 171.41990291 0 0 171.41990291 1E+030$B$27 ST12 200 0 0 200 1E+030$B$28 S7 150 0 0 150 1E+030$B$29 S8 145.11728608 0 0 145.11728608 1E+030$B$30 S9 150 0 0 150 1E+030$B$31 S10 150 0 0 150 1E+030$B$32 S11 170 0 0 170 1E+030$B$33 S12 200 0 0 200 1E+030$B$16 R7 150 11120.2 0 9.6603841981 10.020916513$B$17 R8 145.5 4660 0 9.3705726721 9.7202890173$B$18 R9 150.22445549 -2000 0 9.089455492 240.28554451$B$19 R10 151.04853976 -2269.4174757 0 5.5485397618 5939.8586602$B$20 R11 171.41990291 -2280.3162409 0 25.919902913 146833.30608$B$21 R12 200 -2280.7571295 0 35.1 3629719.3263$B$22 ST7 150 3300 0 1E+030 30$B$23 ST8 145.5 3300 0 1E+030 10.117286085$B$24 ST9 150.22445549 -3098.6650485 0 9.46068799 5.7201440844$B$25 ST10 151.04853976 -3357.510722 0 5.7751538068 26.721549394$B$26 ST11 171.41990291 -3367.9818253 0 26.978526316 36.18556701$B$27 ST12 200 -5580.7571295 0 35.1 3629719.3263$B$28 S7 150 0 120 30 1E+030$B$29 S8 145.11728608 0 135 10.117286085 1E+030$B$30 S9 150 6398.66504854 150 5.7201440844 9.46068799$B$31 S10 150 6657.51072203 150 26.721549394 5.7751538068$B$32 S11 170 6667.98182532 170 36.18556701 26.978526316$B$33 S12 200 8880.75712949 200 3629719.3263 35.1

7 8 9 10 11 12HM 0 9.56784789 5.6113873 26.213494 35.4975728 0RM 150 145.5 150.224455 151.04854 171.419903 200SM 150 145.117286 150 150 170 200TM 0 0.38271392 0.22445549 1.04853976 1.41990291 0

OF 3534766.72

ConstraintsT7 0 >= 0T8 0.38271392 >= 0.38271392T9 0.22445549 >= 0.22445549T10 1.04853976 >= 1.04853976T11 1.41990291 >= 1.41990291T12 0 >= 0R7 150 = 150R8 145.5 = 145.5R9 150.224455 = 150.224455R10 151.04854 = 151.04854R11 171.419903 = 171.419903R12 200 = 200ST7 150 = 150ST8 145.5 = 145.5ST9 150.224455 = 150.224455ST10 151.04854 = 151.04854ST11 171.419903 = 171.419903ST12 200 = 200S7 150 >= 120S8 145.117286 >= 135S9 150 >= 150S10 150 >= 150S11 170 >= 170S12 200 >= 200

exercises

Documents