Question

California TrueFarms produces and sells a lot of oranges each year. The oranges are collected at the company’s two farms and transported to the company’s two warehouses. Then they are distributed to four major retailers to be sold to local supermarkets. The shipping costs (per ton) are shown in the tables below:

From\To Warehouse 1 Warehouse 2 Farm 1 $40 $35 Farm 2 – $25

FROM/ TO RETAILER 1 RETAILER 2 RETAILER 3 RETAILER 4 WAREHOUSE 1 $60 $35 - - WAREHOUSE 2 - $55 $50 $65

Farms 1 and 2 can produce up to 500 and 300 tons of oranges in each month, respectively. The cost of producing each ton of orange at Farm 1 is $35, whereas the cost at Farm 2 is $47 because of limited water availability. The four retailers show average monthly demands of 200, 100, 150, and 200 tons, respectively. Because of limited truck capacities, at most 250 tons of orange can be transported between Farm 1 and Warehouse 1.  

a)    Formulate a linear program that determines optimal amounts of production at each farm as well as optimal shipping of oranges in the network to satisfy demands at lowest possible (production + shipping) cost. Clearly define your variables, and write the objective function and all constraints in algebraic form.

b)    Create a spreadsheet model for this problem in Excel and solve with Solver. (Attach three snapshots:  Final setup, Formula view, Solver Menu)

c)     What is the optimal solution? What is the total cost of this production and distribution plan?

d)    Assume TrueFarms can use another truck company to provide additional assistant on the shipments from Farm 1 to Warehouse 1 (so it can ship beyond 250 tons). How much should TrueFarm be willing to pay to the new truck company to carry each additional ton of oranges? (Explain how you came up with that price).

e)    Assume that some oranges perish while being kept at warehouses. In particular, assume that 5% of oranges at Warehouse 1, and 10% of oranges at Warehouse 2 go bad in storage and need to be discarded (before shipping out to retailers). How would this change your algebraic formulation in part (a)? Clearly write down the changes in formulation in algebraic form. Update your Excel setup accordingly, re-solve the problem, and provide a snapshot of the new setup with solutions (no need to get Formula view and Solver menu again)

Answer 1

(a) Linear Program

Decision variables: Let Xij be the quantity (tons) to transport from Farm i to Warehouse j ea

and Xjk be the quantity (tons) to transport from Warehouse j to Retailer k

where, i {1,2}, j {3,4}, k {5,6,7,8}

Objective function: Min (35+40)X13+(35+35)X14+(47+25)X24+60X35+35X36+55X46+50X47+65X48

s.t.

X13+X14 <= 500

X24 <= 300

X13-X35-X36 = 0

X14+X24-X46-X47-X48 = 0

X35 = 200

X36+X46 = 100

X47 = 150

X48 = 200

X13 <= 250

X13, X14, X24, X35, X36,

X46, X47, X48 >= 0

b) Spreadsheet model and solution using Solver is following

Final setup and Solver parameters/menu

Formulas:

J3 =SUMPRODUCT(B3:I3,$B$15:$I$15)   copy to J3:J4, J6:J7, J9:J13

c) Optimal solution

X13	250
X14	250
X24	150
X35	200
X36	50
X46	50
X47	150
X48	200

Total cost = $ 84,050

d) The revised solution is following

Total cost of this plan = $ 83,300

Cost saved = 84050 - 83300 = $ 750

Quantity to transport from Farm to Warehouse 1 = 300 tons

Additional quantity transported = 300-250 = 50

TrueFarm should be willing to pay $ 750/50 = $ 15 per ton to the new truck company to carry each additional ton of oranges.