Description and model design
Using the Iteration functionality, one can define the global run of a project as a subset of multiple sequential runs of one or several workbooks. We call one iteration, one run for a given workbook.
In the entire article, we consider the following model example composed of 4 workbooks: Model parameters, B, M, and Consolidation. We want the project to be calculated as follows:
- First, the workbook Model parameters is calculated once.
- Second, the workbook B is calculated 3 times (3 iterations). Each iteration of the workbook B is labeled B_i with i the index of the iteration: B_1, B_2, B_3. The output of the previous iteration is used as the input of the following iteration. Thus, the output of B_1 is used as the input of B_2 and the output of B_2 as the input of B_3.
- Third, the workbook M is calculated n times (n iterations). Similarly to the workbook B, each iteration of the workbook M is labeled M_i, with i the index of the iteration: M_1, M_2, …, M_n. Thus, the output of M_(i-1) is used as the input of M_i.
- Finally, the workbook Consolidation is calculated once.
Step 1: Defining the order of calculations of your workbooks
This step is optional if you have only one workbook in your model.
The order of calculations of your workbooks is defined on a grid flagged with /CalculationSteps. The grid is composed of 2 columns:
- the first one lists the name of all the workbooks in the project;
- the second one defines the order of calculations for the corresponding workbook with an integer, 1 being the first workbook(s) that are calculated.
Several workbooks can have the same number on the second column, meaning that the calculations is parallelized between those workbooks. In general, it is more optimized to parallelize as possible the calculations.
In our example, the most optimized solution for the desired results is the following:
| Name | Calculation Steps |
| Model parameters | 1 |
| B | 2 |
| M | 3 |
| Consolidation | 4 |
If M is not using calculations from B, meaning that B and M are independent and that calculations can be parallelized, the most optimized solution for the grid would be:
| Name | Calculation Steps |
| Model parameters | 1 |
| B | 2 |
| M | 2 |
| Consolidation | 3 |
Step 2: Perform sequential calculations on a given workbook
To define sequential run in a given workbook, use the function MM_ITERATIONS on the workbook. The function is only used once per workbook.
Step 3: Defining the inputs of your model for each iteration
To define inputs and outputs from for a workbook with iterations, use the flags /iterationinput.(input_name) and /iterationoutput.(input_name); input_name is a label used to identify a couple of input and output grids.
After running your model, you can access the different values of an input grid by choosing the index of the iteration you desire. The design is exactly the same as if you are selecting the index of a loop you want to display. You can consider the iteration as a special loop.
On the first iteration, the values of an input grid flagged with /iterationinput.(input_name) will be assumptions (either default values from the Excel or imported data from a csv or txt upload).
On the following iteration, the values of that same input grid are equal to the values of the previous iteration of the grid flagged with /iterationoutput.(input_name).
Step 4: Displaying the details of your calculations on iterated workbooks
Inside a workbook with iterations, the values of the iterations is not automatically saved to save RAM. To disable this behaviour on the grids on which you want to display results for all iterations, use the flag /.
Step 5: Retrieving information from a workbook with iterations
To retrieve information from the iterations of a workbook on a different workbook, use the function MM_READITERATION. In our example, we would like to retrieve results from the workbooks B and M in the Consolidation workbook. Using this function is necessary as the iteration “loop” only exists in the workbooks on which iterations are defined, meaning it does not work like regular loops that are propagated on dependent cells. You can learn more about MM_READITERATION here.