Linear Programming: Sensitivity Analysis and Interpretation of Solution

which is about Linear Programming: Sensitivity Analysis and Interpretation of Solution

Student Name ________________________
Chapter 8
Linear Programming: Sensitivity Analysis and Interpretation of
Solution
Case Problem 1: Product Mix, Page 355 in text
YOU WILL REPLACE THE PROFIT PER NUT MIX, AND SHIPMENT VALUES OF THE TEXT’S PROBLEM
WITH THOSE PROVIDED IN THE SPREADSHEET TEMPLATE.
1.
Cost per pound of ingredients. Type answers into the appropriate spaces. Name your file
A1FirstnameLastname.docx. Eg “A1MichaelConyette”. Submit the document through Moodle as a Word
attachment.
Cost per pound Almonds = $______________
Cost per pound Brazil
= $______________
Cost per pound Filberts = $______________
Cost per pound Pecans = $______________
Cost per pound Walnuts = $______________
Cost of nuts in three mixes:
Cost per pound Regular mix = $______________
Cost per pound Deluxe mix = $______________
Cost per pound Holiday mix = $______________
2.
Let R = pounds of Regular Mix produced
D = pounds of Deluxe Mix produced
H = pounds of Holiday Mix produced
Note that the cost of the five shipments of nuts is a sunk (not a relevant) cost and should not affect the
decision. The difference between relevant and sunk costs is critical.
The following linear programming model can be solved to maximize profit contribution for the nuts already
purchased. Write below using proper format (as introduced in Chapter 7 starting on page 252) the standard
form for solving this problem.
Max
______R
+
_____D
+
_____H
Subject
to (s.t.)
Also, email an Excel spreadsheet showing your solution, and include the sensitivity report for this problem. Excel
should generate this report when you direct it to do this after it solves the LP problem. Excel will create an
additional worksheet that you’ll see at the bottom of spreadsheet.
The optimal objective function value is ______________________
3.
Place your response to question 3 from the text here.
_____________________________________________________________________________________________
_____________________________________________________________________________________________
_____________________________________________________________________________________________
_____________________________________________________________________________________________
_____________________________________________________________________________________________
_____________________________________________________________________________________________
4.
Put your clear explanation here. Attach appropriate sensitivity analysis to support your points.
_____________________________________________________________________________________________
_____________________________________________________________________________________________
_____________________________________________________________________________________________
_____________________________________________________________________________________________
_____________________________________________________________________________________________
_____________________________________________________________________________________________
5.
Skip question 5.
• Submit this Word document form with your assignment through Moodle in
the Dropbox as a Word attachment. Name your file
A1FirstnameLastname.docx.
• Submit also the Excel template & student data File named “A1 LP Product
Mix for DE students.xlsx”
• It is based on Chapters 7 & 8 so you should have completed them before
attempting this assignment.
Name
Nut Mixes
Regular
Given Profit Contribution/lb
Deluxe
4.00
2.00
Holiday
3.00
Shipment (lbs)
Almond
6604
Brazil
7500
Filbert
6214
Pecan
6000
Walnut
8979
Regular Mix produced
10000
Deluxe Mix produced
3000
Holiday Mix produced
5000
Use the profit and shipment values above
instead of those from the text’s problem
Use the Cost per shipment as provided in text
See Case Problem 1 – Product Mix on Page 355 of the text.
Do questions 1-4 and submit Excel spreadsheet template provided named ‘answers to submit’ with
See also Word document template for your answers to complete and submit via email.
Email your Excel spreadsheet with formulas through Moodle email as an Excel attachment
Name your file A1FirstnameLastname.xlsx.
Product Mix
Nuts
Almond
Brazil
Filbert
Pecan
Walnut
Given Profit Contribution/lb
Nut Mixes
Regular Deluxe Holiday
Model
Decision Variables
Regular Deluxe Holiday
Number of Pounds
Maximize Total Profit
Constraint
Almond
Brazil
Filbert
Pecan
Walnut
Regular Mix produced
Deluxe Mix produced
Holiday Mix produced
LHS
RHS
0
0
0
>=
>=
>=
10000
3000
5000
Calculations by Solver should show in blue areas and elsewhere
ided named ‘answers to submit’ with your formulas included.
ete and submit via email.