Question

1. The Childfair Company has three plants producing child push chairs that are to be shipped to four distribution centers. Plants 1, 2 and 3 produce 9, 19, and 12 shipments per month, respectively. Each distribution center needs to receive 10 shipments per month. The distance from each plant to the respective distribution centers is given below: Distance to Distribution Center (Miles)
1		2		3		4
Plant
1	650		1,300		750		700
2	1,100		1,400		800		1,100
3	600		1,200		800		600

The freight cost for each shipment is $73 plus 51 cents/mile. How much should be shipped from each plant to each of the distribution centers to minimize the total shipping cost? Formulate this problem as a transportation problem on a spreadsheet and then use the Excel Solver to obtain an optimal solution. Also, interpret the results using at least 150 words.

2. Consider a resource-allocation problem having the following data. Resource Usage per Unit of Each Activity
Resource	1		2		3		Amount of Resource Available
A B C	30 0 15		15 15 15		0 35 25		450 620 1180
Contribution per unit		$32		$48		$70

(a) Express this model in algebraic form for this problem using equations. In other words,

write up objective function, constraints, and decision variables. These equations should be written manually in a word document, not in Excel.

(b) Solve a linear programming model for this problem on a spreadsheet, and interpret the

results using at least 100 words. In other words, what are the optimal number of activity 1, 2, and 3, and what is the total profit at those activity levels?

Answer 1

Calculate the cost from each plant to each distribution center = 73 + Distance * 0.51

Resulting cost matrix is following:

	Distribution center
Plant	1	2	3	4
1	404.5	736	455.5	430
2	634	787	481	634
3	379	685	481	379

Spreadsheet model and solution using Excel Solver is following:

EXCEL FORMULAS:

	Distribution center
Plant	1	2	3	4	Capacity
1	404.5	736	455.5	430	9
2	634	787	481	634	19
3	379	685	481	379	12
Demand	10	10	10	10
	Optimal distribution plan
=A2	=B2	=C2	=D2	=E2	Sent
=A3	9	0	0	0	=SUM(B10:E10)
=A4	0	9	10	0	=SUM(B11:E11)
=A5	1	1	0	10	=SUM(B12:E12)
Received	=SUM(B10:B12)	=SUM(C10:C12)	=SUM(D10:D12)	=SUM(E10:E12)
Total Cost =	=SUMPRODUCT(B10:E12,B3:E5)

Optimal distribution plan:

The company must distribute the shipments as shown below:

	Optimal distribution plan
Plant	1	2	3	4
1	9	0	0	0
2	0	9	10	0
3	1	1	0	10

9 shipments must be sent from plant 1 to distribution center 1,

9 and 10 shipments must be sent from plant 2 to distribution center 2 and 3 respectively

1, 1 and 10 shipments must be sent from plant 3 to distribution center 1, 2 and 4 respectively.

Total cost = $ 20,388