Circular reference caused by interest effects on profit and cash
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
Without further details I am only able to comment from a conceptual point of view.
To recap the situation;
An opening balance is used to calculate a variety of P&L and balance sheet items.
However, the result of these calculations are themselves used to adjust the opening balance due to certain constraints.
I would suggest that you run parallel calculations where one uses the original values as per the actual opening balance and the second includes the manual entry. Logically the outcome of a calculation can not then perform as an input into that calculation. By separating the two processes you break the circular reference and retain the logical flow the adjustment violates.
Alternatively, you can create an action which is similar to a cut and paste process in which the balance is overridden with a manual adjustment. However, you will need to perform this action whenever there is a change
Chris Heathcote Bedford Consulting
Gold Partner and Regional Partner of the Year 2021, EMEA