I need support with this Excel question so I can learn better.
Shelby Shelving is a small company that manufactures two
types of shelves for grocery stores. Model S is the stan-
dard 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 appropri-
ate 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. Sep-
arate assembly departments are used for the final stage of
The file C13_01.xlsx contains relevant data for Shelby:
The hours required on each machine for
each unit of product are shown in the range B5: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 assem-
bly 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. The plant’s
engineer suggested that the current production of model S
shelves be cut back. According to him, “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 disagreed. He said that the problem was the
model S assembly department trying to absorb a large over
-head with a small production volume. “The model S units
are making a contribution to overhead. Even though produc-
tion 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 prob-
lem, then run Solver, and finally make a recommendation to
Shelby management, with a short verbal argument support-
ing the engineer or the controller.
"Place your order now for a similar assignment and have exceptional work written by our team of experts, guaranteeing you A results."