File Home Insert Page Layout Formulas Data Review View Help Cut Calibri 11 Wrap Text Copy Paste BIU x v x A Merge & Center Format Painter > Alignment Clipboard E26 Font fx A Criteria B D E General x $ % 9 .00 0 .000 Number F G F Conditional Format as Cell Formatting Table Styles H Styles J 0 1 1 1 1 2 Identify all decision variables 3 State the objective function as a mathmatical formula 4 Identify all constraints 5 Build an appropriate decision model in Excel 6 Set up constraints with "left side" and "right side" Problem 1 Problem 1a Problem 1b Problem 1c Problem 2 Problem 3a Problem 3b n/a n/a n/a 1 1 1 n/a n/a n/a 1 0 1 n/a n/a n/a 1 1 1 n/a n/a n/a 1 1 1 n/a n/a n/a 1 1 1 7 binary constraints connected to model? 8 Solver dialog set up properly? n/a n/a n/a n/a 1 1 1 n/a n/a n/a n/a n/a n/a n/a 9 -objective cell 1 n/a n/a n/a 1 1 1 10 -constraints 1 n/a n/a n/a 1 1 1 11 -correct engine 1 n/a n/a n/a 1 1 1 Total % Solution stated or clearly identified Points Possible 1 1 1 1 1 1 1 Earned Points 13 Totals of Earned Points 8 1 1 1 10 9 10 40 95.2% 14 Points Possible 9 1 1 1 10 10 10 42 In Fast is moving some employees into a newly renovated floor in one of their buildings. There are 14 employees involved and there are 10 offices on the renovated floor, four of which seat two people. The Fast HR (Human Resources) director knows happy employees are productive employees and let the 14 staff members visit the new space and give their preference by a rank of 10 (first choice) to 1 (last choice). Office Rank Employee Office Capacity 1 2 3 1 1 3 5 Z 1 2 3 m00 4 5 6 7 8 9 10 11 10 7 1 4 2 6 8 3 3 9 1 5 9 10 99 3 1 1 2 1 2 6 1 4 1 46 5 2 6 1 5 7 2 8 8 2 9 9 2 10 10 1 7 10 Emp. Seniority Ranking 5 M6 61798496 9 9 8 5 7 5 5 6 295 10 1 10 3 3 MN 5 2 4 25 4 4 2 8 3 4 3 4 3 00 60 8 9 10 99 2 6 7 7 8 8 2 1 7 10 4 9 5 10 4 3 7 10 4 7 3 2 7 5 21673 1 7 8 6 2 273529810465 1 9 689 265 12 13 14 6 3 10 5 9 10 1 1 2 2 2 3 3 10 4 4 4 7 5 8 8 7 7 7 5 4 1 How should the HR manager assign the offices to maximize the preference of all 14 employees? Hints: . The decision variables are the employee office assignments. The objective function would be to maximize the sum of preferences the employees get, e.g. ifemployee 1 gets office 1, and employee 3 gets office 2, the sum of their preferences would be 3+8=11. Assignments must be binary. Employees and rankings are integers. No employee can have more than 1 office assignment. Every employee must have an assignment. Every office must be used to capacity, i.e. a 2-person office must have 2 employees assigned and 1-person offices can only have 1 employee assigned. Some of the hints are constraints. 2 The HR manager has decided to include consideration of the employees' seniority in the decision. Revise your decision model to include using the Employee Seniority Ranking to weight their preference scores. An employee's Preference Score with a higher Seniority Rank is worth more than an employee's Preference Score with a lower Seniority Rank. Rerun Solver to find a new solution.