Shift Determination Full Time (8 Hour) Shifts F01 8:00am to 9:00am 9:00am to 10:00am 10:00am to 11:00am 11:00am to 12:00pm 12:00pm to 1:00pm 1:00pm to 2:00pm 2:00pm to 3:00pm 3:00pm to 4:00pm 4:00pm to 5:00pm 5:00pm to 6:00pm 6:00pm to 7:00pm F02 F03 F04 P01 Part Time (2 Hour) Shifts P02 P03 P04 Part Time (2 Hour) Shifts P05 P06 P P07 P08 P09 P10 P11 Part Time (4 Hour) Shifts P12 P13 P14 P15 P16 P17 P18 Scenario 1 Full Time (8 Hour) Shifts F01 Solution Minimize 8:00am to 9:00am 9:00am to 10:00am 10:00am to 11:00am 11:00am to 12:00pm 12:00pm to 1:00pm 1:00pm to 2:00pm 2:00pm to 3:00pm 3:00pm to 4:00pm 4:00pm to 5:00pm 5:00pm to 6:00pm 6:00pm to 7:00pm F02 F03 F04 P01 Part Time (2 Hour) Shifts P02 P03 P04 P05 P06 P07 P08 Part Time (4 Hour) Shifts P09 P10 P11 P12 P13 P14 P15 e (4 Hour) Shifts P16 P17 P18 LHS $0.00 Sign 0 > 0 > 0 > 0 > 0 > 0 > 0 > 0 > 0 > 0 > 0 > RHS Scenario 2 Full Time (8 Hour) Shifts F01 Solution Minimize 8:00am to 9:00am 9:00am to 10:00am 10:00am to 11:00am 11:00am to 12:00pm 12:00pm to 1:00pm 1:00pm to 2:00pm 2:00pm to 3:00pm 3:00pm to 4:00pm 4:00pm to 5:00pm 5:00pm to 6:00pm 6:00pm to 7:00pm 35% Total Employees 60% Total Employees F02 F03 F04 P01 Part Time (2 Hour) Shifts P02 P03 P04 P05 P06 P07 P08 Part Time (4 Hour) Shifts P09 P10 P11 P12 P13 P14 P15 e (4 Hour) Shifts P16 P17 P18 LHS $0.00 Sign 0 > 0 > 0 > 0 > 0 > 0 > 0 > 0 > 0 > 0 > 0 > 0.0 > 0.0 RHS Scenario 3 Full Time (8 Hour) Shifts F01 Solution Minimize 8:00am to 9:00am 9:00am to 10:00am 10:00am to 11:00am 11:00am to 12:00pm 12:00pm to 1:00pm 1:00pm to 2:00pm 2:00pm to 3:00pm 3:00pm to 4:00pm 4:00pm to 5:00pm 5:00pm to 6:00pm 6:00pm to 7:00pm 35% Total Employee Hours 60% Total Employee Hours F02 F03 F04 P01 Part Time (2 Hour) Shifts P02 P03 P04 P05 P06 P07 P08 Part Time (4 Hour) Shifts P09 P10 P11 P12 P13 P14 P15 e (4 Hour) Shifts P16 P17 P18 LHS $0.00 Sign 0.0 > 0.0 > 0.0 > 0.0 > 0.0 > 0.0 > 0.0 > 0.0 > 0.0 > 0.0 > 0.0 > 0.0 > 0.0 RHS Scenario 4 Full Time (8 Hour) Shifts F01 Solution Minimize 8:00am to 9:00am 9:00am to 10:00am 10:00am to 11:00am 11:00am to 12:00pm 12:00pm to 1:00pm 1:00pm to 2:00pm 2:00pm to 3:00pm 3:00pm to 4:00pm 4:00pm to 5:00pm 5:00pm to 6:00pm 6:00pm to 7:00pm 35% Total Employee Costs 60% Total Employee Costs F02 F03 F04 P01 Part Time (2 Hour) Shifts P02 P03 P04 P05 P06 P07 P078 Part Time (4 Hour) Shifts P09 P10 P11 P12 P13 P14 P15 e (4 Hour) Shifts P16 P17 P18 LHS $0.00 Sign 0.0 > 0.0 > 0.0 > 0.0 > 0.0 > 0.0 > 0.0 > 0.0 > 0.0 > 0.0 > 0.0 > 0.0 > 0.0 RHS Last Name, First Name Student Number: xxxxxxxxx Last Name, First Name Student Number: xxxxxxxxx Last Name, First Name Student Number: xxxxxxxxx Last Name, First Name Student Number: xxxxxxxxx Business Management 2321 - Practical Exercise #4 Due prior to the beginning of class on Friday, July 15 th Replace the information above by typing the last names, first names, and student numbers for the group members. If the group has less than 4 members, delete the unused lines. The names must be in alphabetical order by last name. Use the last names and first names as specified on Carmen. Your last name is the name on your OSU e-mail account. Your student number is a 9-digit number on your Buck-ID and the Student Information System. DO NOT MODIFY this cover sheet. This cover sheet must be filled in (typed) completely and signed to be accepted. All practical exercise material must be stapled to this cover page. By signing below, I/we attest that I/we have performed this analysis. I/we understand that any violation of this statement by handing in another student's work as my own will result in a suspected case of academic misconduct. Signature: ______________________________________________ Date: __________________ Signature: ______________________________________________ Date: __________________ Signature: ______________________________________________ Date: __________________ Signature: ______________________________________________ Date: __________________ Staffing Problem The problem of staffing the customer service representatives (CSR) in a call center becomes a major problem. When there are too few CSRs, the average customer wait for service becomes longer than desired. When there are too many CSRs, they sit around waiting for customers to call. The demand for CSRs at a particular call center is given below. From 8am 9am 10am 11am Noon 1pm 2pm 3pm 4pm 5pm 6pm To 9am 10am 11am Noon 1pm 2pm 3pm 4pm 5pm 6pm 7pm CSRs 7 9 13 15 19 20 17 14 12 6 4 Full-time CSRs work an 8-hour shift. There are 4 possible full-time shifts and the full-time CSRs are paid $19.00 per hour. Part-time CSRs work either a 2-hour or a 4-hour shift. There are 18 possible part-time shifts and part-time CSRs are paid $15.00 per hour. The must be a minimum number of CSRs manning the phones during all time periods. It is OK to have additional CSRs in any given time period if necessary to fulfill the other constraints. Procedure Download the staffing problem EXCEL spreadsheet from Carmen for Exercise #4. On the \"Shift Determination\" tab, determine the unique full-time and part-time shifts (when does each shift start and how many hours do the CSRs work on that shift). On the \"Scenario 1\" tab, determine the objective function to minimize the total cost. Use the information from the \"Shift Determination\" tab and formulate the constraints for minimum number of CSRs for the 11 time periods listed in the table above. Complete the linear programming formulation by adding the objective function coefficients, the formulas for the LHS column, the constraint signs, and the constants for the RHS. Scenario 1: Using Solver, run the model as an integer program (adding a constraint in Solver to have all 22 decision variables to be integer). There are three additional sets of scenarios that the call center management wants in investigate as integer programs. Using the formulations for Scenario 1 as the foundation, add 2 constraints to the formulation for each of the following: At least 35% but no more than 60% of the total employees working (not total employees required) must be fulltime employees. Scenario 2: At least 35% but no more than 60% of the total employee working hours (not employee required hours) must be full-time employee hours. Scenario 3: Using Solver, run the model as an integer program (adding a constraint in Solver to have all 22 decision variables to be integer). Using Solver, run the model as an integer program (adding a constraint in Solver to have all 22 decision variables to be integer At least 35% but no more than 60% of the total employee working cost (not employee required cost) must be full-time employee cost. Scenario 4: Using Solver, run the model as an integer program (adding a constraint in Solver to have all 22 decision variables to be integer For scenarios 2, 3, and 4, it is incorrect to multiply the RHS values of the constraints by 35% and 60% (this is a percentage of the CSRs scheduled; the problem asks for the percentage of the CSRs working). Hint: Think ratios. Please note that multiple optimal solutions exist for all of the scenarios. Students only have to find one of the solutions for the scenarios. Cutting Problem Carol is making a patchwork quilt for charity. The quilt is made by sewing 20 \"squares\" together. Each \"square\" is made by sewing 13 pieces of material together to form the pattern shown below. The pieces of material range from a 4\" by 4\" square of material up to a rectangle of material that is 4\" by 22\". Length x Width Red Material 4\" x 4\" 4\" x 10\" 4\" x 16\" Yellow Material 4\" x 4\" 4\" x 10\" 4\" x 16\" Green Material 4\" x 7\" 4\" x 13\" 4\" x 19\" Blue Material 4\" x 7\" 4\" x 13\" 4\" x 19\" 4\" x 22\" Carol wants to minimize the amount of material that she needs to purchase to make the quilt. The red, yellow, and green material is 40\" wide; the blue material is 48\" wide. Carol decides to keep the orientation of the pieces the same on the purchased material. [Note that having strips run in both directions makes this problem extremely difficult!] Assume that the pieces will run across the width of the material (40\" or 48\"). See the diagram below: Procedure: Since the material is 40\" or 48\" wide, determine all of the cutting patterns for each color from that width of material. The easiest way to ensure that you don't miss any cutting patterns is as follows: Sort the widths required for a color in the table with the longest on the left. Add a column for the amount of scrap. Determine the maximum number of the longest width that can be cut. For the red material, Carol can only cut 1-22\" pieces (2-22\" pieces would require 44\" of material). After cutting 1-22\" pieces, she will have 18\" of material remaining. Determine the maximum number of the next longest width that can be cut. With only 18\" remaining, Carol can only cut 1-16\" piece. After cutting 1-16\" piece, she will have 2\" of material remaining. Determine the maximum number of the next longest width that can be cut. With only 2\" remaining, Carol cannot cut any 10\" pieces. Determine the maximum number of the next longest width that can be cut. With only 2\" remaining, Carol cannot cut any 4\" pieces. Once the first cutting pattern is determined, decrement the number of the smallest piece. Do not include any cutting patters that have scrap larger than the smallest piece. For red material, decrement the number of 16\" pieces from 1 to zero. Determine the maximum number of 10\" pieces, 4\" pieces, and the scrap. Continue the process until only the shortest width is cut. The possible cutting patterns for the red material is shown below. Red Material 40\" Material Width 22\" Pieces 16\" Pieces 10\" Pieces 4\" Pieces Scrap Pattern #1 1 1 0 0 2 Pattern #2 1 0 1 2 0 Pattern #3 1 0 0 4 2 Pattern #4 0 2 0 2 0 Pattern #5 0 1 2 1 0 Pattern #6 0 1 1 3 2 Pattern #7 0 1 0 6 0 Pattern #8 0 0 4 0 0 Pattern #9 0 0 3 2 2 Pattern #10 0 0 2 5 0 Pattern #11 0 0 1 7 2 Pattern #12 0 0 0 10 0 Download the cutting problem EXCEL spreadsheet from Carmen for Exercise #4. Determine the cutting patterns for the yellow material, the green material, and the blue material in EXCEL (Cutting Patterns tab). There are 9 cutting patterns for the yellow material, 7 cutting patterns for the green material, and 8 cutting patterns for the blue material. Formulate integer programs for the red material, the yellow material, the green material, and the blue material on the appropriate tab in the EXCEL spreadsheet. Decision variables: The decision variables will be the number of each cutting pattern that will be used (the decision variables must be defined as integer in Solver). Objective function: Carol wants to minimize the length of the material that she needs to purchase. Since all of the pieces have a length of 4\