Assignment Overview:

Read the $$Case Study $-$ Staffing a Call Center$$ provided in the attached PDF$.$

Based on the information in the case study, answer the following questions:

Question $1$Course Title: Supply Chain Analytics

PGP$-$BL $04-$ End Term Examination

Case Study $-$ Staffing a Call Center

The Hospital has been receiving numerous customer complaints because of its confusing,

decentralized appointment and registration process. To re$-$engineer and improve its

appointment and registration process, the hospital has decided to:

Centralize the process by establishing one call center devoted exclusively to

appointments and registration.

Operate this call center from $7$ a$.$m$.$ to $9$ p$.$m$.$ during the weekdays.

The hiring of a Consulting Firm

The hospital hired a consulting firm to assist in the planning process.

The consultants determined the number of calls at the call center by totaling the number of

appointment and registration$-$related calls received by all clinics and departments.

After visiting all the clinics, recording every call$-$related data, and considering various factors like

missed calls and repeat calls, the consultants forecasted the average number of calls the call

center should expect during each hour of a weekday. This forecast is mentioned in the table. The consultants also estimated that, on average, $20\%$ of the calls were from Spanish Speakers

and $80\%$ from English.

Staffing the Call Center

Given these forecasts, the hospital manager wants to decide how to staff the call center during

each $2-$hour shift on a weekday.

The consultants estimated that an operator could process an average of six calls per hour. full$-$

time $($FT$)$ and part$-$time $($PT$)$ operators are available to staff the call center.

Full$-$time $($FT$)$ Employees:

Work $8$ hours per day, but because of compulsory paperwork, they spend only $4$ hours

per day on the phone.

Alternate the $2-$hour shifts between answering phones and completing paperwork $-$ to

balance the work.

Can start their day by answering phones or completing paperwork on the first shift.

Can begin work at the beginning of the $7$:$00-9$:$00$ a$.$m$.$ shift, $9$:$00-11$:$00$ a$.$m$.$ shift, $11$:$00$

a$.$m$.-1$:$00$ p$.$m$.$ shift, or $1$:$00-3$:$00$ p$.$m$.$ shift.

Can speak Spanish or English, but none are bilingual. Part$-$time $($FT$)$ Employees:

Work for $4$ hours.

Only answer calls.

Only speak English.

Can start work at the beginning of the $3$:$00-5$:$00$ p$.$m$.$ shift or the $5$:$00-7$:$00$ p$.$m$.$

shift.

Payment Considerations:

All operators, without any discrimination, are paid $$10$ per hour for work before $5$ p$.$m$.$ and $$13$

per hour for work after $5$ p$.$m$.$

For the given analysis, consider only the labor cost for the time employees spend answering

phones. The cost for paperwork time is charged to other cost centers.

How many Spanish$-$speaking and English$-$speaking operators do the hospital need to staff the call center during each $2-$hour shift of the day so that the total labor cost is minimum? Provide an integer number since half a human operator makes no sense. $(15$ marks$)$

Question $2$

Suppose the average number of calls per hour increases to $50($from $35)$ for the shift $5$:$00-7$:$00$ p$.$m$.$ What is the new optimal staffing plan? How many Spanish$-$speaking and English$-$speaking operators are required during each $2-$hour shift of the day? $(5$ marks$)$

Note: Solve both questions using Excel Solver and interpret the solution obtained.

The data and solution for each of the two questions are to be depicted in two different sheets of an MS Excel workbook. Based on the question, each of the two sheets should include the following:

$$ Data and input parameters, as applicable

$$ Mathematical program of the problem including objective function and constraints variables

$$ Derivation of decision variable, using relevant formulae

$$ Formula to derive the objective function

$$ Solution of the problem using Excel Solver

Assignment Deliverables:

As the deliverable of the assignment, you need to submit an MS Excel Workbook, which includes the following three worksheets:

$1.$ Worksheet $1$ Name of the Team Members

$2.$ Worksheet $2$ Data and solution based on Question $1$

$3.$ Worksheet $3$ Data and solution based on Question $2$

Summary of Review Criteria

The data and solution for each of the two questions are to be depicted in two different sheets of an MS Excel workbook. Your submission will be graded based on the following criteria.

$$ Relevance and correctness of data and input parameters, as applicable.

$$ Correct depiction of Mathematical Model, including objective function and constraints.

$$ Definition and derivation of decision variable, using relevant formulae.

$$ Correct formula to derive the objective function.

$$ Solution of the problem using Excel Solver.

$$ Correct settings of the parameters in the Solver Parameters dialog box.

$$ Correct derivation of the solution.

please sharre detailed calculation along with excel solver