Task $1(20$ marks$)$

Walsh's Fruit Company contracts with growers in Ohio, Pennsylvania and New York to

purchase grapes. The grapes are processed into juice at the farms and stored in refrigerated

vats. Then the juice is shipped to two plants, where it is processed into bottled grape juice

and frozen concentrate. The juice and concentrate are then transported to three food

warehouses$/$distribution centres. The transportation costs per ton from the farms to the

plants and from the plants to the distributors, and the supply at the farms and demand at

the distribution centres are summarised in the following tables.

Formulate the equations to determine the optimal monthly shipments from the farms to the

plants and from the plants to the distribution centres to minimise total transportation costs

$(8$ marks$).$

Solve the equations using Excel Solver to determine all the optimum monthly shipment

quantities $(6$ marks$).$

What would be the effect on the solution if the capacity at each plant were $140,000$ tons?

Formulate the additional equations, incorporate them into Solver and solve for new

optimum quantities $(6$ marks$)$