OR / MS - Optimization Problem needs to be solved in excel spreadsheet / SOLVN.

CHALKS AIRLINE

Regular Season Scenario

Chalkies Airlines flies a fleet of seaplanes between Miami and the Bahamas. It has requirements for between 8 and 18 agents depending on the time of day. It has most flights scheduled in the middle of the day because toursists dont like to get up early and like to get to their destination by miller time. The table below indicates the agents needed at various times during the day:

Time period	Agents reqd
9:00-10:00	10
10:00-11:00	12
11:00-12:00	14
12:00-13:00	16
13:00-14:00	18
14:00-15:00	17
15:00-16:00	15
16:00-17:00	10

The airline currently has 12 full time agents and a roster of part-timers. A part timer must put in 4 hours a day and can start on the hour between 9:00 and 13:00. Full-timers work from 9:00 to 17:00. Half of them eat lunch from 11:00-12:00 and the other half from 12:00-1:00. The airline limits part-timers to a maximum of 50% of the days total requirements and they earn $6 per hour. Full-timers earn $75 per day in salary and fringes. The airline would like to set a schedule that minimizes its total labor costs and is willing to reduce the number of full-timers if economically justified.

Off Season Scenario

Like all most tourist dependent businesses, Chalks volume is highly seasonal. They have just completed the following forecast for hourly agent requirements (starting at 9:00): 5, 6, 7, 8, 9, 8, 15, and 5. Using the same initial set of scheduling rules as during the season, the airline would like to set a schedule that minimizes its total labor costs. (Note that the agent requirements for 15:00 16:00 remained constant because that flight goes to the year around popular destination of Margaritaville which attracts tourists through their Sirius-XM radio station using the theme Changes in latitudes, changes in attitudes. Tourists go there to look for their lost shaker of salt, consume the native cheeseburgers, and to live by the national anthem Lets get drunk and).

A new VP of Operations decided that, in her opinion, there really was no reason to have more full time than part-time employees. She decided that she could manage with a minimum 4 full time employees as long as they split when to take the lunch break between them (1/2 at 11:00 and at 12:00). Of course, if it made financial sense she would accept more than 4.

HINTS ON HOW TO HANDLE FULL TIME EMPLOYEES AND BREAKS FOR CHALK'S AIRLINE PROBLEM (STILL NEED TO SOLVE FOR PART TIME)

Let

F

number of full time employees you want to have (according to the LP solution, not those that you actually have)

F12

number of employees that take their lunch break at 12 (and hence are available to work at 11)

F11

number of employees that take their lunch break at 11 (and hence are available to work at 12)

Then

F = F12 + F11

this makes sure that all fulltime employees get their lunch break

(X9 + X10 + X11 + X12 + X13) <= F

this makes sure that the number of part time employees is less than half of the full time employees

-1 <= F11- F12 <=1

Sends an equal number on lunch break at 11 and at 12 UNLESS F is an odd number, in which case an even number go to lunch at one hour and the remaining the next hour

(this allows F to be an odd number (note that if you use F11 = F12, you are forcing F to be an even number)

NOTE that before using these, you have to get all variables to the left side of the inequality. You cannot have variables in the RHS column.