MSP Case Analysis (via Excel Workbook)

The analysis performed for this case will be submitted via a single Excel workbook which includes the entirety of the analysis performed for the project group. Only one Excel workbook will be accepted from each project group.

The workbook containing the project's analyses may and very likely will have multiple tabs (multiple spreadsheets), each containing its own variant of the problem under study. The spreadsheet analysis should be correct and error free. If the solver or other tool is invoked, it should perform without error in executing analysis in the workbook.

The key to delivering an effective workbook is to make the analysis clear to a person who is not familiar with the approach taken. This includes providing headings, descriptors, labeling cells, applying the conventions used in the course for decision variables and more. The use of text boxes to explain assumptions or summarize results is one method to add some clarity or structure to a large workbook. Formatting consistency across different worksheets is also appreciated.

There is no limit to the number of spreadsheets tabs that can be included in the workbook. The contents of the spreadsheet tabs should be described in the presentation appendix slide. Labeling tabs and organizing them in a logical manner makes sense. A table of contents for the tabs can also be provided.

Tips for Delivering Effective Excel workbooks/spreadsheets:

• Label the cells in the sheet
• Use labels to indicate the presence of an objective function or constraints, if relevant.
• Justify labels and numbers the same (center or right justify is preferred)
• Put the mouse in the upper left-hand corner of the spreadsheet when submitting
• Ensure that the spreadsheet opens to the first tab.
• Set the sheet to view at 100%
• It is not necessary to use coloring in cells

Case 1: Scheduling Employees at Big Town Fire

This case focuses on developing a deeper understanding of the personnel scheduling problem.

Big Town Fire Department would like to schedule their human resource requirements in fire and safety operations, aka, firefighters. The town runs two schedules per day, 12 hours each. Firefighters are compensated hourly at $30 per hour plus an additional 33% is added to this amount to pay for the benefits (health insurance, retirement, etc.) Firefighters who work the night shift received $5 more per hour and have a commensurate increase in their benefits cost.

The weekly firefighter personnel requirements are:

	Mon	Tues	Weds	Thurs	Fri	Sat	Sun
5a-5p	11	11	12	18	19	19	14
5p-5a	16	17	18	20	22	24	20

The "normal" shift for a firefighter is 4 days 12 hours a day followed by 3 consecutive days off, working on either the day or the night shift (but not both). In general the managers of Big Town would like to schedule the firefighters in the least cost way, but must make sure the requirements are met?

Questions to Address

1. What is the fundamental challenge that Big Town faces in this setting? Why is this problem important? What are the implications of failing to address this problem effectively? What assumptions are made when building a model to schedule the fire fighters or in any similar personnel scheduling problem?
2. Given the above information, and assuming that firefighters stick to a normal schedule, what is the optimal schedule for firefighters that meets (or exceeds) the personnel needs for Big Town Fire? What is the associated cost of this schedule? Call this solution and the associated total cost the "benchmark solution".
3. How does the benchmark solution change with increases in the needed number of firefighters on the weekend night shifts (Friday, Saturday, and Sunday) of 5%, 10%, and 20%?

Note: Assume that increases mean that the number of firefighters needed at the specified times go up by 5% or more, 10% or more, etc. Percentage increases lead to fractional numbers, however, it doesn't make sense to schedule fractional numbers of firefighters so address these issues in a way that makes the most sense.

1. How does the benchmark optimal solution change if there are part-time employees that are added who work two consecutive days with 5 days off either day or night but not both day and night? Suppose that these part time employees receive the same hourly pay rates, but that Big Town is not required to pay their associated benefits. How does this solution compare with the original benchmark case? What would you recommend to Big Town managers? Does this make sense in the greater context?
2. Suppose there is a limit on the total number of hours that part time firefighters can work. (Why might this be the case). Suppose these limits are specified as part timers can work at most X% of the total hours worked in a week by ALL firefighters. Provide a table with different solutions and solution values for limits of 0%, 10%, 20%, 30%, 40%, of the total hours worked for part time firefighters as defined in part d. For example how does the schedule and associated cost look when the hours worked by part time firefighters is limited to be at most 20% of the total hour firefighter hours worked?
3. What if the total number of firefighters are limited due to turnover or other challenges in hiring? Starting from the benchmark solution, limit the total number of firefighters by 1, 2, 3, ..., 10. To enable finding a feasible answer that covers the requirements, allow some firefighters to work "double" shifts (day and night). Double shift employees would be compensated by "time and a half" for their work on all hours over 48 hours per week. Report the implications in terms of cost.

Note 1: If the benchmark solution had 22 firefighters in total (not the actual answer), then, the goal here would be to find the best schedule with 21 firefighters, with some overtime involved. Then repeat for 20, 19, etc.

Note 2: To include shifts that allow for overtime, you may need to make some assumptions about how much overtime is permitted for a firefighter per week. How safe is it if firefighters are working too much?

Note 3: Overtime hours include 1.5 times the regular pay and benefits.

1. Summarize and generalize the findings in this case.

Never merge cells in a spreadsheet.