AnsweredAssumed Answered

Financial trial balance

Question asked by eibcga on Nov 15, 2013
Latest reply on Nov 16, 2013 by philmodjunk

Title

Financial trial balance

Post

     I have a layout called Trial Balance, based on the Ledger table, that lists all the accounts as of a certain date (e.g., October 31, 2013).  The Body of the report provides a sub-summary when sorted by account number.  A trailing grand summary totals all the account balances that were listed in the sub-summary part.

     The balances shown are as at October 31, 2013 accumulating since the first financial transaction was entered.  I would like to be able to show on one report a true trial balance where it shows all the balance sheet accounts (i.e., assets, liabilities, etc.) with their respective perpetual balances accumulated since time zero to October 31, 2013, all the income statement accounts (i.e. revenues and expenses), with their respective periodic balances accumulated since the opening date I specify (e.g, since January 1, 2013 to October 31, 2013), and any opening equity accounts (i.e., opening retained earnings) with the opening balance as at the specified opening date.  Thus we have…

     Time Zero                              Opening Balance              Closing Balance

     0                                            January 1, 2013               October 31, 2013

     |<———————————————————————————————>|  Perpetual = Time Zero to October 31/13

                                                  |<—————————————————>|  Periodic = January 1, 2013 to October 31/13

     |<————————————>|                                                               Opening = Time Zero to January 1, 2013

      

     I already have all the relationships set up, but I would like to use a filtered portal on the sub-summary part in the Trial Balance layout, where the portal is one row only.  Currently, the Trial Balance report just lists all the accounts and their perpetual balances (i.e., time zero to October 31, 2013).  Could I filter something like, if the account number starts with 1, 2 or 3, then sum to October 31/13; if account number starts with 4 or 5, then sum from January 1/13 to October 31/13, else sum to December 31, 2012 or (i.e., January 1/13 minus 1).  Is this the right idea?

     The relationships graph shows object tables in grey, and event tables in green.  Query relationships are in red.  The Journal table tracks Transactions.  The Ledger table tracks Transaction Line Items.  A financial transaction can effect many accounts at one time, and an account can have many transactions at one time.  So, the Ledger table serves as the join table to resolve the many-to-many relationship between Journal and Account.

     Any guidance please for a beginner using FMPA 12.0v5 on the Mac?  Thank you very much indeed! 

Screen_Shot_2013-11-15_at_3.30.19_PM.png

Outcomes