Export (0) Print
Expand All

Modeling in Excel

Solver Foundation 3.0

You can use the Solver Foundation Excel add-in to model problems and optimize their solutions. The add-in provides a modeling task pane to create, verify, and solve models, and a ribbon to import/export files, deploy models to OML or C#, bind data, and generate results. 

Models can be entered in the Modeling Pane. You can use the Optimization Modeling Language (OML) to express goals and constraints as logical, arithmetic, iteration, and set operations.

The following table describes the UI elements in the Modeling Pane.

UI Element

Description

Parameters

Binds data from an Excel sheet to the model. Parameters can be declared as Sets that are later used as indices (in other Parameters or Decisions), or as singled-valued constants of type Reals, Integers, or Booleans.

Decisions

Binds the results of the model being solved. Data can be bound to a specific Excel sheet. Supported types for Decisions can be Reals, Integers, or Booleans. Decisions are also referred to as variables.

Goals

Defines the business goals that you are trying to accomplish. These are used to specify a quantity or equation that should be maximized or minimized.

Constraints

Adds restrictions to the decisions in the model.

Directives

Provides solver hints and other solver specific attributes.

Model

Shows the model in OML format. Advanced users can manually edit OML directly from this pane.

Log

Logs the output details of the solver. This is similar to the command line interface output. The log feature uses the information on the Report Verbosity group of the Solver Foundation ribbon.

Parameter Tab

You can use the Parameter tab to bind data from an Excel sheet to the model. Excel can import data from many different sources using the Get External Data button on the Data tab in the ribbon. After the sheet contains the model data, you can use the Input and Output tabs in the Modeling Pane to bind the data to and from the model.

The following table lists the three types of parameters.

Parameter Type

Description

Parameter

Represents real or integer values over a range. A parameter must be bound to data.

Scenario Parameter

Represents random values specified by scenarios, which have an associated value and probability of occurrence. A scenario parameter can be set without binding.

Distribution Parameter

Represents random values specified by a discrete or continuous random distribution. A distribution parameter can be set without binding.

Parameters can be single-valued or indexed by using sets. A set is an unordered list of items that defines the valid set of indexes for a Parameter or Decision. Both random and nonrandom parameters can be indexed.

For more information, see Walkthrough: Binding Data to Parameters.

Decisions Tab

You can use the Decisions tab to bind decisions generated from Solver Foundation directly into an Excel sheet, but decisions are not required to be bound to data. The following table lists the types of decisions.

Decision Type

Description

Decision

An output value that represents values that the solver should determine.

Recourse Decision

An output value in a stochastic model that is made in response to the realization of a random parameter.

Decisions can also be indexed using sets.

Goals

You can add a goal to a model to configure how a model should be optimized. For example, a typical goal can be to minimize cost or maximize profit. For more information, see How to: Create a Goal.

Constraints

You can add a constraint to a model to restrict a model for business or logical reasons. For example, a constraint could be to make sure that the sum of demand over all routes is equal to the total demand, or to cap server load at 90%. For more information, see How to: Create a Constraint.

Directives

You can add an optional directive to define the type of solver to use or customize the behavior of a solver. For example, you can adjust the arithmetic precision of the solver or set the time limit. You can also use the directive to generate reports. For more information, see How to: Add a Directive.

Validating and Solving the Model

After you bind data to parameters and decisions and configure goals, constraints, and directives, you can validate and solve the model. Then, you can view the output binding with a graph, chart, summary, or report. For more information, see How to: Solve a Model.

Use the ribbon in the Solver Foundation Excel add-in to create a model and bind data. The following table lists the functionality of the ribbon.

Group Name

UI Element

Description

Model

Model

Hides or shows the modeling pane window.

Import

Supports importing .OML, .MPS, .QPS, and SMPS files.

Export

Saves a model as MPS, QPS, or OML.

Deploy

Deploys a model directly to SharePoint (via OMLX format) or Visual Studio (via C#).

Check

Performs syntax checking on the model and displays the results in the Model Validation text box in the Modeling Pane.

Report Verbosity

Clear Log On Solve

Clears the options after each solve.

Decisions

Displays detailed decision information when available.

Directives

Displays directive related information.

Infeasibility

Includes solver-specific infeasibility information.

Sensitivity

Includes solver-specific sensitivity information when available.

Solver Details

Include details such as solve time, algorithms used, and other information.

Simulation

Method

Sets options to solve stochastic models. Choose between Automatic (the default), Monte Carlo, and Latin Hypercube.

Count

Sets the number of samples. Larger values lead to improved accuracy but require more time to solve.

Seed

Sets the seed used to initialize the random number generator. Using the same seed leads to more predictable solution behavior.

Solve

Solve

Runs the appropriate solver and generates results in the Solver Foundation Results worksheet.

Next

Iterates through constraint programming problems that have multiple solutions, which are shown sequentially in the Solver Foundation Results sheet.

Stop

Interrupts the Check, Solve, or Summary functions.

Analyze

Summary

Generates a report overview plus any solver execution details.

Help

Help

Opens the Solver Foundation For Excel programming primer.

Show:
© 2015 Microsoft