troubleshooting Question

Optimization Problem (via Excel Solver)

Avatar of ExpExchHelp
ExpExchHelpFlag for United States of America asked on
Math / SciencePuzzles / RiddlesMicrosoft Excel
19 Comments1 Solution1214 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 19 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros