7 Replies Latest reply on May 30, 2012 11:03 AM by philmodjunk

    Accounting Systems

    Vinny

      Title

      Accounting Systems

      Post

      Anyone have good info to share about setting up an accounting system?

      I have a solid understanding of accounting & how accounting systems work, but I've never implemented one.  I'm looking to implement a perpetual system that updates instantly (i.e. no batching).

      I'm most curious about how people have implemented posting to the ledgers (i.e. from invoices, credit memos, etc.).

      Any info or direction would be much appreciated.

       

      Thank you,

        • 1. Re: Accounting Systems
          davidanders

          The White Paper for FMP Novices is useful  - 
          http://www.foundationdbs.com/downloads.html

          Filemaker Free - Listing of free resources  - 
          http://www.filemakerfree.com/

          Filemaker Wikipedia  - 
          http://en.wikipedia.org/wiki/FileMaker

          Filemaker - Version By Version  - 
          http://www.tokerud.com/filemaker/

          Lynda dot com has Filemaker Videos (portions are free)
          http://www.lynda.com/FileMaker-training-tutorials/116-0.html

          Free unlocked templates are useful for examining design
          Starting Point -
          http://www.fmstartingpoint.com/
          Donations -
          http://filemakerdonations.com/
          Recruiter -
          http://rccrecruiter.com/alt-index.html/
          Data4Life -
          http://www.data4life.net/
          RCC Blog  - 
          http://filemakerbits.wordpress.com/

          A free calendar is available to be bolted onto your database
          http://www.seedcode.com/cp-app/prod/calendarfree10/

          You can search specific Filemaker sites on this Custom Google Search
          http://www.google.com/cse/home?cx=001044389222327874554:vi8it1bulm8/

          Looking at FMP business database demos is useful - some are fully useable
          The Excelisys Business Tracker V3.0  - 
          http://www.excelisys.com/exbiztracker3.php/

          Filemaker released a FMP runtime database in 2007 for students called "Campus Productivity Kit" and is available online, notably at CNet
          Google "Campus Productivity Kit"
          WIN http://download.cnet.com/FileMaker-Campus-Productivity-Kit/3000-2124_4-10577579.html/
          Mac http://download.cnet.com/FileMaker-Campus-Productivity-Kit/3000-2124_4-10577567.html/

          • 2. Re: Accounting Systems
            Vinny

            Yeah, thanks.  I have visited all of these sites already (except the broken links). Unfortunately, there isn't a single (free) solution that has a ledger based system that is open to investigate.

            I'm looking for methodology, like how to post to the ledgers.

            For example, an invoice will have related (posted) transactions to a ledger.  This would be for double entry accounting, like inventory debits, A/P credits, etc.

            Do I want to run a "post" script every time we leave an invoice record (i.e. close window, or on record load)?  Should I repost the transactions every time, or check to see if they have changed first?

            • 3. Re: Accounting Systems
              philmodjunk

              You are asking for a lot of different things. Breaking things down into specific questions will make it easier for others to respond to them with suggested answers.

              Here's a basic set up for a Ledger table:

              Fields:

              AccountID 
              TransActionID (autoentered serial number, primary key for this table)
              Description ( text)
              TransDate ( Date, can be set to auto-enter today's date)
              Debit
              Credit
              cBal (calculation Debit - Credit )
              sRunningBal ( Summary, running total of cBal, restart totals when grouped by AccountID)
              Total ( Summary, Total of cBal )

              You can set up a list or tableview layout with AccountID, TransDate, Description, Debit, Credit and sRunningTotal to get an electronic equivalent of your basic ledger. You can perform finds to limit the transactions entered to a single account or you can pull up multiple accounts at once and sort by AccountID to group them by account. Either way, the running balance field will show the typical running balance of such a ledger.

              Entering a single entry in such a table is a matter of creating one new record in this table. Making it double entry--if I remember what I learned in a basic account course taken many years ago--whould require making a second entry with a different account and the debit and credit entries switched. Such actions can be done manually (a good way to confirm that you have your table and layout set up correctly) and also can be scripted.

              When it comes to posting transactions from sales invoices, you can take one of two approaches: The invoices layout can be based on this ledger layout with auto-enter settings that automatically enter the correct AccountID for sales transactions. In which case, creating and completing a new sales invoice automatically "posts" that transaction to this table.

              Or you can use a button with a script to post the invoice by creating a matching entry in the ledger table when the button is clicked. Such a script would need to check for an existing "post" before making a new one.

              • 4. Re: Accounting Systems
                Vinny

                My apologies for being vague.  I'm looking for vague answers & ideas - similar to your last two paragraphs (thank you).

                I have determined that my invoices table is better made to match sales orders, quotes, etc. - basically anything that has a list of line items which are part numbers.  It has helped for re-use of many fields.

                I have thought about both of your approaches, only with a little twist:

                Approach 1:

                The invoice *is* the ledger transaction.  If for example, we were to view inventory level for an item, the current balance would be made up from a calculation that uses (as well as other variables/data) all invoices with that item.  It would use "invoices" as well as "vender purchase orders" as the transactions from which to compute the current inventory level.

                Approach 2:

                Every invoice will have related posts/transactions to the main ledger.  Every time a user udpates the invoice, it re-writes all ledger transactions with the appropriate double entry accounting methods (debits and credits).  If an invoice is deleted, then the transactions are deleted along with it.

                ----

                Approach 2 seems like the better design, not sure why, but it's my gut feeling.  With Approach 2, I wouldn't know when to run the "posting" scripts...normally I would do something like this when a user hits "save", but I have no save buttons with my filemaker solution.  

                I would prefer to implement Approach 1 because of it's (assumed) simplicity - invoices are posting items, and would be part of various calculations (wether it be to accounts payable, inventory, etc.).  Is calculating everything on the fly a good or bad thing?

                I need to make a decision and run with it, and so I was looking for advice.  I have a feeling that several people have some great lessons learned, but finding those people (and one that wants to "help") is difficult.

                • 5. Re: Accounting Systems
                  philmodjunk

                  At some point we need to make sure that we are discussing the same design details and not assuming incorrectly. A typical invoicing system looks like this at the relationship level:

                  Customers-----<Invoices----<LineItems>-----ProductsServices

                  The key detail is that items/services sold are listed in the related line items table--typically via a portal, not in invoices. I'm just making sure that's what you have in mind here.

                  Approach 1 would work well for posting changes to your sales account automatically. A script would be needed to manage inventory changes unless you used the Line Items table as an Inventory Ledger to log items out of inventory as they are sold. The same line items table can be used to log all inventory changes due to shrinkage, RTV, received shipments, Product Manufactured/assembled, etc.

                  You can design your layout to use a Save button with a bit of careful layout design. You can also use the OnRecordCommit trigger to perform the script you want to assign to the save button to make this happen automatically.

                  Approach 2 will probably "feel" better for an accountant or bookkeeper has it keeps account transactions separate from the other details of a sales invoice, but from a data base design point of view, it results in redundant data as you now have sales numbers in Invoices and also in your ledger table. That requires careful work to ensure that the two are always in complete agreement with each other.

                  But on the flip side, you'll have many other accounts that have nothing to do with sales transactions.

                  I need to make a decision and run with it

                  I'm not going to recommend one approach over the other and I suggest that you not lock yourself in to either approach just yet. The best approach could be either or a different one not yet suggested. Instead, experiment with both approaches and see which works best for your particular business model.

                  • 6. Re: Accounting Systems
                    Vinny

                    Phil,

                    My solution is normalized.

                    The first issue I noticied with approach 1 is that it will be very difficult to compile "transactions" from several tables to form a ledger view.

                    To make a ledger view for one account (i.e. accounts payable), it seems more appropriate to have a layout based on the ledger (i.e. transactions). 

                    How else would I show a ledger view of invoices, debit memos, discounts, etc. all compiled into one view?

                    Also, while transactions impacting inventory items may have to be handled differently than transactions impacting an account.

                    Updating the transactions OnRecordCommit seems like way too often.  I guess I'll have to see if it impacts performance at all.

                    • 7. Re: Accounting Systems
                      philmodjunk

                      How else would I show a ledger view of invoices, debit memos, discounts, etc. all compiled into one view?

                      That hits on the key distinction, I think. If you put sales invoices into their own table and don't try to include other accounts in the same layout, it's very straight forward, trying to include other account transactions becomes complicated as you'd end up needing to move amounts into debit/credit fields anyway to get the ledger totals/balances to compute correctly.

                      Keep in mind that you can create any number of layouts that refer to the same table.

                      Updating the transactions OnRecordCommit seems like way too often.

                      That will depend on your layout design and may not happen nearly as often as you might think. It's also not the only option as you can disable the automatic save and add a button to save the layout changes. It takes a bit of layout dsign creativity, however, as clicking the blank background of the layout still commits the record even when auto save is turned off. You end up covering the layout background with an invisible, empty web viewer to intercept the mouse clicks to keep the mouse clicks from committing the record.