Shelby Shelving is a small company that The file C13_01.xlsx contains relevant data fo
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.
Shelby. (See Figure XXXXXXXXXXThe 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. Cu
ently 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 cu
ent 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 abso
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
ecommendation to Shelby management, with a short ve
al argument supporting the engineer or the controller.
Notes on Accounting Data Calculations
The fixed overhead is distributed using activity-based costing principles. For example, at cu
ent production levels, the forming machine spends 100 hours on model S shelves and 700 hours on model LX shelves. The forming machine is used 800 hours of the month, of which 12.5% of the time is spent on model S shelves and 87.5% is spent on model LX shelves.The $95,000 of fixed overhead in the forming department is distributed as $11,875 (= 95,000 × XXXXXXXXXXto model S shelves and $83,125 (= 95,000 × XXXXXXXXXXto model LX shelves.The fixed overhead per unit of output
is allocated as $29.69 (= 11,875/400) for model S
and $59.38 (= 83,125/1400) for model LX. In the calculation of the standard overhead cost, the fixed and variable costs are added together, so that the overhead cost for the forming department allocated to a model S shelf is $149.69 (= XXXXXXXXXX, shown in cell G20 rounded up to $150). Similarly, the overhead cost for the forming department allocated to a model LX shelf is $229.38 (= XXXXXXXXXX, shown in cell H20 rounded down to $229).
Data
Shelby Shelving Data for Cu
ent Production Schedule
Machine requirements (hours per unit) Given monthly overhead cost data
Model S Model LX Fixed Variable S Variable LX
Stamping 0.3 0.3 Stamping $125,000 $80
Chris Al
ight: Overhead per unit of model S
$90
Chris Al
ight: Overhead per unit of model LX
Forming 0.25 0.5 Forming $95,000 $120 $170
Model S Assembly $80,000 $165 $0
Model S Model LX Model LX Assembly $85,000 $0 $185
Cu
ent monthly production 400 1400
Standard costs of the shelves -- based on the cu
ent production levels
Hours spent in departments Model S Model LX
Model S Model LX Totals Direct materials $1,000 $1,200
Stamping 120 420 540 Direct labor:
Forming 100 700 800 Stamping $35 $35
Forming $60 $90
Percentages of time spent in departments Assembly $80 $85
Model S Model LX Total direct labor $175 $210
Stamping 22.2% 77.8% Overhead allocation
Forming 12.5% 87.5% Stamping $149 $159
Forming $150 $229
Unit selling price $1,800 $2,100 Assembly $365 $246
Total overhead $664 $635
Assembly capacity 1900 1400 Total cost $1,839 $2,045
9. Consider the graphical solution to the product mix problem. Now imagine that another constraint—
any constraint—is added. Which of the following three things are possible: (1) the feasible region shrinks; (2) the feasible region stays the same; (3) the feasible region expands? Which of the following three things are possible: (1) the optimal value in objective cell decreases; (2) the optimal value in objective cell stays the same; (3) the optimal value in objective cell increases? Explain your answers. Do they hold just for this particular model, or do they hold in general?