Question

A toy manufacturer currently uses two warehouses 1 and 2 to supply three retail outlets A, B and C with backyard playsets. The warehouse 1 and 2 capacity, store A, B and C demand and unit transportation cost ($ per backyard playset) from warehouse 1 and 2 to retail store A, B and C are shown in the table below

The toy manufacturer is considering the opening of a third warehouse that will have a capacity of 500 units of backyard playsets per week. Two locations N1 and N2 are being considered for the new warehouse. The transportation cost for each unit from the new location N1 to stores A, B and C are $6, $8 and $7 respectively. The transportation cost for each unit from the new location N2 to stores A, B and C are $10, $6 and $4 respectively.

Develop and solve LP models to determine the location of the new warehouse. Show the details (Variables, Objective Function and Constraints) for one of the models and attach a copy of the answer report for each model.

To                From	A	B	C	Capacity (Units per Week)
1	8	3	7	500
2	5	10	9	400
Demand (Units per Week)	400	600	350

**Can you use excel with showing inputed data and solver please?**

Answer 1

To select the location, we will use binary variable. For value = 0 means location should not be selected and value = 1 means location should be selected.

First of all, we will construct the LP table basis the detail provided. And selection column will be blank as it will be calculated by solve.

In second table, we will prepare table for the optimal solution. It will look like as below-

Below is the screenshot of the formula applied -

Below is the screenshot of the solver -

Below is the result -