1. Explain your observations about the optimal solution returned by the Solver. 2. If the company has budget to increase the total capacity by 1,000 units, at which plant would you recommend them to expand? What would be total cost savings (i.e., potentially more reduction in the total costs) with this expansion? Refer to the sensitivity report and explain your answer. (Copy and paste the constraints section of the sensitivity report here.) 3. Suppose that the Atlanta plant had to reduce capacity by 1,000 units to repair and renovate. How much would this cause the total (optimized) transportation costs to increase? Refer to the sensitivity report and explain your answer. All i need is help with explaing these answers using the sensitivy reports and answer reports.
1. Explain your observations about the optimal solution returned by the Solver. 2. If the company has budget to increase the total capacity by 1,000 units, at which plant would you recommend them to expand? What would be total cost savings (i.e., potentially more reduction in the total costs) with this expansion? Refer to the sensitivity report and explain your answer. (Copy and paste the constraints section of the sensitivity report here.) 3. Suppose that the Atlanta plant had to reduce capacity by 1,000 units to repair and renovate. How much would this cause the total (optimized) transportation costs to increase? Refer to the sensitivity report and explain your answer. All i need is help with explaing these answers using the sensitivy reports and answer reports.
Practical Management Science
6th Edition
ISBN:9781337406659
Author:WINSTON, Wayne L.
Publisher:WINSTON, Wayne L.
Chapter2: Introduction To Spreadsheet Modeling
Section: Chapter Questions
Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...
Related questions
Question
100%
1. Explain your observations about the optimal solution returned by the Solver.
2. If the company has budget to increase the total capacity by 1,000 units, at which plant would you recommend them to expand? What would be total cost savings (i.e., potentially more reduction in the total costs) with this expansion? Refer to the sensitivity report and explain your answer. (Copy and paste the constraints section of the sensitivity report here.)
3. Suppose that the Atlanta plant had to reduce capacity by 1,000 units to repair and renovate. How much would this cause the total (optimized) transportation costs to increase? Refer to the sensitivity report and explain your answer.
All i need is help with explaing these answers using the sensitivy reports and answer reports.
![MErosoft Excel 16.55 Answer Report
Worksheet: [Q1_template-2.xlsm]data and model
Report Created: 12/14/21 5:18:27 PM
Result: Solver found a solution. All constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 9997.253 Seconds.
Iterations: 10 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Solve Without Integer Constraints, Assume NonNegative
Objective Cell (Min)
Cell
Name
Original Value
Final Value
$G$16 Los Angeles Minimize total shipment costs
$0
$86,800
Variable Cells
Cell
Name
Original Value
Final Value
Integer
0.0 Contin
$B$16 Los Angeles East
$$16 Los Angeles Midwest
$D$16 Los Angeles South
ŞE$16 Los Angeles West
0.0
0.0 Contin
0.0
0.0 Contin
0.0
10,000.0 Contin
$B$17 Atlanta East
0.0
0.0 Contin
$C$17 Atlanta Midwest
0.0
3,000.0 Contin
$D$17 Atlanta South
0.0
6,000.0 Contin
$E$17 Atlanta West
0.0
3,000.0 Contin
$B$18 New York City East
0.0
9,000.0 Contin
$C$18 New York City Midwest
$D$18 New York City South
0.0
3,000.0 Contin
0.0
0.0 Contin
$E$18 New York City West
0.0
0.0 Contin
Constraints
Cell
Name
Cell Value
Formula
Status
Slack
$B$23 East Total received
9,000.0 $B$23>=$D$23 Binding
6,000.0 $B$24>=$D$24 Binding
6,000.0 $B$25>=$D$25 Binding
13,000.0 $B$26>=$D$26 Binding
10,000.0 $B$30<=$D$30 Binding
12,000.0 $B$31<=$D$31 Binding
12,000.0 $B$32<=$D$32 Not Binding
0.0
$B$24 Midwest Total received
0.0
$B$25 South Total received
0.0
$B$26 West Total received
0.0
$B$30 Los Angeles Total Shipped
$B$31 Atlanta Total Shipped
$B$32 New York City Total Shipped
2000
$B$16 Los Angeles East
$C$16 Los Angeles Midwest
$D$16 Los Angeles South
$B$16>=0
Binding
Binding
Binding
0.0
0.0 $C$16>=0
0.0
0.0 $D$16>=0
0.0
SE$16 Los Angeles West
$B$17 Atlanta East
10,000.0 ŞE$16>=0
0.0 $B$17>=0
3,000.0 $C$17>=0
Not Binding 10,000.0
Binding
0.0
$C$17 Atlanta Midwest
Not Binding
3,000.0
$D$17 Atlanta South
6,000.0 $D$17>=0
Not Binding
6,000.0
$E$17 Atlanta West
3,000.0 $E$17>=0
Not Binding
3,000.0
9,000.0 $B$18>=0
3,000.0 $C$18>=0
$B$18 New York City East
Not Binding
9,000.0
$C$18 New York City Midwest
$D$18 New York City South
Not Binding
Binding
Binding
3,000.0
0.0 $D$18>=0
0.0
ŞE$18 New York City West
0.0 $E$18>=0
0.0](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F439be9f9-0d64-43a9-83b1-9d3989d4f7af%2F022fbf79-c178-40b5-ac0a-621d8096ca9e%2Fy27vobc_processed.png&w=3840&q=75)
Transcribed Image Text:MErosoft Excel 16.55 Answer Report
Worksheet: [Q1_template-2.xlsm]data and model
Report Created: 12/14/21 5:18:27 PM
Result: Solver found a solution. All constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 9997.253 Seconds.
Iterations: 10 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Solve Without Integer Constraints, Assume NonNegative
Objective Cell (Min)
Cell
Name
Original Value
Final Value
$G$16 Los Angeles Minimize total shipment costs
$0
$86,800
Variable Cells
Cell
Name
Original Value
Final Value
Integer
0.0 Contin
$B$16 Los Angeles East
$$16 Los Angeles Midwest
$D$16 Los Angeles South
ŞE$16 Los Angeles West
0.0
0.0 Contin
0.0
0.0 Contin
0.0
10,000.0 Contin
$B$17 Atlanta East
0.0
0.0 Contin
$C$17 Atlanta Midwest
0.0
3,000.0 Contin
$D$17 Atlanta South
0.0
6,000.0 Contin
$E$17 Atlanta West
0.0
3,000.0 Contin
$B$18 New York City East
0.0
9,000.0 Contin
$C$18 New York City Midwest
$D$18 New York City South
0.0
3,000.0 Contin
0.0
0.0 Contin
$E$18 New York City West
0.0
0.0 Contin
Constraints
Cell
Name
Cell Value
Formula
Status
Slack
$B$23 East Total received
9,000.0 $B$23>=$D$23 Binding
6,000.0 $B$24>=$D$24 Binding
6,000.0 $B$25>=$D$25 Binding
13,000.0 $B$26>=$D$26 Binding
10,000.0 $B$30<=$D$30 Binding
12,000.0 $B$31<=$D$31 Binding
12,000.0 $B$32<=$D$32 Not Binding
0.0
$B$24 Midwest Total received
0.0
$B$25 South Total received
0.0
$B$26 West Total received
0.0
$B$30 Los Angeles Total Shipped
$B$31 Atlanta Total Shipped
$B$32 New York City Total Shipped
2000
$B$16 Los Angeles East
$C$16 Los Angeles Midwest
$D$16 Los Angeles South
$B$16>=0
Binding
Binding
Binding
0.0
0.0 $C$16>=0
0.0
0.0 $D$16>=0
0.0
SE$16 Los Angeles West
$B$17 Atlanta East
10,000.0 ŞE$16>=0
0.0 $B$17>=0
3,000.0 $C$17>=0
Not Binding 10,000.0
Binding
0.0
$C$17 Atlanta Midwest
Not Binding
3,000.0
$D$17 Atlanta South
6,000.0 $D$17>=0
Not Binding
6,000.0
$E$17 Atlanta West
3,000.0 $E$17>=0
Not Binding
3,000.0
9,000.0 $B$18>=0
3,000.0 $C$18>=0
$B$18 New York City East
Not Binding
9,000.0
$C$18 New York City Midwest
$D$18 New York City South
Not Binding
Binding
Binding
3,000.0
0.0 $D$18>=0
0.0
ŞE$18 New York City West
0.0 $E$18>=0
0.0
![Mcrosoft Excel 16.55 Sensitivity Report
Worksheet: [Q1_template-2.xlsm]data and model
Report Created: 12/14/21 5:18:28 PM
Variable Cells
Final Reduced Objective Allowable Allowable
Cell
Name
Value
Cost
Coefficient Increase
Decrease
$B$16 Los Angeles East
$C$16 Los Angeles Midwest
$D$16 Los Angeles South
$E$16 Los Angeles West
$B$17 Atlanta East
5.6
5
1E+30
5.6
3.5
3.5
1E+30
3.5
5
4.2
1E+30
10000
2.2
3.1
1E+30
1.2
3.2
1E+30
1.2
$C$17 Atlanta Midwest
3000
2.6
0.5
0.1
$17 Atlanta South
6000
1.8
1
2.3
$E$17 Atlanta West
3000
4.8
0.1
3.1
$B$18 New York City East
$C$18 New York City Midwest
$D$18 New York City South
$E$18 New York City West
9000
2.5
1.2
2.5
3000
3.1
0.1
0.5
1
3.3
1E+30
0.1
5.4
1Е+30
0.1
Constraints
Final Shadow Constraint Allowable Allowable
Cell
Name
Value
Price
R.H. Side
Increase
Decrease
$B$23 East Total received
9000
2.5
9000
2000
9000
$B$24 Midwest Total received
6000
3.1
6000
2000
3000
$B$25 South Total received
6000
2.3
6000
2000
3000
$B$26 West Total received
13000
5.3
13000
2000
3000
$B$30 Los Angeles Total Shipped
$B$31 Atlanta Total Shipped
$B$32 New York City Total Shipped 12000
10000
-3.1
10000
3000
2000
12000
-0.5
12000
3000
2000
14000
1E+30
2000](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F439be9f9-0d64-43a9-83b1-9d3989d4f7af%2F022fbf79-c178-40b5-ac0a-621d8096ca9e%2Fo6jr21e_processed.png&w=3840&q=75)
Transcribed Image Text:Mcrosoft Excel 16.55 Sensitivity Report
Worksheet: [Q1_template-2.xlsm]data and model
Report Created: 12/14/21 5:18:28 PM
Variable Cells
Final Reduced Objective Allowable Allowable
Cell
Name
Value
Cost
Coefficient Increase
Decrease
$B$16 Los Angeles East
$C$16 Los Angeles Midwest
$D$16 Los Angeles South
$E$16 Los Angeles West
$B$17 Atlanta East
5.6
5
1E+30
5.6
3.5
3.5
1E+30
3.5
5
4.2
1E+30
10000
2.2
3.1
1E+30
1.2
3.2
1E+30
1.2
$C$17 Atlanta Midwest
3000
2.6
0.5
0.1
$17 Atlanta South
6000
1.8
1
2.3
$E$17 Atlanta West
3000
4.8
0.1
3.1
$B$18 New York City East
$C$18 New York City Midwest
$D$18 New York City South
$E$18 New York City West
9000
2.5
1.2
2.5
3000
3.1
0.1
0.5
1
3.3
1E+30
0.1
5.4
1Е+30
0.1
Constraints
Final Shadow Constraint Allowable Allowable
Cell
Name
Value
Price
R.H. Side
Increase
Decrease
$B$23 East Total received
9000
2.5
9000
2000
9000
$B$24 Midwest Total received
6000
3.1
6000
2000
3000
$B$25 South Total received
6000
2.3
6000
2000
3000
$B$26 West Total received
13000
5.3
13000
2000
3000
$B$30 Los Angeles Total Shipped
$B$31 Atlanta Total Shipped
$B$32 New York City Total Shipped 12000
10000
-3.1
10000
3000
2000
12000
-0.5
12000
3000
2000
14000
1E+30
2000
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 5 images
Recommended textbooks for you
Practical Management Science
Operations Management
ISBN:
9781337406659
Author:
WINSTON, Wayne L.
Publisher:
Cengage,
Operations Management
Operations Management
ISBN:
9781259667473
Author:
William J Stevenson
Publisher:
McGraw-Hill Education
Operations and Supply Chain Management (Mcgraw-hi…
Operations Management
ISBN:
9781259666100
Author:
F. Robert Jacobs, Richard B Chase
Publisher:
McGraw-Hill Education
Practical Management Science
Operations Management
ISBN:
9781337406659
Author:
WINSTON, Wayne L.
Publisher:
Cengage,
Operations Management
Operations Management
ISBN:
9781259667473
Author:
William J Stevenson
Publisher:
McGraw-Hill Education
Operations and Supply Chain Management (Mcgraw-hi…
Operations Management
ISBN:
9781259666100
Author:
F. Robert Jacobs, Richard B Chase
Publisher:
McGraw-Hill Education
Purchasing and Supply Chain Management
Operations Management
ISBN:
9781285869681
Author:
Robert M. Monczka, Robert B. Handfield, Larry C. Giunipero, James L. Patterson
Publisher:
Cengage Learning
Production and Operations Analysis, Seventh Editi…
Operations Management
ISBN:
9781478623069
Author:
Steven Nahmias, Tava Lennon Olsen
Publisher:
Waveland Press, Inc.