we can make product1 and product2. there are 3 resources; resource1, resource2, resource3. product1...

We can make Product1 and Product2.There are 3 resources; Resource1, Resource2, Resource3. Product1 needs one hour of Resource1, nothing of Resource2, and three hours of resource3. Product2 needs nothing from Resource1, two hours of Resource2, and two hours of resource3. Available hours of resources 1, 2, 3 are 4, 12, 18, respectively. Contribution Margin of product 1 and Product2 are $300 and $500, respectively.Formulate the ProblemSolve the problem using solver in excel

Linear Programming

Objective Function Z = 3 x1 +5 x2ConstraintsResource 1x1 4 Resource 2 2x2 12Resource 3 3 x1 + 2 x2 18Nonnegativityx1 0, x2 0

Problem Formulation

Wyndor Example; Enter dataOrganize the data for the model on the spreadsheet. Type in the coefficients of the constraints and the objective function

Noncomputational Entries and Changing CellsChanging cells. Assign a set of cells to represent the decision variable in the model.The problem starts with assuming a value of 0 in each decision variable cell, and find the optimal solution.

Constraint cells. For each constraint, create a formula in a separate cell that corresponds to the left-hand side (LHS) of the constraint. SUMPRODUCT function is used to multiply element by element of two tables and addup all values. SUMPRODUCT: sums the products of individual cells in two ranges. SUMPRODUCT(C6:E6, C4:E4) = C6*C4 + D6*D4+ E6*E4The two specified ranges must be of the same size ( the same number of rows and columns). For linear programming you should try to always use the SUMPRODUCT function (or SUM) for the objective function and constraints. This is to remember that the equations are all linear.In LP we do not have square, square-root, log, exponential, cos, etc.SUMPRODUCT

SUMPRODUCT

Copy Down; LHS, and the Objective Function

Target Cell: The Objective FunctionTarget cell. A cell containing a formula that corresponds to the objective function.

Tools/Add-Ins/Solver

The target cell contains an equation that defines the objective and depends on the decision variables. You can only have one objective function. Target cell must be a single cell.In the Solver dialogue box select the Set Target Cell window, then click on the cell that you have already defined it as the objective function. This is the cell you wish to optimize. Then lick on the radio button of either Max or Min.Defining the Target Cell ( The Objective Function)

Identifying the Changing Cells ( Decision Variables) You next tell Excel which cells are decision variables, i.e., which cells excel is allowed to change when trying to optimize. Move the cursor to the By Changing Cells window, and drag the cursor across all cells you wish to treat as decision variables

Click on the Add button to the right of the constraints window.

A new dialogue box will appear. The cursor will be in the Cell Reference window within this dialogue box.

Click on the cell that contains the quantity you want to constrain.

The default inequality that first appears for a constraint is

Adding Constraints

You may define a set of similar constraints (e.g., all = constraints) in one step if they are in adjacent rows.

Simply select the range of cells for the set of constraints in both the Cell Reference and Constraint window.

After you are satisfied with the constraint(s),click the Add button if you want to add another constraint, orclick the OK button if you want to go back to the original dialogue box.

Notice that you may also force a decision variable to be an integer or binary (i.e., either 0 or 1) using this window. Adding Constraints

The Solver dialogue box now contains the optimization model, including the target cell (objective function), changing cells (decision variables), and constraints.Important Options

Click on the Options button in the Solver dialogue box, and click in both the Assume Linear Model and the Assume Non-Negative box.

The Assume Linear Model option tells Excel that it is a linear program. This speeds the solution process, makes it more accurate, and enables the more informative sensitivity report.

The Assume Non-Negative box adds non-negativity constraints to all of the decision variables.

Important Options

Check the Options then Solve

The Solution

When it is done, you will receive one of four messages:

Solver found a solution. All constraints and optimality conditions are satisfied. This means that Solver has found the optimal solution.

Cell values did not converge. This means that the objective function can be improved to infinity. You may have forgotten a constraint (perhaps the non-negativity constraints) or made a mistake in a formula.

