Multistep problem involving stock options and profit. Solutions in MS Excel. Complete with Limit, Sensitivity and Answer reports.

The linear programming (LP) problem whose output follows determines how many necklaces (x1), bracelets (x2), rings (x3), and earrings (x4) a jewelry store should stock. The objective function measures profit; it is assumed that every piece stocked will be sold. Constraint 1 measures display space in units, constraint 2 measures time to set up the display in minutes. Constraints 3 and 4 are marketing restrictions.

         LINEAR PROGRAMMING PROBLEM

Max    100X1 + 120X2 + 150X3 + 125X4

s.t.
  1)      X1 +   2X2 +   2X3 +   2X4 < 108
  2)     3X1 +   5X2 +            X4 < 120
  3)      X1 +            X3         <  25
  4)              X2 +    X3 +    X4 >  50

OPTIMAL SOLUTION

Objective Function Value = 7475.000

       Variable                Value               Reduced Costs
          X1                   8.000                   0.000
          X2                   0.000                   5.000
          X3                  17.000                   0.000
          X4                  33.000                   0.000

      Constraint           Slack/Surplus             Dual Prices
           1                   0.000                  75.000
           2                  63.000                   0.000
           3                   0.000                  25.000
           4                   0.000                 -25.000

OBJECTIVE COEFFICIENT RANGES

       Variable           Lower Limit       Current Value      Upper Limit
          X1                 87.500            100.000       No Upper Limit
          X2            No Lower Limit         120.000           125.000
          X3                125.000            150.000           162.500
          X4                120.000            125.000           150.000

   RIGHT HAND SIDE RANGES

      Constraint          Lower Limit       Current Value      Upper Limit
           1                100.000            108.000           123.750
           2                 57.000            120.000       No Upper Limit
           3                  8.000             25.000            58.000
           4                 41.500             50.000            54.000


Use the output on the problem to answer the questions.

a. How many necklaces should be stocked?

b. How many bracelets should be stocked?

c. How much space will be left unused?

d. How much time will be used?

e. By how much will the second marketing restriction be exceeded?

f. What is the profit?

g. To what value can the profit on necklaces drop before the solution would change?

h. By how much can the profit on rings increase before the solution would change?

i. You are offered the chance to obtain more space. The offer is for 15 units and the total price is $1500.  What should you do?