5g: Source Structure - REDUCE and STACK

Function Name
All 5g functions require a name. The template is named "ReduceAndStackλ." When creating your function, modify this name to suit your needs.

Function Arguments
With rare exceptions, All 5g functions require arguments (aka parameters).  The template has three arguments: Opening, Gains, and Losses. 

• Opening - This is the closing amount from the last period before this model's timeline (if any). It is a single number.

• Gains - This is a row of values for each of the model's periods that increase the corkscrew's closing balance.

• Losses - This is a row of values for each of the model's periods that decrease the corkscrew's closing balance.

Your function will require different arguments. Change these as needed to suit your needs.

Pre calcs
Our LAMBDA's formula starts with a LET() function. LET allows us to split complex formulas into smaller formulas known as LET steps. LET steps are like formula cells. In traditional modeling we have a calculation block with several small formulas in cells. In 5g functions we have a function with small formulas in LET steps. 

The first section (after inline help - not shown) are pre calcs. 

Pre calcs  - This section is for performing calculations that can be performed once and not for every iteration of REDUCE(). Your function may require several such calculations but there are two that are required for the REDUCE and STACK template:

• Columns - REDUCE() will require a period counter. There should be the same number of columns in one of the argument arrays as there are model periods so the first step in creating a period counter is to use COLUMNS() to determine how many columns, and thus periods, are in the array, and thus, in our model.

• Periods - The second step in creating the period counter is to use SEQUENCE() to generate the numbers 1 through the number of periods in our model. 

Process each column

This section processes each column using REDUCE().

REDUCE()

REDUCE is a LAMBDA helper function that processes each element in an array, one by one. This is very similar to SCAN() but whereas SCAN() produces an array of many results, REDUCE produces only the last result of a SCAN. REDUCE() has three arguments: 

• Initial Value - Many applications of the REDUCE and STACK template have an opening balance, in which case, place it here.

• Array - The REDUCE and STACK template process each column one at a time. We place our period counter here to control processing.

• LAMBDA - REDUCE's LAMBDA processes each column.

REDUCE's LAMBDA

REDUCE's LAMBDA has three arguments:

• Acc - Acc is a mnemonic for  "accumulator". The accumulator's value starts with REDUCE's initial value which is when REDUCE processes the array's first element. After processing the array's first element, REDUCE places the result from the last LAMBDA calculation in its accumulator. 

• Period - REDUCE() passes each array value, one at a time, to its LAMBDA. In this example our period counter is the array and each of the array's values is a period number. 

• Formula - The formula, as in many LAMBDA cases, is best broken into smaller formulas using LET(). 

Get Opening Balance

The opening balance will come from REDUCE's initial value, or the last LAMBDA's result. In most cases, the last period's closing balance, which is the current period's opening balance, is in the last column's  last row. In either case, REDUCE's initial value or LAMBDA's last result are placed in the accumulator, and whether the accumulator contains one value, as would be the case for REDUCE's initial value, or one or more columns from REDUCE's LAMBDA, the opening balance is usually found in the bottom right corner of the accumulator. TAKE( Acc, -1, -1) takes the bottom right row of single numbers or arrays. 

NOTE! This is identical to traditional modeling structures. In our corkscrew example, when processing the first period, the opening value comes from I13 which is in the closing balance row and just prior to the first period. In the second period the opening balance comes from J13 which is, again, in the closing balance row and just prior to the current period.

Get this period's values

In our corkscrew example, J11 and J12 pull values from the same period of rows 7 and 8. In this template we use INDEX and our period counter to do the same thing.

Calculate close

At this point we have all of our 'ingredients.' We have the current period's open, gain, and loss values. We can now perform the same calculation found in cell J13 of our corkscrew example: J10 + J11 - J12 which is Open + Gain - Loss.

Vertically stack current period's values into a column

In this LET step we place the individual values: Open, Gain, Loss and Close, vertically in a column. 

Horizontally stack columns together

When our LAMBDA completes we want it to place into the accumulator, the column when completing the first period, or the column stacked horizontally against other columns already placed in the accumulator (see animation at top). 

Return REDUCE's result

The last line in our LET() function returns our result.