Reference no: EM13285461

Splash Soft Drinks

Splash Soft Drinks Inc. (SSD) has recently achieved sales that exceeded its expectations after it introduced a new beverage that was greatly welcomed by their customers. The company is currently considering opening a new plant to which some of the production of the other plants may be shifted.

The management has decided to undertake a preliminary analysis in order to evaluate the minimum cost for the network configuration under the assumption that the network will be designed from scratch (i.e., as if no plants currently exist)

The annual fixed and production costs as well as the plant capacity at each location is given in the Table 1.

TABLE 1	Fixed cost	Capacity	Production cost
Plant	(In $)	(in hectoliters)	($/hectoliter)
A	81400	22000	0.5
B	83800	24000	0.45
G	88600	28000	0.42
H	91000	30000	0.44
	79000	20000	0.56
C	86200	26000	0.5
	88600	28000	0.5
J	91000	30000	0.46
E	79000	20000	0.43
F	80200	21000	0.41

 

+ a hectoliter is 100 liters

* Excel clawfiles are included 

The distance in miles between the potential plants and markets is given in Table 2. The demand for markets A through F must be met and is given in the bottom of Table 2.

The trucks have capacity of 150 hectoliters and a cost of $0.92 per mile. Note that when a truck makes a delivery trip, it goes back to the plant empty. State any assumptions you make and address the following questions:

1)         Formulate a linear/integer programming model to minimize the total cost for the network and to determine the number of plants to be opened and their locations. Make sure you clearly define your decision variable, objective function and constraints.

2)         Implement the formulated model in Excel and use Solver to find the optimal plant locations when the total cost is minimized. Report a summary of your answer.

3)         Suppose that the maximum distance between a plant and a market cannot exceed ISO miles. What additional decision variables and/or constraints do you need to add to your model in question (1) to accommodate this condition?

4)         Modify your model in question (2) to take into account the new condition in question (3) and resolve the problem. Report the new locations and minimum cost. 

TABLE 2^.. Distance in miles between potential plants and markets

		A	Markets		D	E	F
	A	0	76.1	30.4	139.4	72.6	11.7
	B	76.1	0	71	77.2	144.5	83.7
	G	20.8	92.9	47.2	156.1	47.5	11.7
	H	54.7	113.3	52.9	187.2	93	45.2
Plants	I	13.5	85.5	28	148.7	67.3	9.3
	C	30.4	71	0	138.2	94.5	38.1
	D	139.4	77.2	138.2	0	207.9	146.9
	J	47.8	106.5	46.2	180.2	86.7	38.9
	E	72.6	144.5	94.5	207.9	0	63.4
	F	11.7	83.7	38.1	146.9	63.4	0
	Demand	14000	10000	8000	12000	10000	9000

* Excel data files are included

Notes:

1)   Refer to case guidelines when you write your report

2)   You can assume that the transportation cost can be calculated for a fraction of a truck.

distance (In miles)	A	8	markets C	D	E	F	Fixed cost (In 8)	CareacHY (in hectolitre)	Production cost ( $ pee Imctollter)
A		76.1	30.4	139.4	72.6	11.7	81400	22000	0.5
8	761	0	-,                 71	77.2	144.5	83.7	83800	24000	0.45
0	20.8	92.9	47.2	156.1	47.5	11.7	88600	28000	0.42
H	54.7	113.3	52.9	1872	93	452	91000	30000	0.44
MaMs          1	13.5	85.5	28	148.7	67.3	9.3	79000	20000	0.56
C	304	71	0	1382	94.5	38.1	86200	28000	0.5
0	139 4	77.2	138 2	0	207.9	148.9	88800	28000	0.5
J	47.8	106 5	462	180 2	88.7	389	91000	30000	0.46
E	72.6	144 5	945	207.9	0	634	79000	20000	0.43
F	11.7	83.7	38.1	146.9	63.4	0     -	80200	21000	0.41
Demand	14000	10000	8000	12000	10000	9000