Sky Queen (SQ) has hired you to develop its production plan

Sky Queen (SQ) has hired you to develop its production plan for the next 12 months (September - August.) The objective is to minimize total cost. SQ can hire and fire employees but this action incurs a hiring cost of $600 per employee and a firing cost of $950 per employee. Other relevant costs are: $8.00 per unit produced in overtime, $1.60 per unit per month inventory cost, and $900 per employee per month for regular employment.

Other relevant data include:
number of units a worker can produce in a month in regular time: 300 units
number of units a worker can produce in a month in overtime: 70 units
number of initial inventory: 1000 units
number of initial workforce: 20 workers
Note, if workers are fired at the beginning of the first month they will not be paid for that month but you will incur the firing cost.

Demand for the 12 months is as follows:

September 4,500 March 8,500
October 5,000 April 10,000
November 5,000 May 9,500
December 6,000 June 9,000
January 6,500 July 7,000
February 8,000 August 5,000

You assignment:

For parts 1 and 2 assume that part-time employees are possible.
Use Linear Programming for parts 1-3 and Integer Programming for part 4.

1. Use Solver to determine the minimum cost production schedule. SQ wants to know the total cost as well as the monthly regular time production, overtime production, inventory, total workforce, hirings, and firings. Assume that part-time employees are possible.

2. SQ in not entirely certain what per unit inventory cost will be as they are considering several options ranging from reducing insurance and security to outsourcing storage. They are also considering moving to a Azero inventory@ strategy. Assume that part-time employees are possible. Consequently, they want you to do the following:

a. Determine the minimum per unit inventory cost (within $0.10 per unit) for which the minimum cost production schedule would result in zero inventory (i.e., only the 1,000 unit initial inventory that they already have.)

b. Develop a table and graph that demonstrate how total inventory (i.e., 12 month total) varies with per unit inventory costs. Again, $0.10 increments are accurate enough. Summarize your findings in a few sentences.

3. For various reasons (e.g., quality, reliability, community relations) SQ is concerned about having a flexible work force. Consequently, they are considering having a fixed-sized work force (i.e., no hiring or firing during the year.) They have determined that the minimum size of this work force would be 20. They want you to determine the following. Assume that all other parameters are the same as they were in the original problem and that there will be no part-time (i.e., fractional) employees.

a. What is the minimum cost fixed-sized work force size and what would the total production cost be with a fixed-work force of that size? In your calculations, do not include hiring and/or firing costs in the first period that are necessary to attain the fixed-size workforce.

b. Develop three tables and graphs that demonstrate how total yearly cost, total yearly inventory (in units, not dollars) and total yearly overtime production vary respectively with fixed-work force size. Give a brief recommendation based upon your findings on each of these 3 criteria.

Note: Part 3 should be done with Linear Programming rather than Integer Programming. It requires solving the problem numerous times with different values for the workforce size.

4. Redo Part 1 but now assume that part-time employees are not possible. Use integer programming to do this. Briefly compare the solutions and solution times (in approximate, relative terms; no different, somewhat longer, much longer, etc.)

See attached file for full problem description.

Remember:
Include an appendix or exhibits for each question that presents the relevant Solver outputs and/or graphs to support your answers.
Answers to the questions should be clearly labeled and as succinct as possible. In general, several sentences (or less) and references to appendices or exhibits will suffice.
It is recommended but not required to use the Excel template provided.