Spreadsheet Modeling & Decision Analysis: A Practical Introduction to Business Analytics (MindTap Course List)
8th Edition
ISBN: 9781305947412
Author: Cliff Ragsdale
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Concept explainers
Question
Chapter 4, Problem 9QP
Summary Introduction
To determine: The sensitivity report using solver.
a)
Summary Introduction
To determine: The price drop of watermelons for the given condition.
b)
Summary Introduction
To determine: The price increase of cantaloupes for the given condition.
c)
Summary Introduction
To determine: Whether the solution is optimal for the given condition.
d)
Summary Introduction
To determine: The acres that the farmer should lease and the maximum amount to be paid.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
Using ExcelÂ
Solve the following LP
Maximize $4x + $5y
Subject to       2x + 3y ≤ 20 (labor, in hours)
                               6x + 6y ≤ 36 (materials, in pounds)
                 4x + 4y ≤ 40 (storage, in square feet)
                               x, y ≥ 0
a) Write the original optimal solution and objective function value.
b) What is the optimal solution and objective function value if you acquire 2 additional pounds of material?
c) What is the optimal solution and objective function value if you acquire 1.5 additional hours of labor?
The optimal solution of this linear programming problem is at the intersection of constraints 1 and 2.
Max    6x1 + 3x2
s.t.   Â
Â
Â
Â
Â
Â
4x1Â +Â x2
≤
400
Â
4x1Â + 3x2
≤
600
Â
x1Â + 2x2
≤
300
Â
x1, x2
≥
0
Â
Â
(a)
Over what range can the coefficient of x1 vary before the current solution is no longer optimal? (Round your answers to two decimal places.)
------ to --------
(b)
Over what range can the coefficient of x2 vary before the current solution is no longer optimal? (Round your answers to two decimal places.)
-----Â Â to --------
(c)
Compute the dual value for the first constraint, second constraint & third constraint
The model was solved using solver and part of the results is provided in the table below. Use it to answer the questions that follow. Use it to answer the questions that follow.
                                          Variable Cells
