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!
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.
James_Buist I'm afraid I don't really follow your description. There's a lot of jargon being used that's not part of my vocabulary. Rather than trying to decipher the meaning, I went straight to your sample workbook...
Do the mocked-up totals in both the Opening and Closing Balance sections accurately represent your expected results? If so, the flags don't really seem to be playing a part in any of this... you've got multiple flags with no corresponding VAT payments, and some VAT payments with no flags. There doesn't seem to be any rhyme or reason to it. It would be relatively easy to use the flags as a reset point, but that doesn't seem to be what's actually going on in your sample file. If you could provide some clarity to your mocked-up data, I might have a better chance of determining what needs to be done.
Apologies, in my haste it seems that I have not got these correct. They should be representing the payment dates for each entity's VAT. The months to the left were residue from the trade creditors version and i have now removed these in the example. The flags are ideally all that is used to represent the vat payment dates. I could skip them and work off the payment month and period but that adds more complexity to the corkscrew formulas so I thought easier to do that logic in the flags.
The key is that the vat payments should occur at a set frequency starting on a set month in each year. And be included in the CorkscrewJames_Buist Check out the attached file and see if it's what you needed. It contains a few different options, including a single-cell output...
