Shelby Shelving is a small company that manufactures two types of shelves for grocery stores. Model S is the standard model; model LX is a heavy-duty version. Shelves are manufactured in three major steps: stamping, forming, and assembly. In the stamping stage, a large machine is used to stamp (i.e. cut) standard sheets of metal into appropriate sizes. In the forming stage, another machine bends the metal into shape. Assembly involves joining the parts with a combination of soldering and riveting. Shelby's stamping and forming machines work on both models of shelves. Separate assembly departments are used for the final stage of production.
The file C03_01.XLSX contains relevant data for Shelby. The hours required on each machine for each unit of product are show in the range B6:C6 of the Accounting Data sheet. For example, the production of one model S shelf requires 0.25 hour on the forming machine. Both the stamping and forming machines can operate for 800 hours each month. The model S assembly department has a monthly capacity of 1900 units. The model LX assembly department has a monthly capacity of only 1400 units. Currently Shelby is producing and selling 400 units of model S and 1400 units of model LX per month.
Model S shelves are sold for $1800, and model LX shelves are sold for $2100. Shelby's operation is fairly small in the industry, and management at Shelby believes it cannot raise prices beyond these levels because of the competition. However, the marketing department believes that Shelby can sell as much as it can produce at these prices. The costs of production are summarized in the Accounting Data Sheet. As usual, values in blue cells are given, whereas other values are calculated from these.
Management at Shelby just met to discuss next month's operating plan. Although the shelves are selling well, the overall profitability of the company is a concern. Doug Jameson, the plan't engineer, suggested that the current production of model S shelves be cut back. According to Doug, "Model S shelves are sold for $1800 per unit, but our costs are $1839. Even though we're selling only 400 units a month, we're losing money on each one. We should decrease production of model S." The controller, Sarah Cranston, disagreed. She said that the problem was the model S assembly department trying to absorb a large overhead with a small production volume. "The model S units are making a contribution to overhead. Even though production doesn't cover all of the fixed costs, we'd be worse off with lower production."
Your job is to develop an LP model of Shelby's problem, then run Solver, and finally make a recommendation to Shelby management, with a short verbal argument supporting Doug or Sarah.
Attachment:- Question.zip