Using Excel solve this problem BeGemmed is a popular jewelry store. Using gems like rubies and opals they produce two very popular items. Their first design uses three opals and two rubies. It also requires one hour of labor. This product sells for $400. Their second design is more labor intensive. It requires two hours of labor. This design consists of three rubies and two opals. This product sells for $500. These products are so popular that BeGemmed believes that they can sell whatever they produce. For the current month, BeGemmed has 70 hours of labor hours available. For this month’s production they have ordered 120 opals and that’s the maximum their supplier can provide them this month. However, they have ordered 100 rubies and their supplier can provide them with more rubies for $100 per ruby. Based on past sales, they want to produce at least 20 of design 1 and at least 25 design 2 products. BeGemmed should solve the following LP to maximize its profits: DS 806- Optimization Methods I X1 : Design 1 produced X2 : Design 2 produced R2 : number of rubies purchased max z = 400X1 + 500X2 - 100R s.t. 2X1 +3X2 - R < 100 3X1 + 2X2 < 120 X1 + 2X2 <70 X1 >20 X2 > 25 R, X1, X2 > 0 Please include your solver(AMPL or Excel) screen shot and output. (Use “sensitivity report” to answer the questions). Consider each change by itself.

a. Due to some shortages in the global jewelry market, BeGemmed was informed that they will no longer be able to purchase extra rubies at $100. Instead they would have to pay $190 per ruby. Would BeGemmed’s optimal solution change? What would be the new optimal value of the problem?

b. Going over their past sales data BeGemmed manager thinks that they should not commit to producing 25 of design 2 products. She thinks that it might be an over estimate. What would the profit be if they were to be build at least of 23 design 2 product.

c. BeGemmed is considering asking their jeweler to work extra hours. What is the most that BeGemmed should offer to pay for another hour?

d. Their supplier has told BeGemmed that more opal may be available for purchase and asked them what they are willing to pay? What is the most that BeGemmed should offer for another opal? e. BeGemmed is considering introducing a new designed product that would require four rubies and two opals. This design would require 1 hour of labor. They price this new designed product at $550. Should they introduce this product?