Scheduling Problem

Suppose Polaris needs to ensure employee coverage for the following times between 7am and 4pm.
7am-8am=7; 8am-9am=7; 9am-10am=8; 10am-11am=8; 11am-12pm=9; 12pm-1pm

Employees available for these times include full-time workers, working 7am-4pm, and part time workers who can
7am-11am; 8am-12pm; 9am-1pm; 10am-2pm; 11am-

All full-time employees take their one-hour lunch break at 11am. To ensure part-time shifts can be covered, Pola
more than 3.

Determine the how many of each type of shift need to be hired to minimize the number of total employees. The o

Suppose Polaris is looking to cut costs and wants to minimize the number of full-time employees to help reduce b
Resolve this problem with the new goal and constraint. Discuss both the original and secondary goal recommend
model more robust for ST.

Use Solvertable to see what happens to total employees and FT employees when the required number of workers
would be most helpful if you were the manager when building ST.

Staffing Requirements
Hours: Scheduled Required
7:00-8:00am 7 >= 7
8:00-9:00am 7 >= 7
9:00-10am 8 >= 8
10:00-11:00am 15 >= 8
11:00am-12:00pm 9 >= 9
12:00-1:00pm 16 >= 10
1:00-2:00pm 16 >= 10
2:00-3:00pm 9 >= 9
3:00-4:00pm 8 >= 8

Shift Scheduling 7

Full Time (ex 11-12)

Part Time The analysis from the SolverTable shows

7:00-11:00am 0 us we should hire 7 full time employees
and 10 part time or 17 total employees.
8:00am-12:00pm 1 This be seen by the drastic drop after
9am-1:00pm 0 the increment increases from 9 to 10.
10:00am-2:00pm 7
11:00am-3:00pm 1
12:00-4:00pm 1
Total Part Time 10
Total Employees 17 <= 17
 2

times between 7am and 4pm.

8; 10am-11am=8; 11am-12pm=9; 12pm-1pm=10; 1pm-2pm=10; 2pm-3pm=9; 3pm-4pm=8

ng 7am-4pm, and part time workers who can work one of the following, 4-hour shifts.
m; 8am-12pm; 9am-1pm; 10am-2pm; 11am-3pm; 12pm-4pm

ensure part-time shifts can be covered, Polaris management limits the number of workers in each of the shifts to no

imize the number of total employees. The optimal solution will be 17.

mber of full-time employees to help reduce benefit expenditures without increasing the total number of employees.
the original and secondary goal recommendations and outcomes. A less than or equal to constraint will make your

oyees when the required number of workers needed from 11am-12pm ranges from 6-12. Think about what increments

olverTable shows
time employees
total employees.
stic drop after
s from 9 to 10.
e shifts to no

employees.
l make your

hat increments
One-way analysis for Solver model in Scheduling Problem worksheet

Midday Employees (cell $D$26) values along side, output cell(s) along top

Employees

Employees
Full Time

Total
6 7 17
7 7 17
8 7 17
9 7 17
10 7 7
11 7 7
12 7 7

Sensitivity of Full Time Employees to Midday Employees

8
7
6
5
4
3
2
1
0
6 7 8 9 10 11 12
Midday Employees ($D$26)
 Sensitivity of Full Time Employees to Midday Employees

Full Time Employees

Data for chart
When you select an output from the
dropdown list in cell $K$4, the chart
will adapt to that output.

1
7
7
7
7
7
7
7

ees

12
Marketing Assignment
Sephora has decided they need to conduct focus groups to better understand market demand for face cleansing prod
new types in the next year. Sephora has hired Q-insights to conduct these focus groups to obtain the information. T
markets of interest are those identifying male and female in the 18–25-year-old and 26–45-year-old age ranges. Th
500 focus group sessions with an average of ten individuals in each focus group. (5,000 individuals in total).

The following requirements are also included in the contract with Q-insights:
-At least 800 individuals of the focus groups must be in the 18–25-year-old, identifying male categoty.
-At least 1,200 individuals of the focus groups must be in the 26-45-year-old, identifying female category.
-No more than 2,000 of the participants must be in the 18-25-year-old category.
-At least 40% of total participants must identify as male.
-Representation for each of the 4 individual "types" must be at least 10% of the total particpants.

The contract in place with Q-insights to perform these focus groups and provide feedback will cost $30 for those id
old in age range, $20 for those identifying female in the 18-25-year-old age range, $50 for those identifying male i
and $40 for those identifying female the 26-45-year-old age range. How can Q-insights perform these focus group
cost for Sephora?

The optimal solution will be $180,000 (Some of you may also get $180,010; this is fine)

Use SolverTable to determine the effect on the numbers of individuals interviewed in each interview "type" and tot
identifying as male changes from 30% to 70% in 5-percentage-point increments.

Interview Costs and Definition

Interview
Costs Schedule Type Key
30 1500 CD C = Male
N=
50 500 ND Female
D = 18-25
20 500 CE - Yr Old
E = 26-45
40 2500 NE Yr Old

Interview Guideline Compliance

Interviews
Scheduled
CD 1500 >= 800 Minimum Required
NE 2500 >= 1200 Minimum Required
E 2000 <= 2000 D
C 2000 >= 2000 40% Limit on C
CD 1500 >= 500 10% Limit on CD
ND 500 >= 500 10% Limit on ND
CE 500 >= 500 10% Limit on CE
NE 2500 >= 500 10% Limit on NE

Total 5000 = 5000 Total Required

The solver analysis found that the cost

Total stays consistant regardless of gender
Project and age of the individual interviewed.
Cost $ 180,000.00 This can be seen by the lack of change
in data from the table generated
nd for face cleansing products as they want to launch a few
obtain the information. The demographics for Sephora’s
5-year-old age ranges. The contract with Q-insights requires
ndividuals in total).

ale categoty.
female category.

cpants.

will cost $30 for those identifying male in the 18-25-year-

r those identifying male in the 26-45-year-old age range, and,
erform these focus group interviews at the lowest total project

h interview "type" and total cost when the percentage of those

One-way analysis for Solver model in Marketing Assignment Problem worksheet

Individuals Interviewed (cell $E$40) values along side, output cell(s) along top

ND
CD

NE
CE
30% 1500 500 500 2500
35% 1500 500 500 2500
40% 1500 500 500 2500
45% 1500 500 500 2500
50% 1500 500 500 2500
55% 1500 500 500 2500
60% 1500 500 500 2500
65% 1500 500 500 2500
70% 1500 500 500 2500

Sensitivity of CD to Individuals Interviewed

1600
1400
1200
1000
800
600
400
200
0
30% 35% 40% 45% 50% 55% 60% 65% 70%
Individuals Interviewed ($E$40)
 Sensitivity of CD to Individuals Interviewed

Data for chart

When you select an output from the
1

CD
dropdown list in cell $K$4, the chart
1500 will adapt to that output.
1500
1500
1500
1500
1500
1500
1500
1500