Shelby Shelving Case Study
EXECUTIVE SUMMARY
Shelby Shelving is a small company that produces two types of shelves for stores (Model S and Model LX). Shelves are manufactured in three steps: stamping, forming and assembly. In the stamping stage, a large machine is used to stamp standart 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.
Although the shelves are selling well, the total profit of the company is a concern. An engineer suggested that the current production of model S should be cut back because Model S shelves are sold for $1800 per unit but their costs are $1839. Therefore, company is losing money on each one. But the controller disagreed with that idea. He thought that the problem was the model S assembly department trying to absorb a large overhead with a small production volume.
Our aim is to determine the optimal monthly production in order to maximize the total profit while satisfying the resource constraints such as assembly capacity and available hours for stamping and forming. We will use Excel Solver for optimizing monthly production and for maximizing total profit.
MODEL
We have to determine monthly production of Model S and LX and therefore we have to maximize the total profit. But there are some limitations of company and also there are some assumptions.
Assumptions
* All produced shelves should be sold. * Selling price cannot be changed because of competition. Unit prices of Model S and LX are $1800 and $2100 respectively. * Quantitive of products should be non-negativ
Constraints
* Assembly capacity for Model S is 1900 units/month and for Model LX is 1400 units/month. * Available hours for stamping and forming are 800 hours/month. * Monthly production for Model S and LX should be greater or equal to zero. * Montly production for Model S and LX should be integer.
Some Necessary Formulas
Direct
Shelby Shelving is a small company that produces two types of shelves for stores (Model S and Model LX). Shelves are manufactured in three steps: stamping, forming and assembly. In the stamping stage, a large machine is used to stamp standart 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.
Although the shelves are selling well, the total profit of the company is a concern. An engineer suggested that the current production of model S should be cut back because Model S shelves are sold for $1800 per unit but their costs are $1839. Therefore, company is losing money on each one. But the controller disagreed with that idea. He thought that the problem was the model S assembly department trying to absorb a large overhead with a small production volume.
Our aim is to determine the optimal monthly production in order to maximize the total profit while satisfying the resource constraints such as assembly capacity and available hours for stamping and forming. We will use Excel Solver for optimizing monthly production and for maximizing total profit.
MODEL
We have to determine monthly production of Model S and LX and therefore we have to maximize the total profit. But there are some limitations of company and also there are some assumptions.
Assumptions
* All produced shelves should be sold. * Selling price cannot be changed because of competition. Unit prices of Model S and LX are $1800 and $2100 respectively. * Quantitive of products should be non-negativ
Constraints
* Assembly capacity for Model S is 1900 units/month and for Model LX is 1400 units/month. * Available hours for stamping and forming are 800 hours/month. * Monthly production for Model S and LX should be greater or equal to zero. * Montly production for Model S and LX should be integer.
Some Necessary Formulas
Direct