Nerd Alert - Create Your Own Accounting System Series

To a small business owner, like myself, keeping your books in order and accurate is absolutely vital to your success.  When I started my business just a few months ago I started searching around for methods and tools to help me maintain accurate books.  There are the paid software tools of course like QuickBooks, but just starting out, I didn't want to spend money on a tool like that when I knew there were free options available.

I did some digging and found an open source software called GNU Cash.  Perfect, I said to myself, this is free and it is theoretically built by people who know a little about accounting so they know what I need to track and what I don't.  Being an engineer, I was pretty much clueless about accounting requirements.  I'm still not an expert or licensed, but I've picked up a few things that I think are worth while sharing.

Now keep in mind this is a GNU tool, so it is likely Unix based and the help files are generally helpful, but not always for the novice user.  Knowing these risks, I downloaded the tool and began entering all my information into it.  It took a bit of a learning curve, but the help was helpful and slowly felt like I was getting the hang of it.  That was until, I had my first purchase where tax was involved!  I had a split transaction, mysteriously I had a negative balance in my Accounts Payable.  I couldn't figure it out and simply got frustrated.

I'm a nerd I tell myself.  I bet now that I have an idea what and how things are tracked, I can develop my own accounting system that fits my needs more precisely than this software.  Excel here I come.  I know I wanted everything to stay relatively organized and I liked how in GNU Cash there was a summary page so you could quickly see your assets, liabilities, income, and expenses on one screen.  So I started developing my spreadsheet with some of the things I did like about GNU Cash.  Over the coming weeks, I will delve into my accounting system and the intricacies of each tab and equation, there is too much information to contain in one post.

Today, I'll start with a general description of my tool in general and what is contained in each tab.  A general overview tab that just holds all the information big picture information such as:

The "-" signs to the left in Excel are what's called Group.  It is under the data tab in Microsoft Excel.  I haven't been able to find this feature in Google Docs yet, but it is more of a nicety than a necessity.  The next column is relatively self explanatory.  It merely totals each associated row where the data is contained in subsequent tabs.  I will walk through all the equations in the following weeks to break down each equation and how it related to the big picture.

These are the tabs that I found I needed for my regular tracking.  At least for now, I'm sure that this system grows and matures I may add more or take some away.  Accounts Receivable are any sales I've made.  First I create an invoice for a product, providing the date entered, type (invoice/payment), due date, Customer Name, the account the money is being transferred to, then finally separate columns for the dollar amount for the invoice, payment, and the running balance.  Ideally this tab will always have a zero balance.  Money in this tab is typically transferred to a few places, invoices go into Sales and payments go into one of my asset accounts, be it, Paypal or a checking account. 

Accounts Payable has a similar set up.  It has invoices and payments, the account you're transferring to, payment, invoice, and running balance columns.  What is different here is that I have added three additional columns.  These are ID, Paid, and Partial Payment Difference.  I needed a way to track which invoices were associated with each payment as this tab has many more rows and the payment may not always immediately follow the invoice as is typical in the accounts receivable tab right now.  ID and paid accomplish this for me now.  Each invoice gets and ID, when it is paid, the Paid column gets an 'X' and the row where I paid it, the Paid column gets the invoice ID.  My final column is temporary in my mind.  Since I am just starting out, I do not have the funds to pay myself completely back for all expenses yet.  So with each sale, I am taking a percentage and deducting it from moneys owed to me so I am raising capital in the business while simultaneously reducing the companies debt to me. 

The last four tabs are relatively complicated in their structures in the sense that there are a lot of calculations going on.  In the following weeks, I intend to walk through each tab in greater detail, explaining the equations within them and how they are automatically tallied back into the Account Summary tab.  Generally, the Customer Invoices tab tracks all my sales.  Inventory tracks my total inventory and what the status of each piece, whether it be in process, sold or posted to the internet.  Monthly sales tracks my sales per month and graphically shows the progress.  And finally, Supply Costs, tracks the average cost for each component of my products.

I hope you join me an come back over the next several Fridays to learn how you can create your own accounting system.