Question

The following is an Excel question:

We have three factories (A, B, C) and five distributors (P, Q, R, S, T) of our products.
The products are shipped by truck from factory to distributor. At most 180 units of our
products fit a truck. The shipping cost is $1 per unit to load the truck and $1 per unit to
unload the truck plus the mileage cost. The mileage cost is based on the mileage the
truck must travel between the factory and the distributor. The mileage cost of $15 per
mile is independent of the number of units of products in the truck. The maximum
monthly productions of the factories are: A 4000 units, B 2000 units, and C 1500 units.
The monthly demands by the distributors are: P 1000 units, Q 1600 units, R 1500 units, S
2000 units, and T 1400 units. The mileage chart of the distances between factories and
distributors is:

	Distributors (Mileage Chart)
Factories	P	Q	R	S	T
A	100	140	200	150	35
B	50	65	60	70	80
C	40	150	100	90	130

Use Solver to determine the number of units to send each month from each factory to
each distributor so as to minimize the total shipping costs. In summary, your worksheet should
show:

(i) Number of units to send each month from each factory to each distributor so as to minimize the total shipping costs

(ii) Also show the Number of trucks to send from each factory to each distributor and total shipping cost.

(iii) Note that to find the optimal solution depends heavily on starting a good initial solution, so please test your model thoroughly.

Show ALL FORMULAS used in getting the answer.

Answer 1

The Excel Solver output is:

The Solver Parameters are:

The formulas used are:

The solution is highlighted in yellow colour.

	Distributors (Mileage Chart)
Factories	P	Q	R	S	T	Supply
A	100	140	200	150	35	4000
B	50	65	60	70	80	2000
C	40	150	100	90	130	1500
Demand	1000	1600	1500	2000	1400
	Distributors (Mileage Chart)
Factories	P	Q	R	S	T	Supply
A	1000	1600	0	0	1400	4000
B	0	0	1500	500	0	2000
C	0	0	0	1500	0	1500
Demand	1000	1600	1500	2000	1400
Total cost	633000