Question

1. The Moore & Harman Company is in the business of buying and selling grain. An important aspect of the company’s business is arranging for the purchased grain to be shipped to customers. If the company can keep freight costs low, profitability will improve.

The company recently purchased three rail cars of grain at Muncie, Indiana; six rail cars at Brazil, Indiana; and five rail cars at Xenia, Ohio. Twelve carloads of grain have been sold. The locations and the amount sold at each location are as follows:

Location Macon, GA Greenwood, SC Concord, SC Chatham, NC Number of Rail Car Loads 2. 4 3 3

All shipments must be routed through either Louisville or Cincinnati. Shown are the ship- ping costs per bushel (in cents) from the origins to Louisville and Cincinnati and the costs per bushel to ship from Louisville and Cincinnati to the destinations.

Determine a shipping schedule that will minimize the freight costs necessary to satisfy demand. Which (if any) rail cars of grain must be held at the origin until buyers can be found?

Answer 1

Create spreadsheet model as follows:

H20 А D H B fx =SUMPRODUCT(B3:67,813:617) B C Shipping costs Louisville Cincinnati Macon Greenwood Concord Chatham 1 8 Supply 3 6 5 3 9 w 000 3 2 From/To 3 Muncie 4 Brazil 5 Xenia 6 Louisville 7 Cincinnati 8 Demand 9 34 44 57 34 28 35 32 24 3 2 4 3 10 Optimal Shipping plan Louisville Cincinnati Macon Greenwood Concord Chatham Row Total 0 11 12 From/To 13 Muncie 14 Brazil 15 Xenia 16 Louisville 17 Cincinnati 18 Column Total o 0 0 0 0 0 o 0 0 0 19 20 Total Cost = 21 22

EXCEL FORMULA:

H20 ® fx =SUMPRODUCT(B3:G7,B13:G17) А С D E F G Н B Shipping costs Louisville 8 Cincinnati Macon Greenwood Concord Chatham Supply 3 3 1 2. From/To 3 Muncie 4 Brazil 5 Xenia 6 Louisville 7 Cincinnati 8 Demand 6 8 3 6 9 5 34 34 44 57 32 24 35 28 2 4 3 3 9 10 11 Optimal Shipping plan 12 From/To Louisville Cincinnati Macon Greenwood Concord Chatham Row Total 13 Muncie =SUM(B13:413) 14 Brazil =SUM(B14:14) 15 Xenia =SUM(B15:515) 16 Louisville =SUM(B16:G16) 17 Cincinnati =SUM(B17:617) 18 Column Total =SUM(B13:B17) =SUM(C13:C17) ESUM(D13:017) ESUM(E13:E17) =SUM(F13:F17) =SUM(G13:617) 19 20 Total Cost = =SUMPRODUCT(B3:G7,B13:G17) 21 22 23

Enter Solver Parameters:

Solver Parameters x Set Objective: SH$20 To: Max Min value of: By Changing Variable Cells: $B$13:$C$15, $D$16:$G$17 Subject to the Constraints: $D$18:$G$18 = $D$8:$G$8 $B$18:$C$18 = $H$16:5H$17 SH$13:SH$15 < = SH$3:SH$5 Add Change Delete Reset All Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear.Select the LP Simplex engine for linear Solver Problems. Solve Close

Click Solve to generate the solution. After clicking solve, values appear automatically in yellow cells:

H20 fx =SUMPRODUCT(B3:G7, B13:17) A С F Н K M N 0 P R 1 Supply 3 S Solver Results X B D E G Shipping costs Louisville Cincinnati Macon Greenwood Concord Chatham 8 6 3 8 9 3 44 34 34 32 57 35 28 24 2 4 3 3 6 5 Solver found a solution. All constraints and optimality conditions are satisfied. Reports Answer Keep Solver Solution Sensitivity Limits O Restore Original Values 2 From/To 3 Muncie 4. Brazil 5 Xenia 6 Louisville 7 Cincinnati 8 Demand 9 10 11 12 From To 13 Muncie 14 Brazil 15 Xenia 16 Louisville 17 Cincinnati 18 Column Total 19 Return to Solver Parameters Dialog OK Cancel Optimal Shipping plan Louisville Cincinnati Macon Greenwood Concord Chatham Row Total 0 1 1 6 0 6 0 5 5 2 4 0 0 6 0 0 3 3 6 6 6 2 4 3 3 Solver found a solution. All constraints and optimality conditions are satisfied. When Simplex LP is used, this means Solver has found a global optimal solution. 20 Total Cost = 419 21 22

Optimal shipping schedule is shown in yellow table (B13:G17)

In the solution table, we see that Row Total of Muncie is 1, which is less than the Supply (3)

Therefore, 2 rail cars of grain must be held at Muncie ( unitil buyers can be found.)