The TSA at a large airport has 175 agents hired and trained for the month of January. Agents earn an average of $3300 per month and work 160 hours per month. The projection is 26,400 agent hours will be required for February, 29,040 agent hours will be required in March and 31,994 will be required in the months of April and May. Attrition during the month of January is anticipated to be 5% so only 95% of the agents are trained and working in January will be available for work in February. Efforts are being made to improve attrition: The TSA expects to lose only 4% of agents during February, 3% in March and 2% in May. To ensure that enough agents will be available to meet the demand, new agents must be hired and trained. During the one month training period, trainees are paid $2,600. Existing agents who normally work 160 hours per month are able to work only 80 hours during the months they are training new people. How many agents should be hired during the months of January to May?
The objective is to derive an optimal hiring plan considering demand for hours, cost of hiring, cost of regular working hours and hours lost on training. The solution provides detailed a step by step approach towards solving similar problems using Excel Solver.