3 Segment Cashflow Model Circular Reference

Hello Aabhii,

Don’t worry, welcome to one of the most classic circular dependency examples in Pigment (beginning vs ending balances → for cash flow statements in your case).

There are a couple of articles and questions solved related to this matter but, basically, the quick escape from this situation - if I understood it correctly - would be by using the SELECT modifier and the PREVIOUS function, in a way that instead of referencing directly your Cash and Cash Equivalent metric in your Net Cash Flow metric you need to “rebuild” the entire aggregation (+/-) within it, something like this:

Cash and Cash Equivalent (beginning balance) => ‘Net Cash Flow’ [SELECT: Month/Quarter - 1]
Total Assets => no changes
Management Fee => no changes

Cash Paid for Management Fees => no changes
Net Cash Flow (ending balance) => PREVIOUS(Month/Quarter) + Total Assets + Management Fee + Cash Paid for Management Fees

I hope this helps, let me know otherwise.

Thanks!

Igor

@Igor P , thanks for your reply . But the situation is a bit more complex. Net cashflow is used to derive the cash balances. For example, current cash closing balance is Opening cash+Net cashflow for that period. If we use last year net cashflow , it wont give us the correct answer and i tried playing around using Select and Previous but either way i am probably doing something wrong and would need some guidance. 
Cash Closing Balance=Opening balance+Net cashflow
And Net cashflow = Total Operating+ Total Financing +Total Investing cashflow.

Total Operating cashflow= Cash paid for Management fees + Cash paid for tax 
Management fee= Total Asset * Management fee

Total asset already involves cash closing balance

Plus similar issue exist for Cash paid for tax as it depends on certain balance sheet items etc.

Hello @mmusab!

Indeed, I suspected that would have more to it 🙃

What I described in my previous answer you will probably need to apply in other parts of you cashflow as well, never referencing the “above” result but the Previous period + the movements of the current month, for each of your sub-totals/balances of your cashflow.

For example, you mentioned “Cash Closing Balance=Opening balance+Net cashflow”, but in this case it would have to be the following instead:

Cash Closing Balance = Previous(Month) + all the metrics/movements within the month (not the Net Cashflow metric itself)`

If you could share a quick draft of what you’re trying to achieve so I could have a better idea it would be helpful as well.

Thanks!

@Igor P , Happy to share excel file as well. But here i have attached a screenshot with formula displayed so you can see how they are linked. This is oversimplified version.

@Igor P , any updates on above?