You are in charge of the European division of your global company and you are planning to increase your distribution there.

You currently have two plants (in Strasbourg and Toulouse), and three distribution centers (in Zaragosa, Le Mans, and Torino). You are planning to add another plant in either Limoges or Saint-Etienne because you expect to have a surge in demand for your product.

Your decision will depend on the production costs in the different locations as well as the transportation costs from the plants to the distribution centers. Information on these costs appears below. Your aim is to minimize the total production plus transportation cost.

Transportation cost table (including per unit shipping costs):

	Zaragosa	Le Mans	Torino	supply
Strasbourg	120	80	65	200
Toulouse	35	70	75	200
Demand	225	150	175

The capacity at the new plant will be 200 units.

The production costs (per unit) are as follows:

Strasbourg	Toulouse	Limoges	Saint-Etienne
800	750	715	730

Finally, the following are the per unit transportation costs from the alternatives to the three distribution centers.

	Zaragosa	Le Mans	Torino
Limoges	50	30	100
Saint-Etienne	55	55	50

1. Set up a transportation table for each of the alternatives that you are considering. Display one of the tables below. Then solve both problems with Solver (in Excel), display each solution, and select the optimal location.

1. Which plant(s) will be underutilized in the optimal solution and by how much?

1. In the optimal solution, if you had to send, i.e., redistribute ten units from Strasbourg to Le Mans, by how much would your cost increase? (Note: this is not ten extra units, but you are forcing ten of the existing Strasbourg units to ship on that route.)