The MetLife catering services group requires between10 and 18 employees a day at its main restaurant facility in NYC depending on the time of the day (see below).
The MetLife catering services group currently employs 12 full-time employees at the NYC facility, but also has many people on its roster of available part-time employees. A part-time employee must put in exactly 4 hours per day but can start any time between 9am and 1pm. Part-timers are a fairly inexpensive labor pool because no retirement or lunch benefits are provided for them. Full-timers work from 9am to 5pm.
By corporate policy, the MetLife catering services group limits part-time hours to a maximum of 50% of the days total requirement. Part-timers earn $6 per hour ($24 per day) on average, while full-timers earn $75 per day in salary and benefits on average. The group would like to set a schedule that would minimize total manpower costs. It will release 1 or more of its full-timers if it is profitable to do so. Formulate the linear programming in the excel sheet.
You are required to use Solver in Excel to complete this assignment.
-
Figure out all possible tours or shift combinations in the excel spreadsheet (B5 through I10).
-
Set up an objective function in cell B15.
-
Set up the cells for constraints.
-
Constraint 1 (B2:I2 and B12:I12)
F + P1 10 (9am to 10am needs) F + P1 + P2 12 (10am to 11am needs) F + P1 + P2 + P3 14 (11am to noon needs) F + P1 + P2 + P3 + P4 16 (noon to 1pm needs) F + P2 + P3 + P4 + P5 18 (1pm to 2pm needs) F + P3 + P4 + P5 17 (2pm to 3pm needs) F + P4 + P5 15 (3pm to 4pm needs) F + P5 17 (4pm to 5pm needs)
-
Constraint 2: The number of assigned to each tour should integer (F, P1, P2, P3, P4, and P5 = integer)
-
. Constraint 3 (K5): The MetLife catering services group currently employs 12 full-time employees.
-
Constraint 4 (K16, L16): The MetLife catering services group limits part-time hours to a maximum of 50% of the days total requirement.
-
Use solver to figure out the number of employees assigned to each tour and the labor costs.
-
Fill out the employee schedule table in A21.
Time period # of employees required Time period # of employees required 9am-10am 10 18 10am-11am 12 17 1pm-2pm 2pm-3pm 3pm-4pm 4pm-5pm 11am-Noon 14 15 Noon-1pm 16 17 Constraint 1 # in each time 9-10 10-11 11-12 12-1 1-2 2-3 3-4 4-5 Constraint 2 # assigned to each tour should be integer. (F, P1, P2, P3, P4, P5 Integer) Number Needed Time of day Decision Vairables Number Assigned to each Tour Employees 9-10 10-11 11-12 12-1 1-2 2-3 3-4 4-5 Constraint 3 FT # Wages 12 Ftime P1 75 24 24 24 P2 P3 P4 P5 24 24 Total number of employees working 0 Number Working Objective Function Constraint 4 PT hours PT hours Total hours 0 0.00 Excess 9-10 10-11 11-12 12-1 1-2 2-3 3-4 4-5 Schedule Excel File Edit Data Window Help 100% Mon Aug 24 1:45 PM Ariel Colon View Insert Format Tools BESU ... AutoSave OFF Scheduling Example 1, 2 Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X Calibri (Body) 11 VAA === ab, General Insert v 6728 4 V Delete Paste BIU V Idcas Conditional Format Cell Formatting as Table Styles Format v Sart & v Filter Find & Select 114 fix C D E F G H - L L . N 0 Q R S T Number Needed Sun Wed Total 1 2 3 3 Mon 5 Tue 5 Thu 5 Fri 5 Sal 5 3 6 35 4 Decision Vairables Number Assigned to each Tour T.e Wed Sun a Thu 1 Fri 1 1 5 6 7 8 Man 0 0 1 1 1 1 D 1 1 Sat 1 1 1 1 1 1 Tour Options Tour 1 Tour 2 Tour 3 Tour 4 Tour 5 Tour 6 Tour 7 D D 1 1 0 9 1 1 D 1 1 a 1 1 1 0 1 1 1 0 1 a 1 1 1 1 1 1 a 1 1 1 0 Number Working 10 11 12 13 14 15 16 17 18 19 20 21 Objective Function Fxcess Schedule Sun Mon Tue Wed Thu Fri Sal 22 23 24 25 26 27 28 29 30 Example1 Example2 + E W u + 100% 77 NOU 24 W P Excel File Edit Data Window Help A 100% Za Mon Aug 24 1:45 PM Ariel Colon Q View Insert Format Tools BESU ... AutoSave OFF Scheduling Example 1, 2 Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X Calibri (Body) VAA 11 = = Insert ab, General FH 28 0 4 V 2X Delete v Paste Ideas V BIU Conditional Format Cell Formatting as Table Styles Format v Sort & v Filter Find & Select F23 X for A B E F H - N 12am to bem D bam to 12pm 12pm to 6pm 1 z Number Needed 3 6pm to 12am 8 12 15 Decision was Number les pred to each Tour Wages per lour G 7 B 9 Tour Options MN MI AMN AML NN NL PMN PML 12am to bam bam to 12pm 12pm to bpm 6pm to 12am 1 0 0 0 1 1 1 II D 1 D 0 1 1 0 0 0 0 1 0 0 0 1 1 1 II II 1 1 0 0 12 11 15 Number Working 17 19 Objective function 20 24 Press 22 23 Schedule 24 25 26 120m to bam bam to 12pm 12pm to 6pm 6pm to 12am 28 29 30 31 12 33 34 35 Example Example2 + NOU 24 W P Time period # of employees required Time period # of employees required 9am-10am 10 18 10am-11am 12 17 1pm-2pm 2pm-3pm 3pm-4pm 4pm-5pm 11am-Noon 14 15 Noon-1pm 16 17 Constraint 1 # in each time 9-10 10-11 11-12 12-1 1-2 2-3 3-4 4-5 Constraint 2 # assigned to each tour should be integer. (F, P1, P2, P3, P4, P5 Integer) Number Needed Time of day Decision Vairables Number Assigned to each Tour Employees 9-10 10-11 11-12 12-1 1-2 2-3 3-4 4-5 Constraint 3 FT # Wages 12 Ftime P1 75 24 24 24 P2 P3 P4 P5 24 24 Total number of employees working 0 Number Working Objective Function Constraint 4 PT hours PT hours Total hours 0 0.00 Excess 9-10 10-11 11-12 12-1 1-2 2-3 3-4 4-5 Schedule Excel File Edit Data Window Help 100% Mon Aug 24 1:45 PM Ariel Colon View Insert Format Tools BESU ... AutoSave OFF Scheduling Example 1, 2 Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X Calibri (Body) 11 VAA === ab, General Insert v 6728 4 V Delete Paste BIU V Idcas Conditional Format Cell Formatting as Table Styles Format v Sart & v Filter Find & Select 114 fix C D E F G H - L L . N 0 Q R S T Number Needed Sun Wed Total 1 2 3 3 Mon 5 Tue 5 Thu 5 Fri 5 Sal 5 3 6 35 4 Decision Vairables Number Assigned to each Tour T.e Wed Sun a Thu 1 Fri 1 1 5 6 7 8 Man 0 0 1 1 1 1 D 1 1 Sat 1 1 1 1 1 1 Tour Options Tour 1 Tour 2 Tour 3 Tour 4 Tour 5 Tour 6 Tour 7 D D 1 1 0 9 1 1 D 1 1 a 1 1 1 0 1 1 1 0 1 a 1 1 1 1 1 1 a 1 1 1 0 Number Working 10 11 12 13 14 15 16 17 18 19 20 21 Objective Function Fxcess Schedule Sun Mon Tue Wed Thu Fri Sal 22 23 24 25 26 27 28 29 30 Example1 Example2 + E W u + 100% 77 NOU 24 W P Excel File Edit Data Window Help A 100% Za Mon Aug 24 1:45 PM Ariel Colon Q View Insert Format Tools BESU ... AutoSave OFF Scheduling Example 1, 2 Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X Calibri (Body) VAA 11 = = Insert ab, General FH 28 0 4 V 2X Delete v Paste Ideas V BIU Conditional Format Cell Formatting as Table Styles Format v Sort & v Filter Find & Select F23 X for A B E F H - N 12am to bem D bam to 12pm 12pm to 6pm 1 z Number Needed 3 6pm to 12am 8 12 15 Decision was Number les pred to each Tour Wages per lour G 7 B 9 Tour Options MN MI AMN AML NN NL PMN PML 12am to bam bam to 12pm 12pm to bpm 6pm to 12am 1 0 0 0 1 1 1 II D 1 D 0 1 1 0 0 0 0 1 0 0 0 1 1 1 II II 1 1 0 0 12 11 15 Number Working 17 19 Objective function 20 24 Press 22 23 Schedule 24 25 26 120m to bam bam to 12pm 12pm to 6pm 6pm to 12am 28 29 30 31 12 33 34 35 Example Example2 + NOU 24 W P
