Circular reference caused by interest effects on profit and cash

CommunityMember82902

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

 

 

CommunityMember82902

HI Chris

yes thanks, by replicating the macro process we have in excel and Paste valuing the circular figures.

However, as you said, the process has to be run over and over again manually each time it causes a change, which could make it time consuming.

 

I am just investigating how many times it would need to be run for each planning cycle to understand this time requirement better.

ChrisAHeathcote

@CommunityMember82902 

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 

 

CommunityMember82902

Thanks Chris, this is pretty much the conclusion I had come to as well.

Gary Atkinson

ChrisAHeathcote

@CommunityMember82902 

Did you manage to break the circular reference?