Question

3 Segment Cashflow Model Circular Reference

  • 31 August 2023
  • 5 replies
  • 301 views
A
Rank
Badge

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
 

 

5 replies

Igor P
Rank
Userlevel 2
Badge +3

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 Poulis | Solutions Architect & Expert Modeler @ Pigment
M
Rank
Userlevel 3
Badge +4

@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.

 

 

Igor P
Rank
Userlevel 2
Badge +3

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 Poulis | Solutions Architect & Expert Modeler @ Pigment
M
Rank
Userlevel 3
Badge +4

@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.

M
Rank
Userlevel 3
Badge +4

@Igor P , any updates on above?

Reply


Ask your question here!

Terms & ConditionsCookie settings