Excel Solver Airplane Scheduling

eyobzeleke

New Member
Joined
Mar 19, 2019
Messages
4
We have let's say 10 private planes that we use to transport customers. Each customer has their own departure and arrival airports. I am trying to see if Excel Solver can help me decide which plane to use for any of the customers based upon closeness and meeting the departure/arrival time in each case. It doesn't make sense to reposition a plane to a far customer site when I can use another plane that just landed to a closer city. So to elaborate this a little more let's say Plane A landed in Columbus, OH and I have a customer that needs to be picked up in Cleveland, OH in about 2 hours. I would need the solver to tell me Plane A is my best choice based upon the closeness of Columbus, OH and Cleveland, OH. Can someone give me an example of this sort with departure time constraints? I have an example excel document but couldn't attach it here. Please let me know.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,010
Welcome to the Board


  • What Excel version are you using? Do you already have some setup to calculate distance between cities?
  • Maybe this task can be accomplished only with formulas, without using Solver.
  • To share a document, upload it to a hosting site e.g. Drop Box and paste a link here.
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,010

ADVERTISEMENT

Please confirm that my assumptions are correct:


  • One input for the problem is a list of available planes, with their respective locations.
  • Another input is the current time of day.
  • The departure timetable is fixed. If there are no more planes departing from a certain location on that day, will the customer stay overnight?
  • Another input is a list with the desired origin-destination legs for each customer.
  • There is something called the fleet assignment problem, I am trying to determine if this is the case.
  • Can you provide sample input data for the problem?
  • To use Solver, we will need to define an objective function to minimise or maximise.
 

eyobzeleke

New Member
Joined
Mar 19, 2019
Messages
4
· One input for the problem is a list of available planes, with their respective locations. YES
· Another input is the current time of day. YES
· The departure timetable is fixed. If there are no more planes departing from a certain location on that day, will the customer stay overnight? No, we have to provide a plane if customer has already book even if it means we have to reposition a plane from a distance location. Assume that we’ll never be overbooked. But keep in mind we want the closest available plane to reposition.
· Another input is a list with the desired origin-destination legs for each customer. YES
· There is something called the fleet assignment problem, I am trying to determine if this is the case. Yes it’s a similar scenario and I found an excel example online (http://people.bath.ac.uk/ge277/index.php/vrp-spreadsheet-solver/) for a VPR but it doesn’t do what I want.
· Can you provide sample input data for the problem?
https://www.dropbox.com/s/302detu3qqkd938/sampledata.xlsx?dl=0
· To use Solver, we will need to define an objective function to minimize or maximize.

  • Refer to the attachment worksheets. The first worksheet has flight sample data. The 2nd worksheet has the list of Jets with their respective locations. There are 7 planes listed. We don’t have to use them all for this trip. How do we optimize the usage of planes? Minimum number of planes is one goal. And the ones to be used have to be optimized for shortest distance so fuel cost would be minimized. All of these in keeping the departure times intact.
Any inputs are appreciated!
Thank you!
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,010

  • You did not provide a task example; please confirm if my sample task is correct.

  • Question: can a customer fly multiple legs?
  • My current idea is to use a mix of VBA and Solver to do it.
  • Maybe the first solution step should be to reposition planes if there is a stranded customer.

 

Watch MrExcel Video

Forum statistics

Threads
1,108,994
Messages
5,526,125
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top