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!!!