# Excel Solver Airplane Scheduling

#### eyobzeleke

##### New Member
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.

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

> how?
>

#### eyobzeleke

##### New Member
> 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

##### Well-known Member

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
· 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

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

Replies
0
Views
3K
Replies
9
Views
17K
Replies
1
Views
2K
Replies
0
Views
351
Replies
7
Views
4K