Responses to Week 3 Poll Questions

MGMT 306

• Can you go over again how to solve an LP model in Excel?

  Here is an example from start to finish. Let’s imagine we have the following problem description:

  A company needs to hire staff for a customer support help desk. Staff can be hired on one of three shifts:

  • Shift A staff work from 8am-2pm and cost $107 per day
  • Shift B staff work from 10am-4pm and cost $95 per day
  • Shift C staff work from 12pm-6pm and cost $84 per day

  Management has set coverage requirements:

  Time slot	Required staff
  8am-10am	8
  10am-12pm	14
  12pm-2pm	18
  2pm-4pm	10
  4pm-6pm	6

  Write an LP model to minimize total costs while meeting coverage requirements. Solve this LP in Excel.

  We will use the variables \[\begin{aligned} &A&&\text{number of staff to hire in shift A}\\ &B&&\text{number of staff to hire in shift B}\\ &C&&\text{number of staff to hire in shift C}\\ \end{aligned}\] and consider the LP model \[\begin{aligned} \min\quad & 107 A + 95 B + 84 C &&\text{total cost}\\ \text{s.t.}\quad & A \geq 8 && \text{8am-10am coverage}\\ & A+B \geq 14 && \text{10am-12pm coverage}\\ & A+B+C \geq 18 && \text{12pm-2pm coverage}\\ & B+C \geq 10 && \text{2pm-4pm coverage}\\ & C \geq 6 && \text{4pm-6pm coverage}\\ & A,B,C\geq 0 &&\text{nonnegativity} \end{aligned}\]

  To implement this in Excel, the first thing we will do is to write a data table with all of the coefficients in our LP model. The data table will have:

  • one column per variable, followed by a “limit” column
  • one row per constraint (other than nonnegativity), followed by an objective row

  

  Next, we choose some cells in the Excel sheet for our variables. In this case, since our model includes nonnegativity constraints on the variables, we will also indicate that on the Excel sheet.

  

  Next, we will implement the constraints in Excel. We will compute the left-hand-side of each constraint using a sumproduct between the variable cells and the coresponding row of the data table. We will use a formula to copy the right-hand-side of each constraint from the data table itself. I have used a dollar sign $ to freeze the row-reference corresponding to the variable cells to allow myself to autofill all of the left-hand-side formulas.

  

  Finally, we will use a sumproduct formula between the variable cells and the final row of the data table to implement the objective function.

  

  And the completed Excel model:

  

  Finally, we will use Solver to solve this Excel model. In the dialog, we will declare the objective function, that this is a minimization problem, all of our constraints, that our variables are nonnegative, and that we would like to use Simplex LP as the solution method.

  

  After, hitting solve, Solver reports “Solver found a solution. All constraints and optimality conditions are satisfied.” This gives us the following solved Excel model: