Project. Workforce Planning

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 different numbers of customers needed service. The table below indicates the number of workers eeded at various hours that the bank is open.

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

Full-time Employees: The bank currently has employs 14 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, many are available, to cover the additional tellers needed. A part-time employee will work exactly 4 hours per day, but can be scheduled to start at any time between 9am and 1pm

Hourly pay

full-timers will be paid $120 per day($15 per hour) in salary and benefits on average.

Part-timers will be paid $40 per 4-hour shift (10 per hour) 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.) no lunch for part timers

Corporate policy: The bank limits part-time hours to a maximum of 40% of the days 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.

a. summarize the optimal decision on the number of full time employees to use, the number of part time employees of different 4 hour ranges to hire, and the number of full timers' lunch schedule that minimize the total cost.

Part II. Use the Answer Report and Sensitivity Report of the solver outputs to answer the following questions. (Note: It is required that we support our answer by referring your answer to the exact place in the Answer Report and Sensitivity Reports. It is not allowed to find the answers to questions by solving the LP problems by changing the parameters again and again).

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

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

iii)If the required number of employees in period 10:00-11:00am is Increased to 14, 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 increased by 2, what can you say about the optimal total costs, and optimal decision on Full and Part time employees?

v) if the required number of employees in period 11:00-12:00am is changed to 11, what can you say about the optimal total cost, and optimal decision on Full and Part time employees?

*Show solution formulas as well

Please show solution using excel