The Alpha Airlines passenger service director is trying to decide how many new ones flight attendants to recruit and train in the next six months. Requirements in time flights are:
The problem is complicated by two main factors. The education of new air hostesses takes a month before they can be used on normal flights. Hence, recruitment should be done one month before the need arises. Also, the training of new air hostesses takes time from already trained flight attendants. It takes about 100 hours of regular flight from every practitioner in the educational season.
That is, by the number of flight times available for each regular flight attendant are deducted 100 hours spent on overseeing a practitioner in the month of training for the latter. However, since the hours of the practitioner are taken into account in total hours provided, in fact, there is no reduction in hours provided by the trained flight attendant.
The passenger service manager is not worried about January because there are 70 flight attendants available. The company's rules require flight attendants not to work over 150 hours a month. This means that the manager has in his possession for January 10,500 hours (1,500 hours more than required). The flight attendants are not dismissed in this case, they just work fewer hours. The company's records showed that 10% of the flight attendants resign for several reasons at the end of each month, while more than 50 flight attendants can not be trained per month.
For the company, the cost for a trained flight attendant is 1,500 € per month (salary and extra bonuses), no matter how many hours worked (of course the hours cannot be over 150). The cost of each trainee is 800 € per month.
SOLUTION:
So, finally I ended up with a solution.My mathematical-modeling approach for this problem is:
1)Definition of variables
- $X_{i1}=$The number of trainee flight attendants in month $i$,$i=1,2,3,4,5$
- $X_{i2}=$The number of educated flight attendants in month $i,i=1,2,3,4,5$
2)Objective function
It's a minimize problem so, we'll have:
- $Minc=Z=1500 \cdot \sum_{i=1}^{6} X_{i1}+ 800 \cdot \sum_{i=1}^{6} X_{i2}$
3)S.b.t(Constraints)
- Available flight attendants in January
$X_{12}=70$
- Max education of new air hostesses every month
$X_{i1}\leq 50, i=1,2,3,4,5$
- Min number of flight attendants concerning the demanding hours of flight
$150 \cdot x_{12} \geq 9000$
$150 \cdot x_{22} \geq 8000$
$150 \cdot x_{32} \geq 9000$
$150 \cdot x_{42} \geq 11000$
$150 \cdot x_{52} \geq 10000$
$150 \cdot x_{62} \geq 13000$
- Non-negativity Constraints
$X_{i1} \geq 0, i=1,2,3,4,5,6$
$X_{i2} \geq 0, i=1,2,3,4,5,6$
- Resignation Constraint(per month)
$X_{i2}=0.9 \cdot X_{(i-1),2},i=1,2,3,4,5,6$
QUESTION:
Is my approach right? What I have to do so to solve this problem in Excel using the solver solution? I'll use a non-linear or simplex LP solving method and why? (We only know about simplex LP solution so far)
I would really appreciate a thorough solution and explanation for this problem (in excel, using solver),since I don't know even how to start. Thanks,in advance!