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!
Replied to your PM but it said I had used up my quota so it never went through. The teams invite bounced too. I'm only brass so perhaps my PM limit is tiny!! I'm also on imesssage and whatsapp or can send invite to another email address. Not sure when I can pm again but can receive. Zoom is also fine if you have it. time suggested works well
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 Corkscrew
My recollection is that MMULT used with an upper triangular matrix can be remarkably efficient with SCAN only outperforming it for very large problems.
On a separate issue, I used your IF({1}, ...) broadcast formula recently to build a 2D array of FIFO results to cover multiple companies and products. The alternative would have been to introduce the additional dimensions as separate nested steps but broadcasting allowed me to introduce the 2D array of 2D arrays with a single application of MAP, plus a sprinkling of thunks.
The OP was horrified, but that's life!
'actuals and forecasts'
I suspect that to try to combine 'actuals' (which feed into and sit alongside assumptions) with forecasts is to introduce self-inflicted damage. By all means bring them together at the end by using HSTACK, as part of the presentation layer, but I wouldn't try to combine the calculations.
A further observation on my approach to the 'array of array' problem (hopefully a temporary problem, but who knows). My objective is to create 'universal' approaches that work as the Microsoft helper functions should have worked, and with the computational tricks hidden from the user. I have also tried to avoid methods that are specific to a particular class of problem. As an example, it is easier to find approaches for summation, but if multiplication also enters the picture (as for interest payment or other growth assumptions) the problem becomes harder and it can be a case of 'back to the drawing board'!
Please accept my apologies for inflicting my thoughts and opinions on you somewhat uninvited!
- I played around with the options for integrating actuals with forecasts and tweaked my original thought process of having another input to the corkscrew of the actual closing balances. Initially I thought this would be straightforward but of course was wrong. What I ended up doing was to feed in the delta of the closing balances and as actuals would only have values in historic periods and forecasts only have values in forecast periods (due to the flags) it worked out well. This way I can just feed the entire closing balance into the Balance sheet with no further logic.
- No apologies needed. Inflict away. Much better that way. But with regard to combining actual and forecasts, the corkscrew is really only needed for the forecasts other than the most recent closing balance. I haven't fully decided but I may feed the actuals into the middle section just above the closing balance as a line item. Flags control this. Then the CB will be the sum of Inflow, Outflows and Actuals. As there will be no inflows and outflows in the actuals periods that means the CB will be the actuals CB and in the forecast periods, the actuals will be zero so again that will work. So no special calcs needed. With an independent opening balance then the rest of the corkscrew just flows
Good stuff! I'm glad to hear you found an application for the broadcasting method. Reception so far has been mixed... some love it and some don't. Most users tend to stick with what's familiar and avoid straying outside their comfort zone.
MMULT works well in many different situations, but it's not without its drawbacks. For starters, it can be difficult to get the x and y axis in the right direction. I've also noticed it has a tendency to fail with the 'Excel ran out of resources' error when working with larger datasets, whereas the newer dynamic array helper functions will finish processing the results, even though they are typically slower.
On a side note, data structure can make or break any spreadsheet. A good structure (normalized data in related tables) can be easy to query, summarize and output as needed, whereas a poor structure (e.g. cross-tabulated data) will usually require more advanced methods to manipulate. Many of these questions/scenarios could potentially be avoided simply by organizing your data in a better structure (although that's just speculation based on the skeleton sample data provided at each turn). 😉
I agree. I am trying to build a new version of an older type of best practices financial model using DAs through out. And they can enable one to skip a few steps and rather than having three sections for each key entry one can reduce it to one. But also, I debated for ages on the handling of multiple entities - for smaller organisations - and decided that there was more flexibility in combining the entities into a single group and doing each operation on the group as a whole rather than having separate sheets for each entity. Often easier to group by part - Revenue, Overheads, Working Capital calcs separated by sheet and do all entities in each group. This way one can just expand the group zones as required. Consolidations by entity have been more of a challenge and those you have hugely helped me with, But input sections and line item processing can be independent of entity and just needs an entity code to enable aggregation or consolidation. As you will have seen from my samples. Clearly I am just giving a small subset of the model to demonstrate the requirements for each post. The flow from inputs to middle tier calcs and the final outputs is one directional unless especially required. ie taking a feed from an output back to a mid tier is avoided even if it may seem more convenient. I hate bi directional references; sheet refs should flow in one direction only between sheets and typically top to bottom on sheets.
However, the grouping of entities has definitely posed a number of additional challenges as often a single row is less complex to calculate using a DA than a full 2D array. At least for me at my level. But I do like the challenge and have and am learning a huge amount from it
