Forum Discussion
Matrix Setup
Hi Husseinch01 ,
Thanks for reaching out to our community. Is this what you want? As you can see, names are under 'Cash in' or 'Opening'.
In the row field, put the type field above the name. And then use the drill down to drill on rows.
The attachment is for your reference.
Using drill-down actions with the matrix visual
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Husseinch01,
Based on your description above, I understand that you have customer names, but while Sales Orders (SO) are associated with transactions involving customers, Purchase Orders (PO) are generated from transactions with vendors, not customers. If you want to capture both cash inflows from customers and cash outflows to vendors comprehensively, the best approach is to use your bookkeeping data from your ERP system.
Typically, most transactions related to cash inflow and outflow are recorded through the Accounts Receivable (AR) module for sales transactions and the Accounts Payable (AP) module for purchase transactions. What you need to do is identify the journal entries in the company's accounting system that include bank accounts. Then, use the offsetting account of these bank transaction entries to determine either cash collections from customers recorded with customer codes through the AR module, or cash payments to suppliers, recorded with supplier codes through the AP module. In some rare cases where transactions do not go through the AP or AR modules, GL accounts like tax and interest GL accounts may be used as the offsetting side of the bank journal entries.
The article in the link below explains how to prepare a cash flow analysis if you have access to the company's bookkeeping records maintained in your company's ERP system:
https://p3adaptive.com/power-pivot-cracks-direct-method-cashflow-conundrum/
However, if you don't have access to journal entry data, an alternative solution will need to be developed. In my experience, for a standalone company with a complete set of bookkeeping journal entries that involve direct AP and AR transactions with third-party customers and suppliers (without intermediary intercompany transactions), using the offsetting account information from bank journal entries is one of the most effective methods for compiling a cash flow analysis. This approach accurately identifies each cash flow transaction in the company, specifying where the money came from and where it was paid.
Best regards,
