SOLVE USING EXCEL SOLVER

3.13. Workforce Planning A software company is anticipating increased demand for its products. However, management is concerned about the adequacy of their programmers to meet the increased demand given the history of workforce turnover (5 percent of the programmers leave the company at the end of each month). Rather than hiring new workers, management is contemplating enrolling some or all of their programmers in a month-long intensive training program. After the successful completion of the training program, a programmer would receive an increase in salary and would also sign a contract not to leave the company for at least 6 months. Trained programmers would therefore be immune from normal turnover. Management believes that successful completion of the program would increase a programmer's productivity by 20 percent and plans to implement a no-layoff policy to encourage participation. However, only 90 percent of the programmers are predicted to complete the training program successfully. Those who enroll in training but do not complete the program successfully will return to the workforce at their pre-training skill level. (For simplicity, assume that they are not candidates for turnover during their training month and that they can enroll in the training program again later.) The monthly demand for untrained programmers for the next six months is shown in the table below. If trained programmers are available, their higher productivity allows management to satisfy demand with fewer programmers. For example, the demand in January can be satisfied with 100 untrained programmers, or with 82 untrained and 15 trained programmers (since 82 + 1.20 x 15 = 100). Number of untrained programmers required Jan Feb Mar Apr May Jun Month Programmers 100 100 115 125 140 150 A programmer cannot be engaged in production and participate in the training program during the same month. At the beginning of January, there are 145 (untrained) programmers on the workforce. Monthly payroll costs to the company are $3000 per untrained programmer (engaged in either production or the training program) and $3300 per trained programmer. (a) Determine a training schedule for the months of January through June that meets the workforce requirements at minimum cost. What is the optimal cost? (Allow for fractional decisions.)