I have an issue with a circular reference for a revolving cash facility (RCF) calculation, which effects interest and cash figures, which then determine what the RCF closing value should be.
The transaction/calculation is circular in its nature rather than it being an anaplan specific circular reference.
Basic summary of the issue:
I have an opening RCF balance each period, which generates and interest charge.
This interest is then flowed through P&L and balance sheet (cash)
All other cashflows are calculated through P&L and Balance sheet including Corporation tax charge.
I then need to adjust the RCF balance to meet a minimum cash requirement figure.
this adjustment then effects balance sheet & P&L interest charges, which move cash figures and mean the RCF adjustment is now different to meet the minimum cash balance.
The solution we have in excel is a macro which Paste values the revised closing cash balance over and over again until the movement is immaterial.
I cant really provide screens shots as there are a lot of components which cause the circular ref, but my basic model setup has linked P&L, balance sheet and cashflow together to create the double entries as per a ledger system.
has anyone come across this before or has an idea how I can replicate the macro solution, without having to keep clicking a button manually to paste values.
Any help would be greatly appreciated, Thanks in advance
