Question

Assign trucks to delivery routes so that total costs are minimized, given the cost data shown. What is the total cost? (Leave no cells blank - be certain to enter "0" wherever required. Omit the "$" sign in your response.)

		ROUTE
		A	B	C	D	E
	1	4	5	9	8	7
	2	6	4	8	3	5
Truck	3	7	3	10	4	6
	4	5	2	5	5	8
	5	6	5	3	4	9

	Optimal Table
	A	B	C	D	E
1
2
3
4
5

Total cost           $ ______?

Answer 1

The problem is formulated in excel as below. The below 2 images show the formulation as well as the formulas used

A B C D G H I J L K Total Cost I A B E 1 E F ROUTE C D 5 9 4 3 10 21 51 6 WINI Truck 5 ROUTE в |с |р | 10 = 0 = Truck 31 0 = 0 = 0 = 0 0 0 0 0 IwN 1 1 1 1 1 1

в с т р ETFT отнт ROUTE E Total Cost =SUMPRODUCT(D3:H7,D12:H16) com 104 6 5 8 ROUTE C A B D E = 1 Truck =SUM(D12:H12) =SUM(D13:H13) =SUM(D14:H14) =SUM(D15:H15) -SUM(D16:H16) = 1 = 1 =SUM(D12:016) =SUM(E12:E16) =SUM(F12:F16) =SUM(G12:G16) SUM(H12:H16)

Post these the solver parameters need to be entered as per the below image.

Solver Parameters Set Objective: SLS1 TO: O Max Min Value Of: By Changing Variable Cells: SD$12:$H$16 Subject to the Constraints: $D$17:SH$17 = $D$19:SH$19 SI$12:$1$16 = $K$12:$K$16 Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Once this is done, click on solve. The solver will solve the problem and show the message stating the same. Click on ok. The excel will now have the final solution as per below image.

| | M LA | BI clD | E IF I G | H | | || K | ROUTE Total Cost A B IC D E Truck 3 | | | 4 3 21 10 | | 1 = 1 이 ROUTE BC 1 이 | | 이 이 1 | | Truck D 이 이 이 이 1 1 | 11 | | 이 | 1 1 1 1 1 | = = = = | | 1 1 | 1 | | 1 | 1 1 11 11

Hence we can see that the total cost is 18 with below assignment

Truck 1 - A

Truck 2 - E

Truck 3 - D

Truck 4 - B

Truck 5 - C