# Presentation: 548094

## Basic Trade Models in Excel

- Monisha Shah
- June 2004
- Kathmandu, Nepal

## Linear Programming Basics

- Objective Function – max or min
- Decision variables
- Constraints – define the feasible region
- Non-negativity

## Various Types of Software

-  Xpress MP
-  Cplex
-  Excel Solver
-  etc.

## Solver: Algorithm and methods

- Microsoft Excel Solver uses the Generalized Reduced Gradient (GRG2) nonlinear optimization code developed by Leon Lasdon, University of Texas at Austin, and Allan Waren, Cleveland State University.
- Linear and integer problems use the simplex method with bounds on the variables, and the branch-and-bound method, implemented by John Watson and Dan Fylstra, Frontline Systems, Inc. For more information on the internal solution process used by Solver, contact:
- Frontline Systems, Inc.P.O. Box 4288Incline Village, NV 89450-4288(702) 831-0300Web site: http://www.frontsys.comElectronic mail: info@frontsys.com
- Portions of the Microsoft Excel Solver program code are copyright 1990, 1991, 1992, and 1995 by Frontline Systems, Inc. Portions are copyright 1989 by Optimal Methods, Inc.

## Define and solve a problem by using Solver

- On the **Tools** menu, click **Solver**. If the **Solver** command is not on the **Tools** menu, you need to install the Solver add-in.
- In the **Set Target Cell** box, enter a cell reference or name for the target cell. The target cell must contain a formula.
- To have the value of the target cell be as large as possible, click **Max**. To have the value of the target cell be as small as possible, click **Min**.
- To have the target cell be a certain value, click **Value of**, and then type the value in the box.
- In the **By Changing Cells** box, enter a name or reference for each adjustable cell, separating nonadjacent references with commas. The adjustable cells must be related directly or indirectly to the target cell. You can specify up to 200 adjustable cells. To have Solver automatically propose the adjustable cells based on the target cell, click **Guess**.
- In the **Subject to the Constraints** box, enter any constraints you want to apply.
- Click **Solve**.
- To keep the solution values on the worksheet, click **Keep Solver Solution** in the **Solver Results** dialog box. To restore the original data, click **Restore Original Values**.

## Solver Parameters

## Solver Parameters

**Set Target Cell: **Specifies the target cell that you want to set to a certain value or that you want to maximize or minimize. This cell must contain a formula.
**Equal to: **Specifies whether you want the target cell to be maximized, minimized, or set to a specific value. If you want a specific value, type it in the box.
**By Changing Cells: **Specifies the cells that can be adjusted until the constraints in the problem are satisfied and the cell in the **Set Target Cell** box reaches its target. The adjustable cells must be related directly or indirectly to the target cell.
**Guess: **Guesses all nonformula cells referred to by the formula in the **Set Target Cell** box, and places their references in the **By Changing Cells** box.
**Subject to the Constraints: **Lists the current restrictions on the problem.

## Solver Parameters (Cont’d)

**Add: **Displays the **Add Constraint** dialog box.
**Change: **Displays the **Change Constraint** dialog box.
**Delete: **Removes the selected constraint.
**Solve: **Starts the solution process for the defined problem.
**Close: **Closes the dialog box without solving the problem. Retains any changes you made by using the **Options**, **Add**, **Change**, or **Delete** buttons.
**Options: **Displays the **Solver Options** dialog box, where you can load and save problem models and control advanced features of the solution process.
**Reset All: **Clears the current problem settings, and resets all settings to their original values.

## Solver Results

## Solver Results

**About the Solver Results dialog box: **Displays a completion message and the resulting values that are nearest the solution you want.
**Keep Solver Solution: **Click to accept the solution and place the resulting values in the adjustable cells.
**Restore Original Values: **Click to restore the original values in the adjustable cells.
**Save Scenario: **Opens the **Save Scenario** dialog box, where you can save cell values for use with the Microsoft Excel Scenario Manager.

## Solver Results Reports

**Reports: **Creates the type of report you specify, and places each report on a separate sheet in the workbook.
**Answer: ** Lists the target cell and the adjustable cells with their original and final values, constraints, and information about the constraints.
**Sensitivity**: Provides information about how sensitive the solution is to small changes in the formula in the **Set Target Cell** box in the **Solver**
**Parameters** dialog box or the constraints. This report is not generated for models that have integer constraints. For nonlinear models, the report provides values for reduced gradients and Lagrange multipliers. For linear models, the report includes reduced costs, shadow prices, objective coefficient (with allowable increase and decrease), and constraint right-hand side ranges.
**Limits:** Lists the target cell and the adjustable cells with their respective values, lower and upper limits, and target values. This report is not generated for models that have integer constraints. The lower limit is the smallest value that the adjustable cell can take while holding all other adjustable cells fixed and still satisfying the constraints. The upper limit is the greatest value.

## Basic Example

- Objective Function:
- 			Max Z = 140 x1 + 160 x2
- Subject to: 	5x1+5x2 ≤ 50
- 				2x1+4x2 ≤ 28
- 				x1 ≤ 8
- 				x2 ≤ 6
- 				x1 , x2  0

## Gas Model

- Simulate a simple gas market and potential for importing natural gas from one source to another via pipeline given various options
- (see Excel example)

## Electricity Example

- Simulate a simple gas market and potential for importing natural gas from one source to another via transmission network given various options and environmental constraints
- (see Excel example)

## Other Solver Applications

- Can also use Solver to optimize both:
- Non-linear
- Integer or mixed integer programs
- (see Excel example)

## Thank You

- Monisha Shah
- Monisha.shah@eia.doe.gov