Solver could not find a feasible solution. This means that Solver could not find a feasible solution to the constraints you entered. You may have made a mistake in typing the constraints or in entering a formula in your spreadsheet.

Conditions for Assume Linear Model not satisfied. You may have included a formula in your model that is nonlinear. There is also a slim chance that Solver has made an error. (This bug shows up occasionally.)The Solution

If Solver finds an optimal solution, you have some options. First, you must choose whether you want Solver to keep the optimal values in the spreadsheet (you usually want this one) or go back to the original numbers you typed in.

Click the appropriate box to make you selection. you also get to choose what kind of reports you want.

Once you have made your selections, click on OK.

You will often want to also have the Sensitivity Report.

To view the sensitivity report, click on the Sensitivity Report tab in the lower-left-hand corner of the window.The Solution

What is the Optimal Solution?

What is the value of the Objective Function for the Optimal Solution?

If the net profit for a product changes, will the optimal solution change?

If more (or less) of a resource is available, how it affect our profit?

For example:

How much do you pay to have one extra unit of Resource1 How much do you pay to have one extra unit of Resource3 How much do you pay to have one extra unit of Resource2

Questions Answered by Excel

Sensitivity

Final ValueThe value of the variable in the optimal solution

Reduced CostIncrease in the objective function value per unit increase in the value of a zero-valued variable (a product that the model has decided not to produce).

AllowableDefines the range of the cost coefficients in Increase/ the objective function for which the current Decrease solution (value of the variables in the optimal solution) will not change.Output from Computer Solution : Changing Cells

Sensitivity Report 1

Microsoft Excel 11.0 Sensitivity Report

Worksheet: [LP.xls]Sheet1

Report Created: 9/20/2007 11:22:14 AM

Adjustable Cells

FinalReducedObjectiveAllowableAllowable

CellNameValueCostCoefficientIncreaseDecrease

$B$9Solution Product12034.53

$C$9Solution Product260510000000000000000000000000000003

Constraints

FinalShadowConstraintAllowableAllowable

CellNameValuePriceR.H. SideIncreaseDecrease

$D$5Resource1 LHS20410000000000000000000000000000002

$D$6Resource2 LHS121.51266

$D$7Resource3 LHS1811866

Sheet1

Wyndor Glass Co. Product Mix

Hours used per unit producedWe needWe have

Product1Product2LHSRHS

Resource11024

Resource2021212

Resource3321818

Unit Profit3536

Solution26

Sheet2

Sheet3

Final Value The usage of the resource in the optimal solution.

Shadow price The change in the value of the objective function per unit increase in the right hand side of the constraint: Z = (Shadow Price)(RHS) (Only for change is within the allowable range)

Output from Computer Solution : Constraints





Adjustable Cells





Constraints



$D$5Resource1 LHS20410000000000000000000000000000002



Sheet1




Resource11024

Resource2021212

Resource3321818

Unit Profit3536

Solution26

Sheet2

Sheet3

Constraint The current value of the right hand side of the R.H. Side constraint (the amount of the resource that is available).

Allowable The range of values of the RHS for which Increase/ the shadow price is valid and hence for which Decrease the new objective function value can be calculated. (NOT the range for which the current solution will not change.)Output from Computer Solution : Constraints





Adjustable Cells





Constraints



$D$5Resource1 LHS20410000000000000000000000000000002



Sheet1




Resource11024

Resource2021212

Resource3321818

Unit Profit3536

Solution26

Sheet2

Sheet3

Net Profit product 1 = $7 Change the profit to 7, Solver again, Solve again





Adjustable Cells





Constraints



$D$5Resource1 LHS20410000000000000000000000000000002



Sheet1




Resource11024

Resource2021212

Resource3321818

Unit Profit3536

Solution26

Sheet2

Sheet3

Net Profit product 1 = $8 Change the profit to 8, Solver again, Solve again





