Auto Power has acquired a new manufacturing facility to produce car parts. They will need to hire and train employees over the next nine months, and potentially lay off employees later in the season due to the seasonality of their product sales. The monthly wage rates for trained employees and the manufacturing labor requirements are given in the following table.Trainees can be hired at the beginning of each month. One consideration to take into account is that workers must have one month of classroom instruction before they can work in manufacturing. Therefore, a trainee must be hired a month before the worker is actually needed. Each classroom student uses 70 hours of a trained manufacturing employees time, so there are 70 fewer hours available for manufacturing. Each trained employee can work up to 190 hours a month (total time, instructing plus in manufacturing). Management may lay off at most 15% of the trained employees at the beginning of the month, but must pay one-half-months wage for severance pay. A trainee is paid 75% of regular wages for a trained employee during their training month. There are 51 trained employees available at the beginning of January. Formulate this hiring-and-training-and-layoffs problem as a linear programming spreadsheet model, and solve it using Solver.

Monthly Wage Rate Mfg. Hours Required Jan $6500 8800 Feb $6500 9500 Mar $6700 11,200 Apr $6700 10,300 May $7100 9700 June $7100 11,600 July $7200 10,700 Aug $7200 7900 Sep $7300 8700