Sensitivity Analysis

Reduced Cost  |  Shadow Price

Sensitivity analysis gives you insight in how the optimal solution changes when you change the coefficients of the model. After the solver found a solution, you can create a sensitivity report.

1. Before you click OK, select Sensitivity from the Reports section.



Below you can find the optimal solution and the sensitivity report.





It is optimal to order 94 bicycles and 54 mopeds. This solution gives the maximum profit of 25600. This solution uses all the resources available (93000 units of capital and 101 units of storage). You can find these numbers in the Final Value column.

Reduced Cost

The reduced costs tell us how much the objective coefficients (unit profits) can be increased or decreased before the optimal solution changes. If we increase the unit profit of Child Seats with 20 or more units, the optimal solution changes.

1. At a unit profit of 69, it's still optimal to order 94 bicycles and 54 mopeds. Below you can find the optimal solution.




2. At a unit profit of 71, the optimal solution changes.



Conclusion: it is only profitable to order child seats if you can sell them for at least 70 units.

Shadow Price

The shadow prices tell us how much the optimal solution can be increased or decreased if we change the right hand side values (resources available) with one unit.

1. With 101 units of storage available, the total profit is 25600. Below you can find the optimal solution.



2. With 102 units of storage available, the total profit is 25700 (+100).



Note: with a shadow price of 100 for this resource, this is according to our expectations. This shadow price is only valid between 101 - 23,5 and 101 + 54 (see sensitivity report)..

Read More »

Capital Investment

Formulate the Model  |  Trial and Error  |  Solve the Model

Use the solver in Excel to find the combination of capital investments that maximizes the total profit.

Formulate the Model

The model we are going to solve looks as follows in Excel.




1. To formulate this binary integer programming (BIP) model, answer the following three questions.

a. What are the decisions to be made? For this problem, we need Excel to find out which capital investments to make (Yes=1, No=0).

b. What are the constraints on these decisions? First, the amount of capital used by the investments cannot exceed the limited amount of capital available (50). For example, investment One uses 12 units of capital. Second, only investment One or investment Two can be made. Third, only investment Three or investment Four can be made. Fourth, investment Six and investment Seven can only be made if investment Five is made.

c. What is the overall measure of performance for these decisions? The overall measure of performance is the total profit of the capital investments made, so the objective is to maximize this quantity.

2. To make the model easier to understand, name the following ranges.



3. Insert the following five SUMPRODUCT functions.



Explanation: cell K7 (the amount of capital used) equals the sumproduct of the range C7:I7 and YesNo, cell K8 equals the sumproduct of the range C8:I8 and YesNo, etc. Total Profit equals the sumproduct of Profit and YesNo.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

1. For example, if we make investment One and Two, the second constraint is violated.



2. For example, if we make investment Six and Seven, without making investment Five, the fourth constraint is violated.



3. However, it's OK to make investment One, Five and Six. All constraints are satisfied.



It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.


Solve the Mode

To find the optimal solution, execute the following steps.

1. On the Data tab, in the Analyze group, click Solver.



Note: can't find the Solver button? Click here to load the Solver add-in.

Enter the solver parameters (read on). The result should be consistent with the picture below.



2. Enter TotalProfit for the Objective.

3. Click Max.

4. Enter YesNo for the Changing Variable Cells.

5. Click Add to enter the following constraint.



6. Click Add to enter the following constraint.



Note: binary variables are either 0 or 1.

7. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.

8. Finally, click Solve.

Result:



The optimal solution:



Conclusion: it is optimal to make investments Two, Four, Five and Seven. This solution gives the maximum profit of 146. All constraints are satisfied..

Read More »

Maximum Flow Problem

Formulate the Model  |  Trial and Error  |  Solve the Model

Use the solver in Excel to find the maximum flow from node S to node T in a directed network. Points in a network are called nodes (S, A, B, C, D, E and T). Lines in a network are called arcs (SA, SB, SC, AC, etc).

Formulate the Model

The model we are going to solve looks as follows in Excel.



1. To formulate this maximum flow problem, answer the following three questions.

a. What are the decisions to be made? For this problem, we need Excel to find the flow on each arc. For example, if the flow on SB is 2, cell D5 equals 2.

