Linear Programming using Excel solver
Using excel solver
1. Generate Input Sheet
2. Answer report
3. Sensitivity report to the following LP Model
=number of necklaces to be made
= number of bracelets to be made
= number of ear-studs to be made
Maximize Profit (Z) =
Subject to
Maximum availability of gold
Maximum availability of platinum
Minimum necklace required
Exact ear studs to be produced
(Non-negativity constraint)
Note that fractional values for the jewels are acceptable.
4. Answer the following questions based on your answer and sensitivity report. To some of these questions you cannot fill up the answer. For such questions provide
your answer in a sentence or two.
Important:
First copy and paste these questions in a word document. Answer the questions in the word document. After answering all the questions, copy the questions a through
j, paste it under the data input sheet of your excel file. Save your excel file in your name.
Upload your excel file to Blackboard before the due date/time. You should not upload your word file.
a. What is the solution to the problem?
b. Which of the resource(s) is/are completely used?
c. The current solution will change if the profits of necklace is increased above $_________ and decreased below $___________
d. The current solution will change if the profits of bracelet is increased by $_________ and decreased by $___________
e. If we increase the availability of gold by 15 ounces, what is the new profit in $______
f. Similarly if we decrease the availability of platinum by 3 ounces, what will be the new profit?
g. The shadow prices will remain valid if the availability of gold is between _______ and _______
h. What is shadow price?
i. Write a sentence or two about the shadow prices you obtained in your sensitivity report.
j. If the objective coefficient of x2 which is 75 is not known, how will you calculate it. Show your calculations
