Assignment 2 Shelby Shelving Case
From
Date April 13, 2014
Executive Summary:
The problem is that Shelby Shelving, a company that manufactures different types of shelves for grocery stores, needs to optimize their profits. The key issue is that Shelby Shelving cannot increase prices of their shelves. The solution is to change the company’s operations by adjusting resource allocation and product production. We investigate the product mix that optimizes company profits as well as the resources and activities that serve as bottlenecks throughout the manufacturing process. In addition, we explore the underlying accounting processes of the company to ensure that profit calculations are done correctly. Finally, we also seek to understand the impact of increasing capacity and changing unit costs on the production plan.
Model:
Decision: Optimize resource allocation
Objective: To determine the production plan (product mix and resource allocation) that optimizes profits for the company.
Assumptions: Four resource constraints include: Stamping, Forming, Model S assembly, and Model LX assembly; Shelby Shelving has no other sources of profit aside from the Model S and Model LX shelves;
Structure:
Analysis:
Using Solver we calculated the optimal number of Model S and Model LX units based on the resource constraints, with the objective function set as profit per month and the desirable variables of units per month for each model.
To determine the bottlenecks resources/activities we examined the Sensitivity Report, specifically identifying the resources with 0 slack, and shadow prices of $0. From there, we determined the two bottlenecks to be Forming and Model S Assembly, with Forming as the more profitable group with added capacity.
Without using Solver, we determined Shelby could earn an additional $290 per hour with expanded capacity in the Forming group. This was calculated by subtracting the fixed overhead costs per unit ($200), from the shadow price ($490). To calculate the break-even point (at which the marginal cost is equal to the shadow price), we used a one-way data table with an input of Forming capacity to determine the number of hours at which there is no profit loss.
To determine the impact of an increase of $100 per unit in material costs, we examined the Sensitivity Report. From the report, it is clear that the production plan will remain the same up until an increase of $137.50 in material costs. The impact on profit is the number of units to be produced (1,900) * the decrease in profit per unit ($100). The total profit loss is $19,000.
Conclusions:
Answer (a): The optimal number of shelves that Shelby should produce is 1,900 of the Model S and 650 of the Model LX shelves. The total monthly corresponding profit is $268,250. The corresponding allocation of the monthly available resources involved is depicted in the table below.
Resource/Activity
Model S
Model LX
Stamping
765 hrs
800 hrs
Forming
800 hrs
800 hrs
Model S Assembly
1,900 hrs
1,900 hrs
Model LX Assembly
650 hrs
1,400 hrs
The shelving production process has two bottlenecks in the process. These two are Forming and Model S Assembly. Each of these resources/activities has no associated shadow price or available slack.
Answer (b): The optimal product mix currently includes 1,900 units of the Model S and 650 units of the Model LX shelves. However, in the case, Shelby is producing 400 and 1,400 units respectively. Currently, the firm includes fixed overhead costs in making their allocation decisions, which
