Spreadsheet: 675872d1588530419-optimize-freight-distribution-by-priority-distance-and-mass-yield-freight-example-increased-complexity-
Sheet: LOGISTICS PLANNING

PRIORITY | FREIGHT | TONS |  | A LOGICAL SOLUTION:
 | JOB 1 |  |  | TRUCK 1 | TRUCK 2 | TRUCK 3 | TRUCK 4 | TRUCK 5 | TRUCK 6 | TRUCK 7 | TRUCK 8 | TRUCK 9 | TRUCK 10 | TRUCK 11
 | JOB 2 | 3.2 |  | JOB 1 (100%) | JOB 16 (46.37%) | JOB 16 (46.37%) | JOB 16 (7.26%) | JOB 13 (79.36%) | JOB 13 (20.64%) | JOB 11 (100%) | JOB 6 (46.90%) | JOB 7 (100%) | JOB 18 (92.07%) | JOB 12 (95.60%)
 | JOB 3 | 4.5 |  | JOB 2 (100%) |  |  | JOB 17 (100%) |  | JOB 4 (100%) | JOB 6 (53.10%) | JOB 20 (100%) | JOB 8 (100%) | JOB 19 (100%) | JOB 15 (100%)
 | JOB 4 | 18 |  | JOB 3 (100%) |  |  |  |  | JOB 10 (100%) |  | JOB 9 (100%) | JOB 18 (7.93%)
 | JOB 5 |  |  | JOB 14 (100%) |  |  |  |  | JOB 5 (100%) |  |  | JOB 12 (4.40%)
 | JOB 6 | 14.5
 | JOB 7
 | JOB 8 |  |  | SIDE NOTE: OUR LOGISTICS COMPANY IS A LITTLE CARELESS, AND TENDS TO GET THE DELIVERIES MIXED UP. IT IS BEST TO KEEP THE NUMBER OF JOBS ON THE TRUCKS TO A MINIMUM…
 | JOB 9 |  |  | SO THE COLUMN'S MARKED IN GREEN ARE A BIT OF A PROBLEM. IT WOULD BE BEST IF WE COULD KEEP THE NUMBER OF JOB'S PER TRUCK TO LESS THAN 3. THIS HOWEVER, IS LESS
 | JOB 10 |  |  | IMPORTANT THAN ACHIEVING A HIGHER % YIELD (>90%). AS YOU CAN SEE, TRUCK 9 IS A PROBLEM.
 | JOB 11
 | JOB 12
 | JOB 13
 | JOB 14 |  |  | INCREASE IN COMPLEXITY: THE PROBLEM OF LOAD DISTRIBUTION HAS BEEN RESOLVED, WITH AN ELEGANT, OPTIMAL EXCEL SOLUTION. (ALL THANKS TO HYDRAULICS)
 | JOB 15 |  |  | ONE MIGHT NOTICE THE NEW SHEET, LABELLED "DISTANCES (KM)" ATTACHED TO THIS WORKBOOK. ONE MAY ALSO NOTICE THE NEW COLUMN,
 | JOB 16 |  |  | ADDED TO THE INFORMATION TABLE ON THE LEFT LABELLED  "PRIORITY".
 | JOB 17
 | JOB 18 |  |  | [NAME] THE TRUCKS ARE TO BE LOADED IN ORDER OF PRIORITY. ERGO, TRUCK 1, MIGHT ONLY CONTAIN PRIORITY 10 JOBS,
 | JOB 19 |  |  | TRUCK 2 MIGHT CONTAIN SOME OF THE REMAINING PRIORITY 10 JOBS, WITH SOME PRIORITY 9 JOBS ETC… BUT… THE MINIMUM DISTANCE
 | JOB 20 |  |  | BETWEEN JOB DELIVERIES SHOULD ALSO BE ACHEIVED. THIS IS A TYPICAL "TRAVELLING SALESMAN" PROBLEM, BUT IS A LITTLE MORE COMPLEX
 | TOTAL: |  |  | (ALL THINGS CONSIDERED)
 | TRUCKS:
 |  |  |  | IN SUMMARY:
 | tons/truck | 30 |  | TRUCK LOAD DISTRIBUTION SHOULD BE OPTIMIZED IN TERMS OF (IN DESCENDING ORDER OF RELAVENCE):
 | percentage | 0.9 |  | 1)   PRIORITY
 | num jobs | 3 |  | 2)   DISTANCE BETWEEN JOB DELIVERY POINTS
 | job limit | 1 |  | 3)   YIELD
 |  |  |  | OPTIMIZED EXCEL SOLUTION
 |  |  |  | TRUCK 1 | TRUCK 2 | TRUCK 3 | TRUCK 4 | TRUCK 5 | TRUCK 6 | TRUCK 7 | TRUCK 8 | TRUCK 9 | TRUCK 10 | TRUCK 11
 |  | 12.6 | JOB 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |  |  | 1
 |  | 3.2 | JOB 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  |  | 1
 |  | 4.5 | JOB 3 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |  |  | 1
 |  | 18 | JOB 4 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  |  | 1
 |  | 0.8 | JOB 5 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |  |  | 1
 |  | 14.5 | JOB 6 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |  |  | 1
 |  | 12.9 | JOB 7 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |  |  | 1
 |  | 11.1 | JOB 8 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  |  | 1
 |  | 8.5 | JOB 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |  |  | 1
 |  | 1.4 | JOB 10 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  |  | 1
 |  | 22.3 | JOB 11 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  |  | 1
 |  | 15.9 | JOB 12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |  |  | 1
 |  | 37.8 | JOB 13 | 0.70899471 | 0 | 0 | 0 | 0 | 0 | 0 | 0.29100529 | 0 | 0 | 0 |  |  | 1
 |  | 9.5 | JOB 14 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |  |  | 1
 |  | 14.8 | JOB 15 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  |  | 1
 |  | 64.7 | JOB 16 | 0 | 0.11901082 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.46367852 | 0.41731066 |  |  | 1
 |  | 23 | JOB 17 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |  |  | 1
 |  | 16.4 | JOB 18 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |  |  | 1
 |  | 14.9 | JOB 19 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |  |  | 1
 |  | 12.4 | JOB 20 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  |  | 1
 |  |  | JOB 13 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |  |  | # parts
 |  |  | JOB 16 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1
 |  |  | constr 13
 |  |  | constr 16
 |  |  | lowest
 |  |  | highest | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
 |  |  | #jobs