Financial account analysis

[eibcga]

Hello all,

I'm a beginner using FMPA11v4 on Mac OS X Lion. I designed a basic database that helps analyze financial transactions in various ways using standard double-entry bookkeeping journal entries. It suits my needs so far, but I'd like to add more features, etc. as I learn new skills in FileMaker.

I'm trying to automate these steps in FileMaker using a script, etc. Here are the steps I currently do manually in FileMaker:

1) Enter the LEDGER layout in table view (LEDGER tracks JOURNAL details or line items, with an amount field that has positive amounts for debits, negative amounts for credits, so the sum of all child records equal zero indicating debits equal credits)

2) Do a Find on all account ID’s that pertain only to revenue and expenses.

3) Enter the layout called Allocated Revenue/Expense which is based on LEDGER table occurrence.

4) The layout will show me all transactions for the period based on the found set in step 2

5) I’m interested in all transactions in step 4 listed on the layout where the total is zero. (at present the found set from step 2 shows ALL transactions affecting revenue and expense accounts only, whether they total to zero or not)

6) Based on the “found set” in step 5 of transactions I see with or without zero totals, I do a new Find in the LEDGER layout table view for all transaction ID’s which only had zero totals in step 5.

I was hoping I could have FileMaker do all this for me automatically in some way, or at least automate most of these steps.

Below is my relationships graph. Thanks very much for your help. If you need more details please ask.

[LaRetta]

Hi, and welcome to FM Forums!

I'm not sure I fully understand. Can we run through it at a more basic level first?

It appears that you want to find all Ledger entries which are revenue or expense but only those who also have at least one child record in Journal with zero in an amount field. You want to end up with a set of Ledger records, is this right?

What field(s) in Ledger indicate whether revenue or expense? What field in Journal is the Amount field and is it number field or calculation? If calculation, please paste the calculation here as well. Are you using that Jedger GTRR table occurrence for something else or is that an attempt to find your records using GTRR?

You can search from either Ledger or Journal; either by performing find or by GTRR and/or constraining; the best method would depend upon your answers.

[LaRetta]

