sporting goods bikes product mix decision & sensitivity analysis

September 18, 2016

Question:

A larger sporting goods store is placing an order for bicycles with its supplier. Four models can be ordered: the adult Open trail, the adult cityscape, the girl's sea sprite, and the boy's trail blazer. It is assumed that every bike ordered will be sold, and their profits, respectively, are 30, 25, 22, and 20. The LP model should maximize profit. There are several conditions that the store needs to worry about. One of these is space to hold theinventory. An Adult's bike needs 2 feet, but a child's bike needs only 1 foot. The store has 500 feet of space. There are 1200 hours of assembly time available. The child's bikes need 4 hours of assembly time; the open trail needs 5 hours and the cityscape needs 6 hours. The store would like to place an order for at least 275 bikes.

  1. Formulate a model for this problem.
  2. Solve your model with any computer package available to you. (In class we use excel for Goal Seek and Solver….if that helps).
  3. How many of each kind if bike should be ordered and what will profit be?
  4. What would the profit be if the store had 100 more feet of storage space?
  5. If the profit in the cityscape increases to 35.00, will any of the cityscape bikes be ordered?
  6. Over what range of assembly hours is the dual price applicable?
  7. If we require 5 more bikes in inventory, what will happen to the value of the optimal solution?
  8. Which resource should the company work to increase, inventory space or assembly time?

Answer:

  1. Letting the variables X1, X2, X3, and X4 denote the four different types of bikes, the linear program for the problem may be set up as shown below. The objective is to maximize aggregate profit under various constraints.

Maximize Z = 30X1 + 25X2 + 22X3 + 20X4
subject to:
2X1 / + / 2X2 / + / X3 / + / X4 / ≤ / 500 / Space constraint
5X1 / + / 6X2 / + / 4X3 / + / 4X4 / ≤ / 1200 / Assembly hours
X1 / + / X2 / + / X3 / + / X4 / ≥ / 275 / Minimum demand
X1, X2, X3, X4 / ≥ / 0 / Non-negativity conditions

The problem may be easily implemented in Excel. The solution is shown on next page; however, the Excel file is attached with this document.

  1. The model is shown above.
  2. The optimal solution prescribes 100 of Adult Open Trails and 175 of Girl’s Sea Sprite. The maximum profit possible is $6,850.
  3. The storage space is not a scarce constraint since only 375 feet of space is consumed. Therefore, additional 100 feet of space has no relevance.
  4. To answer this, we may look at the sensitivity report generated by Solver.

In the reduced cost column, we see that the profit function for X2 will need to go beyond (25 + 13) = $38 for this particular bike to be included in the optimal solution. As such, if the profit margin for City Scape increases to $35, it will have no bearing. It has to go beyond $38.

  1. The dual price (shadow price)for assembly hours is positive ($8 per hour) since we observe that the assembly time constraint is fully used up. Again, this information may be extracted from the sensitivity report; this is shown below:

Constraints
Final / Shadow / Constraint / Allowable / Allowable
Cell / Name / Value / Price / R.H. Side / Increase / Decrease
$G$24 / Space / 375 / 0 / 500 / 1E+30 / 125
$G$25 / Assembly / 1200 / 8 / 1200 / 125 / 100
$G$26 / Demand / 275 / -10 / 275 / 25 / 35

The applicable range is 1,100 ≤ 1,200 ≤ 1,325.

  1. In the table above, we see that there is a negative shadow price associated withthe demand constraint; that is, if we add one more to the inventory the optimal profit will reduce by $10. So, if we were to add another five bikes, the total profit will reduce by 5 x $10 = $50.
  2. Clearly, the resource that yields a positive impact is the assembly hours. We may increase the assembly time to as much as 1,325 hours.

1 / SAYED M. IRFAN | M.Engg. (Industrial Engineering) / Certified Supply Chain Design Analyst, MIT