Question

USE EXCEL TO SOLVE

The Sav-Us Rental Car Agency has six lots in Nashville, and it wants to have a certain number of cars available at each lot at the beginning of each day for local rental. The agency would like a model it could quickly solve at the end of each day that would tell it how to redistribute the cars among the six lots in the minimum total time. The times required to travel between the six lots are as follows:

	To (min.)
From	1	2	3	4	5	6
1	—	12	17	18	10	20
2	14	—	10	19	16	15
3	14	10	—	12	8	9
4	8	16	14	—	12	15
5	11	21	16	18	—	10
6	24	12	9	17	15	—

The agency would like the following number of cars at each lot at the end of the day. Also shown is the number of available cars at each lot at the end of a particular day. Determine the optimal reallocation of rental cars using the computer.

	Lot (cars)
Cars	1	2	3	4	5	6
Available	37	20	14	26	40	28
Desired	30	25	20	40	30	20

Answer 1

Time Matrix (time taken to move)

Movement Matrix:

The Moved should not exceed the available.

The available should be equal to the desired

Total time should be minimized

Solver Parameters:

Optimal Solution:

	Time Matrix	To
		1	2	3	4	5	6
From	1	30	5	0	2	0	0
2	0	20	0	0	0	0
3	0	0	14	0	0	0
4	0	0	0	26	0	0
5	0	0	0	10	30	0
6	0	0	6	2	0	20

Reallocations are:

From 1 we will keep 30 cars in 1, will move 5 cars to 2 and will move 2 cars to 4.

From 2 we will keep 20 cars in 2.

From 3 we will keep 14 cars in 3.

From 4 we will keep all available 26 cars in 4.

From 5, we will move 10 cars to 4 and will keep 30 cars in 5.

From 6, we will move 6 cars to 3, 2 cars to 4 and will jeep 20 cars in 6.

So, the optimal minimized time taken is: 364 min.

********

IF YOU HAVE ANY DOUBT, KINDLY COMMENT. I WOULD LOVE TO HELP YOU!!!

IF YOU LIKE THE ANSWER, PLEASE GIVE AN UP-VOTE OR THUMB UP. THIS WILL ENCOURAGE ME TO ANSWER MORE!!