California Truefarms Produces Sells Lot Oranges Year Oranges Collected Company S Two Farms Q28965486

California TrueFarms produces and sells a lot of oranges eachyear. The oranges are collected at the company’s two farms andtransported to the company’s two warehouses. Then they aredistributed to four major retailers to be sold to localsupermarkets. The shipping costs (per ton) are shown in the tablesbelow:

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

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

b)    Create aspreadsheet 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? Whatis the total cost of this production and distribution plan?

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

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