Speedy Delivery provides two-day delivery service of large parcels across the United States. Each morning at each collection center, the parcels that have arrived overnight are loaded several trucks for delivery throughout the area. Since the competitive battlefield in this business is speed of delivery, the parcels are divided among the trucks according to their graphical destinations to minimize the average time needed to make the deliveries.
On this particular morning, the dispatcher for the Blue River Valley Collection Center, Sharon Lofton, is hard at work. Her three drivers will be arriving in less than an hour to make the deliveries. There are nine parcels to be delivered, all at locations many miles apart. As usual, Sharon has loaded these locations into her computer. She is using her company's special software package, a decision support system called Dispatcher. The first thing Dispatcher does is using these locations to generate a considerable number of attractive possible routes for the individual delivery trucks. These routes are shown in the following table (where the numbers in each column indicate the order of the deliveries), along with the estimated time required to traverse the route.
| Delivery |
Attractive Possible Route |
| Location |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
| A |
1 |
|
|
|
1 |
|
|
|
1 |
|
| B |
|
2 |
|
1 |
|
2 |
|
|
2 |
2 |
| C |
|
|
3 |
3 |
|
|
3 |
|
3 |
|
| D |
2 |
|
|
|
|
1 |
|
1 |
|
|
| E |
|
|
2 |
2 |
|
3 |
|
|
|
|
| F |
|
1 |
|
|
2 |
|
|
|
|
|
| G |
3 |
|
|
|
|
|
1 |
2 |
|
3 |
| H |
|
|
1 |
|
3 |
|
|
|
|
1 |
| I |
|
3 |
|
4 |
|
|
2 |
|
|
|
| Time (in hours) |
6 |
4 |
7 |
5 |
4 |
6 |
5 |
3 |
7 |
6 |
Dispatcher is an interactive system that shows these routes to Sharon for her approval or modification. (For example, the computer may not know that flooding has made a particular route infeasible.) After Sharon approves these routes as attractive possibilities with reasonable time estimates, Dispatcher next formulates and solves an optimization model for selecting three routes that minimize their total time while including each delivery location on exactly one route.
Unfortunately, the Dispatcher is down and Sharon is seeking your assistance in helping her set up an Excel spreadsheet to help her determine the routes.
(Hint: You do not need to generate the answer and sensitivity reports)