Allen Furniture is a manufacturer of hand-crafted furniture. At the start of January, Allen employs 20 trained craftspeople. They have forecasted their labor needs over the next 12 months as shown in the table below. Each trained craftsperson provides 200 labor-hours per month and is paid a wage of $3,000 per month. Hiring new craftspeople requires advertising, interviewing, and then training at a cost of $2,500 per hire. New hires are called apprentices for their first month. Apprentices spend their first month observing and learning. They are paid $2,000 for the month, but provide no labor. In their second month, apprentices are reclassified as trained craftspeople. The union contract allows for firing a craftsperson at the beginning of a month, but $1,500 must be given in severance pay. Moreover, at most 10% of the trained craftspeople can be fired in any month. Allen would like to start next year with at least 25 trained craftspeople. How many apprentices should be hired and how many craftspeople should be fired in each month to meet the labor requirements at the minimum possible cost?

a. Visualize where you want to finish. What numbers will Allen require? What are the decisions that need to be made? What should the objective be?

b. Suppose one apprentice is hired in January. Calculate by hand how many labor-hours would be available in January and February. Calculate by hand the total costs in January and February.

c. Make a rough sketch of a spreadsheet model, with blocks laid out for the data cells, changing cells, output cells, and objective cell.

d. Build a spreadsheet model for January and February and thoroughly test the model.

e. Build and solve a linear programming spreadsheet model to maximize the profit over all 12 months?