# Script Nested Loop – Data Table in excel

5.05K views
0

Im trying to replicate the data table functionality from excel with a while nested loop. The idea would be to populate a matrix like this:

EV is the result of EBITDA*Multiple. My idea was to define a nested while loop (2 while loops) and store the values in a matrix called MatrixEV. So I would get the full matrix shown above. I tried a script but the result is not that good.  I attach the model in case someone can give me a hand.

——Script text format—–
// Define the counters
def int y=0;
def int x=0;
// Define initial Debt and increment
def double EBITDA=10;
def double EBITDAInc=10;
// Define initial multiple and increment
def double Multiple=5;
def double MultipleInc=1;
// Define Matrix to store EV values – result of Multiple*EBITDA as defined in Matrix1
def double[][] MatrixEV= new double [5][5];
while (x<5) {
|Matrix1::EBITDA:Value|.value=EBITDA;
while (y<5) {
|Matrix1::Multiple:Value|.value=Multiple;
MatrixEV[x][y]=|Matrix1::EV:Value|.value;
Multiple=Multiple+MultipleInc
y++
model.ensureCalculated()
continue
}
EBITDA=EBITDA+EBITDAInc;
x++;
model.ensureCalculated()
continue
}
println (MatrixEV)

Juan Jose Legarda Answered question December 15, 2018

### Attachments

DataTable_Script.model

0

Obviously not. I posted the script because the model Im working on is much more complex. A single formula does not work. I put the example to make it easier. Actually im looking for an IRR resulting from a model with 40 matrices.
thanks anyway

Juan Jose Legarda Answered question December 15, 2018
0

Is this what you want? A single formula will do it.

jlin Answered question December 15, 2018

### Attachments

Data-Table-Excel.model