Question

I am not understanding how to do this in excel, I have half of it, but not sure where to put the 1100, 2900, 2400, 1500.

Questions asked are a plus.

M4_A1. Edsel Motors produces cars in Los Angeles and Detroit. The company supplies cars to customers in Houston and Tampa. The costs of shipping a car between various points shown in the table below. The dashes in the table indicate a shipment between locations that is not allowed. Los Angeles can produce up to 1100 cars, and Detroit can produce up to 2900 cars. Houston must receive 2400 cars and Tampa must receive 1500 cars.

		TO
		LA	DETROIT	HOUSTON	TAMPA
FROM	LA	---	$140	$90	$225
	DETROIT	$145	---	$110	$119
	HOUSTON	$89	$109	---	$137
	TAMPA	$210	$117	$129	---

Formulate this problem in Excel and solve to determine the minimum shipping costs required to meet demands. (Hint: you can force the solution to avoid certain paths by making the cost between those paths very high)

a) What is the total cost to meet demands in Houston and Tampa?

b) How many cars are shipped from LA to Houston?

c) How many cars are shipped from LA to Tampa?

d) How many cars are shipped from Detroit to Houston?

e) How many cars are shipped from Detroit to Tampa?

Answer 1

Let the variables be as defined below:

	To
From	LA	Detroit	Houston	Tampa
LA	LA1	LA2	LA3	LA4
Detroit	D1	D2	D3	D4
Houston	H1	H2	H3	H4
Tampa	T1	T2	T3	T4

Objective function = (LA1*0)+(LA2*140)+(LA3*90)+(LA4*225)+(D1*145)+(D2*0)+(D3*110)+(D4*119)+(H1*89)+(H2*109)+(H3*0)+(H4*137)+(T1*210)+(T2*117)+(T3*129)+(T4*0)

Constraints:

1. LA1+LA2+LA3+LA4 <=1100 (supply from LA cannot exceed 1100)

2. D1+D2+D3+D4<=2900 (supply from Detroit cannot exceed 2900)

3. LA1,D2,H3, T4 = 0 (supply from LA to LA not allowed, from detroit to detroit not allowed and so on)

4. (LA3+D3+H3+T3) - (H1+H2+H3+H4) = 2400 (net cars received by Houston should be = 2400)

5. (LA4+D4+H4+T4) - (T1+T2+T3+T4) = 1500 (net cars received by Tampa should be = 1500)

6. H1+H2+H3+H4<=LA3+D3+H3+T3 (cars shipped from houston cannot be more than cars received by it)

7. T1+T2+T3+T4<=LA4+D4+H4+T4 (cars shipped from Tampa cannot be more than cars received by it).

Solving in excel, using solver function, the following solution is obtained:

		To
	From	LA	Detroit	Houston	Tampa
	LA	0.00	140.00	90.00	225.00
	Detroit	145.00	0.00	110.00	119.00
	Houston	89.00	109.00	0.00	137.00
	Tampa	210.00	117.00	129.00	0.00
		To
	From	LA	Detroit	Houston	Tampa
	LA	0.00	0.00	1,100.00	0.00
	Detroit	0.00	0.00	1,300.00	1,500.00
	Houston	0.00	0.00	0.00	0.00
	Tampa	0.00	0.00	0.00	0.00
Total cost	420,500.00			Formula
				(LA1*0)+(LA2*140)+(LA3*90)+(LA4*225)+(D1*145)+(D2*0)+(D3*110)+(D4*119)+(H1*89)+(H2*109)+(H3*0)+(H4*137)+(T1*210)+(T2*117)+(T3*129)+(T4*0)
Constraints
1,100.00	<=	1,100.00		LA1+LA2+LA3+LA4 <=1100
2,800.00	<=	2,900.00		D1+D2+D3+D4<=2900
0.00	=	0.00		LA1=0
0.00	=	0.00		D2=0
0.00	=	0.00		H3=0
0.00	=	0.00		T4=0
2,400.00	=	2,400.00		(LA3+D3+H3+T3) - (H1+H2+H3+H4) = 2400
1,500.00	=	1,500.00		(LA4+D4+H4+T4) - (T1+T2+T3+T4) = 1500
0.00	<=	2,400.00		H1+H2+H3+H4<=LA3+D3+H3+T3
0.00	<=	1,500.00		T1+T2+T3+T4<=LA4+D4+H4+T4

a. Total cost = $420,500

b. 1100 cars are shipped from LA to Houston

c. 0 cars are shipped from LA to Tampa

d. 1300 cars are shipped from Detroit to Houston

e. 1500 cars are shipped from Detroit to Tampa

Excel image (2 images):

Thank you for asking. Please don't forget to hit like button...!!