Something seemed strange when I was responding and it struck me ... you show the _kf_trans_id (Journal's primary key?) in Ledger. One of the reasons for always naming your 'primary' and 'primary foreign' keys by their table names is to avoid confusions such as these; been there, done it.

The Ledger is the parent holding only the total of its Journal entries (maybe a Sum calc or maybe a summary field in Journal; awaiting clarification on the Amount field and the amount field in Journal). The 'child' table (many side, in this case Journal) should hold the parent's (one side, in this case Ledger) key.

I believe that you need to just end your script with a constrain based upon Amount but to say any more without clarification would simply be guessing.

[eibcga]

Yes, would like to have a result that is a found set of all ledger records where the sum of these child records equals to zero, but only if all the child records affect revenue or expense accounts. That is, account IDs >3999. Revenue accounts have ID of 4000-4999. Expenses have account ID of 5000-5999. Ledger_GTRR is a query relationship used in a separate and unrelated script. Journal is basically another name for transaction. Ledger is basically transaction detail or journal line items. You're right, _kf_trans_id could be renamed _kf_journal_id.

For example, I enter a transaction (a journal record) dated Sep 6/011 for a phone bill, then enter the details in a portal. Debit phone expense $50, credit bank -$50. The amount is entered in the LEDGER::amount field. The JOURNAL::total summary field is the total of the LEDGER::amount field. The total of all child records related to each journal parent record should be zero ($50 - 50 = 0). But what if I make a subsequent transaction to correct the precious entry by debiting Internet expense $50 and crediting phone expense -$50? (to correct an entry error). For a certain period I want to find all transactions (LEDGER records) where such error corrections took place, where child records affect only revenue and/or expense accounts (as illustrated above in the correction entry example).

[LaRetta]

Per Wikipedia:

A ledger^[1] is the principal book or computer file for recording and totaling monetary transactions by account, with debits and credits in separate columns and a beginning balance and ending balance for each account. The ledger is a permanent summary of all amounts entered in supporting journals which list individual transactions by date. Every transaction flows from a journal to one or more ledgers. A company's financial statements are generated from summary totals in the ledgers.

Ledger is basically transaction detail or journal line items. You're right, _kf_trans_id could be renamed _kf_journal_id.

That is not what I said. I said that the LedgerID must be in Journal (which assigns the account number to the detail transaction). The General Ledger is the ‘one’ side to many journal entries. Ledger holds the Accounts and, in this case, one Amount field which is a summary of all Journal lines for that account. Journal should hold each detail including the amount (debit or credit listed as plus or minus) and the account number it is posted to. Then the Ledger Amount is the TOTAL of all Journal lines for each account number.

For example, I enter a transaction (a journal record) dated Sep 6/011 for a phone bill, then enter the details in a portal.

The journal IS the portal. But I also need you to either recognize that you have your naming backwards or explain further; sometimes people call subsidiary journals ledgers. Creating an accounting module is a major undertaking which should only be taken on by an advanced developer with close advisement from an accountant.

Please post your file (zipped first) with a few dummy (but logical) entries in both Journal and Ledger. :smile:

[eibcga]

Hi LaRetta, I think you and I are on the same page. Perhaps I'm just not explaining things properly and have not provided all the details. I'm a beginner FileMaker user, but I'm a professional accountant working in an accounting firm for many years.

I'm trying to develop a solution to be used to analyze financial transactions in various ways for auditing and fraud investigation purposes. It is not designed to be bookkeeping or accounting software. I have tested this very crude solution by using it to track my own personal finances. As well, I have been using this solution to import clients' transaction data to analyze transactions in various ways, and to assist with selection of sample sizes for testing during financial statement audit work.

I have been doing this analytical process manually for years by exporting a client's entire general ledger report from their accounting software into Excel. Then the data in Excel is converted into a table format (flat file) so it can be used for doing analysis using filters and pivot tables. I want to automate this process by essentially eliminating Excel and replace it with FileMaker (with all the required "pivot tables" already created in separate layouts in FileMaker).

So far this crude database has saved me a lot of time doing analysis work for various clients… while learning about database design using FM in the process, adding new features as my skills slowly improve. Unlike many accounting software packages which are designed only for bookkeeping, I can answer many different complex questions using my database using the same data (e.g., give me all transactions during the year that are $25,000 or more; give me all payroll transactions for the year that were dated on a date other than a regular Friday pay day; run a GTRR script step on a particular account for the found set in that account; give me all transactions for the year with only certain suppliers or customers over a certain amount; and so on).

When I was taking a crash course in relationships and data modelling, I referred to an Invoicing solution as an example. At a basic level, an Invoicing solution usually has a Customer table, and other tables like Invoice, Invoice Line Items, and Product. I adapted this schema to a financial ledger as follows:

Table name -- Table name

in Invoicing db -- in my db

Customer -- Name table (of customers and vendors)

Invoice -- Journal or Transaction table

Invoice Line Items -- Ledger or Transaction Line Items table

Products -- Account table

I am perhaps using the wrong terminology such as Journal or Ledger, or I'm generalizing too much. But, I think you may now see the logic. As well, because I never see the inner workings (the relationship graph or schema) of popular accounting software available today, I have to try and duplicate the results shown in reports in my database. My database assumes there is only one type of journal, that is, the General Journal or just Journal. I don't need to track different types of journals (e.g., a separate journal for sales, receipts, purchases, disbursements, payroll, etc.).

Correct me if I'm wrong, but at the very basic level, a General Ledger report in database terms, is just a listing of account transaction details or line times sorted by transaction date, grouped by account (account as break field sorted by account number), with total and running total fields to track the respective account balances. An account can have many transactions or journal entries, and a transaction or journal entry can involve many accounts (hence the use of a Ledger or Transaction join table, since there's a many-to-many relationship between Account and Journal. As we know, at a minimum, journal entry record must at least have two entries or child records, one that's a debit, and one that's a credit. In my database, I only distinguish between debit or credit in the journal screen, and in various reports. But when I enter transactions, any debit entry is just a positive amount in the amount field, and any credit entry is just a negative amount in the amount field.

For example and for simplicity, let's say on September 6, 2011 I buy a widget from Supplier A for $10.00 cash and track it in an expense account. The usual transaction would be:

September 6, 2011

Dr. Expense account $10.00

Cr. Cash account $10.00

To record purchase of widget from Supplier A

But in a database, I had designed the following entities and attributes as follows (I'm purposely leaving out Account, and Name tables):

TRANSACTION TABLE

Transaction ID_pk

NameID_kf

Date field

Memo field

cTotal [calculation field = Sum ( TRANSACTION LINE ITEMS::amount )]

I would create a new transaction record on the transaction or Journal layout related to the transaction table, enter 06/09/2011 in the date field, the supplier ID for Supplier A in the NameID field (using a drop down list based on the Name table), enter "To record purchase of widget" in the memo field.

To prevent duplicate and redundant data, the Transaction Table is a parent table so that the supplier name, date and memo field are not repeated again for each and every transaction line item. The child table is:

TRANSACTION LINE ITEMS

Line Item ID_pk

Transaction ID_fk

Account_ID_fk

Line Item Memo

Amount

cTotal

cDebit

cCredit

cBalance

I have a portal on the Journal layout related to the transaction line items table and enter the following new child records in the portal: Enter new child record and select the Account ID for the Cash account in the Account_ID field (using a drop down list based on the Account table), and enter −10.00 in the amount field. Add one more child record and select the Account ID for the expense account in the Account_ID field, and enter 10.00 in the amount field.

After entering the above journal entry and its details, the cTotal summary field, total of amount, will equal to zero since 10.00 less 10.00 = 0. In other words, debits equal credits.

I can run a trial balance report, or a general ledger report, from the layouts I created based on the transaction line items table. I have created other summary or calculation fields to make the reports look more traditional. For example, a separate debit and credit column is used on these reports (if the amount in the amount field is positive, show the amount in the cDebit field, and if negative in the cCredit field). I keep a running total or balance of each account after each transaction detail by having a cBalance summary field, running total of amount field.

Because this is a crude database, I have to change the found set in the transaction detail table so that it shows only dates on or before a certain date when I want to see the perpetual balance of a balance sheet account like assets, liabilities or equity. If I want to have the found set show only transactions in a date range for revenue and expense accounts, say for the calendar year 2010, I enter in the date field (in the Ledger table view) in Find mode "2010"). I usually do not have more than one fiscal year in the same database at one time, so the need to have hard-coded perpetual account balances for balance sheet accounts is not relevant at this point.

I'm afraid I don't know how to attach a file in this forum, but it is ready for upload. Actually, you have seen my solution before in another forum.

Thanks for baring with me as I learn databases.

[bcooney]

This thread and Rod's ERD might be helpful as it shows a complete data model.

[comment]

Now that you have clarified your structure:

Transactions -< TXitems >- Accounts

can you review your search criteria?

Keep in mind that the balance of a Transaction is usually produced by a calculation field summing the child TXitems - and as such is unaffected by any found set.

A summary field, OTOH, summarizes the found set - and the process cannot be reversed. IOW, you cannot use the intended result of a summary field as a criteria to create the found set.

[eibcga]

Thank you bcooney, and comment.

I tried to get the following script working, but it's not giving the desired results (i.e., the found set is giving me transaction line items in the LEDGER table that also involve accounts with account IDs < 3999 when this is not what I asked for] .

Go to Layout ["LEDGER" (LEDGER)]

View As [View as Table]

Enter Find Mode []

Set Field [LEDGER::_kf_acct_id; "> " & 3999]

Set Field [JOURNAL::date; Year (Get(CurrentDate))]

Set Field [JOURNAL::total; 0]

Set Error Capture [On]

Perform Find []

If [Get (FoundCount)>0]

Go to Related Record [show only related records; Match found set; From table; "LEDGER"; Using layout: <Current Layout>;New window]

Else

Show Custom Dialog ["No records were found"]

End If

[comment]

This is not helping. Please describe in simple language which records (and in which table) do you want to end up with.

[eibcga]

I'm understanding now that, while I understand the accounting theory and what information I want, I'm having difficulty trying to explain it in database terms, and to design the database to reflect the real-world situation. I'll try:

I want to find any and all particular journal entries that only:

1. debits one or more expense accounts, and credits one or more expense accounts, or

2. debits one or more revenue accounts, and credits one or more revenue accounts, or

3. debits one or more expense accounts, and credits one or more revenue accounts

Notes

#1 above can arise if a client posted to the wrong expense account, and they subsequently correct the original posting by making a new journal entry to move an amount from one expense account, to another expense account.

#2 above can arise if a client posted to the wrong revenue account, and they subsequently correct the original posting by making a new journal entry to move an amount from one revenue account, to another revenue account.

#3 above can arise if a client posted to an expense to a revenue account (or posted revenue to an expense account), and they subsequently correct the original posting by making a new journal entry to move an amount to an expense account, from a revenue account.

I find such journal entries manually in FileMaker by doing the following steps:

1) enter the LEDGER table in table view

