Boston University Shelby Shelving Business Analytics Excel Worksheet

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

production.

The file C13_01.xlsx contains relevant data for Shelby:

C13_01.xlsx

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.

PreviousNext