Create Your Own Accounting System Series - Accounts Receivable

This week in my series, create your own accounting system, I will talk about the Accounts Receivable tab of overall system.  After posting my initial post last week, I got several comments on Etsy relating to my initial overview spreadsheet that I would like to share first. 

First, and this should have been obvious to me, but I missed it.  We need to track our inventory and materials as an asset to your company.  The easy part is, I already have a tab tracking my inventory and what is in process, sold, and for sale.  Therefore I simply added a new row called, "Inventory" and summed items that are either in process and or still for sale.  Then I ensured that my assets included my new row. 

Another error in my ways, while I was tracking Sales Tax Collected, I was tracking it as an Expense.  Not so, it should be tracked as an Income.  I moved this from my expense section to my Income section of my Summary tab.

Finally, an interesting nuance that I discovered.  While all the expenses to run a legitimate business need to be tracked, there are slight variations of these expenses.  For instance, some are deductible and some are not.   For instance, while raw materials are a legitimate expense, we cannot deduct them, but the fees we pay to Etsy for posting or paypal are deductible.  Both need to be tracked, but for different purposes.  To ease my confusion, I broke out the items I will be able to deduct and not deduct.

On to the topic of the day, the Accounts Receivable tab.  As I said last week, the goal of this page is to stay at zero.

Here is a sample of what your page should look like.  I left the Customer name out in this example to focus on the important stuff.  Similar to the Accounts Summary page, this to is a summary to a certain degree, it summarizes your sales and any other receivables you expect to get.  In my case, it is mostly sales.  When I create an invoice in my Customer Invoices tab, I also come this tab and enter the date of the invoice, that it was an invoice, the date I expect payment by, what invoice this is associated with so if needed I can get more details, who the customer is, where the money is transferred to, and the value of the invoice.  If the new line item is a payment, simply replace invoice with payment in the type column and put the value in the payment column.  The Balance column is calculated using the formula "Balance = Payment - Invoice."  This equation is dragged down the length of the column so that it can be automatically updated as new inputs are added.

At this point and time there are very few options for the transfer column.  They are "Sales" which is used only when recording an invoice, Checking Account, or Paypal Checking.  The last two are used depending on the type of payment I receive.  Paypal is for those payments received through Paypal and Checking is for all other forms of payment.

So how do I get my Account Summary page to automatically update anytime I make a change here.  That is what makes this so powerful of course, and allows us to trust our system to work with us instead of aiding in mistakes.  In the picture above there are four things that are relevant to the Account Summary page.  They are Sales, Checking Account, Pay Pal Checking, and Balance.  Let's start with Balance.  The Balance value relates back to the Account Receivable Total in the Account Summary Tab.  You say to yourself, well that's easy, just grab the last used row and link it to the Account Summary Tab.  That's what I thought at least.  Well that was easier said than done.  I tried all sorts of tricks, if statements, index functions, I searched high and low.  Then I remembered my philosophy when I was still a software engineer.  K.I.S.S. - Keep it simple, Stupid!  How do you accomplish this here?  Well what is the equation for the balance column?  Payment - Invoice of course.  So what did I do but simple put in my accounts receivable cell in the Account Summary Tab but "=SUM('Accounts Receivable'!H:H)-SUM('Accounts Receivable'!G:G)."  Column H is the payment column and Column G is the Invoice column and by using the notation H:H or G:G you are asking excel to get the data from the entire column, so you don't have to worry about updating it as you add more entries.  

The other three are essentially the same equation with different test associated with them.  They use my new favorite equation, the SUMIF equation.  This equation looks through a specified range testing your conditions for true or false, if it is false it will add the values with any previous values, if it is false, the row is ignored.  There is more to each of the equations than just pulling data from the Accounts Receivable tab, but for right now we will talk about just that portion.  If you to want to calculate Checking Account from this tab only, your equation will look like the following:

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

Your first term is the column you are testing, in this case we are testing the transfer column.  Since here we are testing for transfers into our checking account our test case is "=Checking Account".  The quotes and equal sign are both required here to get the appropriate results.  If you were testing for another case, you simply will exchange Checking Account for your new test case, say, Paypal Checking.  The rest will remain the same.  The final parameter in the SUMIF formula is the column that is to be summed.  Here we are added up the values of payments received so it is the payment column.

Please keep in mind that this is only part of the equations associated with the Accounts Summary tab.  Many of these transfer accounts are used in other tabs and therefore will require additional terms to the equations.  As we discuss these other tabs, I will remind you of this fact.

So that's it...once you have implemented these equations your Account Summary page should automatically update as you make changes.  Oh, I should mention that these equations are entered in the Accounts Summary pages in the cell associated with its respective label.