LLP Solver for Parket Sisters

PARKET SISTERS

Computers and word processors notwithstanding, the art of writing by hand recently entered a boom era. People are buying fountain pens again, and mechanical pencils are becoming more popular than ever. Joe Script, the president and CEO of Parket Sisters, a small but growing pen and pencil manufacturer, wants to establish a better foothold in the market. The writing market is divided into two main sectors. One, dominated by Mont Blanc, Cross, Parker Brothers, Waterman, Schaffer, and a few others, caters to people who want writing instruments. The product lines from these companies consist of pens and pencils of elaborate design, lifetime warranty, and high price. At the other end of the market era manufacturers like BIC, Pentel , and many companies from the far east, offering good quality items, low price, few trims, and limited diversity. These pens and pencils are meant to be used for a limited time and disposed of when the ink in a ballpoint pen runs out, or when the lead in a mechanical pencil won't retract or extend. In short, these items are not meant for repair.
Joe thinks there must be a middle ground, and that is where he wants to position his company. Parket Sisters makes high-quality items, with limited trim and diversity, but also offers lifetime warranties. Furthermore, its pens and pencils are ergonomically efficient. Joe knows that some people want the status of the Mont Blanc Meisterstuck pen, for example, but he has never met a person who said that writing with such a pen is enjoyable. The pen is too large and clumsy for smooth writing. Parket Sisters' products, on the other hand, have a reputation for working well, are easy to hold and use, and cause limited "writer's fatigue."
Parket Sisters makes only three items-a ballpoint pen, a mechanical pen, and a fountain pen. All are available in just one color, black and are sold mostly in specialty stores and from better catalog companies. The per-unit profit of the items is $3.00 for the ballpoint pen, $3.00 for the mechanical pen, and $5 .00 for the fountain pen. These values take into account labor, the cost of materials, packing, quality control, and so on.
The company is trying to plan its production mix for each week. Joe believes that the company can sell any number of pens and pencils it produces, but production is currently limited by the available resources. Because of a recent strike and certain cash flow problems, the suppliers of these resources are selling them to Parket Sisters in limited amounts. In particular, Joe can count on getting at most 1,000 ounces of plastic, 1,200 ounces of chrome, 2,000 ounces of stainless steel each week from his suppliers, and these figures not likely to change in the near future, Because of Joe's excellent reputation, the suppliers will sell Joe any amount (up to his limit) of resources he needs when he requires them. That is, the suppliers did not require Joe to buy some fixed quantities in advance of his production of pens and pencils; therefore, these resources can be considered variable costs rather than fixed costs for the pen and pencils.
Each ballpoint pen requires 1.2 ounces of plastic, 0.8 ounces of chrome, and 2 ounces of stainless steel. Each mechanical pencil requires 1.7 ounces of plastic, no chrome, and 3 ounces of stainless steel. Each fountain pen requires 1.2 ounces of plastic, 2.3 ounces of chrome, and 4.5 ounces of stainless steel. Joe believes LP (linear programming) could help him decide what his weekly product mix should consist of.
Getting his notes and notebooks, Joe grapples with the LP formulation. In addition to the constraints of the available resources, he recognizes that the model should include many other constraints (such as labor time availability and materials for packing). However, Joe wants to keep his model simple. He knows that eventually he'll have to take other constraints into account, but as a first-pass model, he'll restrict the constraints to just three resources: plastic, chrome, and stainless steel.
With only three constraints, Joe can formulate the problem easily as

MAX: 3.0X1 + 3.0X2 + 5.0X3
Subject to: 1.2X1 + 1.7X2 + 1.2X3 ≤ 1,000
0.8X1 + 0X2 +2.3X3 ≤1,200
2.0X1 + 3.0X2 + 4.5X3 ≤2000
X1, X2, X3 ≥0

Where:
X1= the number of ballpoint pens
X2= the number of mechanical pencils
X3- the number of fountain pens

Joe's knowledge of Excel and the Solver feature is limited, so he asks you to enter and solve the problem for him, then answer the following questions. (Assume that each question is independent unless otherwise stated.)

a) What should the weekly production mix consist of, and what is the weekly net profit?

b) Is the optimal solution to question 1 degenerate? Explain your response.

c) Is the optimal solution from question 1 unique, or are there alternate answers to this question? Explain your response.

d) What is the marginal value of one more unit of chrome? Of plastic?

e) A local distributor has offered to sell Parket Sisters an additional 500 ounces of stainless steel for $0.60 per ounce more than it ordinarily pays. Should the company buy the steel at this price? Explain your response

f) If Parket Sisters buys the additional 500 ounces of stainless steel noted in question 5, what is the new optimal profit? Explain your response

g) Suppose the distributor offers to sell Parket Sisters some additional plastic at a price of only $1.00 over its usual cost of $5.00 per ounce. However, the distributor will sell the plastic only in lot sizes of 500 ounces. Should Parket Sisters buy one such lot? Explain your responses

h) The distributor is willing to sell the plastic in lots of just 100 ounces instead of the usual 500-ounce lots, still at $1.00 over Parket Sisters cost of $5.00 per ounce. How many lots (if any) should Parket Sisters buy? What is the optimal product mix if the company buys these lots, and what is the optimal profit?

i) Parket Sisters has an opportunity to sell some of its plastic for $6.50 per ounce to another company. The other company (which does not produce pens and pencils and, therefore, is not a competitor) wants to buy 300 ounces of plastic from Parket Sisters. Should Parket Sisters sell the plastic to the other company? What happens to Parket Sisters product mix and overall profit if it does sell the plastic? Be as specific as possible.

j) The chrome supplier might have to fulfill an emergency order, and would be able to send only 1,000 ounces of chrome this week instead of the usual 1,200 ounces. If Parket Sisters receives only 1,000 ounces of chrome, what is the optimal product mix and optimal profit? Be specific as possible.

k) The R&D department at Parket Sisters has been redesigning the mechanical pencil to make it more profitable. The new design requires 1.1 ounces of plastic, 2.0 ounces of chrome, and 2.0 ounces of stainless steel. If the company can sell one of these pencils at a net profit of $3.00, should it approve the new design? Explain your response

l) If the per-unit profit on ball point pens decreases to $2.50m what is the optimal product mix and what is the company's total profit?
m) The marketing department suggested introducing a new felt tip pen that requires 1.8 ounces of plastic, 0.5 ounces of chrome, and 1.3 ounces of stainless steel. What profit must this product generate to make it worthwhile to produce?

n) What must the minimum per-unit profit of mechanical pencils be to make them worthwhile to produce?

o) Management believes that the company should produce at least 20 mechanical pencils per week to round out its product line. What effect would this have on overall profit? Give a numerical answer.

p) If the profit on a fountain pen is $6.75 instead of $5.00, what is the optimal product mix and optimal profit?