$\backslash$table$[[,$A$,B,$ 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 Shelby Shelving.xlsx contains relevant data for Shelby. The hours required on each

machine for each unit of product are shown in the range B$5$:C$6$ of the Accounting Data

sheet. For example, the production of one model S shelf requires $0\mathrm{.}25$ hour on the forming

machine. Both the stamping and forming machines can operate for $800$ hours each

month.

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. The marketing

department believes that Shelby can sell as much as it can produce at these prices.

The plant$$s engineer suggested that the current production of model S shelves be cut

back. According to him, Model S shelves were sold for $$1800$ per unit, but their costs were

$$1839.$ So$,$ they were losing money on each unit. The controller disagreed. He said that the

problem was that the model S assembly department trying to absorb a large overhead

with a small production volume. According to him, the model S units were contributing to

overhead, and they would be worse off with lower production.

Notes on Accounting Data Calculations: The fixed overhead is distributed using

activity$-$based costing principles. For example, at current 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\mathrm{.}5\%$ of the

time is spent on model S shelves and $87\mathrm{.}5\%$ is spent on model LX shelves. The

$$95,000$ of fixed overhead in the forming department is distributed as $$11,875(=$

$95,0000\mathrm{.}125)$ to model S shelves and $$83,125(=95,0000\mathrm{.}875)$ to model LX

shelves. The fixed overhead per unit of output is allocated as $$29\mathrm{.}69(=11,875/400)$

for model S and $$59\mathrm{.}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\mathrm{.}69(=29\mathrm{.}69+120,$ shown in cell G$20$ rounded up to $$150).$ Similarly, the

overhead cost for the forming department allocated to a model LX shelf is $$229\mathrm{.}38$

$(=59\mathrm{.}38+170,$ shown in cell H$20$ rounded down to $$229)$

Develop a constrained optimization model for Shelby$$s problem of allocating the available production capacity

among the two products. Clearly identify the decision variables, resource constraints, and the profit function, paying

particular attention to the units involved. State any assumptions you make and explain why. Assume that assembly for

each model takes one hour.

a$.$ Determine the optimal number of shelves that Shelby should produce and the corresponding monthly profit. Show the

corresponding allocation of the available resources, i$.$e$.,$ how much time in each department should be spent on each

type of shelf. Where are the bottlenecks? $($Answer: Maximum monthly profit $=$ $$268,250)$

b$.$ Compare the optimal solution with the one given in the case and the changes that they have proposed. Comment on

their method of allocating the fixed overhead between the two products, and how it has led them to an incorrect

assessment of profitability and product mix.

In answering the following questions, wherever possible, use that to give exact numerical answers, without rerunning the Solver.

c$.$ Suppose the company can increase the capacity in any one department at an additional cost of $$200$ per hour due to the

overtime premium and additional energy costs involved. Which department should they expand and by how much?

d$.$ Suppose that due to raw material shortages, the material cost for shelf Model S goes up by $$100.$ How would that affect

Shelby$$s production plan and monthly profit?

e$.$ Suppose that due to reduced competition, the selling price of Model LX can be raised to $$2400.$ What effect, if any, would it

have on Shelby$$s production plan and profit?

can you show each answer on excel?