Parker Pens Inc (PPI) produces five different models of pens: Alpha, Bravo, Charlie, Delta, and Echo. Each pen requires up to four ingredients - steel, gold, silver, and rubber. Weights of ingredients needed per pen for each model of pen is specified below along with the maximum monthly demand and profit per pen for each model of pen. PPI must purchase all of the needed ingredients from a single supplier, and the supplier is able to supply only 12,000 kilograms of Steel, 120 kilogram of gold, 240 kilogram of silver, and 6000 kilograms of rubber per year (all ingredients equally distributed across 12 months). Each model of pen faces the monthly demand shown below. In addition, PPI must produce at least 100 Alpha, 150 Bravo, 200 Charlie, and 1,000 Echo pens every month to keep its network of retailers committed to selling different models of its pens. Decide how many pens of each type should PPI make per month to maximize its monthly profit given the stated constraints, and then answer the questions presented below. (100 points)

Note: 1 kilogram = 1,000 grams

Pen Model	Profit per pen	Monthly Demand	Steel needed per pen	Gold needed per pen	Silver needed per pen	Rubber needed per pen
Alpha	$79	1,000	10 grams	15 grams	6 grams	0 gram
Bravo	$69	1,500	11 grams	6 grams	16 grams	0 gram
Charlie	$29	2,000	12 grams	1 gram	1 gram	0 gram
Delta	$5	5,000	13 grams	0 gram	2 grams	5 grams
Echo	$1	10,000	14 grams	0 gram	0 gram	10 grams

1. State the goal AND identify the decision variables, objective function, and constraints (including any upper or lower bounds). (32 points)

How your response will be graded:

1. 2 points for correctly stating the objective and the goal in words.
2. 4 points for correctly stating all of the decision variables.
3. 2 points for correctly stating the objective function as a linear combination of the decision variables (i.e. as a MATHEMATICAL EXPRESSION).
4. 24 points for correctly identifying and stating all of the constraints in WORDS and as a linear combination of the decision variables (i.e. as a MATHEMATICAL EXPRESSION).

1. Mathematically formulate and run a linear optimization model using Excel. (28 points)

How your response will be graded:

1. 4 points for correctly setting up the objective function on the worksheet.
2. 12 points for correctly setting up the constraints on the worksheet.
3. 10 points for correctly adding all the constraints in the solver dialog box, and for selecting correct options.
4. 2 points for correct & logical quantities for five types of pen PPI should make per month.

1. Using Solver's sensitivity analysis report as the ONLY basis, explain how each of the following changes (each change starts from the original values for all variables) will impact the optimal solution (i.e. how many pens of each type PPI should make per month) / value of the objective (monthly profit) as applicable. Include relevant calculations and references to appropriate numbers from the sensitivity report: (40 points)
2. Profit per Alpha pen increases to $84 (4 points)
3. Profit per Bravo pen changes to $65 (4 points)
4. Maximum monthly demand for Delta decreases to 4,970 (4 points)
5. Maximum monthly demand for Delta decreases by 4,970 (4 points)
6. Annual supply of silver decreases by 600 grams (4 points)
7. Annual supply of silver decreases by 1200 grams (4 points)
8. Minimum monthly production quantity required for Alpha increases to 300 (4 points)
9. Minimum monthly production quantity required for Alpha decreases by 1 (4 points)
10. Minimum monthly production quantity required for Charlie increases by 1,900 (4 points)

Minimum monthly production quantity required for Charlie increases to 1,900 (4 points)