You are the logistics manager at Pacific Minerals who oversees all transportation activities. Pacific Minerals has...

Transcribed Image Text:

You are the logistics manager at Pacific Minerals who oversees all transportation activities. Pacific Minerals has three plants (1, 2, 3), two warehouses (1, 2), and it serves four markets (1, 2, 3, 4) as seen in the diagram. The maximum amount to ship out from each plant, the minimum amount required by each market, and the cost of shipping materials (per ton) in each shipping route are provided in the spreadsheet template. O Plant 1 Plant 2 Plant 3 O O O Warehouse 1 O Warehouse 2 O Market 1 Market Market 3 O Pacific Minerals has an option to outsource (partially or fully) its transportation activities out of Warehouse 1 to a third-party shipping firm Express Shipping. Dashed lines represent routes that Pacific Minerals can outsource its work to Express Shipping. There is a fixed setup cost of $600 to do business with Express Shipping. The cost of shipping (per ton) that Express Shipping charges as well as the maximum amount to outsource to Express Shipping are provided in the spreadsheet template. For simplicity, assume that all other costs are sunk costs. Formulate a spreadsheet model for this problem, then use Solver to solve and determine the optimal solution that will minimize the total cost while satisfying all constraints. Market You are the logistics manager at Pacific Minerals who oversees all transportation activities. Pacific Minerals has three plants (1, 2, 3), two warehouses (1, 2), and it serves four markets (1, 2, 3, 4) as seen in the diagram. The maximum amount to ship out from each plant, the minimum amount required by each market, and the cost of shipping materials (per ton) in each shipping route are provided in the spreadsheet template. O Plant 1 Plant 2 Plant 3 O O O Warehouse 1 O Warehouse 2 O Market 1 Market Market 3 O Pacific Minerals has an option to outsource (partially or fully) its transportation activities out of Warehouse 1 to a third-party shipping firm Express Shipping. Dashed lines represent routes that Pacific Minerals can outsource its work to Express Shipping. There is a fixed setup cost of $600 to do business with Express Shipping. The cost of shipping (per ton) that Express Shipping charges as well as the maximum amount to outsource to Express Shipping are provided in the spreadsheet template. For simplicity, assume that all other costs are sunk costs. Formulate a spreadsheet model for this problem, then use Solver to solve and determine the optimal solution that will minimize the total cost while satisfying all constraints. Market