b. What are the constraints on these decisions? The Net Flow (Flow Out - Flow In) of node A, B, C, D and E should be equal to 0. In other words, Flow Out = Flow In. Also, each arc has a fixed capacity. The flow on each arc should be less than this capacity.

c. What is the overall measure of performance for these decisions? The overall measure of performance is the maximum flow, so the objective is to maximize this quantity. The maximum flow equals the Flow Out of node S.

2. To make the model easier to understand, name the following ranges.



3. Insert the following functions.



Explanation: The SUMIF functions calculate the Net Flow of each node. For node A, the first SUMIF function sums the values in the Flow column with an "A" in the From column (Flow Out). The second SUMIF function sums the values in the Flow column with an "A" in the To column (Flow In). Maximum Flow equals the value in cell I4, which is the flow out of node S. Because node A, B, C, D and E have a Net Flow of 0, Flow Out of node S will equal Flow In of node T.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

1. For example, the path SADT with a flow of 2. The path SCT with a flow of 4. The path SBET with a flow of 2. These paths give a total flow of 8.



It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.



Solve the Model

To find the optimal solution, execute the following steps.

1. On the Data tab, in the Analyze group, click Solver.



Note: can't find the Solver button? Click here to load the Solver add-in.

Enter the solver parameters (read on). The result should be consistent with the picture below.



You have the choice of typing the range names or clicking on the cells in the spreadsheet.

2. Enter MaximumFlow for the Objective.

3. Click Max.

4. Enter Flow for the Changing Variable Cells.

5. Click Add to enter the following constraint.



6. Click Add to enter the following constraint.



7. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.

8. Finally, click Solve.

Result:



The optimal solution:



Conclusion: the path SADT with a flow of 2. The path SCT with a flow of 4. The path SBET with a flow of 2. The path SCET with a flow of 2. The path SACET with a flow of 1. The path SACDT with a flow of 1. These paths give a maximum flow of 12..

Read More »

Shortest Path Problem

Formulate the Model  |  Trial and Error  |  Solve the Model

Use the solver in Excel to find the shortest path from node S to node T in an undirected network. Points in a network are called nodes (S, A, B, C, D, E and T). Lines in a network are called arcs (SA, SB, SC, AC, etc).

Formulate the Model
The model we are going to solve looks as follows in Excel.



1. To formulate this shortest path problem, answer the following three questions.

a. What are the decisions to be made? For this problem, we need Excel to find out if an arc is on the shortest path or not (Yes=1, No=0). For example, if SB is part of the shortest path, cell F5 equals 1. If not, cell F5 equals 0.

b. What are the constraints on these decisions? The Net Flow (Flow Out - Flow In) of each node should be equal to Supply/Demand. Node S should only have one outgoing arc (Net Flow = 1). Node T should only have one ingoing arc (Net Flow = -1). All other nodes should have one outgoing arc and one ingoing arc if the node is on the shortest path (Net Flow = 0) or no flow (Net Flow = 0).

c. What is the overall measure of performance for these decisions? The overall measure of performance is the total distance of the shortest path, so the objective is to minimize this quantity.

2. To make the model easier to understand, name the following ranges.



3. Insert the following functions.



Explanation: The SUMIF functions calculate the Net Flow of each node. For node S, the SUMIF function sums the values in the Go column with an "S" in the From column. As a result, only cell F4, F5 or F6 can be 1 (one outgoing arc). For node T, the SUMIF function sums the values in the Go column with a "T" in the To column. As a result, only cell F15, F18 or F21 can be 1 (one ingoing arc). For all other nodes, Excel looks in the From and To column. Total Distance equals the sumproduct of Distance and Go.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

1. For example, the path SBET has a total distance of 16.



It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.

Solve the Model

To find the optimal solution, execute the following steps.

1. On the Data tab, in the Analyze group, click Solver.



Note: can't find the Solver button? Click here to load the Solver add-in.

Enter the solver parameters (read on). The result should be consistent with the picture below.



You have the choice of typing the range names or clicking on the cells in the spreadsheet.

2. Enter TotalDistance for the Objective.

3. Click Min.

4. Enter Go for the Changing Variable Cells.

5. Click Add to enter the following constraint.



6. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.

7. Finally, click Solve.

Result:



The optimal solution:



Conclusion: SADCT is the shortest path with a total distance of 11..

Read More »

Assignment Problem

Formulate the Model  |  Trial and Error  |  Solve the Model

Use the solver in Excel to find the assignment of persons to tasks that minimizes the total cost.

Formulate the Model
The model we are going to solve looks as follows in Excel.



1. To formulate this assignment problem, answer the following three questions.

a. What are the decisions to be made? For this problem, we need Excel to find out which person to assign to which task (Yes=1, No=0). For example, if we assign Person 1 to Task 1, cell C10 equals 1. If not, cell C10 equals 0.

b. What are the constraints on these decisions? Each person can only do one task (Supply=1). Each task only needs one person (Demand=1).

c. What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the assignment, so the objective is to minimize this quantity.

2. To make the model easier to understand, name the following ranges.



3. Insert the following functions.



Explanation: The SUM functions calculate the number of tasks assigned to a person and the number of persons assigned to a task. Total Cost equals the sumproduct of Cost and Assignment.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

For example, if we assign Person 1 to Task 1, Person 2 to task 2 and Person 3 to Task 3, Tasks Assigned equals Supply and Persons Assigned equals Demand. This solution has a total cost of 147.



It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.

Solve the Model

To find the optimal solution, execute the following steps.

1. On the Data tab, in the Analyze group, click Solver.



Note: can't find the Solver button? Click here to load the Solver add-in.

Enter the solver parameters (read on). The result should be consistent with the picture below.



You have the choice of typing the range names or clicking on the cells in the spreadsheet.

2. Enter TotalCost for the Objective.

3. Click Min.

4. Enter Assignment for the Changing Variable Cells.

5. Click Add to enter the following constraint.



Note: binary variables are either 0 or 1.

6. Click Add to enter the following constraint.



7. Click Add to enter the following constraint.



8. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.

9. Finally, click Solve.

Result:



The optimal solution:



Conclusion: it is optimal to assign Person 1 to task 2, Person 2 to Task 3 and Person 3 to Task 1. This solution gives the minimum cost of 129. All constraints are satisfied..

Read More »

Transportation Problem

Formulate the Model  |  Trial and Error  |  Solve the Model

Use the solver in Excel to find the number of units to ship from each factory to each customer that minimizes the total cost.

Formulate the Model

The model we are going to solve looks as follows in Excel.



1. To formulate this transportation problem, answer the following three questions.

a. What are the decisions to be made? For this problem, we need Excel to find out how many units to ship from each factory to each customer.

b. What are the constraints on these decisions? Each factory has a fixed supply and each customer has a fixed demand.

c. What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the shipments, so the objective is to minimize this quantity.

2. To make the model easier to understand, name the following ranges.



3. Insert the following functions.



Explanation: The SUM functions calculate the total shipped from each factory (Total Out) to each customer (Total In). Total Cost equals the sumproduct of UnitCost and Shipments.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

For example, if we ship 100 units from Factory 1 to Customer 1, 200 units from Factory 2 to Customer 2, 100 units from Factory 3 to Customer 1 and 200 units from Factory 3 to Customer 3, Total Out equals Supply and Total In equals Demand. This solution has a total cost of 27800.



It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.

Solve the Model

To find the optimal solution, execute the following steps.

1. On the Data tab, in the Analyze group, click Solver.



Note: can't find the Solver button? Click here to load the Solver add-in.

Enter the solver parameters (read on). The result should be consistent with the picture below.



You have the choice of typing the range names or clicking on the cells in the spreadsheet.

2. Enter TotalCost for the Objective.

3. Click Min.

4. Enter Shipments for the Changing Variable Cells.

5. Click Add to enter the following constraint.



6. Click Add to enter the following constraint.



7. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.

8. Finally, click Solve.

Result:



The optimal solution:



Conclusion: it is optimal to ship 100 units from Factory 1 to Customer 2, 100 units from Factory 2 to Customer 2, 100 units from Factory 2 to Customer 3, 200 units from Factory 3 to Customer 1 and 100 units from Factory 3 to Customer 3. This solution gives the minimum cost of 26000. All constraints are satisfied..

Read More »