Solverxls---Paul-McFedries-Tech-Tonic by akgame

VIEWS: 16 PAGES: 8

More Info
									Break Even (Goal Seek)

Price Units Revenue Unit Cost Variable Costs Fixed Costs Total Costs Product Profit Total Profit

Finley Langstrom Sprocket Wrench $24.95 $19.95 1 1 $25 $20 $12.50 $13 $100,000 $100,013 ($99,988) ($174,977) $9.50 $10 $75,000 $75,010 ($74,990)

Page 1

Break Even (Solver)

Price Units Revenue Unit Cost Variable Costs Fixed Costs Total Costs Product Profit Total Profit

Finley Sprocket 24.95 1 =B3 * B4 12.5 =B7 * B4 - C4 100000 =B8+B9 =B5-B10 =B12+C12

Langstrom Wrench 19.95 1 =C3 * C4 9.5 =C7 * C4 - B4 75000 =C8+C9 =C5-C10

Page 2

Break Even (Solver)

Break-Even Model Target Cell: Changing Cells: Constraint #1: Constrant #2: Solver Options:

=$B$14=0 =COUNT($B$4,$C$4) =$B$12=0 =$C$12=0 ={100,100,0.000001,0.05,FALSE,TRUE,FALSE,1,1,1,0.0001,FALSE}

Page 3

Microsoft Excel 11.0 Answer Report Worksheet: [Solver.xls]Transportation Problem Report Created: 2/28/2004 6:03:18 PM

Target Cell (Min) Cell Name $B$20 Shipping Total: San Fran

Original Value 83

Final Value 3200

Adjustable Cells Cell Name $B$7 S. Carolina San Fran $C$7 S. Carolina Denver $D$7 S. Carolina Chicago $E$7 S. Carolina Dallas $F$7 S. Carolina N.Y. $B$8 Tennessee San Fran $C$8 Tennessee Denver $D$8 Tennessee Chicago $E$8 Tennessee Dallas $F$8 Tennessee N.Y. $B$9 Arizona San Fran $C$9 Arizona Denver $D$9 Arizona Chicago $E$9 Arizona Dallas $F$9 Arizona N.Y.

Original Value 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1

Final Value 0 0 80 0 220 0 0 100 160 0 180 80 20 0 0

Constraints Cell Name $G$7 S. Carolina TOTALS $G$8 Tennessee TOTALS $G$9 Arizona TOTALS $B$10 TOTALS San Fran $C$10 TOTALS Denver $D$10 TOTALS Chicago $E$10 TOTALS Dallas $F$10 TOTALS N.Y. $B$7 S. Carolina San Fran $C$7 S. Carolina Denver $D$7 S. Carolina Chicago $E$7 S. Carolina Dallas $F$7 S. Carolina N.Y. $B$8 Tennessee San Fran $C$8 Tennessee Denver $D$8 Tennessee Chicago $E$8 Tennessee Dallas $F$8 Tennessee N.Y. $B$9 Arizona San Fran $C$9 Arizona Denver

Cell Value 300 260 280 180 80 200 160 220 0 0 80 0 220 0 0 100 160 0 180 80

Formula $G$7<=$H$7 $G$8<=$H$8 $G$9<=$H$9 $B$10=$B$11 $C$10=$C$11 $D$10=$D$11 $E$10=$E$11 $F$10=$F$11 $B$7>=0 $C$7>=0 $D$7>=0 $E$7>=0 $F$7>=0 $B$8>=0 $C$8>=0 $D$8>=0 $E$8>=0 $F$8>=0 $B$9>=0 $C$9>=0

Status Not Binding Binding Binding Not Binding Not Binding Not Binding Not Binding Not Binding Binding Binding Not Binding Binding Not Binding Binding Binding Not Binding Not Binding Binding Not Binding Not Binding

Slack 9.999999999 0 0 0 0 0 0 0 0 0 80 0 220 0 0 100 160 0 180 80

$D$9 $E$9 $F$9

Arizona Chicago Arizona Dallas Arizona N.Y.

20 $D$9>=0 0 $E$9>=0 0 $F$9>=0

Not Binding Binding Binding

20 0 0

