7 Replies Latest reply on Feb 22, 2012 1:35 PM by philmodjunk

    Accounting p

    BrianSweeney

      Title

      Accounting p&l / bal sheet summary and date range help

      Post

      I'm stumped.

      I have an accounting solution built into my database:

      Account: id, Type (Asset, liability, equity, A/R, A/P, income, expenses), name (bank acct, office supplies, etc), sum of related journal debits and credits, asset account balance (sum of related debits - sum of related credits) and vice versa, and summary field of the like.

       

      Journal: related account id, related transaction id, debit, credit, and various failed calculation fields

       

      Transaction: id, date, payee payor, memo

       

      I need a formula to get the total equity (Assets = Liabilities + Owner's Equity + Revenue - Expenses - Draws).  

      I have a working P&L, and the net profit shows up fine.  The balance sheet works ok with the get summary asset field: getsummary(summary asset; Type) in the sub-summary when sorted by type.  But I don't know how to subtract liabilities in the trailing grand summary or subtract the net profit.  I also can't seem to get a date range working properly.  The P&L and Bal sheets are based on the Account table.  I tried it on the Journal table but it seems to list records.  

      The script I use for P&L is: Go to layout P&L (account); enter find mode specify type: income, type: cogs, type: expense, perform find; sort records based on value list (income, cogs, expenses); 

      The script I use for bal sheet is: go to layout balsheet (account); enter find mode specify type: asset, type: liability, type: equity; perform find, sort records based on value list (assets, liabilities, equity)

       

      Any help is appreciated.  Thank you in advance.

        • 1. Re: Accounting p&l / bal sheet summary and date range help
          philmodjunk

          Not being an accountant or bookkeeper, it's not immediatly obvious to me when I read your post how, if you were working this out with paper and pencil, you would compute the Liabilities, owners Equity, etc totals that you need to compute the total equity.

          I can make a few general observations and that may be enough to get you unstuck here:

          The balance sheet works ok with the get summary asset field: getsummary(summary asset; Type) in the sub-summary when sorted by type.

          Actually, if you were to place the summary asset field in this sub summary layout part you'd also get the correct sub total. I point this out to illustrate an important point:

          The same summary field when placed in a sub summary part will compute a sub total. When placed in a grand summary, it will compute the total fo all records in your found set. If you refer to a summary field in a calculation field, it's as though you placed the summary field in your grand summary part, it returns the total of your current found set. This is were GetSummary comes into play as you can use it to access the subtotals from your sub summary parts by referring to the same "break" field in the getSummary function as you specified as the "when sorted by" field in the sub summary part.

          Thus, if you can get the individual totals that you need for your total equity, in the sub summary part, you can create a calculation field to add and subtract these values to get the answer you want.

          • 2. Re: Accounting p&l / bal sheet summary and date range help
            BrianSweeney

            After writing the below paragraphs, I've figured out the two things I think I need.  When I run the P&L, how to I capture the net profit from a summary field with a variable?  Do you have any idea why my date ranges aren't working?

             

             

            Thank you for your response.  I'd like to clarify a little more and see if I'm just missing it.  I think I've been working on it too long.  


            Basically you run a profit and loss statement on one layout.  Input a date range (which I haven't figured out yet), perform a find of the income and expense accounts in that range and the result is your net profit for the period.  The net profit works right now because the expenses are recorded as a negative (revenues are recorded opposite - credit is an increase whereas for expenses a debit is an increase) so the summary function automatically subtracts it from the income.

            On the balance sheet layout you perform a find of the same date range and asset accounts then subtotal, liability accounts and subtotal.  The difference would be the owner's equity: Owner's Equity = Assets - Liabilities.  I would then need to add any equity the owner put in, and draws he pulled out, and add the net profit from the aforementioned profit & loss statement.

             

            Assets = Liabilities + Owner's Equity + Revenue - Expenses - Draws

            where: Revenues increase Owner's Equity

            Expenses decrease Owner's Equity

            Draws or Dividends decrease Owner's Equity

            • 3. Re: Accounting p&l / bal sheet summary and date range help
              philmodjunk

              You haven't indicated how you are trying to use your date range. Here's the general idea, let me know if this does or does not work for you.

              First you need a field of type date for your dates where you want to specify the date range. This seems obvious, but a lot of folks use text fields and don't realize that they have. This field must show "date" as the field type when you check it in manage | Database | Fields.

              Next you enter find mode and specify a range of dates in this field as your find criteria or you use wild cards as part of the criteria.

              Date range example:

              entering 1/1/2012...1/31/2012 will find all records in the Month of January 2012. (Assumming MMDDYYYY date settings.

              Wild card example

              entering 1/*/2012 will also find the same records

              Both methods can be used in manual finds or in scripts.

              • 4. Re: Accounting p&l / bal sheet summary and date range help
                BrianSweeney
                 Since the dates are located in the transaction table I'm not sure how to do it. The layout is based on the account table.
                • 5. Re: Accounting p&l / bal sheet summary and date range help
                  philmodjunk

                  The principle is the same regardless of the table, but there are two basic areas where date ranges come in to play. Performing a find--what I gave an example of in my previous post--or matching records via a relationship based on a range of dates. That is also possible using two date fields to define the range and inequality operators to compare the values to the same date field in the related table.

                  You'll need to tell me exactly what you need to do here before I can post any additional help.

                  • 6. Re: Accounting p&l / bal sheet summary and date range help
                    BrianSweeney

                    It's like trying to search in a portal, a date find only looks at the first related record but return them all.  The dates are in the Journal table.  I'm doing a balance sheet based on accounts so that's why the layout is based on the accounts table.  A bank account (which is one record in the Accounts table) will have many journal entries.  

                    If I switch the layout and based it on journal entries, the date range find works.  The problem with that is the report shows the account balance multiple times, one for each entry.

                    • 7. Re: Accounting p&l / bal sheet summary and date range help
                      philmodjunk

                      Please remember that I have no idea how your layouts are designed.

                      a date find only looks at the first related record

                      That's not quite correct, if you specify criteria in a field from a related table, the find does look at all the related records, but you are performing a find for the layout's table of record, not the related table so if you have even one related record the layout's record to which it is linked is included in the found set. Thus, if you specify a date range of 1/1/2012...1/31/2012 in the Journal's date field when performing a find on the accounts table, you'll find all accounts records with at least one journal record in the specified date range, but any portals, calculations etc will ignore this criteria once you return to browse mode and each account record found will link to all Journal records regardless of the specified criteria used in the Find.

                      It comes down to what you are trying to find and why. Do you want to find Account records or Journal records? Do you want to find all records with at least one journal record in the specified date range? (That's what you should be getting now) or all such records but portals, calculations etc must only refer to related records in the specified range?

                      All of the above is possible, I just need to know what it is that you are trying to achieve. A screen shot of the layout might help.