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