Embedded Files
Description
SCAN and STACK is a faster alternative to REDUCE and STACK. It is less familiar to financial modelers because instead of processing each column, one by one like REDUCE and STACK, SCAN and STACK resolves the entire closing balance first. This is possible in SCAN, but less intuitive.
The important thing to remember about SCAN is the opening balance is always in SCAN's LAMBDA's accumulator.
NOTE! SCAN and STACK is at least twice as fast as REDUCE and STACK.
Learning Objectives
At the end of this course, learners will know:
The basics of SCAN and STACK
How to use SCAN and STACK to gain performance improvements.
Prerequisites
Understanding of the 5g basic source structure template.
Microsoft's free add-in: Advanced Formula Environment (AFE)
Discussion
This template is at least twice as fast as REDUCE and STACK though less intuitive.
If a REDUCE and STACK function performs poorly, we can sometimes convert it to this template to speed things up. The major difference here is we use SCAN to only calculate Close. From Close we can derive Open.
To focus on the calculations, we will remove all lines related to inline help. We expect any function derived from this function to add customized inline help. For more on inline help see the 5g basic source structure template.
To help explain the SCAN and STACK template we will convert a common corkscrew calculation; however, this is not intended to be a "corkscrew" template. A much faster and simpler, but less familiar-to-traditional-modelers corkscrew template is offered here.
Below shows a traditional corkscrew calculation. We will refer to this figure and focus on column J. All columns to the right of J are copies of column J's formulas and hold no particular interest for us.
Template
Below is BXL's 5g SCAN and STACK template. It can be downloaded using AFE from this URL:
https://gist.github.com/CHatmaker/3e6cba6414fadfabfa3725255da2ba74
NOTE! The circled numbers are NOT part of the code. There are added to mark sections for discussion.
Function Name
All 5g functions require a name. The template is named "ScanAndStackλ." 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.
Flows - Processing the flows outside of the SCAN function speeds processing.
Calculate Close
This section processes create the Close row using SCAN().
SCAN()
SCAN is a LAMBDA helper function that processes each element in an array, one by one. SCAN() 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 SCAN and STACK template process each Close value one at a time. We place our period counter here to control processing.
LAMBDA - SCAN's LAMBDA processes each column.
SCAN's LAMBDA
SCAN's LAMBDA has three arguments:
Acc - Acc is a mnemonic for "accumulator". The accumulator's value starts with SCAN's initial value which is when SCAN processes the array's first element. After processing the array's first element, SCAN places the result from the last LAMBDA calculation in its accumulator.
Period - SCAN() 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 current period values
In our pre-calcs, we combined our flows so we only need to use INDEX() to get the combined flows' value.
Calculate close
In this LET step we add the accumulator to the current period's combined flows value.
Return result
The last line in a LET function returns SCAN's result.
Derive Open from Opening and Close
In this LET step we start with the Opening value and place the Close array, less Close's last value to its right.
Vertically stack rows together
In this LET step we place the Open row on top of the Gains, Losses, and Close rows.
Return our function's result
The last line in our LET() function returns our result.
Summary
Summary
It is alien to financial modelers to calculate a running total in one row without a corkscrew structure which is why I say this template is less familiar. But its faster.
Do not use this for a simple corkscrew. This template adds value when we need to select current period values using INDEX. We do not need to do that for simple corkscrews.
Page updated
Google Sites
Report abuse