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