Modeling in Excel

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. 

Modeling Pane

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.

Solver Foundation Ribbon

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.

See Also

Concepts

Developing Models using Solver Foundation