2) enter Find mode and enter the value ">3999" in the LEDGER::_kf_acct_id field

3) enter the layout based on LEDGER called "Allocated Revenue or Expenses"

On layout enter, a script trigger runs to sort the found set in 2) by the LEDGER::_kf_trans_id field. The layout shows a listing of all journal entries (i.e., transaction line items or details) with the following columns, with their values based on the pertinent fields:

- Transaction #: JOURNAL::__kp_trans_id

- Date: JOURNAL::date

- Name: NAME::name

- Memo: JOURNAL::memo

- Other memo: LEDGER::memo

- Total: LEDGER::total (summary field, total of LEDGER::amount)

- and other pertinent columns such as Debit and Credit

4) Because the layout is currently also including irrelevant records, I scan with my eyes the Total column to see any journal entries where the Total is equal to zero.

5) For all journal entries identified in step 4) with zero totals, I take note of the corresponding Transaction #'s

6) I investigate all journal entries identified in step 5).

I would like to automate the above steps by having a script do all this work for me. If I could be provided the proper found set in the LEDGER table in the first place, then I can enter the layout mentioned in step 3) above and see a nice clean list of all relevant entries were the total equaled zero.

Any more clarification needed, please ask. It's so much more difficult trying to explain something rather than somebody just looking at the database themselves.

