corrugated carton                     number of units                         profit 6 4 0                   constraints                         cutting 2 3 0 = < 120               pinning 2 1 0 = < 60                                               LHS SIGN RHS                                                                                                                       Part B                         Step 1: Use the formulated problem as in part A with the return  and constraint coefficients to set up Excel sheet                                         Step 2                         Setting up the worksheet                       Key in the  profit coefficients  per   box type  in range B3:c3                     ■ key in the  LHS  constraint coefficients in terms of   Decision variables,  for example   variable "x1" or "b" has a coefficient of 2 is written 2x1 or 2b           using the range B5:C7                                                   Use sum product  formula in range D5: D7 similar to cell D3  to compute usage on the LHS                                              Using RHS constraint values for Part A formulation, Key in these RHS values in range  F5: F7                                             Leave the range for Decision variables B2: C2 empty or type in zeros (Solver will  get the values for you)                                           ■ In cell D3- use formula  Excel to compute total profit ( the sumproduct is useful here)                                             Step 3: Instructing Solver( please ensure that you have solver added in or go to Tools, Add-ins to add solver)               ■ Click the Tools menu and click Solver.                       ■ The Solver Parameters window will appear with the cursor located in the window                   to the right of Set Target Cell.                                                 ■ Click on cell F3  to make this the target cell, the target  total profit. In the next                   row Check that to the right of Equal To:, the button indicating Max is selected.                     ■ Under "By Changing Cells" type B2:E2( this represents the decision variables for which values are needed )                                           ■ Click the Add button to the right of Subject to the Constraints, which brings                     up the Add Constraint window. With the cursor in the Cell Reference window                                                                                                 After adding the constraints click Ok                                                 ■ You return to the Solver Parameters window and observe that there are entries under                   Subject to the Constraints.                                                 Click the Options button to the right of the window, and click Assume Linear                     Model and Assume Non-Negative, then OK.                                                 ■ Back in the Solver Parameters window click the Solve button on the top right                   of the window. The solution will appear in the worksheet.                                               ■ The Solver Results window appears. Click OK to retain the solution after selecting the answer report and sensitivity report

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...
icon
Related questions
Question
  corrugated carton                    
number of units                        
profit 6 4 0                  
constraints                        
cutting 2 3 0 = < 120              
pinning 2 1 0 = < 60              
                         
      LHS SIGN RHS              
                         
                         
                         
                         
Part B                        
Step 1: Use the formulated problem as in part A with the return  and constraint coefficients to set up Excel sheet              
                         
Step 2                        
Setting up the worksheet                      
Key in the  profit coefficients  per   box type  in range B3:c3                    
■ key in the  LHS  constraint coefficients in terms of   Decision variables,  for example   variable "x1" or "b" has a coefficient of 2 is written 2x1 or 2b          
using the range B5:C7                        
                         
Use sum product  formula in range D5: D7 similar to cell D3  to compute usage on the LHS                  
                         
 Using RHS constraint values for Part A formulation, Key in these RHS values in range  F5: F7                  
                         
Leave the range for Decision variables B2: C2 empty or type in zeros (Solver will  get the values for you)                
                         
■ In cell D3- use formula  Excel to compute total profit ( the sumproduct is useful here)                  
                         
Step 3: Instructing Solver( please ensure that you have solver added in or go to Tools, Add-ins to add solver)              
■ Click the Tools menu and click Solver.                      
■ The Solver Parameters window will appear with the cursor located in the window                  
to the right of Set Target Cell.                      
                         
■ Click on cell F3  to make this the target cell, the target  total profit. In the next                  
row Check that to the right of Equal To:, the button indicating Max is selected.                    
■ Under "By Changing Cells" type B2:E2( this represents the decision variables for which values are needed )                
                         
■ Click the Add button to the right of Subject to the Constraints, which brings                    
up the Add Constraint window. With the cursor in the Cell Reference window                  
                         
                         
                         
After adding the constraints click Ok                      
                         
■ You return to the Solver Parameters window and observe that there are entries under                  
Subject to the Constraints.                      
                         
Click the Options button to the right of the window, and click Assume Linear                    
Model and Assume Non-Negative, then OK.                      
                         
■ Back in the Solver Parameters window click the Solve button on the top right                  
of the window. The solution will appear in the worksheet.                    
                         
■ The Solver Results window appears. Click OK to retain the solution after selecting the answer report and sensitivity report              
                         
                         
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps with 7 images

Blurred answer
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Practical Management Science
Practical Management Science
Operations Management
ISBN:
9781337406659
Author:
WINSTON, Wayne L.
Publisher:
Cengage,
Operations Management
Operations Management
Operations Management
ISBN:
9781259667473
Author:
William J Stevenson
Publisher:
McGraw-Hill Education
Operations and Supply Chain Management (Mcgraw-hi…
Operations and Supply Chain Management (Mcgraw-hi…
Operations Management
ISBN:
9781259666100
Author:
F. Robert Jacobs, Richard B Chase
Publisher:
McGraw-Hill Education
Business in Action
Business in Action
Operations Management
ISBN:
9780135198100
Author:
BOVEE
Publisher:
PEARSON CO
Purchasing and Supply Chain Management
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…
Production and Operations Analysis, Seventh Editi…
Operations Management
ISBN:
9781478623069
Author:
Steven Nahmias, Tava Lennon Olsen
Publisher:
Waveland Press, Inc.