Financial account flows summary

[eibcga]

I'm trying to get a pivot table that summarizes all the financial transactions flowing in and out of a specified account, by the related accounts. Put another way, give me a data set of all the records related to a specified account, then show those records in a pivot table summarized by account. Getting the desired data set is the hard part - presenting the data in a pivot table is the easy part. Put yet another way, show me the total accounting journal entry that would be necessary to record all activity in a particular account. This account "flows summary" is an excellent analysis tool lacking in most accounting software today. I can do this analysis in a database, but would like to do the same analysis using PowerPivot.

I have the following relationships and relevant columns:

CONTACT (dimension table to track customers, suppliers, employees, etc.)
id_contact (primary key)
contact_name
contact_drbalance=CALCULATE(SUM(detail[debit]),ledger)
contact_crbalance=CALCULATE(SUM(detail[credit]),ledger)

ACCOUNT (dimension table to track assets, liabilities, equity, revenue and expense accounts)
id_account (primary key)
account_name
account_drbalance=CALCULATE(SUM(detail[debit]),ledger)
account_crbalance=CALCULATE(SUM(detail[credit]),ledger)

JOURNAL (fact table to track transactions, i.e., double-entry bookkeeping journal entries)
id_journal (primary key)
id_contact (foreign key)
date
memo
reference

LEDGER (fact table to track transaction line items or details, i.e., the bridge table between JOURNAL & ACCOUNT as required by double-entry bookkeeping)
id_ledger (primary key)
id_journal (foreign key)
id_account (foreign key)
amount
comment
debit=IF(amount>0,amount,"")
credit=IF(amount<0,amount,"")

Any guidance please? For example, if I ran a pivot table on the Cash account, I would like to be able to see all the sources and uses of cash (flows in and out), summarized by the related accounts. Cash is just an example. I should be able to run a pivot table on any account which will show the flows in and out of its related records. Thank you!

 

[scottsen]

Do you have a sample workbook you are willing to share? (dropbox,onedrive,googledrive?). If so, I would be willing to play with this for a bit. Blog fodder...

 

[eibcga]

Link to sample workbook: https://www.dropbox.com/s/qb798o1ofg7pa0e/account.xlsx?dl=0

Thank you very much indeed for your time.

 

[scottsen]

Perfect, thanks. Unfortunately, I probably won't get to this until the weekend - it's a tricky little problem, likely requiring you to add a copy of the account table so that you can filter on accounts from the src... and display accounts to the destination from the other table. I kinda wish the Ledger had src & dest, but apparently things aren't that simple (with 1 thing going to 3 things, etc)...

 

[eibcga]

Thank you again. As mentioned, I have previously been able to get the desired report using a database such as FileMaker Pro, that is, setting up a self-join (as you pointed out will be required), showing all related records of a specified account, then presenting it using a cross-tabulated report. However, I would like to replace this by using PowerPivot instead. The workbook only includes relevant columns for the purposes of demonstration.

The Journal table is used to track financial transactions, that is, journal entries, with each journal entry assigned a unique number (id_journal). The Ledger table is used to track transaction (journal entry) line items or details. There is a many-to-many relationship between the Journal table and the Account table, since an account can have many transactions, and a transaction can have many accounts, as required by double-entry bookkeeping. So each journal entry must have at least two or more line items.

I believe that you can see the source and destination by sorting the Ledger table by journal entry number, that is, by id_journal. Note that all positive amounts are debits and all negative amounts are credits. You will note that each transaction line item of a particular journal entry number will have an account number (id_account), etc. If one were to sum the amount column, the total would be equal to zero, since all debits must equal all credits.

I forgot to include in the workbook the two columns in the Ledger table for debit and credit, as defined in the earlier post - these will be needed in the solution.

Lets say a particular account has a lot of transaction line items in it, each line item having a journal entry number. With this data set, I would like to expand the data set by showing all the related records that match each journal entry number in the data set (this requires a self-join or recursive relationship). Thus, we have all related records for that particular account. If one were to sum the amount column of this new found set of related records, the total would be equal to zero, since total debits (positive amounts) must equal total credits (negative amounts). Then present this data set in a pivot table, with two columns (Debit, and Credit), and the rows list each related account with a total for each account in the appropriate column.

If you have any more questions, please ask.