Microsoft Excel 11.0 Sensitivity Report Worksheet: [Solver.xls]Transportation Problem Report Created: 2/28/2004 6:03:19 PM

Adjustable Cells Cell $B$7 $C$7 $D$7 $E$7 $F$7 $B$8 $C$8 $D$8 $E$8 $F$8 $B$9 $C$9 $D$9 $E$9 $F$9 Name S. Carolina San Fran S. Carolina Denver S. Carolina Chicago S. Carolina Dallas S. Carolina N.Y. Tennessee San Fran Tennessee Denver Tennessee Chicago Tennessee Dallas Tennessee N.Y. Arizona San Fran Arizona Denver Arizona Chicago Arizona Dallas Arizona N.Y. Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 0 6 10 1E+30 6 0 3 8 1E+30 3 80 0 6 0 1 0 0 5 1E+30 0 220 0 4 4 1E+30 0 4 6 1E+30 4 0 2 5 1E+30 2 100 0 4 2 0 160 0 3 0 1E+30 0 4 6 1E+30 4 180 0 3 4 1E+30 80 0 4 2 1E+30 20 0 5 1 2 0 1 5 1E+30 1 0 6 9 1E+30 6

Constraints Cell $G$7 $G$8 $G$9 $B$10 $C$10 $D$10 $E$10 $F$10 Name S. Carolina TOTALS Tennessee TOTALS Arizona TOTALS TOTALS San Fran TOTALS Denver TOTALS Chicago TOTALS Dallas TOTALS N.Y. Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease 300 0 310 1E+30 10 260 -2 260 80 10 280 -1 280 80 10 180 4 180 10 80 80 5 80 10 80 200 6 200 10 80 160 5 160 10 80 220 4 220 10 220

Microsoft Excel 11.0 Limits Report Worksheet: [Solver.xls]Limits Report 1 Report Created: 2/28/2004 6:03:20 PM

Target Cell Name $B$20 Shipping Total: San Fran

Value 3200

Cell $B$7 $C$7 $D$7 $E$7 $F$7 $B$8 $C$8 $D$8 $E$8 $F$8 $B$9 $C$9 $D$9 $E$9 $F$9

Adjustable Name S. Carolina San Fran S. Carolina Denver S. Carolina Chicago S. Carolina Dallas S. Carolina N.Y. Tennessee San Fran Tennessee Denver Tennessee Chicago Tennessee Dallas Tennessee N.Y. Arizona San Fran Arizona Denver Arizona Chicago Arizona Dallas Arizona N.Y.

Value 0 0 80 0 220 0 0 100 160 0 180 80 20 0 0

Lower Target Limit Result -4.12513E-10 3200 -1.79455E-10 3200 80 3200 -3.65901E-10 3200 220 3200 -4.12513E-10 3200 -1.79455E-10 3200 100 3200 160 3200 -5.05736E-10 3200 180 3200 80 3200 20 3200 -3.65901E-10 3200 -5.05736E-10 3200

Upper Target Limit Result -4.12513E-10 3200 -1.79455E-10 3200 80 3200 -3.65901E-10 3200 220 3200 -4.12513E-10 3200 -1.79455E-10 3200 100 3200 160 3200 -5.05736E-10 3200 180 3200 80 3200 20 3200 -3.65901E-10 3200 -5.05736E-10 3200

95998e37-749f-4735-8924-b6183586a85d.xls

Number to ship from plant X to warehouse Y (at intersection). Warehouses Chicago Dallas 1 1 1 1 1 1 3 3 200 160

Plants: San Fran S. Carolina 1 Tennessee 1 Arizona 1 TOTALS 3 Warehouse Demands 180

Denver 1 1 1 3 80

N.Y. 1 1 1 3 220

TOTALS 5 5 5

Supply 310 260 280

Plants: San Fran S. Carolina 10 Tennessee 6 Arizona 3 TOTALS 19 Shipping Total:

Denver 8 5 4 17

Warehouses Chicago Dallas 6 5 4 3 5 5 15 13

N.Y. 4 6 9 19

83

Shipping costs from plant X to warehouse Y (at intersection).

Page 8


								
To top