Question

Please solve the following problem using Microsoft Excel Solver and provide screen shots of cells, formulas, constraints, etc.

Jenko and Schmidt are going to Law School! After their recent cases led to significant property damage and cost too much for the department to handle, Jump Street has been disbanded and Jenko and Schmidt have been reassigned to do undercover work for the white collar crimes division. They must now attend law school for training. Rather surprisingly, Jenko finds that years of watching Law and Order reruns were great preparation for law school and becomes the head of the pro-bono law services student organization.

Each student volunteering for the organization donates 50 hours per month. Here is the caseload that Jenko anticipates for the organization during his final year at law school:

Month	Hours Needed	Month	Hours Needed
September	650	February	650
October	450	March	750
November	600	April	900
December	500	May	800
January	700	June	650

A student volunteer can start in any month and will typically volunteer for three (consecutive) months. What is the minimum number of student volunteers that Jenko needs to recruit each month in order to cover the above caseload?

Answer 1

We can solve this problem using LP model: total number of students needed each month = hours/50

Decision variables: As shown in fig1 in green

number of volunteers to be recruited each month:

volunteers recruited in september = X1

similarly volunteers recruited each month = X2,X3...X10

Objective function :As shown in fig1 in yellow\

minimize the total number number of recruits.

minimize = X1+X2+X3+...+X10

in excel: minimize = B10+C11+D12+E13+F14+K19+G15+H16+I17+J18

constraints:

1. total number of students volunteering each month should be more than or equal to the students needed each month.

September, students needed = 650/50 = 13, students working: X1 $\geq$ 13, in excel = L10 $\geq$ M10

october, students needed = 9, students working = X1 + X2 $\geq$ 9, excel = L11 $\geq$ M11

november, X1+X2+X3 $\geq$ 12, excel =  L12 $\geq$ M12

now, the students who started working in september will stop working

december, X2+X3+X4 $\geq$ 10

similarly for all other months.

students hired in May would work only 2 months, and students hired in June wold work only in june.

2. All decision variables $\geq$ 0

solving the excel model in solver as shown below we get the minimum students to be hired each month as shown in fig1

fig1

HoursStudents Needed needed MonthNeeded needed 13 15 HoursStudents Month 13 February 2 September 3 October 4 November 5 December 6 January 9 March 12 April 10 May 14 June 16 13 ota students students uir o. of students working each month 13 10 September 11 October 12 November 13 December 14 Janua 15 Februa 16 March 17 April 18 Ma 19 June 20 21 13 13 13 10 14 14 15 24 24 13 13 13 12 10 14 13 15 18 16 13 10 10 10 4 13 13 51 Total number of students required 23

Solver Parameters Set Target Cell: SGS2 Solve Equal To: Max By Changing Cells: Min Value of 0 Close $8$10,SC$11,50$12,SE$13,SF$14,SG$15SH: | | | Guess Subject to the Constraints: Options SL$10 >= SMS10 SLS11 >= SMS11 SLS12 >= SMS12 SL$13$M$13 L$14$M$14 SLS15 >= SMS15 Add Change Delete Reset All Help

fig2