Adjustable Cells





Constraints



$D$5Resource1 LHS20410000000000000000000000000000002



Sheet1




Resource11024

Resource2021212

Resource3321818

Unit Profit3536

Solution26

Sheet2

Sheet3

Wyndor Optimal Solution What is the optimal Objective function value for this problem?What is the allowable range for changes in the objective coefficient for Product 2What is the allowable range for changes in the RHS for Resource 3.If the coefficient of Product 2 in the objective function is changed to 7, what will happen to the value of the objective function?If the coefficient of Product 1 in the objective function is changed to 8, what will happen to the value of the objective function?If the RHS of Resource 2 is increased by 2 , what will happen to the objective function.If the RHS of Resource 1 is increased by 2, what will happen to the objective function.If the RHS of Resource 2 is decreased by 10, what will happen to the objective function.

Wyndor Optimal Solution

Practice: Given the following Sensitivity Analysis Report



Worksheet: [LPEXample2.xls]Sheet2

Report Created: 9/20/2007 1:23:55 PM

Adjustable Cells



$B$9Solution Product10-1.2514.99999999941.251000000000000000000000000000000

$C$9Solution Product2500020.00000000014.99999999980.42

$D$9Solution Product3375024.99999999990.714.9999999998

Constraints



$E$5Resource1 LHS40005001000000000000000000000000000000100

$E$6Resource2 LHS35012.500000000935049.999999999449.9999999995

$E$7Resource3 LHS15099.999999998315024.999999999718.7499999997

Sheet2

Delta Production

Minutes per claim

Product1Product2Product3LHSRHS

Resource10.450.50.4400500

Resource20.50.40.4350350

Resource30.10.150.2150150

Profit15202519375

Solution0500375

Sheet3

Sensitivity AnalysisWhat is the optimal objective function value for this problem? a.It cannot be determined from the given information. b.$900. c.$987.5. d.$875. e.$19375.What is the allowable range for the objective function coefficient for Product 3? a.0.71 P3 5. b.20 P3 25.71. c. 0 .71 P3 25.71. d.25 P3 25.71. e.non of the above.What is the allowable range of the right-hand-side for Resource1? a.500 RHS1 . b.0 RHS1 500. c.100 RHS1 . d.400 RHS1 . e.- RHS1 400.

Sensitivity AnalysisIf the coefficient for Product2 in the objective function changes to $24, then the objective function value: a.will increase by $24. b.will increase by $120,000. c.will increase by $2000. d.will remain the same. e.can only be discovered by resolving the problem.If the coefficient for Product1 in the objective function changes to $5, then the objective function value: a.will increase by $5. b.is $0. c.will increase by $10. d.will remain the same. e.can only be discovered by resolving the problem.If the coefficient of Product2 in the objective function changes to $15, then: a.the original solution remains optimal. b.the problem must be resolved to find the optimal solution. c.the optimal solution will decrease by 2500. d.the shadow price will decrease by 5. e.the optimal solution will increase by 2500.

Sensitivity AnalysisIf the right-hand side of Resource1 increases, then the objective function value: a.will increase. b.will decrease. c.will decrease then increase. d.will remain the same. e.will increase then decrease.If the right-hand side of Resource2 changes to 370, then the objective function value: a.will increase by $370. b.will increase by $350. c.will increase by $250. d.will remain the same. e.can only be discovered by resolving the problem.If the right-hand side of Resource3 changes to 130, then: a.the original solution remains optimal. b.the problem must be resolved to find the optimal solution. c.the objective function will decrease by 130. d.the objective function will decrease by 3000. e. the objective function will increase by 3000

More than one profit OR More than one resource If the sum of the ratio of (Change)/(Change in the Corresponding Direction)

