1 Reply Latest reply on Sep 4, 2012 1:53 PM by philmodjunk

    Petty Cash accounting



      Petty Cash accounting


      I'm wondering if anyone on the forum could suggest to me an appropriate data structure for dealing with PETTY CASH in a solution that tracks expenses.

      The solution is something I put together for various functions, but it is meant primarily to track expenses for a film production. SQL based Production Accounting software is out of my budget range for the size of Productions I manage, so I'm trying to use Filmemaker as a substitute. I'm currently the only user and I'm developing it on the fly. Up until now I have managed to keep the data structure very simple. I have 2 "General Ledger" tables that I use to import data from my budget (one for major Accounts and the other for related Sub-Accounts). I have tables for Cast_Members, Crew_Member and Vendors. And I have a table for Expenses that is linked via relationships to all of my other entities, which enables me to compare the Expenses to my budget using the GL codes and to track how much I've paid out to all of these entities. Records in the Expenses table have fields for the expense amounts, dates when the payment was commited/cleared and various foreign keys that link them to the other entities.

      When this Expense table is sorted in list view by Account/Sub-Account number it gives me a very nice cost report that enables me to compare my costs with my budget. I can enter various deposits into the bank account as negative transactions to offset my totals, instead of tracking them in a seperate Accounts_Receivable table. However, the thing that I'm stumped on is how to deal with Petty cash expenses without manipulating my schema more than I have to. If it weren't for the challenges of dealing with petty cash, my solution would work perfectly as is!

      For example, I have several checks written out to Petty Cash and a few ATM withdrawels for Petty Cash. These PC funds are typically blended together into some kind of cash box (so they end up funding a single petty cash account, if you will). Cash is used to pay certain things directly and is handed out to various employees who ideally bring back receipts for their PC purchases, plus whatever cash is left over, for me (the de-facto accountant) to reconcile the PC. Up until now I have created an expense record for each Petty Cash check and each withdrawal. But since petty cash expenses are split up between different GL accounts, I have to figure out a way for my PC withdrawals to be split up into different Expense records. I also have to be able to create a list of pending/cleared checks for reconcilation to assess how many charges are pending on the bank account. Currently I am able to do this in a layout based on the Expenses table by simply sorting based on checks cleared and checks pending, omiting Expense records that are not specifically CHECKS. 

      My suspicion is that the only way to effectively pull this off is to create an Accounts_Payable table, an Accouns_Receivable table, a Petty_Cash table and perhaps a Check_Log table... and to somehow relate them all to the Expenses table. That way my Petty Cash checks will be records in the Petty_Cash table, and each PC expense can be created through a relationship between the Petty_Cash and Expenses tables and coded appropriately. I will obviously need a join table between these two since there will be a many to many relationship between the Expenses and the Petty Cash withdrawal tables. 

      Before I start completely redoing all of my schema I was hoping that I might be able to get some suggestions from PhilModJunk or one of the other FM Forum gods! :-) I don't expect anyone to make an ERD for me. But any pointers would be much appreciated! Perhaps someone could suggest another resource or sample solution that deals with these accounting specific things. Thanks!!!


        • 1. Re: Petty Cash accounting

          That took awhile to read!

          I'm not sure that I am at all clear on every detail of what you need here. But this all reminds me of a set up I created for managing personal spending by setting up a DB enhanced Check Register.

          I could enter each check, Debit Card, credit Card, deposit and ATM withdrawel as individual records in the same table. I had a Deposit field for deposits and transfers from other accounts and a withdrawel field for all other transactions. I then added a portal to a related table so that I could itemize the spending for a given transaction linking different portions of the total transaction to different budget categories. A drop down in this portal allowed me to pick the appropriate budget category.

          That way, my wife could hand me a receipt from Walmart and I could run down the receipt making entries that linked to Food, entertainment, Clothing, etc...

          The Withdrawel field in my "check register" was actually a calculation that used sum to total all the records from the itemized entries in the portal.

          (A checkbox, BTW allowed me to check off each transaction against a bank statement as I confirmed that the amounts matched the statements, performing a find using this check box field quickly gave me a total to match against the final balance on any such statement.)

          Note that each individual item in the portal could be a single item purchased or a group of items that all were spending from the same category, I could be as detailed or general I want and it still worked as long as I got the right sub amounts set up to be linked to my budget categories.