Reference no: EM132056381

Ground Rules

• Create and SAVE an EXCEL file
• Open this file and do your work in this spreadsheet file, saving it often. Organize your responses on separate tabs labeled appropriately. Make sure to identify each response clearly if there are multiple responses on the sametab.

Instructions

• Read each question carefully and make sure that you answer it completely. Express your response using complete sentences. Providing only a numerical value or chart is not sufficient.
• Also, be sure to show all of your work in determining your response whennecessary.

Problem Description

The Liquid Air Company operates three plants and four warehouses to meet customers' demands. The plants produce a highly profitable (but volatile) gas used to produce military- grade lasers. Plants 1, 2, and 3 have monthly production (or output) capacities of 250 pounds, 400 pounds, and 350 pounds, respectively.

This gas has to be transported to each of the four warehouses where the gas can be made available to the laser manufacturing facilities. Each warehouse needs to receive 200 pounds of this gas each month. However, due to varying packaging approaches at the plants, only 97% of the gas shipped out of Plant 1 arrives at the warehouse - the other 3% is lost during transport. The corresponding values for Plants 2 and 3 are 92% and 97%, respectively.

Each warehouse has its own material handling system. While the costs of these material handling systems vary by warehouse, they are computed in a similar fashion. Specifically, the monthly material handling cost at each warehouse is computed as a*(amt_received)2 + b*(amt_received)

+ c, where the parameters a, b, and c for each warehouse are as given in the table below:

Parameter	Warehouse 1	Warehouse 2	Warehouse 3	Warehouse 4
a	0.5	0.9	0.4	0.7
b	-1	-1.4	-1.8	-1.2
c	6	8	4	12

In addition, there is a transportation cost per pound for shipping gas from each plant to each warehouse.  These unit costs are given in the table below:

	Warehouse 1	Warehouse 2	Warehouse 3	Warehouse 4
Plant 1	$32.00	$28.00	$41.00	$37.00
Plant 2	$21.00	$30.00	$28.00	$53.00
Plant 3	$26.00	$34.00	$22.00	$35.00

As the company's logistics manager, you have been charged with the task of finding a shipping plan that minimizes the sum of the monthly transportation and handling costs across all plants and warehouses while still meeting the capacity and demand requirements.

TASKS

a. Construct a transportation model, incorporating the constraints and costs discussed above, that will determine the optimal solution to this nonlinear programming problem. Be sure to use spreadsheet design principles in the construction of yourmodel.

b. Model the problem in Analytic Solver Platform, and provide a screenshot of the EXCEL SOLVER Dialog Box ("SolverParameters").

Solve the problem and provide an interpretation, using complete sentences, of the optimal solution obtained - be sure to include other relevant information from the model in your write-up of the solution.

c. To better understand the structure of the optimal solution and how it evolves as the minimum delivery to each warehouse (currently 200 pounds) changes, create a table that displays the minimum total cost and the corresponding shipping quantities to eachwarehouse from each plant as the minimum delivery to the warehouses changes from 20 to 260 per warehouse in increments of20.

d. Answer the following questions on the basis of the table that you present as your response to part (c).

How many different transportation shipment patterns are present?

Describe how the transportation shipment patterns evolve as the minimum delivery to each warehouse changes.

Determine the maximum total amount of gas that can be delivered to all of the warehouses and use this value to explain what happens in the problem when the minimum delivery to each warehouse is large.

e. Construct a chart of the minimum total cost as the minimum delivery to each warehouse changes. Describe the nature of the nonlinearity that you observe in thischart.