Sensitivity AnalysisIf the objective coefficients of Product1 is increased by 1 and the objective coefficient of product3 is decreased by 2, then: a.the objective function will decrease. b.the objective function will increase. c.the optimal solution will remain the same. d.the shadow prices will remain the same. e.can only be discovered by resolving the problem.If the right-hand side of all three resources, each increases by 10 units: a.the optimal solution remains the same. The objective function value will increase. b.the optimal solution will change. The objective function value will increase. c.the optimal solution and the shadow prices will remain the same. d.the optimal solution and the shadow prices both will change. e. can only be discovered by resolving the problem. If the right-hand side of resouses1 and 2 each decreases by 20 and the right and side of resource 3 increases by 5: a.the objective function value will increase by 250. b.the objective function value will decrease by 250. c.the objective function value will remain the same. d.the optimal solution will remain the same. e. can only be discovered by resolving the problem.

Assignment The following 11 Questions refer to the following sensitivity report.

Assignment ( Taken from The management Sciences Hillier and Hillier)What is the optimal objective function value for this problem? a.it cannot be determined from the given information. b.$1,200. c.$975. d.$8,250. e.$500.What is the allowable range for the objective function coefficient for Activity 3? a.150 A3 . b.0 A3 650. c. 0 A3 250. d.400 A3 . e.300 A3 500.What is the allowable range of the right-hand-side for Resource A? a.0 RHSA 60. b.0 RHSA 110. c.60 RHSA 110. d.110 RHSA 160. e.0 RHSA 160.

Assignment ( Taken from The management Sciences Hillier and Hillier)If the coefficient for Activity 2 in the objective function changes to $400, then the objective function value: a.will increase by $7,500. b.will increase by $2,750. c.will increase by $100. d.will remain the same. e.can only be discovered by resolving the problem.If the coefficient for Activity 1 in the objective function changes to $50, then the objective function value: a.will decrease by $450. b.is $0. c.will decrease by $2750. d.will remain the same. e.can only be discovered by resolving the problem.If the coefficient of Activity 2 in the objective function changes to $100, then: a.the original solution remains optimal. The objective function value decreases. b.the problem must be resolved to find the optimal solution. c.the shadow prices will remain the same. d.the original solution and the objective function value remain the same. e.none of the above.

Assignment ( Taken from The management Sciences Hillier and Hillier)If the right-hand side of Resource B changes to 80, then the objective function value: a.will decrease by $750. b.will decrease by $1500. c.will decrease by $2250. d.will remain the same. e.can only be discovered by resolving the problem.If the right-hand side of Resource C changes to 140, then the objective function value: a.will increase by $137.50. b.will increase by $57.50. c.will increase by $80. d.will remain the same. e.can only be discovered by resolving the problem.If the right-hand side of Resource C changes to 130, then: a.the original solution remains optimal. The objective function value will increase. b.the problem must be resolved to find the optimal solution. c. the shadow prices remain the same. The objective function value will decrease.d.the shadow prices remain the same. the objective function value remains the same. e. the shadow prices will change. The objective function value will increase.

Assignment ( Taken from The management Sciences Hillier and Hillier)If the objective coefficients of Activity 2 and Activity 3 are both decreased by $100, then: a.the objective function will decrease by 2750. b. the objective function will decrease by less than 2750. c. the objective function will decrease by more than 2750. d. The objective function will remain the same. e. can only be discovered by resolving the problem. If the right-hand side of Resource C is increased by 40, and the right-hand side of Resource B is decreased by 20, then: a.the optimal solution remains the same. b.the objective function value increases by 1500. c.the shadow prices remain the same. d. can only be discovered by resolving the problem. e.the objective function value decreases by 1500. If the right-hand side of Resource A is increased by 25, and the right-hand side of Resource B is decreased by 20, then: a.the optimal solution remains the same. b.the objective function value increases by 1500. c.the objective function value remains the same. d.can only be discovered by resolving the problem. e.the objective function value decreases by 1500.

we can make product1 and product2. there are 3 resources; resource1, resource2, resource3. product1...

Documents

objective functiontarget

single cell

separate cell

decision variable cell

cells window

constraint cells

cell reference window

set target cell window