Cell
Name
Final Value
Reduced Cost
Objective Coefficient
Allowable Increase
Allowable Decrease
$B$10
X1
4
0
10
6
4
$B$11
X2
4
0
12
2.666667
4
$B$12
X3
4
0
12
2.666667
4
Â
Constraints
Cell
Name
Final Value
Shadow Price
Constraint R.H Side
Allowable Increase
Allowable Decrease
$B$5
Manufacturing Usage
20
3.6
20
6.6666667
10
$B$6
Assembly Usage
20
1.6
20
6.666667
10
$B$7
Testing Usage
20
1.6
20
6.666667
10
Â
Determine the optimal solution to the problem.
(a) Which resource constraint(s) are binding? Give a reason.Â
(b) If the company could increase manufacturing hours by 10 hours per week or increase the assembly hours by…
Chapter 4 Solutions
Spreadsheet Modeling & Decision Analysis: A Practical Introduction to Business Analytics (MindTap Course List)
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, management and related others by exploring similar questions and additional content below.Similar questions
- This problem is based on Motorolas online method for choosing suppliers. Suppose Motorola solicits bids from five suppliers for eight products. The list price for each product and the quantity of each product that Motorola needs to purchase during the next year are listed in the file P06_93.xlsx. Each supplier has submitted the percentage discount it will offer on each product. These percentages are also listed in the file. For example, supplier 1 offers a 7% discount on product 1 and a 30% discount on product 2. The following considerations also apply: There is an administrative cost of 5000 associated with setting up a suppliers account. For example, if Motorola uses three suppliers, it incurs an administrative cost of 15,000. To ensure reliability, no supplier can supply more than 80% of Motorolas demand for any product. A supplier must supply an integer amount of each product it supplies. Develop a linear integer model to help Motorola minimize the sum of its purchase and administrative costs.arrow_forwardAnswer the given question with a proper explanation and step-by-step solution.  Consider the following optimization problemMinimize 3x1+8x2s.t.2x1+6x2 > 6x1+2x2 > 2.5x1+3x2 > 0.5x1>0 x2>0a) Graph the feasible region of this linear program. Is the feasible region bounded?Why? (you may use an online tool like desmos.com/calculator)b) Are any of the above constraints redundant? If so, indicate which one(s). Explainwhy.c) Solve the linear program using the graphical method. Explain your approach.d) Consider the feasible region drawn in Part (a) and answer the following: If we addthe constraint 3x1+x2> a (hint: you may use desmos.com/calculator and shift theparameter a to see how the feasible region changes)i. For what values of a is this a redundant constraint, if any? Why?ii. For what values of a is the above optimal solution no longer optimal, ifany? Why?iii. For what values of a does the problem become infeasible, if any? Why?e) Consider the original formulation…arrow_forwardFind the optimal solution for the following problem.    Maximize C = 4x + 12y subject to 3x + 5y ≤ 12  6x + 2y ≤ 10 and x ≥ 0, y ≥ 0.   What is the optimal value of x?      What is the optimal value of y? (Round your answer to 3 decimal places.)      What is the maximum value of the objective function? (Round your answer to 3 decimal places.)arrow_forward
- Find the optimal solution for the following problem. (Round your answers to 3 decimal places.) Maximize C = 14x + 13y + 5z subject to 9x + 11y + 18z ≤ 61  14x + 15y + 12z ≤ 122 and x ≥ 0, y ≥ 0, z ≥ 0.   What is the optimal value of x? What is the optimal value of y? What is the optimal value of z? What is the maximum value of the objective function?arrow_forwardSolve for the Simplex Method. The Munchies Cereal Company makes a cereal from several ingredients. Two of the ingredients, oats and rice, provide vitamins A and B. The company wants to know how many ounces of oats and rice it should include in each box of cereal to meet the minimum requirements of 48 milligrams of vitamin A and 12 milligrams of vitamin B while minimizing cost. An ounce of oats contributes 8 milligrams of vitamin A and 1 milligram of vitamin B, whereas an ounce of rice contributes 6 milligrams of A and 2 milligrams of B. An ounce of oats costs $0.05, and an ounce of rice costs $0.03.arrow_forwardThe optimal solution of this linear programming problem is at the intersection of constraints 1 and 2. Max    3x1 + x2 s.t.         4x1 + x2 ≤ 400  4x1 + 3x2 ≤ 600  x1 + 2x2 ≤ 300  x1, x2 ≥ 0 Over what range can the coefficient of x1 vary before the current solution is no longer optimal? (Round your answers to two decimal places.) Compute the dual value for the third constraint.arrow_forward
- Please help with correct answers in details: step by step  Q1 The optimal solution of this linear programming problem is at the intersection of constraints 1 and 2. Max    3x1 + x2 s.t.         4x1 + x2 ≤ 400  4x1 + 3x2 ≤ 600  x1 + 2x2 ≤ 300  x1, x2 ≥ 0 Over what range can the coefficient of x1 vary before the current solution is no longer optimal? (Round your answers to two decimal places.)  ______ to ______? Over what range can the coefficient of x2 vary before the current solution is no longer optimal? (Round your answers to two decimal places.) _______ to _______? Compute the dual value for the first constraint. _______ Compute the dual value for the second constraint. _______ Compute the dual value for the third constraint. _______arrow_forwardConsider the following linear programming model with 4 regular constraints:Maximize 3X + 5Y (a) Draw your graph in the space below:subject to: 4X + 4Y ≤ 48 (constraint #1) 4X + 3Y ≤ 50 (constraint #2) 2X + 1Y ≤ 20 (constraint #3) X ≥ 2 (constraint #4) X, Y ≥ 0 (non-negativity constraints)(a) Which of the constraints is redundant? Constraint #______.Justify by drawing a graph similar to Figure 7.14 on p.263.(b) Is point (9,3) a feasible solution? _____. Explain your answer (by analyzing each of the constraints).Constraint #1: _______________________________________________________________Constraint #2: _______________________________________________________________Constraint #3: _______________________________________________________________Constraint #4: ______________________________________________________________arrow_forwardGiven this linear programming model, solve the model and then answer the questions that follow.Maximize Z = 12x1 + 18x2 + 15x3 where x1 = the quantity of product 1 to make, etc.Subject toMachine 5x1 + 4x2 + 3x3 ≤ 160 minutes Labor 4x1 + 10x2 + 4x3 ≤ 288 hoursMaterials 2x1 + 2x2 + 4x3 ≤ 200 poundsProduct 2 x2 ≤ 16 units x1, x2, x3 ≥ 0 a. Are any constraints binding? If so, which one(s)?arrow_forward
- Solve the LP problem. If no optimal solution exists, indicate whether the feasible region is empty or the objective function is unbounded. (Enter EMPTY if the region is empty. Enter UNBOUNDED if the function is unbounded.) Minimize c = x + 2y subject to x + 3y ≥ 23 8x + y ≥ 23 x≥ 0, y≥ 0.  c= x,y=arrow_forwardThe problem below involves three variables. Solve it with the simplex method, Excel, or some other technology.Patio Iron makes wrought iron outdoor dining tables, chairs, and stools. Each table uses 8 feet of a standard width wrought iron, 2 hours of labor for cutting and assembly, and 2 hours of labor for detail and finishing work. Each chair uses 6 feet of the wrought iron, 2 hours of cutting and assembly labor, and 1.5 hours of detail and finishing labor. Each stool uses 1 foot of the wrought iron, 1.5 hours for cutting and assembly, and 0.5 hour for detail and finishing work, and the daily demand for stools is at most 18. Each day Patio Iron has available at most 142 feet of wrought iron, 63 hours for cutting and assembly, and 50 hours for detail and finishing. If the profits are $60 for each dining table, $48 for each chair, and $36 for each stool, how many of each item should be made each day to maximize profit? tables chairs stoolsFind the maximum profit.$arrow_forwardDevelop a linear programming model to minimize the total dollars needed to be invested now to meet the expansion cash needs in the next 8 years. Hint: this is the objective function. Use Excel Solver, solving method: Simplex LP. How many units of each security 1, 2, and 3 should the corporation purchase? What is the investment amount in each security 1, 2, and 3 in year 1? How much should the corporation place in the savings account in each of the 8 years? Assuming the corporation currently has $1.8 Million available cash to invest and given the cash needs for the expansion, is the investment plan that you developed feasible? Explain your answer and provide reasoning. I am unable to figure out how to calculate the optimal solution amounts so that I can calculate teh remaining itemsarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Practical Management ScienceOperations ManagementISBN:9781337406659Author:WINSTON, Wayne L.Publisher:Cengage,
Practical Management Science
Operations Management
ISBN:9781337406659
Author:WINSTON, Wayne L.
Publisher:Cengage,