-IF POSSIBLE, PLEASE SHOW THIS IN EXCEL. I NEED TO SEE THE SCREENSHOTS AND WORK IN EXCEL TO BE ABLE TO UNDERSTAND PLEASE!!!!

Mexico City Bank of Commerce and Industry at a specific location is a busy bank branch. Due to different number of customers need services at different times, the branch manager would like to assign different numbers of tellers at the counter to optimize the overall daily operation costs. The table below indicates the number of bank tellers needed at different hours of the day at one of its branches.

Time: 9am-10am, 10am-11am, 11am-Noon, Noon-1pm, 1pm-2pm, 2-m-3pm, 3pm-4pm, 4pm-5pm

# of Tellers Needed: 10, 14, 18, 16, 20, 16, 14, 12

Employees. The branch will use two different types of tellers: Full-time Tellers and Part-time Tellers. The following is a summary of the info.

Full-time Employees: Full-time tellers work from 9 a.m. to 5 p.m. Each Full-time Employee will be paid $100 per day on average. The bank branch currently has employed 18 Full-time Employees and will not hire more in the near future. However, the manager may use only a portion of the Full-Time Employees in the Branch and relocate the rest to other branches. Relocation of each employee will cost the branch $10 per day. That is, the cost of each relocated employee to the branch is $10 a day, instead of $100 a day. Each Full-time employee will have one-hour lunchtime break. To effectively use the workforce, the manager will divide the Full-time employees in this branch into 2 groups. Group 1 will be assigned lunchtime between 11 am-noon, and the reminding Group 2 will be assigned lunchtime between noon-1 pm

Part-time Employee: The bank branch will hire Part-time Employees, many are available, to cover part of the tellers needed. Each part-time employee will work for one of the 4-hour shifts with the shift starting time from 9 am, 10 am, 11 am, 12 pm, and 1 pm. Each part-timer will be paid $48 per 4-hour shift ($12 per hour) on average. There is no lunchtime for part-timers.

In addition, the bank limits part-time hours to a maximum of 40% of the daily labor hours. The bank manager needs to make the best decisions on

1) The number of full-time employees to be used in the branch

2) The lunch schedule for the full-time employees working in the bank branch

3) The number of part-time employees to hire for Each 4-hour shift, so that the total operating costs are minimized.

You can use the Linear Programming method learned to help the manager to make the optimal decision. More specifically, we need to complete the following

Part 1. Formulate the problem into a Linear Programming model. Note: In order to answer questions in Part 3 b i) and b ii), you need to define the Number of Full time Employees to relocate to the other branches as a decision variable.

Part 2. Solve the Linear Programming using Excel Solver.

Part 3. Use the Solver Output reports answering the following questions. Summarize your optimal decision on

1) the number of Full-time Employees to be kept in the Branch

2) the number of Full-time Employees to schedule for lunch during 11-12 pm, and 12-1 pm

3) the number of part-time employees to be hired for each of the 4-hour shifts

4) the total workforce-related costs each day, including the relocation cost. Remark: Since we use the Linear Programming model to make the workforce scheduling decision, the optimal solution (decision) from the solver may not be Integers (should be integers in real applications). We assume that we will accept non-integer solution. That is, we will not require decision variables to be integers in our LP formulation and solver setup. Otherwise, we will not able to answer questions in

b). Use the Answer the Sensitivity Reports to answer the following questions. (Note Again: the questions should be answered ONLY using the information from the Answer Report and Sensitivity Report. It is not allowed to answer the questions by Re-solving the LP models with updated parameters again and again. It is required that you explain how you come with your answer by referring to the exact location in the Reports).

1) If the relocation cost is Reduced by $5, will the optimal decision remain the same? What will be the optimal objective costs now? Explain.

2) If the relocation cost is Increased by $20, will the optimal decision remain the same? Explain.

3) If the required number of employees in 11:00-12:00 pm is Reduced by 2 Tellers, how much will this save on workforce-related costs? Will this Reduction in the required number of Tellers lead to a change in the optimal decision?

4) If the required number of employees in 11:00-12:00 pm is Reduced to 14 Tellers, what may happen to the optimal decision and optimal hiring costs?