Air Canada is adding more flights to and from its airport, and so it needs to hire

additional customer service agents. However, it is not clear just how many more should be

hired. Management recognizes the need for cost control while also consistently providing a

satisfactory level of service to customers. Therefore, a team is studying how to schedule

the agents to provide satisfactory service with the smallest personnel cost.

Based on the new schedule of Nights, an analysis has been made of the minimum number of

customer service agents that need to be on duty at different times of the day to provide a

satisfactory level of service. The rightmost column of Table shows the number of agents

needed for the time periods given in the first column. The other entries in this table reflect

one of the provisions in the companys current contract with the union that represents the

customer service agents. The provision is that each agent works 8-hour shift 5 days per

week, and the authorized shifts are

Shift 1: 6:00 A.M. to 2:00 P.M.

Shift 2: 8:00 A.M. to 4:00 P.M.

Shift 3: Noon to 8:00 P.M.

Shift 4: 4:00 RM. to midnight

Shift 5: 10:00 RM. to 6:00 A.M.

Checkmarks in the main body of Table show the hours covered by the respective shifts.

Because some shifts are less desirable than others, the wages specified in the contract differ

by shift. For each shift, the daily compensation (including benefits) for each agent is shown in

the bottom row. The problem is to determine how many agents should be assigned to the

respective shifts each day to minimize the total personnel cost for agents, based on this

bottom row, while meeting (or surpassing) the service requirements given in the rightmost

column.

Time Period	Time Periods Covered By Shift					Minimum Number Of Agents Needed
	1	2	3	4	5
6:00 A.M. to 8:00 A.M.						48
8:00 A.M. to 10:00 A.M.						79
10:00 A.M. to noon						65
Noon to 2:00 P.M.						87
2:00 P.M. to 4:00 P.M.						64
4:00 P.M. to 6:00 P.M.						73
6:00 P.M. to 8:00 P.M.						82
8:00 P.M. to 10:00 P.M.						43
10:00 P.M. to Midnight						52
Midnight to 6:00 A.M.						15
Daily Cost per agent	170	160	175	180	195

a) Formulate the decision problem as a LP and solve it using Excel Solver. (10 marks)

b) Discuss on the obtained results and make suggestions. For example, is there any

redundant constraints in your formulation? how sensitive is the optimal solution? (10

marks)

Please Solve On Your Own and DO NOT COPY from similar questions. Please share the excel formula sheet for the solution.