Question
Retailer Staffing problem The campus store has decided to hire some students to work part time helping customers and performing various functions that used to
Retailer Staffing problem The campus store has decided to hire some students to work part time helping customers and performing various functions that used to be contracted out to outside vendors. There are two work shifts per day (AM and PM) and the store is open five days a week. Exactly one student must be assigned to each shift. To become sufficiently familiar with the stores operations, each student that is hired must work at least two shifts per week. The store has identified a pool of eight qualified students from which to hire. For each student, the store has estimated how much he or she would add to the stores profit, per shift worked. This information is shown in the table below. The table also marks each shift for which a particular student is available with a 1. Finally, it also gives the maximum number of shifts per week that each student can work. To maximize its profits, which students should the store hire and which shifts should each student work? Check the answer and layout: CM, GR, JE and SR work and maximum profit is 910.
Solve in Excel sheet and show all the steps along with formulas.
\begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline \multirow[t]{2}{*}{ Student } & \multirow[t]{2}{*}{\begin{tabular}{l} Profit \\ /Shift \\ ($) \end{tabular}} & \multicolumn{2}{|c|}{ Mon } & \multicolumn{2}{|l|}{ Tue } & \multicolumn{2}{|c|}{ Wed } & \multicolumn{2}{|c|}{ Thu } & \multicolumn{2}{|l|}{ Fri } & \multirow[t]{2}{*}{ Max shifts } \\ \hline & & AM & PM & AM & PM & AM & PM & AM & PM & AM & PM & \\ \hline CM & 100 & 1 & & & & 1 & 1 & 1 & & & & 3 \\ \hline FI & 50 & & 1 & & 1 & & 1 & & 1 & & 1 & 4 \\ \hline GR & 95 & & 1 & 1 & & & 1 & & 1 & 1 & & 2 \\ \hline HS & 75 & & & 1 & & & & 1 & & 1 & 1 & 2 \\ \hline JD & 70 & & & & & 1 & & & & 1 & & 2 \\ \hline JE & 90 & & & & & & & 1 & 1 & 1 & & 2 \\ \hline SR & 80 & 1 & & & 1 & 1 & & & 1 & 1 & 1 & 3 \\ \hline TR & 85 & & 1 & & & & 1 & & & & 1 & 2 \\ \hline \end{tabular} The yellow cells are the decisions. One set of decisions is whether a person is hired for the shift (cells D14:M21). Another decision is whether the person is hired or not (work or not). Total shifts worked is the sum of shifts worked by person (example: D14:M14). Total shifts worked must be less than (work or not * Max shifts). Total shifts worked must be greater than or equal to (work or not * Min Shifts). Finally, the column totals (example: D14:D21) must be equal to oneStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started