This problem should be solved using Excel and Solver function in it.

Problem:

The Sanitation Department has a problem with overtime (OT) spending. OMB claims the Department over spends its OT budget by millions. The Department claims it has to run the OT because OMB hasn't authorized it to hire enough workers to do all the jobs that must be done. When there is more work to do than workers available, the Department runs overtime by calling workers in on their day off. That is, workers are scheduled to work five of the six days of the Monday to Saturday workweek. Some get Monday off, some Tuesday, etc.

Because of a variety of reasons, there are months in which there are not enough workers available to do the work. To get enough workers in on a day when there is more work than workers, workers are called in on their scheduled day off rather than just having their day extended as is done in other organizations or industries. Work on the scheduled day off, the sixth work day of the week, is paid at the overtime rate.

On the other hand there are months in which the Department has extra or surplus workers. Those surplus workers are put to work, but the work they do does not reduce the workload of future months. As such in some months there is OT and others surplus.

To solve the problem the Department set out to build a workforce planning model. It consisted of two parts. The first part was an analysis of all the jobs for which the Department was responsible, namely refuse collection, waste disposal, and street cleaning. Since the amount of refuse to collect and the amount of litter to pick up varies seasonally and from month to month, the total number of worker-days needed to do the work varies as well. As such the Department conducted a thorough study of exactly how many worker-days of effort were needed for the average day of each month.

The second part was an analysis of workforce availability. The percentage of the workforce that shows up for work varies seasonally and from month to month as well. Summer months are vacation months and other times of the year are marked by more sick or injury days. High attrition, mainly due to retirements, is more likely to occur at certain times of the year as well.

The number of worker-days of work required and worker-days available for each month given an initial headcount on January 1 are listed in Table 1. This is the baseline of work that needs to be done under the current agreement with OMB and the baseline of worker-days available for the current workforce assuming no additional workers are hired. It includes all absences for vacation, sick leave, etc. as well as attrition. The salaries of the current workers are already built into the budget and therefore can be considered for this analysis a fixed cost.

When the Department hires new workers, it puts them through a one-month training period during which they are paid their full salary but are not available for any fieldwork. Assume they are hired on the first day of one month and begin field service the first day of the next month. The training facility can train up to 400 new recruits at one time.

New workers earn $3,360 per month. For modeling purposes, the average cost for a worker when they work their sixth day is $350 per day. Assume each month has the same number of work days - 26 days - and that new hires are available for 17 days of straight time work each month after training.

The Department wants to find a hiring strategy that minimizes cost yet still meet all work requirements. Model one year of work to answer the following questions by using Solver:

1.How much overtime cost is incurred if no one is hired? Use Solver to find the answer. The "Base OT" is $16,744,000.

2.What reduction in cost is achieved by hiring optimally while also allowing OT? (Note: We allow non-integer values for number of new hires, new hires can work for 17 work days per month.)

3.How many workers would have to be hired (and when) if OMB said they were limiting the Department to $3 million in overtime?

4.In March of the year being modeled, the Mayor said he wanted to do more street cleaning and ordered OMB to budget for 100 more worker-days* of street cleaning work be done each work day for the rest of the year starting in July. Determine what would happen to the hiring strategy by adding this additional requirement while keeping the $3 million OT limit.

*i.e. 50 more worker-days per work day in a month would be 50 x 26.

Table I ( data available on Canvas ) Monthly Requirement ( in Monthly Available Month worker days ) ( in worker days ) January 89. 050 96, 200 February 86, 580 92, 950 March 87, 360 95. 160 April 90. 740 90, 220 May 91 , 260 89, 570 June 92, 040 87, 230 July 97, 890 86. 450 August 95, 680 85. 540 September 95, 160 88, 920 October 92 , 950 88, 010 November 92, 430 86. 840 December 91, 130 88. 660