Please include how to set up the answer in Excel with Excel Solver.

A soft drink manufacturing company has 3 factories set up one in each of the three cities: Orland, Tampa, and Port St. Lucie and it supplies the produced soft drink bottles to 3 warehouses located in the city of Miami. The associated per-unit transportation cost table is provided below:

Transportation Costs ($)

Factories/Warehouse (W)	W1	W2	W3
Tampa	4	3	7
Orlando	7	6	4
Port St. Lucie	3	6	6

The factory at Tampa has a capacity of 15,000 units.

The factory at Orlando has a capacity of 18,000 units.

The factory at Port St. Lucie has a capacity of 8,000 units

The requirements of the warehouses are:

Warehouse	Requirement (Bottles)
W1	18,000
W2	12,000
W3	5,000

Questions:

1) Determine how much of the companys production should be shipped from each factory to each warehouse to minimize the total transportation cost.

Also, answer the following questions:

2. What would be the effect on the optimal solution and the optimal cost if the capacity at Port St. Lucie increases to 10,000 units? What would be the effect on the optimal solution and the optimal cost if the capacity at Tampa increases to 20,000 units?

3. Suppose the company can supply all the required soft drink for all its warehouses from a factory in Jacksonville. The transportation cost from Jacksonville to W1 is $7 per unit, from Jacksonville to W2 is $5 per unit and from Jacksonville to W3 is $4 per unit. Should the company supply all, none, or some of its soft drink from Jacksonville rather than supply it from the other factories?