An electronics manufacturer wants to expand its market in Europe. The demand in Europe is forecasted as:

England	France	Spain	Germany	Italy	Sweden
24	32	28	19	35	27

*in million units

Further analysis shows the variable costs (production, inventory and holding, transportation, tax, and tariffs) of meeting demand from a source to a market per 1,000,000 units:

	Demand Region
	Production and Transportation Cost per 1,000,000 units
Supply Region	England	France	Spain	Germany	Italy	Sweden
England	150	180	133	169	128	120
France	154	232	190	191	202	72
Spain	227	231	168	174	143	135
Germany	149	115	100	137	116	99
Italy	236	178	122	94	243	226
Sweden	248	73	70	226	246	116

*in thousands of dollars

The company considers two plant sizes in each location. The annualized fixed cost of plants for each location is found to be:

	Fixed Cost ($)	Low Capacity	Fixed Cost ($)	High Capacity
England	17,000	20	19,000	35
France	12,000	20	22,000	35
Spain	10,000	20	18,000	35
Germany	9,000	20	15,000	35
Italy	8,000	20	12,000	35
Sweden	9,000	20	16,000	35

*in thousands of dollars

1. What is the lowest cost achievable for the production and distribution network?
2. What is the minimum cost configuration (where are the plants located and in what is the capacity of each plant)?
3. Which plants serve which market in what volume?

NOTE: Please remember to add a constraint that will ensure having either a small capacity or a high capacity facility at a location. To do so, you can add up the binary variables of plant capacity for a location, and set it less than or equal to 1 as the right-hand side value.

PLEASE USE EXCEL TO SOLVE THIS PROBLEM

	Demand Region
	Production and Transportation Cost per 1,000,000 units
Supply Region	England	France	Spain	Germany	Italy	Sweden	Fixed Cost ($)	Low Capacity	Fixed Cost ($)	High Capacity
England	150	180	133	169	128	120	17000	20	19000	35
France	154	232	190	191	202	72	12000	20	22000	35
Spain	227	231	168	174	143	135	10000	20	18000	35
Germany	149	115	100	137	116	99	9000	20	15000	35
Italy	236	178	122	94	243	226	8000	20	12000	35
Sweden	248	73	70	226	246	116	9000	20	16000	35
Demand	24	32	28	19	35	27
Decision Variables
	Demand Region
	Production and Transportation Cost per 1,000,000 units		Low Capacity	High Capacity
Supply Region	England	France	Spain	Germany	Italy	Sweden	(1=open)	(1=open)
England
France
Spain
Germany
Italy
Sweden
Constraints
Supply Region	Excess Capacity
England	=H20*I5+I20*K5-SUM(B20:G20)
France	=H21*I6+I21*K6-SUM(B21:G21)
Spain	=H22*I7+I22*K7-SUM(B22:G22)
Germany	=H23*I8+I23*K8-SUM(B23:G23)
Italy	=H24*I9+I24*K9-SUM(B24:G24)
Sweden	=H25*I10+I25*K10-SUM(B25:G25)
	England	France	Spain	Germany	Italy	Sweden
Unmet Demand	=B11-SUM(B20:B25)	=C11-SUM(C20:C25)	=D11-SUM(D20:D25)	=E11-SUM(E20:E25)	=F11-SUM(F20:F25)	=G11-SUM(G20:G25)
Objective Function	=SUMPRODUCT(B20:G25,B5:G10)+SUMPRODUCT(H20:H25,H5:H10)+SUMPRODUCT(I20:I25,J5:J10)