After speaking to a few people, I've noticed that a number of startups and SaaS companies are unsure on the best way to integrate their custom billing systems with their backend accounting package (i.e. Xero, Sage, Freshbooks etc...).
If you're processing a large number of invoices, it's simply not practical or possible to copy each individual invoice over to your accounting package and reconcile every payment against a payment in your bank account. Not to mention, it's hardly ideal to need to store every invoice in two seperate systems.
This blog post is going to explain how we transfer our accounting data from our billing system into our backend accounting software.
Your billing system
We have a billing system which handles all our automated billing and stores all customer details & invoices. Whenever we need to take some money from a customer, we create an invoice and then try to take a payment for it using our payment processor.
With every invoice, we store a few important bits of information which we needed when we export our financial data to our accounting system (we use Xero).
- The total amount we charged the customer's card.
- The amount of VAT on the invoice.
- The total processing fees from the payment processor for the transaction.
- The payment process which was used to take the payment.
Then, every morning at 3am we gather up the invoices which were raised on the previous day and copy the total invoices and payment fees over to our accounting system.
The technique I'm about explain uses what's know as double-entry bookkeeping. Here's a quick primer on it in case you're not already aware.
Double-entry bookkeeping dictates that every financial transaction which takes place will have at least two entries in your accounts. Both a credit and a debit from different accounts.
Example #1: You buy a book which costs Â£20 using your debit card. You begin by debiting (removing) the Â£20 from your bank account - this will deduct the amount of money in your account as you would expect. Next, you credit Â£20 to a Book Expenses account - this allows you to keep track of the total you have spent on books and will appear on your Profit & Loss reports as an expense.
Example #2: You buy a computer which costs Â£1,000 (again, using your debit card). Rather than having just two entries, this time we will have three entries although the total debited will equal the total credited. You paid the supplier Â£1,000 so you start by debiting this amount from your bank account. Next, you need to credit your Computer Equipment account with Â£900 as this is the value of the computer before VAT. The remaining Â£100 was taken by the supplier as VAT and you can reclaim this. You credit this onto your VAT account which keeps a running total of the VAT you owe or is owed to you by the tax man. You can see that the total debit and credit are the same although they split account multiple accounts.
A Journal Entry is a set of individual updates to your accounts. Each of the above examples, would be a journal entry with 2 or 3 entries respectively. It'll look a little like this:
Every day, you will submit a single journal entry from your billing system to your accounting software. This will contain the total sales, VAT and payment processing fees for the previous day.
Nothing is that simple
While you now should understand double entry bookkeeping and how to add journals, that's only half the story and I doubt any accounting software actually uses the same style of book keeping shown above - that's purely to illustrate the two sides of a journal.
If I were to tell you that, everything I've just told you was the wrong way around, what would you say?
Although you're actually taking money from your bank account, you're actually crediting it in your accounting system. Sound crazy? A little. Let me explain...
The reason for this is that, depending on the type of account, credits & debits do different things to the running balance of the account. The table below shows what happens when you credit/debit an account:
|Capital||Decreases Balance||Increases Balance|
|Assets||Increases Balance||Decreases Balance|
|Liabilities||Decreases Balance||Increases Balance|
|Revenue||Decreases Balance||Increases Balance|
|Expenses||Increases Balance||Decreases Balance|
Every account in your system is assigned to one of these types which defines how it will behave and how you want the money in that account to be treated when you produce your financial documents. For example, assets, liabilities & capital will appear on your balance sheet and revenue & expenses will appear on your profit & loss documents.
- Capital - this is money which you have in-hand. For example, a bank account or cash.
- Assets - for example, computer equipment or money which is owed to you by your clients but not yet paid.
- Liabilities - for example, any loans you may have or supplier invoices which you have received but not yet paid.
- Revenue - these accounts store a running total of all the money which you have earned.
- Expense - these accounts store a running total of the money which you have spent. For example, on salaries.
We can now see that when we credit our bank account (capital), we're actually decreasing the balance which is what we observe in real life because we gave money to the book seller. We can also see that we're debiting our books (expense) account, which will increase the balance - a balance which stores the total amount spent on books.
A receivables account is an asset account where you can keep track of customers who owe you money. This is like a holding account for money which hasn't yet arrived in your bank account but has been paid.
For example, your payment gateway may hold onto money for up to 7 days before transferring it into your bank account. In cases where this happens.
Example: If a customer pays you Â£2,000 for a product, you'd credit your Subscription Sales account which keeps track of the total sales which you have made. Then, you would debit your Receivable Account with the amount which has been paid.
As soon as this money arrives in your bank account, you can simply move the money from the receivable account into your bank account. This should be a feature of your accounting software.
Your chart of accounts
Your accounting software will allow you to define what is known as a chart of accounts. This is basically just a list of accounts which you can receive money into or spend money from. We've already referenced a number of accounts in this post - the Bank Account, Computer Equipment, Book Purchases & VAT account are all accounts.
To transfer finances from your billing system, you'll need a few accounts. These are shown below:
A sales (revenue) account - this will store the total sales for your application. We'll call it Subscription Sales. This should be categoried as Revenue and should appear on your Profit & Loss account.
A receiveables (asset) account for your payment processor - this will store money which you have taken from a customer but haven't yet received into your bank account. We'll call this Stripe Receivables. This should be a Current Asset account and should appear on your Balance Sheet.
A payment processing fees (expenses) account - this will store all the fees you incur from processing payments. We'll call this Stripe Fees and it should be a Direct Cost account and should appear on your Profit & Loss account.
If you charge a sales tax (VAT), you'll also need a liability account to keep track of any amounts which you have collected but not yet paid to your tax authority. We'll just call this VAT.
Once you've created these, we can go ahead and prepare a journal to copy to our accounting software.
Preparing your journal
Before we get started, I'm going to preface this with a note about VAT. VAT handling within the EU is currently a bit of a nightmare with the whole VAT MOSS system.
Firstly, you need to choose a period which you want to export. We export every day but you can do it as frequently or infrequently as you wish. Begin by calculating the total values for everything listed below:
Total total amount of money you have collected. We'll call this Total Gross Revenue. This should be the total (including tax) for every invoice in the period. For this example it is Â£5,000.
The total amount of tax which has been collected. We'll call this Total Tax. For this example, it is Â£1,000.
The total amount of money you have collected before you added VAT. We'll call this Total Net Revenue. For this example, it is Â£4,000.
The total amount payment processing fees you have incurred. We'll call this Total Payment Processing Fees. For this example, it is Â£50.
Creating a journal
Once you've got these numbers, creating your journal export is quite simple.
You'll notice that we haven't included any payment processing fees. If your payment processor deducts fees before they send you money, you can simply transfer them from the Receiveable account to your Fees account.
If you processor doesn't include fees like this, you can ignore them and wait for them to send an invoice.
A note about tax: some systems will calculate the VAT amounts for you and prepare a tax return for you. If they do this, you may simply be able to select a tax rate alongside your Subscription Sales line and it'll be calculated automatically. This is how Xero handles it - it's not quite ideal as it can result in a few rounding issues).
It would be madness to try and reconcile every invoice with a payment so we simply reconcile every transfer we receive from Stripe with a single transfer transactions which moves money from the Stripe Receivables into our bank account.
If we ever need to look into a specific payment, we can easily check with Stripe to see which invoices were included in a transfer and back to our own billing system to see details of the invoices.
With any luck, your accounting software has an API which allows you to do all this automatically. If so, explore their documentation and set it up to happen automatically - it's well worth it!
I absolutely love this method of transferring financial data. The benefits are great:
We have a single authoritative source for all invoices. There's no need to copy loads of invoices into our accounting system. This allows us to keep things clean and organizised and allows our accountant to easily export data to produce our end of year accounts.
We have an almost live idea of our finances. We can login to Xero any day and our profit & loss document will always include up-to-date sales figures.