Machinist Hiring Linear Optimization Model -- 2

Submit two files: a word doc with the algebraic formulation and the answers and a spreadsheet with the model and Solver's solution. The Adler Machine Company is planning to add a new product to its line and wishes to hire some experienced machinists. The local union has advised them that machinists are categorized in one of three skill levels: expert, associate, and apprentice. An expert machinist has at least 10 years of experience and is expected to produce 20 units per day. An associate machinist must have at least 6 years of experience and can produce 16 units per day. An apprentice machinist must have at least 1 year of experience and must be able to produce 10 units per day. The union contract calls for wages of $75, $60, and $40 per hour for the three skill levels. Currently, there are 3 experts, 8 associate, and 11 apprentice machinists available for hire. Adler has budgeted $35,000 per (5-day, 35-hour) week for machinists’ wages. They would like to hire a crew of new machinists that will yield the highest output rate. To keep both the union and present employees happy, they need to ensure that the total level of experience of the workers hired represents a seniority level of at least 75 worker-years.        a    Formulate the linear program that determines the number of machinists of each type that should be hired. Describe the objective function for this LP. Must the decision variables be non-negative?       b    Create a spreadsheet with a table containing the appropriate values. Label each of the constraints and give the units of each resource represented.       c    Run Solver on your spreadsheet to obtain the optimal values for the decision variables in your Linear Programming Model. Answer the following questions, based on this (non-integer) solution:       i    How many units per week are produced, based on this solution? How many are made by experts? How many by associates and how many by apprentices?       ii    What is the total number of worker-years of experience with the crew suggested by the model solution? Would it be worth trying to negotiate with the union to set a lower requirement?       iii    How much of the weekly salary budget is paid to the crew members in the solution suggested by your model?       iv    Suppose a ninth associate machinist becomes available, would it be worthwhile to hire this individual, keeping in mind that the total salary budget is fixed? Explain.         d    Adjust the solution suggested by your model by making sure that the number of hires at each level is an integer (i.e., a whole number). Describe the differences between this new solution and the optimal one.   Extra Credit: Briefly explain why your adjusted solution cannot result in a higher level of unit production.

Project ID: 40620216