asked on
Optimization Problem (via Excel Solver)
I have an optimization problem that I'd like to solve.
Please see attached JPG and two (2) spreadsheets for more details/illustration.
Basically, here's the concept:
- For disaster relief I need to transport 752,000 lbs from a ship to any region ashore
- The means for transportation are unmanned aircraft (UAC)
- I have up to 41 UAC available... each of them can carry a load of 4,000 lbs.
- Thus, to accomplish the transportation of all material, I need 188 flights (752,000 / 4,000)
- All transportation must be completed within a 72 hour time window (or 4,320 minutes)
Optimization Goals:
- The objective is to MINIMIZE the number of UAC while achieving to transport all material (752k lbs) within the given time (4,320 minutes)
Other Information:
o Attached XLS (v01) indicates that I need at least 27 UAC. However, I kinda "cheated" by having hard-coded the constraint of <=7 flights per UAC... but that's ok for right now.
Additional Information that must be considered:
- There's an additional time factor of 15 minutes of "loading time" each time when the UAC returns to the ship.
- Currently, it is estimated that each trip will take 10 hours (600 minutes) per UAC. Adding the 15 minutes makes it 615 min. per trip per UAC. So, in XLS (v02), I tried to account for that by adding the time. However, the Excel Solver solution did NOT take into account that the UAC fly simultaneously.... one after each other in 15 minute increments
My questions:
1. On a piece of paper, I estimate that I need 31 UAC (vs. 27 UAC) in order to account for the 15 minutes of loading time. How can I achieve this solution given the two provided concept in the spreadsheets?
2. How can I remove the hard-coded constraint of <=7 flights per UAC? Ideally, the optimization problem will tell me that's what I need.
Optimization-Problem-v01.xls
Optimization-Problem-v02.xls
Visualization.jpg
Please see attached JPG and two (2) spreadsheets for more details/illustration.
Basically, here's the concept:
- For disaster relief I need to transport 752,000 lbs from a ship to any region ashore
- The means for transportation are unmanned aircraft (UAC)
- I have up to 41 UAC available... each of them can carry a load of 4,000 lbs.
- Thus, to accomplish the transportation of all material, I need 188 flights (752,000 / 4,000)
- All transportation must be completed within a 72 hour time window (or 4,320 minutes)
Optimization Goals:
- The objective is to MINIMIZE the number of UAC while achieving to transport all material (752k lbs) within the given time (4,320 minutes)
Other Information:
o Attached XLS (v01) indicates that I need at least 27 UAC. However, I kinda "cheated" by having hard-coded the constraint of <=7 flights per UAC... but that's ok for right now.
Additional Information that must be considered:
- There's an additional time factor of 15 minutes of "loading time" each time when the UAC returns to the ship.
- Currently, it is estimated that each trip will take 10 hours (600 minutes) per UAC. Adding the 15 minutes makes it 615 min. per trip per UAC. So, in XLS (v02), I tried to account for that by adding the time. However, the Excel Solver solution did NOT take into account that the UAC fly simultaneously.... one after each other in 15 minute increments
My questions:
1. On a piece of paper, I estimate that I need 31 UAC (vs. 27 UAC) in order to account for the 15 minutes of loading time. How can I achieve this solution given the two provided concept in the spreadsheets?
2. How can I remove the hard-coded constraint of <=7 flights per UAC? Ideally, the optimization problem will tell me that's what I need.
Optimization-Problem-v01.xls
Optimization-Problem-v02.xls
Visualization.jpg
Microsoft ExcelMath / SciencePuzzles / Riddles
Last Comment
Rowan Scott