Hi, all, I am currently working on a 3 segment cashflow model within pigment. We are currently trying to model the operating cash flow but are stuck with an issue regarding a circular dependency.
Within the model, we calculate cash flows (investing, operating and financing). The operating cash flows use management fees to calculate the tax paid, however the management fees are based on total assets which include cash and cash equivalents, which in turn is based on the net cash flow (derived from the combination of investing, operating and financing cash flows). This ends up with a circular reference. Our excel document avoids the circular reference by utilizing the net cash flow from the previous quarter, however doing this in pigment ends with the same circular reference
Page 1 / 1
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’ eSELECT: 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.
