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 T06.CaseData.xlsx contains relevant data for Shelby. 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. 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 out costs are $1839. Even though we’re selling only 400 units a month, we’re losing money on each one. We should decease production of model S.” The controller disagreed. He 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 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 the engineer or the controller.

Notes on accounting data calculations

From the attached file, you can find the accounting data that you can use to build you model and make analysis.

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.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 × 0.125) to model S shelves and $83,125 (= 95,000 × 0.875) to 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/400) 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 (= 29.69 + 120, 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 (= 59.38 + 170, shown in cell H20 rounded down to $229).

Requirements

Use the accounting data to figure out the relevant input parameters, and then build a linear programming model in Excel for Shelby Shelving. Define the appropriate range names and then set up Solver to address the following questions.

1. What are the best production plan for Shelby Shelving? What is the best profit the firm can achieve?
2. Who will you support between the engineer and the controller? Give a short verbal argument on why you do so.
3. Suppose Shelby wants to invest on their three departments’ capacity. Do some sensitivity analysis and answer the following questions.
   1. Does all departments’ capacity need to increase? Give the reason to support your claim.
   2. In general, which department’s capacity should be considered the first to increase? Why?
4. Suppose there are some additional restrictions on the capacity expanding investment.
   • Shelby plans to borrow at most $1500 from the bank at annual rate of 12% for one month, to implement any possible capacity expanding.
   • It costs $150 to increase every one hour of capacity in forming department, and $100 for increasing every one hour of stamping capacity.
   • In assembly department, the capacity for Model S and Model LX can be easily converted, without any cost, between each other for any amount smaller than 20 units. But it costs $300 for any conversion beyond the first 20 units and for any unit of new capacity expanding.

Please answer the following questions.

1. If Shelby can only change one department’s capacity, which department should be selected? And why?
2. If Shelby can change multiple departments’ capacity, could you propose a best investment plan? What is it?