Grouping Adjustable Cell Ranges

Figure 1 - Adjustable Cell Group Settings

To add a new group of adjustable cells, click the Group button and select New.

To edit an existing group, select the cells within the group, click the Group button and select Edit.

Grouping adjustable cell ranges together enables them to be adjusted together using a particular solving method. The Adjustable Cell Group Settings window contains information about how the adjustable cell ranges in a group will behave during a simulation. The settings include:

  • Description –A simple descriptive note, to help document the design of the optimization model.
  • Solving Method - Controls how RISKOptimizer will change the cells in the group during the optimization process. RISKOptimizer can use any of six solving methods. Three of the solving methods (Recipe, Order, and Grouping) use entirely different algorithms from one another. The other three are modification of these three, adding additional options. See below for more information on solving methods.

Solving Methods

The following solving methods are available:

Recipe Method

The Recipe method the simplest and most popular solving method. It is used when the values in an adjustable cell group can be varied independently of one another. Think of each variable as the amount of an ingredient in a cake. Using the Recipe solving method is like asking RISKOptimizer to find values that produce the best mix.

Order Method

The Order solving method is the second most popular solving method. It is used in a very specific context: for problems that involve determining the best ordering, or permutation, of items. For example, if there are 10 jobs to schedule on a machine, one after the other, and the desired result is the ordering the results in the least machine downtime.

Grouping Method

The Grouping solving method is used for problems that involve multiple items to be grouped into sets. For example, suppose a large number of securities is to be placed into six groups, so that the variability within groups (on some measure such as 6-month return) is minimized. Simply stated, the aggregated securities in each group need to be as “similar” as possible.

By default, the grouping IDs that RISKOptimizer creates will be equal to the unique values present in the adjustable cells at the start of an optimization. However the group IDs can be manually specified in the Adjustable Cell Group Settings dialog.

Budget Method

The Budget solving method is the same as Recipe, except that sum of the adjustable cell values is fixed at the initial value of the sum. For example, the adjustable cell values might be percentages of a total investment in various stocks. If the initial values of these percentages add to 100% and the Budget method is used, then the percentages will continue to sum to 100% in all trial solutions.

An alternative method would be to use the Recipe method and add a constraint on the sum of the adjustable cell values. These approaches are equivalent if the OptQuest engine is used. However, if the Genetic Algorithm is used, the Budget method is significantly more efficient.

Project Method

Figure 2 - Adjustable Cell Group - Project

The Project solving method is similar to the Order solving method except that certain items (tasks) must precede others. This solving method is especially useful in project management to order tasks with the precedence constraints. In this case, the adjustable cells containing the task order should be in a single column, rather than in a row. This is because the solving method expects the preceding tasks cells to be arranged vertically rather than horizontally.

After specifying the location of the adjustable cells, it’s necessary to specify a set of “preceding tasks”. An example is shown here, where the ordering (of towns to visit) is shown in yellow and the precedence constraints are indicated in columns I to K. For example, towns 2 and 7 must be visited before town 3. The precedence tasks range should have n rows and m columns, where n is the total number of tasks (number of adjustable cells), and m is the largest number of preceding tasks that any one task has.

Schedule Method

The Schedule solving method is used to schedule tasks to time blocks. Each task is assumed to take the same amount of time, much as classes at a school are all of the same length. In this case, the Adjustable Cell Group Settings dialog includes an option to specify the number of time blocks to used.

Figure 3 - Adjustable Cell Group - Schedule

Here is typical example where college classes are being assigned to time slots. The example below illustrates this. Each yellow cell specifies a time slot from 1 to 6 for the class. These are the adjustable cells. The Constraint Cells consist of the bordered range. This range uses “codes” for the spelled-out constraints to their left.

Eight kinds of constraints are possible:

  1. (with) The tasks in columns 1 and 3 must occur in the same time block.
  2. (not with) The tasks in columns 1 and 3 must not occur in the same time block.
  3. (before) The task in column 1 must occur before the task in column 3.
  4. (at) The task in column 1 must occur in the time block in column 3.
  5. (not after) The task in column 1 must occur at the same time or before the task in column 3.
  6. (not before) The task in column 1 must occur at the same time or after the task in column 3.
  7. (not at) The task in column 1 must not occur in the time block in column 3.
  8. (after) The task in column 1 must occur after the task in column 3.

A numeric code (1 through 8) or the description (after, not at, etc.) can be entered for a schedule constraint. (All language versions of RISKOptimizer will recognize the English description entered for a constraint, as well as the translated form). Note: When selecting the schedule solving method, integers starting from 1 are always used regardless of the original values in the adjustable cells.