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
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...
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
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 7 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.