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!
Further musings ...
I suspect that array methods provide the best fit for sorted lists such as the time-series data arising in financial models. One thing I did recently was to define a Lambda helper function that sums and array along reverse diagonals.
The operations were to 1. Form the cartesian product of the two arrays (CAPEX and spread); 2. Pad the right-hand side with blanks to avoid the rightmost terms wrapping to the start; 3. wrap row by row to a grid one cell narrower than the source data, to line up the fields to be summed; 4. sum by column.
Even more complicated is FIFO, where I stack the cumulative inflows and outflows and sort the combined dataset. Each batch with its associated inflow, outflow and price is obtained as a difference and can be grouped by outflow.
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.
- That is pretty amazing. But I'm moving in smaller steps and taking something that is totally old school and moving it to be fully dynamic but still in parts. I don't think many users are ready for a single sheet formula. They always end up popping bits in between however much one tries to prevent it and of course anything typed in the middle will cause a spill error.
I'm also trying to get bankers to leave vLookup behind forever - never used it myself - horrible function - and start using simple spill functions just for columnar data so there are no more fixed ranged of 10K rows in case the data happens to occupy that. Getting people to use dynamic ranges is a big step in itself!! I show them the performance increases and ease of application. It will take a while..We agree on the joys of VLOOKUP!
My refactoring of the revolver part of Luke's financial model was intended to be an exploration of the possible and not a simple incremental development. The 'array of array' problem of Excel is no longer a side issues; it is the main functionality needed for all modelling. By now I have a reasonable level of experience with workarounds (the techniques involving MAP and thunks can replicate entire calculation blocks as arrays) but the sooner they become part the core functionality of Excel the better.
It certainly seems that the financial modelling community is extraordinarily reluctant to accept change; the expectation that modelling require input from expert practitioners is not welcome. It also appears that the Excel formula can provide the basis of understanding between the stakeholders. The trouble is that dumbing down the the most inexperienced user (requiring little more than grade school arithmetic) creates something that is remarkably unstructured and open to error. Simplistic programming at cell level makes to overall logic extremely difficult to follow.
My preference would be to see the model built, and audited, by experts at the most abstract level supported by the language, and protected from end-users. The assumptions and scenarios would, however, be owned by the end-user whose job it is to make decisions based upon the exercise of the model. I would also expect the user to run test cases to establish confidence that the model is fit for their purposes.
That still leaves the issue of communication between stakeholders. I haven't got a clear idea of that issue as yet, but my thinking is that every contractual requirement or policy decision needs to be captured as a human-readable Lambda function with embedded documentation that cites the relevant contract or law, as well as the owner of any policy decisions. All changes should be documented. Such Lambda functions would be passed to the code as parameters along with other assumptions.
There may be pushback along the lines of 'the customer is always right'. Eventually that concept may clash with the professional integrity of the model builder. A point is reached where the correct answer is 'Sorry Sir, we do not undertake work of that nature!' We have a long way to go developing our art before that point is reached though.
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?