Question

Part 4 – Transportation Problem

Pioneer Corporation sells car speakers. They have two factory locations in the Midwest where they manufacture car speakers. One is in Flint, MI and one is in Kalamazoo, MI. They also have two major stores in the Midwest where they sell their speakers, one in Chicago and one in Detroit.

Below is a table that gives the shipping costs for each set of car speakers from each factory to each store.

Shipping Costs	Stores
Factories	Detroit	Chicago
Kalamazoo	$7.43	$5.08
Flint	$3.29	$8.47

The factory in Kalamazoo has a supply of 700 sets of speakers, and the factory in Flint has a supply of 900 sets of speakers. So the factories cannot ship more than they currently have.

The store in Detroit has a demand for 500 sets of speakers, and the store in Chicago has a demand for 1000 sets of speakers. So the stores need to receive at least this many speakers (possibly more).

What should be the shipping schedule that fulfills the demand for each store and minimizes shipping costs?

Set up a spreadsheet to solve this problem. You may use the template provided below.

Speakers	Detroit	Chicago	Sent
Kalamazoo
Flint
Received
Shipping Costs	Detroit	Chicago
Kalamazoo
Flint
		Total Cost

Use the Excel “solver” function to solve this linear programming problem with 4 decision variables. Highlight the optimal decision variables in green and the minimum shipping cost in blue. Do not hard code the target or changing cells (do not enter specific numbers into those cells).

Answer 1

The Solver parameters are:

fx Solver Parameters X А B с D E Set Objective: $B$11 1 Detroit Chicago Supply 7.43 5.08 700 3.29 8.47 900 500 1000 To: O Max O Min O Value of: 0 1 2 Kalamazoo 3 Flint 4 Demand 5 6 7 Kalamazoo 8 Flint 9 Received By Changing Variable Cells: $B$7:$C$8 Detroit Chicago Sent 0 700 700 500 300 800 500 1000 Subject to the Constraints: $B$9:$C$9 = $B$4:$C$4 $D$7:$D$8 <= $D$2:$D$3 Add 10 11 Cost 7742 Change 12 Delete 13 14 15 Reset All 16 Load/Save 17 Make Unconstrained Variables Non-Negative 18 19 Select a Solving Method: Simplex LP Options 20 21 22 Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. 23 24 25 26 Help Solve 27 Close 28

The solution is:

M22 fo А E 1 B C D Detroit Chicago Supply 7.43 5.08 700 3.29 8.47 900 500 1000 2 Kalamazoo 3 Flint 4. Demand 5 6 7 Kalamazoo 8 Flint 9 Received Detroit Chicago Sent 0 700 700 500 300 800 500 1000 10 11 Cost 7742 12 12

Total cost = $7742