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

See similar textbooks

Related questions

Question

corrugated

carton

number of units

profit

constraints

cutting

= <

120

pinning

= <

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

This is a popular solution!

Step by step

Solved in 2 steps with 7 images

SEE SOLUTION Check out a sample Q&A here