Excel Solver Airplane Scheduling

[eyobzeleke]

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.​

 

[Worf]

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.

 

[eyobzeleke]

> I am using office 365 latest version.
> how?
>

 

[eyobzeleke]

> I am using office 365 latest version
> how?
> https://www.dropbox.com/s/3glznzoqwaw45sg/Linear Programming.xlsx?dl=0
The example in the attachment is just a starting point. It is by no means a finished product. I need to include departure time for each flight and integrate it in constraints and so on. But not sure if it can be done. Any leads whether to continue with solver or formulas would be appreciated!

 

[Worf]

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]

· 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 2^nd 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]

• I am using a distance calculator from this page: https://www.contextures.com/excellatitudelongitude.html

• 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.

 

[Worf]

Chapters 12 and 13 of this book show how to deal with a flight assignment model using Excel Solver. That problem can probably be adapted to your specific case.
Are you in the operational research field? Would you consider buying it?

https://books.google.com.br/books?i...Q#v=onepage&q=excel flight assignment&f=false