Mexico City Bank of Commerce and Industry is a busy bank that needs different number of tellers depending on the time of day due to customer. The table below indicates the workers needed at various hours that the bank is open.
 

Time Period	# of Tellers Needed	Time Period	# of Tellers Needed
9 a.m.–10 a.m.	8	1 p.m.–2 p.m.	18
10 a.m.–11 a.m.	12	2 p.m.–3 p.m.	17
11 a.m.–Noon	14	3 p.m.–4 p.m.	15
Noon–1 p.m.	16	4 p.m.–5 p.m.	10

Full-time Employees:
The bank currently has employs 12 full-time tellers right now that work from 9 a.m. to 5 p.m.

Part-time Employee:  
The bank plans to hire Part-time Employees to cover the additional tellers needed.  Many people are available for part-time employees. A part-time employee will work exactly 4 hours per day, but can be scheduled to start at any time between 9 a.m. and 1 p.m.

Hourly pay

full-timers will be paid $80 per day ($10 per hour) in salary and benefits on average

Part-timers will be paid $25 per day (4 hour shift) on average

Lunch time: 
Full-timers are divided into 2 groups for 1 lunch time. (The 1^st group Full-timers eat between 11-noon., the rest (2^nd group) of the Full-timers eat between noon-1pm.)

Corporate policy:
The bank limits part-time hours to a maximum of 40% of the day’s total requirement.

It may release one or more of its full-time tellers to the other department if it is profitable to do so. However, each full time teller reallocation will cost the Bank $10 a day during the planning periods.

The bank would like to set a labor hiring schedule (full time and part time) that would minimize its total manpower costs. Linear Programming can be used to help the bank make the decision.

Tasks:

Part I. You need to do the followings

i)Formulate the problem into a Linear Programming model.

ii)Solve the Linear Programming using Excel solver.

iii)Save the Answer Report and Sensitivity Report of the solver outputs.

iv)Summarize the optimal decision on number of Full time employees to hire and the number of part time employees to hire at different time periods, the total manpower costs, etc. from the LP solver solution.

Part II.  Use the Answer Report and Sensitivity Report of the solver outputs to answer the following questions.

i) If the per pay for the Full-time employee is Reduced by $5, will the optimal decision remain same? What will be the corresponding optimal total costs now?

ii) Will the optimal total costs change if the pay for the Full-time employee is Reduced by $25 a day? Explain your answer?

iii) If the required number of employees in period 10:00-11:00am is Increased to 15, what will be the optimal total costs? Do you think the optimal decision on Full-time and Part-time employees will change?

iv) If the required number of employees in period 10:00-11:00am is Decreased by 2, what can you say about the optimal total costs, and optimal decision on Full and Part time employees?