Question

Build an Excel Solver model to find the optimal solution to create a least cost shipping schedule between markets and plants. The markets are New York, Chicago, Topeka and the plants are Seattle, San Diego. Seattle only have 350 units available of supplies and San Diego only have 600 units. The demand are only in New York, Chicago, Topeka respectively 325, 300, 275 units. The cost to ship between markets and plants is Seattle to New York is 2.5 Seattle to Chicago is 1.7 Seattle to Topeka is 1.8 San Diego to New York is 2.5 San Diego to Chicago is 1.8 San Diego to Topeka is 1.4

Answer 1

Excel model is created as below:

Max Topeka New York Chicago Chicago TopekaTotal New York 12.1 350 1.8 2.5 1.7 Seattle 2 Seattle 4 2.5 1.8 San Diego 3 San Diego Total cost 16.1 275 Min 5 Total

Using Solver, we get:

Max New York Chicago Topeka 1.7 12.1 Chicago Topeka Total New York 2.5 Seattle 2 2 Seattle 1.8 2.5 San Diego 16.1i Total costi___ 3 San Diego 275 325 Min 5 Total Solver Parameters Set Objective: Oyalue Of: 10 O Max ⓔMin To: By Changing Variable Cells: 12 SBS2 SDS3 Subject to the Constraints: Add 16 Change 18 SES3SLS3 Delete 19 20 21 Reset All

And the output is:

Max New York ChicagoTopeka 1.8 510 Chicago Topeka Total 350 New York 1.7 Seattle 0 299.99999 2 Seattle 1.8 2.5 San Diego 275 514 25 6.961E-06 Total cost 3 San Diego 275 325 Min 275 325 5 Total Solver Results Solver found a solution. All Constraints and optimality conditions are satisfied. Reports 10 Answer Sensitivity OKeep Solver Solution Limits 12 13 O Restore Original Values 14 15 outline Reports Return to Solver Parameters Dialog 16 17 Save Scenario... Cancel 18 19 Solver found a solution. All Constraints and optimality conditions are 20 satisfied. When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex IP is used. this means Solver has found a global 21

The Excel file is shared at the dropbox link below:

https://www.dropbox.com/s/18d2g1gsgbw0w7j/Transport%20model.xlsx?dl=0

$\blacksquare$

Please do rate this answer positively if you found it helpful. Thanks and have a good day!