Question

Manufacturing and Distribution

Your company manufactures commercial security locks at four factories in Macon, Louisville, Detroit, and Phoenix. You have seven wholesale distributors in seven cities across the country. The costs of shipping a lock from a factory to a distributor is shown in the following table:

Shipping Costs		(Dollars per unit)
Plants	Tacoma	San Diego	Dallas	Denver	St. Louis	Tampa	Baltimore
Macon	2.5	2.75	1.75	2	2.1	1.8	1.65
Louisville	1.85	1.9	1.5	1.6	1	1.9	1.85
Detroit	2.3	2.25	1.85	1.25	1.5	2.25	2
Phoenix	1.9	0.9	1.6	1.75	2	2.5	2.65

The anticipated demand at each distributor for the next year is as follows:

Plants	Tacoma	San Diego	Dallas	Denver	St. Louis	Tampa	Baltimore
DEMAND:	8500	14500	13500	12600	18000	15000	9000

The costs (in dollars) of manufacturing a lock at each location, and the annual capacities of each factor, are as follows:

Plants	Capacity	Cost/Unit
Macon	18000	35.5
Louisville	15000	37.5
Detroit	25000	39
Phoenix	20000	36.25

You know you will not fill all of your orders, but you would like to fill at least 80% of the demand at each location. How many locks should be manufactured at each location, and where should they be shipped?

If there is any unused capacity at any plant, how should that best be used and distributed?

Please show/explain using excel solver

Answer 1

The Excel setup is:

The formulas are:

The solver parameters are:

The solution is:

Plants	Tacoma	San Diego	Dallas	Denver	St. Louis	Tampa	Baltimore	Capacity
Macon	2.5	2.75	1.75	2	2.1	1.8	1.65	18000
Louisville	1.85	1.9	1.5	1.6	1	1.9	1.85	15000
Detroit	2.3	2.25	1.85	1.25	1.5	2.25	2	25000
Phoenix	1.9	0.9	1.6	1.75	2	2.5	2.65	20000
Demand	8500	14500	13500	12600	18000	15000	9000
Plants	Tacoma	San Diego	Dallas	Denver	St. Louis	Tampa	Baltimore	Capacity	Cost per unit
Macon	0	0	0	0	0	12000	6000	18000	35.5
Louisville	0	0	600	0	14400	0	5.68E-14	15000	37.5
Detroit	0	0	8600	10080	4.55E-13	0	1200	19880	39
Phoenix	6800	11600	1600	0	0	0	0	20000	36.25
Demand	6800	11600	10800	10080	14400	12000	7200
	>=	>=	>=	>=	>=	>=	>=
Atleast 80% condition	6800	11600	10800	10080	14400	12000	7200
Transportation cost	103630
Production cost	2701820
Total cost	2805450

Total cost = $2805450