Question

DUROT is a producer of specialized, patented, industrial-strength sanitizing equipment used by hospitals/healthcare centers, nursing homes, long term care centers and assisted living facilities throughout the United States. Due to the recent pandemic, the company is seeing a significant surge in its demand nationwide. The company anticipates the domestic regional demand for 2021 to be as follows: South – 1.8 million units; Midwest – 1.2 million units; East – 1.1 million units; West – 1 million units

Senior management of DUROT is designing their supply chain network and have identified four potential plant locations – Philadelphia, Jacksonville, Saint Louis and Los Angeles. Each location can have a capacity of either 2 million or 4 million units depending on the size of the plant.

The annual fixed costs for the 4 potential locations, and the production and shipping costs to each regional market from these plants are as follows

The objective here is to minimize the total network cost including fixed cost and production and shipping costs. The management is interested in finding out which plants should be opened and at what capacity - Small (2 million) or Large (4 million).

Prepare an excel sheet with solver, that include the objective function, decision variables and all the constraints.

	Annual Fixed Costs		Markets (Production and Shipping Costs)
	2 Million Capacity	4 Million Capacity	East	South	Midwest	West
Philadelphia	60,000,000	100,000,000	211	232	240	300
Jacksonville	55,000,000	92,000,000	232	212	230	280
Saint Louis	56,000,000	93,000,000	238	230	215	270
Los Angeles	61,000,000	102,000,000	299	280	270	225
Requirements			1,100,000	1,800,000	1,200,000	1,000,000

Answer 1

Create Excel model as follows:

EXCEL FORMULA:

Enter Solver Parameters:

Click Solve to generate result. After clicking Solve, values appear automatically in yellow cells.

Click OK

Result:

Jacksonville plant should be opened with capacity of 4 million units

Los Angeles plant should be opened with capacity of 2 million units

Optimal production and shipping plan is shown in cells D12:G15

Total cost = $ 1,294.8 million