Thanks again for the guidance.

[eibcga]

I wanted to follow up on the problem I was having that I mentioned in the previous posts on this topic. I have figured out a work-around on my own, thanks to all of your guidance I have received in the past.

I created a new calculation field in the Journal table called "total_jrnl_alloc", Unstored, =Sum(Ledger_Alloc::amount). If the sum is equal to zero, then the total debits (any positive values) equal the total credits (any negative values).

I also created a new number global field in the Ledger table called "g_acctid" and input the field value "3999", indicating that account ID's with values greater than 3999 represent revenue and expense accounts.

I made a new "query relationship" by adding a new table occurrence called "Ledger_Alloc", with Ledger as the source table. I then linked this TO to the Ledger TO using a multi-predicate relationship:

Ledger_Alloc::_kf_trans_id = Ledger::_kf_trans_id

Ledger_Alloc:: _kf_acct_id > g_acctid

I then created the following script:

Set Error Capture [On]

Go to Layout [Journal" (Journal)]

Enter Find Mode []

Set Field [Journal::total_jrnl_alloc; 0]

Perform Find []

Go to Related Record [show only related records; Match found set; From table: "Ledger_Alloc"; Using layout: "Ledger" (Ledger)]

//Give me all journal entries entered which only allocate amounts between revenue and/or expense accounts

When I run the script, I get the desired results. Since I have over 10,000 records in the Ledger table, and about 4,000 records in the Journal table, the script does take about eight seconds to run, which gives me the dialogue box indicating, "Find in progress. Processing query."

What I learned here is that, had I not made the new fields, new table occurrence, and new relationship mentioned above, I would not get the desired results. In particular, using the new calculation field "Journal::total_jrnl_alloc" in the script, instead of using an existing calculation field "Journal::total", which, incidentally, has the same calculation except it's using a different TO, made all the difference. The Journal::total unstored calculation is "Sum(Ledger::amount)", and the unstored calc for Journal__total_jrnl_alloc is: =Sum(Ledger_Alloc::amount)". I initially didn't think this step was necessary and I was confused why FMP could not figure this out on its own.

This script, now that it finally works, enables me to import a client's general ledger into FMP when I'm doing a financial audit, and to determine if there were any revenue or expense allocation journal entries made during their fiscal year. This question is otherwise very difficult to answer manually, because client's could have thousands of journal entries, not all of them involve allocations, nor is this feature available in any 3rd party auditing software in the market today.

Thank you all again for your patience and helping me learn FMP. I love it!