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!
Just realised I've been a bit of a bozo. I modeled my corkscrew on the calcs for Trade Debtors & Creditors where the middle sections are pre-calculated and nothing to do with the corkscrew. The corkscrew is just to get the output to feed into the balance sheet. BUT..
There is another type in play whereby the corkscrew is used to get the payment amount. eg VAT
Usually one would use the opening balance in the month of payment due (using flags) and negate it. So then a mid section becomes dependent on an opening and closing balance. I was a bit taken aback when I hit the first circularity that it blurred my mind to the other situations
Have I missed the point in the two calcs one for opening and one for closing balances? I just used opening as closing could just be a standard addition. Should I be using both as just opening works for trade debtors and even if I use both, the VAT payment still results in circularity. I'm probably being really dumb but I can't see an easy way around this.
I tried using a variation of the opening balance scan solution but need to reset based on the vat payment period - currently in an array of flags. But I can't integrate that reset into the scan function. I like that concept of the flags especially for things like payment periods. Again not sure how to tackle this. Much prefer to keep the corkscrew in separate components for flexibility but can abandon this if a better way. I think this would then wrap up the corkscrew issue
Sample in 08a attached
You mention 'keeping corkscrews in separate components'. For me the corkscrew has no particular significance; it is simply a presentational device used to support hand calculation in much the same way that numbers used to be laid out for 'long division' in the days when that was a thing.
I the physical world of engineering quantities that I am used to such quantities the balances are simply the indefinite integral of the flows; conversely one differentiates the balances to return to the flows. In discrete models the quantities become accumulations over time and, the reverse, differences per period. No artistic layout is required; the quantities simply are what they are defined to be.
- I get your point here but used loads in financial models and people like them and expect them. Also they do form a very logical calc flow for all sorts of accounts such as VAT, and various reserve accounts and revolving credit where the balance is based on the difference between inflows and outflows and is then carried forward. I doubt they will disappear soon. Finance people like to see how the numbers are arrived at and follow them through!! They are not very accepting of a black box number.