Question

The staff demand at student store in terms of hours per week is given below:

Week 1 2 3 4

Staff Demand (hours) 300 340 345 265

At the beginning of each week, the store hires students to fulfill its staff demand. Students hired in week 1 work for 3 consecutive weeks (i.e., a student hired in week 1 works for weeks 1, 2 and 3); each of these students works for 15 hours per week and is paid $450 in total. Students hired in weeks 2 and 4 work only for 1 week; each of them works for 20 hours per week and is paid $180 in total. Students hired in week 3 work for 2 weeks; each of them works for 15 hours per week and is paid $290 in total. In each week, the student store needs to have total student working hours no less than its weekly demand described in the table above.

a. Formulate a linear programming model to determine student store’s optimal hiring plan (i.e., the number of students hired at the beginning of each week) to minimize its total cost. (11pts)

b. Solve the model using Excel Solver. (6pts)

c. In this year, week 4 coincides with a national holiday; as a result there is more staff demand in week 4. Suppose week 4’s staff demand for this year is 305 hours instead of the normal 265 hours. Without re-solving your optimization model, please answer the following questions:

i.   Does the total cost change? If so, by how much? (2pts)

ii. Do you now hire a different number of students in the optimal solution? How do you know? Please note that simply answering a number or “yes” or “no” without justification/calculation does not give you any credit. (3pts)

d. Let’s consider a new scenario: suppose that week 4coincides with a national holiday. The staff demand does not change, but we decide to increase student’s wage in week 4 from $180 to $240. Without re-solving your optimization model, please answer the following questions using the sensitivity report:

iii) Does the optimal solution change? (1pts)

iv) What is the new cost? (3pts)

Answer 1

a) From Given information we formulate LP problem is as.

Here, variable is

X1 = number of students hired in week 1 work for 3 consecutive weeks

X2= number of students hired in week 2 and week 4

X3= number of students hired in week 3 for 2 weeks

And Cost value is as

C1= $450 , C1=$180 and C3= $290

Let Objective function is as

Z(min) = 450*X1 + 180*X2+ 290*X3

Here , X1, X2, X3 >=0

b) We solve these problem in Excel using Solver as following path

Enter value of all variables as 0 for each $\rightarrow$ Enter formula for Objective in Cell B8 as "=450*B2 + 180*B3 + 290*B4 " $\rightarrow$ Enter Constraints $\rightarrow$ Data (Menu Bar) $\rightarrow$  Solver $\rightarrow$ Set Objective (B8) $\rightarrow$ To (Min) $\rightarrow$ By Changing variable cells (B2 : B4) $\rightarrow$ Subject to the constraints using Add $\rightarrow$ mark as Make unconstrained variables non-negative $\rightarrow$ Solving Method (Simplex LP) $\rightarrow$ Solve $\rightarrow$ Keep Solver Solution $\rightarrow$ Ok.

Result is as

FILE HOME INSERT PAGE LAYOUT FORMULAS DATA Connection Properties From From From From Other Existing Refresh Access Web Text Sources ConnectionsAll Edit Links Get External Data Connections 113 1 Variable 2 X1 3 X2 4 X3 20 6 Objective 8 Minimize 11850 10 Constraints Inequality RHS 12 13 14 15 16 17 18 19 300 >= 520>- 345 > 265 > 20 - 300 340 345 265 0 0 0 4 6

So,

Hiring of students is as

In 1st week = 20 students

In 2nd and 4th week = 11 students

And In 3rd week = 3 students

And Minimum cost is $11850.

c)

If week 4’s staff demand for this year is 305 hours instead of the normal 265 hours. That is Demand increased by 40 hours. And we know that students how beginning works in week 4th works for 20 hours and cost is $180. That means we increases number of students by 2. That is 13 students start work in 4th week. And cost increases by $180*2 = $360.

That is Objective function value is $11850 + $360 = $12210.

d) If we increase student’s wage in week 4 from $180 to $240. That is increases by $60 per students.

Optimal solution changes by increasing previous cost by $60 * 11 = $660 . That is new cost is $11850 + $660 = $12510.