Forum Discussion
Return of the Corkscrew with Spilled Arrays
James_Buist Just for good measure, MMULT can also get the job done with minimal effort.
Closing Balance (cell G22):
=LET( data, HSTACK(F22:F24,G12:M14+G17:M19), cId, SEQUENCE(,COLUMNS(data)), arr, MMULT(--data,--(DROP(cId,,1)>=TOCOL(cId))), VSTACK(arr,BYCOL(arr,SUM)) )Cheers!
Gradually the whole basis of my modelling is changing. For example, the 360 periods of the model, illustrated , were created as one dynamic array formula in cell H108
using a formula
=LET(
balances, SCAN2Hλ({0; 0}, SeriesData, Modelλ),
opening, HSTACK({0; 0}, DROP(balances, , -1)),
result, Modelλ(opening, SeriesData),
result
)As I remember it, the key steps were to create the function Modelλ that, given opening balances, calculates results at the next timestep. The SCAN2Hλ helper function project the cash and revolver balances forward in time (other quantities were calculated but only the balances were returned by the function as an array of Lambda functions (thunks). Once all the opening balances were known (SCAN actually returns closing balances but a simple step to insert the initial balances and trim the final values convert to opening balances) the Modelλ function is called again but, this time with entire arrays of 360 terms to recover all the quantities shown.
The corkscrews are just a presentational devices that have little to do with the calculation. As long as you remains within the single function environment, you have access to all the intermediate calculations by name, and the display, including blank lines between blocks, is created by stacking results that you determine to be of interest to the user. That does, however, limit the output to a single worksheet.
Just to pile on the agony, this was the formula that captured that section of the FM
/* REVISIONS: Date Developer Description
Jun 14 2024 Luke Phillips Original Development
Jun 26 2024 Peter Bartholomew Modified to use SCAN
Jul 06 2024 Peter Bartholomew Separate model calc from overall structure
Jul 25 2024 Peter Bartholomew Restructured to use SCAN2Hλ
*/
Modelλ
= LAMBDA(openingBalances, seriesData,
LET(
// About Section
Help, TRIM(
TEXTSPLIT(
"FUNCTION: →Modelλ(openingBalances, seriesData) ¶" &
"DESCRIPTION: →Calculates revolver balances, interest, and cash flows over specified periods.¶" &
"NOTE!: →Input arrays must have the same length and stacked to give an array seriesData¶" &
" →Inputs must be numeric otherwise a #VALUE! error results¶" &
"PARAMETERS: →¶" &
"openingBalances →(Required) Two row Array of opening values.¶" &
" →Starting cash in period.¶" &
" →Revolver start in period.¶" &
"seriesData →(Required) Stacked list of Arrays containing series data conprising:¶" &
" →Array of EBIT values.¶" &
" →Array of base interest values.¶" &
" →Array of income tax rates.¶" &
" →Array of cash interest rates.¶" &
" →Array of revolver interest rates.¶" &
" →Array of investing cash flows.¶" &
" →Array of other operating cash flows.¶" &
" →Array of base financing cash flows.¶" &
"→¶" &
"EXAMPLES: →= Modelλ(opening, SeriesData)",
"→",
"¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR(
ISOMITTED(openingBalances),
ISOMITTED(seriesData),
),
// Procedure
// Individual Values out of arrays
EBIT, CHOOSEROWS(seriesData, 1),
BaseInterest, CHOOSEROWS(seriesData, 2),
IncomeTaxRate, CHOOSEROWS(seriesData, 3),
CashInterestRate, CHOOSEROWS(seriesData, 4),
RevolverInterestRate, CHOOSEROWS(seriesData, 5),
InvestingCF, CHOOSEROWS(seriesData, 6),
OtherOperatingCF, CHOOSEROWS(seriesData, 7),
BaseFinancingCF, CHOOSEROWS(seriesData, 8),
//
StartingCashInPer, CHOOSEROWS(openingBalances, 1),
RevolverStartInPer, CHOOSEROWS(openingBalances, 2),
// Array Calculations (also applicable to scalars within sCAN)
RevolverInterest, RevolverStartInPer * RevolverInterestRate,
CashInterest, StartingCashInPer * CashInterestRate,
EBT, EBIT - BaseInterest + CashInterest - RevolverInterest,
CurrentTaxes, IncomeTaxRate * EBT,
NI, EBT - CurrentTaxes,
FCF, InvestingCF + NI + BaseFinancingCF + OtherOperatingCF,
RevolverMovements, -MAP(
RevolverStartInPer,
FCF + StartingCashInPer,
LAMBDA(x, y, MIN(x, y))
),
RevolverEndInPer, RevolverStartInPer + RevolverMovements,
TotalOperatingCF, NI + OtherOperatingCF,
TotalFinancingCF, RevolverMovements + BaseFinancingCF,
ChangeInCash, TotalFinancingCF + TotalOperatingCF + InvestingCF,
EndingCash, StartingCashInPer + ChangeInCash,
// Formatting the result
§, IF(period, ""),
nper, COLUMNS(seriesData),
Result,
IF(
nper=1,
//When called from SCAN, return only the closing balances
VSTACK(EndingCash, RevolverEndInPer),
//Once the opening balances are calculated, return the entire model
VSTACK(
StartingCashInPer,
CashInterestRate,
-CashInterest,
§,
RevolverStartInPer,
RevolverInterestRate,
RevolverInterest,
§,
EBIT,
BaseInterest,
-CashInterest,
RevolverInterest,
EBT,
§,
CurrentTaxes,
NI,
§,
§,
NI,
OtherOperatingCF,
InvestingCF,
BaseFinancingCF,
FCF,
§,
StartingCashInPer,
§,
RevolverStartInPer,
RevolverMovements,
RevolverEndInPer,
§,
§,
TotalOperatingCF,
InvestingCF,
TotalFinancingCF,
§,
StartingCashInPer,
ChangeInCash,
EndingCash
)
),
CHOOSE(Help? + 1, Result, Help)
)
);Not quite the standard Excel formula?