Microsoft Excel 16.

0 Answer Report
Worksheet: [06 Trans-Transship-Assign (LP approach).xlsx]Transport
Report Created: 31-10-2019 17:30:27
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.015 Seconds.
Iterations: 7 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative

Objective Cell (Min)

Cell Name Original Value Final Value
$B$20 Total Transporatation Cost = 0 1800

Variable Cells
Cell Name Original Value Final Value Integer
$B$13 Farm A Project 1 0 0 Contin
$C$13 Farm A Project 2 0 0 Contin
$D$13 Farm A Project 3 0 100 Contin
$B$14 Farm B Project 1 0 50 Contin
$C$14 Farm B Project 2 0 150 Contin
$D$14 Farm B Project 3 0 0 Contin
$B$15 Farm C Project 1 0 0 Contin
$C$15 Farm C Project 2 0 0 Contin
$D$15 Farm C Project 3 0 200 Contin

Constraints
Cell Name Cell Value Formula Status Slack
$B$18 Actual demand met = 50 $B$18=$B$16 Binding 0
$C$18 Actual demand met = 150 $C$18=$C$16 Binding 0
$D$18 Actual demand met = 300 $D$18=$D$16 Binding 0
$G$13 Qty_Supply_A 100 $G$13=$E$13 Binding 0
$G$14 Qty_Supply_B 200 $G$14=$E$14 Binding 0
$G$15 Qty_Supply_C 200 $G$15=$E$15 Binding 0
Microsoft Excel 16.0 Sensitivity Report
Worksheet: [06 Trans-Transship-Assign (LP approach).xlsx]Transport
Report Created: 31-10-2019 17:30:27

Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$13 Farm A Project 1 0 0 4 2 0
$C$13 Farm A Project 2 0 2 2 1E+030 2
$D$13 Farm A Project 3 100 0 8 0 8
$B$14 Farm B Project 1 50 0 5 0 2
$C$14 Farm B Project 2 150 0 1 2 1E+030
$D$14 Farm B Project 3 0 0 9 1E+030 0
$B$15 Farm C Project 1 0 8 7 1E+030 8
$C$15 Farm C Project 2 0 11 6 1E+030 11
$D$15 Farm C Project 3 200 0 3 8 1E+030

Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$B$18 Actual demand met = 50 5 50 0 50
$C$18 Actual demand met = 150 1 150 0 150
$D$18 Actual demand met = 300 9 300 0 50
$G$13 Qty_Supply_A 100 -1 100 50 0
$G$14 Qty_Supply_B 200 0 200 0 1E+030
$G$15 Qty_Supply_C 200 -6 200 50 0
Microsoft Excel 16.0 Limits Report
Worksheet: [06 Trans-Transship-Assign (LP approach).xlsx]Transport
Report Created: 31-10-2019 17:30:27

Objective
Cell Name Value
$B$20 Total Transporatation Cost = 1800

Variable Lower Objective Upper Objective

Cell Name Value Limit Result Limit Result
$B$13 Farm A Project 1 0 0 1800 0 1800
$C$13 Farm A Project 2 0 0 1800 0 1800
$D$13 Farm A Project 3 100 100 1800 100 1800
$B$14 Farm B Project 1 50 50 1800 50 1800
$C$14 Farm B Project 2 150 150 1800 150 1800
$D$14 Farm B Project 3 0 0 1800 0 1800
$B$15 Farm C Project 1 0 0 1800 0 1800
$C$15 Farm C Project 2 0 0 1800 0 1800
$D$15 Farm C Project 3 200 200 1800 200 1800
Harley’s Sand and Gravel (Topsoil Shipment)
UNIT COST TABLE
Projects
Farms Project 1 Project 2 Project 3
Farm A 4 2 8
Farm B 5 1 9
Farm C 7 6 3

SHIPMENT TABLE
Projects
Farms Project 1 Project 2 Project 3 Supply
Farm A 0 0 100 100 =
Farm B 50 150 0 200 =
Farm C 0 0 200 200 =
Demand 50 150 300 500
= = =
Actual demand met 50 150 300

Total Transporatation Cost 1800

Actual Qty Supplied
100
200
200
Harley’s Sand and Gravel (Topsoil Shipment)
UNIT COST TABLE
Projects
Farms Project 1 Project 2 Project 3
Farm A 4 2 8
Farm B 99999 1 9
Farm C 7 6 3

SHIPMENT TABLE
Projects
Farms Project 1 Project 2 Project 3 Supply
Farm A 50 0 50 100 =
Farm B 0 150 50 200 =
Farm C 0 0 200 200 =
Demand 50 150 300 500
= = =
Actual demand met 50 150 300

Total Transporatation Cost 1800

NOTE: B1 --- very large unit cost for prohibited route

Actual Qty Supplied
100
200
200
Harley’s Sand and Gravel (Topsoil Shipment)
UNIT COST TABLE
Projects
Farms Project 1 Project 2 Project 3 Dummy Demand
Farm A 4 2 8 0
Farm B 5 1 9 0
Farm C 7 6 3 0

SHIPMENT TABLE
Projects
Farms Project 1 Project 2 Project 3 Dummy Demand
Farm A 50 0 0 50
Farm B 0 150 0 50
Farm C 0 0 200 0
Demand 50 150 200 100
= = = =
Actual demand met 50 150 200 100

Total Transporatation Cost 950

Supply Actual Qty Supplied
100 = 100
200 = 200
200 = 200
500
Harley’s Sand and Gravel (Topsoil Shipment)
UNIT COST TABLE Penality for
Projects not meeting
Farms Project 1 Project 2 Project 3 the demand
Farm A 4 2 8
Farm B 5 1 9
Farm C 7 6 3
Dummy Supply 20 20 20

SHIPMENT TABLE
Projects
Farms Project 1 Project 2 Project 3 Supply
Farm A 0 0 100 100 =
Farm B 50 150 0 200 =
Farm C 0 0 120 120 =
Dummy Supply 0 0 80 80 =
Demand 50 150 300 500
= = =
Actual demand met 50 150 300

Total Transporatation Cost 3160

Penality for
not meeting
the demand

Actual Qty Supplied

100
200
120
80
Transshipment Problem
Warehouses
Farms Warehouse 1 Warehouse 2 Supply Qty Shipped
Farm A 0 100 100 100
Farm B 0 200 200 200
Farm C 200 0 200 200
Qty Received 200 300

Projects
Warehouses Project 1 Project 2 Project 3 Qty Shipped
Warehouse 1 0 0 200 200
Warehouse 2 50 150 100 300
Demand 50 150 300

Demand met 50 150 300

Total Cost 3050

 Shipping Costs
Warehouses
Warehouse 1 Warehouse 2
Farm A 3 2
Farm B 4 3
Farm C 2.5 3.5

Shipping Costs
Projects
Project 1 Project 2 Project 3
Warehouse 1 2 1 4
Warehouse 2 3 2 5
999999
Job-Employee Assignment Problem

Cost of each assignment

Employee
A B C D
1 15 20 18 24
2 12 17 16 15
Job
3 14 15 19 17
4 11 14 12 13

Employee
A B C D Job Available Job Assigned
1 1 0 0 0 1 = 1
2 0 0 0 1 1 = 1
Job
3 0 1 0 0 1 = 1
4 0 0 1 0 1 = 1
Emp. Available 1 1 1 1
= = = =
Emp. Assigned 1 1 1 1

Total Cost 57