Entering Decision Variables in a Model

Simulation Master Tutorials > Entering Decision Variables in a Model (Premium Edition)

Decision variables are things in the model that you can control.  Simulation Master has two types of decision variables: discrete and optimization decision variables.  A discrete decision variable takes on specific values that are specified in a range of cells within the model.  An optimization decision variable is used in conjunction with the optimizer to find the specific value of the variable based on optimization criteria.

For experienced users, the Simulation Master function for a given decision variable can be entered directly in the model by using the appropriate function and parameters.  The use of the Add Decision Variable form is intended to reduce input errors and its use is left to the discretion of the user.

In this tutorial we will look at examples of both types, and how to add decision variables to your model.

Discrete Decision Variables

An example of using discrete decision variables is shown below with the cell formulas shown instead of cell values.  In this example there are two decision variables that are used as parameters for random variables.  The decision variable in cell E6 can assume the values in range G6:G16 and the decision variable in cell E7 can assume the values in range H6:H16.

If multiple simulations are run, the decision variables will assume the first value in their range (G6 and H6) on the first simulation run.  They will assume the second value in their range (G7 and H7) on the second simulation run.  This will continue for each simulation run.

If there are more simulation runs than possible values, the excess simulation runs will use the last value of the decision variable.  For example, the two decision variables below each have 11 possible values.  If we make 15 simulation runs, runs 11 through 15 will use the values shown in cells G16 and H16.

Entering Formula Directly in the Cell

The Simulation Master discrete decision variable function is as follows:

=RVDECISION (Range)

Where:

Range is the cell range containing the possible values of the variable.

Using the Decision Variable Tool

In the spreadsheet above, we will show how to add the decision variable located in cell E6.

Prior to clicking the Decision Variable button on the ribbon, select the worksheet where the decision variable will reside.

After clicking the Decision Variable button, the Decision Variable form will appear.

Decision Variable Cell Address

For a discrete decision variable, the variable cell address is where the decision variable formula will reside, and also where the current value of the variable resides.  Click the minimize button next to the address box and select the cell where the variable will be located.  In this example, we will locate the variable in cell E6.

Range of Values

Using the minimize button next to the box, select the range of cells that contain the possible values of the decision variable.  For our decision variable, the range G6:G16 contains the values that it can assume.

The completed form is shown below.

Click the Add Variable button to finish.  The decision variable formula will appear in cell E6.

Optimization Decision Variables

An example of optimization decision variables is shown below.  In this example the weighting of each asset in an investment portfolio is a decision variable.  The optimization decision variable definitions are in cells H7 through H11.  During an optimization, cells B10 through F10 are changed within the range specified in the decision variable definition.

In this example we could simulate the portfolio return and use optimization to maximize the mean value of the portfolio return.  We impose the constraint in cell B17 that the sum of weights must equal 1.  To account for our risk appetite, we could also impose a constraint that the 5th percentile of return must be greater than or equal to zero.

During optimization, the weights of the individual assets will be adjusted to find the optimum weights that satisfy the constraints we’ve imposed.

Entering Formula Directly in the Cell

The Simulation Master optimization decision variable function is as follows:

=RVOPTDECISION (Cell, Name, Min, Max, Type)

Where:

Cell is the cell address of the cell whose value will be changed during optimization.

Name is the name of the decision variable.  Name will appear in the decision variable cell.

Min is the minimum value the decision variable can assume.

Max is the maximum value the decision variable can assume.

Type is the type of data.  A type of 0 means the data can assume decimal values.  A type of 1 means the data can assume only integer values.

Using the Decision Variable Tool

In the spreadsheet above, we will show how to add the decision variable located in cell H7.

Prior to clicking the Decision Variable button on the ribbon, select the worksheet where the decision variable will reside.

After clicking the Decision Variable button, the Decision Variable form will appear.  In the Decision Variable Type pane, click the Optimization selector button.

Decision Variable Cell Address

For an optimization decision variable, the variable cell address is where the decision variable formula will reside.  Click the minimize button next to the address box and select the cell where the variable will be located.  In our example, we will locate the variable in cell H7.

Variable Name

Enter a name for the decision variable.  This name will appear in the cell.  We will name this variable "A weight".

Cell to Change

Click the minimize button next to the address box and select the cell that will be changed by the optimizer.  In this example, we will change cell B7.

Data Type

Data type can be either decimal or integer.  If decimal is selected, the cell to change can assume any value within its specified range.  If integer is selected, the cell to change can only assume whole numbers within it specified range.  Our example is a decimal type.

Minimum

Specify the minimum value that the cell to change can assume.  In our example, .1 is the minimum value.

Maximum

Specify the maximum value that the cell to change can assume.  In our example, .4 is the maximum value.

The completed form is shown below.

Click the Add Variable button to finish.  The decision variable formula will appear in cell H7.