Creating your Own Accounting System - Accounts Payable

Welcome to week three of this series!  Last week we talked about the Accounts Receivable system.  This week we are set to talk about it's opposite, Accounts Payable.  In truth, they are almost identical in their set up and calculations.  The goal here is again to keep this account at zero, but in my case I have loaned money to Stitch Silly from my personal accounts in order to get the business going so until that money is paid back, I will not be at zero.

So what goes into your accounts receivable tab? You will want to track the date, the type of transaction (Invoice or Payment) the due date, ID, Paid, Vender, Transfer account, and the dollar amount, whether it be an invoice or a payment.  As I discussed last week, each line item has two parts to it.  An invoice and an associated payment.  In order to keep things straight in my head and visually, I've added two columns to this tab that are not in the Accounts Receivable tab.  Those are ID and Paid.

This could get very confusing, so I've included this picture to help explain it.  In some instances, the vendor is not paid in full on a specific transfer or the pay off date is significantly after the invoice date.  Therefore I needed a way to track these transactions.  My ID and Paid columns are set up to do this.  For each invoice I enter, I associate an ID with it starting with 1.  As invoices are paid, I insert an X in the row where the invoice is entered.  In the row where I actually make the payment, I record the invoice ID that I am paying.

For example, let's look at row 2.   It is the first invoice I recorded.  It was a web posting fee.  As you can see in column D, I assigned it an invoice ID of 1.  You can see from column D that is has been paid.  In order to keep track and to confirm that the invoice is actually paid, you need to go to the transaction in row 8.  Here you can see in column D that I have the invoice ID of 1 marked.  As I explain this and think about this process, I know there must be a better way to approach this, but for now, this works for me. 

The rest of the tracking within this tab and relating back to the Accounts Summary tab is the same process as I used in the in the Accounts Receive post from last week.  Each account type uses the same summation process described last week with one difference.  I'm not sure you would consider it a difference, as it is a logical way to calculate the values, it just took me some time to realize it was allowed in excel.

Let's go back to our Checking Account equation from last week.  All we had in the Total was

=SUMIF('Accounts Receivable'!F:F, "=Checking Account",'Accounts Receivable'!H:H)

Well today we are adding another parameter today.  Since accounts payable are monies that are owed to use we need to subtract it from the accounts receivable sum.  Our new equations looks like this:

=SUMIF('Accounts Receivable'!F:F, "=Checking Account",'Accounts Receivable'!H:H)-SUMIF('Accounts Payable'!G:G,"=Checking Account",'Accounts Payable'!H:H)

This will follow down into any account that will pull data from both the accounts receivable and accounts payable tabs. 

Next week I will discuss my customer invoices and inventory tabs.  I'm currently reading a few small business books and am getting some ideas for some additional information I would like to track and or plot so as I sort through that in my head, I will add it to the commentary in the coming weeks.