# explain the case study and present the results in a professional

The problem details are listed on the first tab while the supporting calculations are on the second tab.
Must be well organized, show all steps and follow the directions in full. (see below)
For this problem you will submit the final product which will be an Excel spreadsheet used to create the model and either a Word document or a Power Point presentation. The final project will be graded not only on the accuracy of the quantitative solutions, but also the analytical approach used and the presentation of the results. Keep in mind that this course is designed for individuals interested in Business Management. As such, the final presentation should be appropriate for a presentation in a professional setting. It will be necessary to clearly explain the case study and present the results in a professional, yet easily understood manner.
The presentation should clearly state the objective, the constraints in obtaining that objective, the factors that can be varied, the sensitivity of the model to the variable factors, and the potential weakness of the conclusions.
Must include LP graphs, and show all work including how you arrived at the answer (solver tables) including ranges (see below)
Must be organized and easy to follow. THE MORE DETAIL AND INFORMATION PROVIDED THE BETTER
Worksheet: [excel.xlsx]Model
Report Created: 7/8/2015 10:48:28 PM
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.016 Seconds.
Iterations: 9 Subproblems: 0
Solver Options
Max Time 100 sec, Iterations 100, Precision 0.000001
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 0.05%, Solve Without Integer Constraints, Assume NonNegative
Objective Cell (Min)
Cell
Name
Original Value
Final Value
\$B\$31
TotGallons
2450
2450
Variable Cells
Cell
Name
Original Value
Final Value
Integer
\$D\$16
Flow
Contin
\$D\$17
Flow
1
1
Contin
\$D\$18
Flow
Contin
\$D\$19
Flow
Contin
\$D\$20
Flow
Contin
\$D\$21
Flow
1
1
Contin
Shelby Shelving
Shelby Shelving is a small company that manufactures two types of shelves for grocery stores. Model S is the standard model, and model LX is a heavy-duty
model. 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 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 feels 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 where as 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 him, “Model S shelves are sold for \$1800 per unit, but our costs are \$1839. Even though we’re only selling 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.
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.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 x 0.125) to model S shelves and \$83,125 (= 95,000 x 0.875) to model LX shelves. The fixed overhead per unit of output is allocated as \$29.69 (=
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 (= 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).
For this problem you will submit the final product which will be an Excel spreadsheet used to create the model and either a Word document or a Power Point presentation.
The final project will be graded not only on the accuracy of the quantitative solutions, but also the analytical approach used and the presentation of the results.
Keep in mind that this course is designed for individuals interested in Business Management. As such, the final presentation should be appropriate for a presentation in a professional setting.
It will be necessary to clearly explain the case study and present the results in a professional, yet easily understood manner.
The presentation should clearly state the objective, the constraints in obtaining that objective,
the factors that can be varied, the sensitivity of the model to the variable factors, and the poten

Place New Order
It's Free, Fast & Safe

### "Looking for a Similar Assignment? Order now and Get a Discount!

Feeling Lucky?

Enter your email address to spin the wheel for a chance to win exciting offers.