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.
I first came across the world of financial modelling with the release of the FAST standard. I absolutely hated it an thought it was an exemplar of poor programming practice. I objected to: "do not use defined names"; "do not use array formulas" and to the idea of "calculation blocks" (I prefer to reference data at the point at which it was first created).
My immediate reaction was never again to use a direct cell reference; always use array formulas (then they were CSE) and avoid daisy-chaining links. EuSpRIG 1704.01142.pdf Eventually it was explained to me that the Standards were not intended to be expressions of best practice; merely standard ways of working that would suite a community of end users, contract developers and modellers of widely varying programming skill levels to work together.
The code extract I published above does, in fact conform to the FAST presentational standards, that being a characteristic of Craig Hatmaker 's 5G, component based modelling. The presentation, however, does not reflect the underlying calculation. The more stylised one wants the output (3-way models come to mind), the more important that separation. Otherwise developing the formulas looks more like filling in a cryptic crossword than any logical development of a solution.
- I'd agree with all you say. I used just to build applications, all locked down, VBA driven and names throughout - even defining relative names like cellbefore or above, or current column or row - great for getting the previous cell value without a ref. But now spend a good portion of my time with auditor modelling so has to fit the standards or with bankers who need simple solutions that they can follow and tweak. Argh. When I get the current template finished it will be mostly dynamic but only per block which is a big step.
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.
- 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.. 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?