Figure 3.38 Data for Shelby Case D H 1 Shelby Shelving Data for Current Production Schedule Given monthly overhead cost data Fixed $125,000 $95,000 3 Machine requirements (hours per unit) Model S Model LX Ava ila ble Variable S Varia ble LX $90 $170 4 Stamping Forming Model S Assembly Model LX Assembly 5 Stamping 6 Foming 0.3 0.3 800 $80 0.25 0.5 800 $120 $80,000 $165 Model S Model LX $85,000 $185 9 Current monthly production 400 1400 Standard costs of the shelves- based on the current production levels Model S $1,000 10 Model LX $1,200 11 Hours spent in de partments 12 13 Stamping 14 Forming Model S Model LX Totals Direct materials Direct labor: Stamping Foming Assembly Total direct labor Overhead alocation Stamping Foming Assembly Total overhead Total cost 120 420 540 100 700 800 $35 $60 $80 $35 15 $90 16 Percentages of time spent in departments $85 $175 Model S Model LX 22.2% 77.8% 12.5% 87 5% $2 10 17 18 Stamping 19 Forming $149 $150 $365 $159 $229 $246 $664 $1839 20 21 Unit selling price $1,800 $2,100 $635 $2,045 22 23 Assembly capacity 1900 1400
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. (See Figure 3.38.) 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 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 plant’s 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.
USING SOLVER PLEASE SHOW FORMULAS
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 10 images
How does the requirement that the number of units of model LX produced be at least 60% of the total production affect profits?
by Bartleby ExpertWhat would the formula be for Cells B9 and C9 be to populate 0? Or is it just left blank?
by Bartleby Expert
