How to Automate Your Startup’s Bookkeeping
This is the first of a series of articles on how to automate your startup's financial operations. The next article in this series will be about Automating Your Startup's Receivables Management.
Bookkeeping is something all companies must do on an ongoing basis, and most startup founders are reluctant to do it themselves. In this article, I will discuss the different paths you can go down to automate or at least hand-off your bookkeeping to lessen the burden on your shoulders.
Before I go any further, I must note that it is not currently possible to let a software handle the entirety of your bookkeeping. There must be a human component to ongoing bookkeeping.
My suggestions are to automate the most time-consuming parts of your bookkeeping process or hire someone else to do your bookkeeping.
I’m sure many of you know what Zapier is (Missouri based startup FTW!) so I won’t go in-depth explaining it. For those of you who haven’t heard of it, Zapier is a tool you can use to easily integrate two software platforms via their APIs and create automations (what they call “Zaps”).
The following is an example of an automation I created utilizing Zapier. Although you may not use this automation exactly, I’ve gone into detail so that you can see how Zapier is used. I encourage you to utilize this automation to come up with ideas for other automations that may be more useful to you.
Previously, each month I spent about an hour downloading data from Stripe and manipulating it in Excel into a format that can be uploaded into QBO as bank transactions. This process was painful and couldn’t be easily automated with a macro because of the variations in data format each month due to Stripe’s poor options for downloading data.
Now, with these three Zaps running, I only spend about 5 minutes matching the transaction in QBO. Over one year, that’s about 11 hours of work saved thanks to less than an hour of set up time in Zapier.
I set up the following Zaps to complete my integration and automate this aspect of bookkeeping for EQ:
Zap 1: New charge in Stripe → Create Sales Receipt and Journal Entry in QBO
To start, you’ll need a Zapier account on the Starter plan to utilize multi-step Zaps and their QBO integration.
Next, you’ll need to “Make a Zap” and choose Stripe for the Zap’s first step.
Make sure to choose “New Charge” for the Trigger Event.
Next, you’ll want to pick “no” for including failed charges because you don’t want to have duplicate transactions in QBO if/when the charge eventually goes through successfully.
Now that step 1 of the Zap is complete, you can move on to step 2: creating a Sales Receipt in QBO.
Now you must customize the Sales Receipt. I only used a handful of fields:
- Note: ‘ID’
- Line Amount: ‘Amount’
- Line Description: ‘Description’
- Product/Service: ‘Sales’
- Line Item Quantity: ‘1’
- Transaction Date: ‘Created Formatted’
- Deposit to Account: ‘Stripe’
Step 3 of the Zap is simply to account for the transaction fee incurred.
Choose QBO and pick “Create Journal Entry” as the action.
The fields I used in the Journal Entry were:
- Journal Date: ‘Created Formatted’
- Debit Account: ‘Bank Charges & Fees’
- Debit Amount: ‘Transaction Fee’
- Debit Description: ‘Description’
- Credit Account: ‘Stripe’
- Credit Amount: ‘Transaction Fee’
- Credit Description: ‘Description’
- Memo: ‘StripeID: ID — SalesReceiptID: ID’
Note that the Memo includes the charge ID from Stripe and the Sales Receipt ID from QBO. This will make it easier for me to pinpoint any issues that pop up during the monthly reconciliations of the accounts.
Now that your Zap is set up, you can turn it on and let Zapier take care of transferring the data from Stripe charges to QBO.
Since there are two other types of transactions that occur in EQ’s Stripe account each month, we must create two more Zaps: one for payouts to the bank account and one for refunds to customers.
Zap 2: New Payout in Stripe → Create Journal Entry in QBO
Just like Zap 1, the first step is to pull data from Stripe and the second is to create a Journal Entry in QBO. The key differences are the fields used.
For the first step, choose “New Event” as the trigger in Stripe.
IMPORTANT: Since Zapier’s integration with Stripe doesn’t have a predefined field for Payouts, we must tell it to look for the custom value of “payout.paid”.
I found this “object” by looking through Stripe’s API documentation. There, you can find more events for your Zap. If Zapier doesn’t allow you to do exactly what you want it to, always dig into the API documentation to see if you can customize the Zap even further like I did with this one.
Before we set up the Journal Entry, we need to filter out the bad data.
“Payouts” come in two forms: money transferring from Stripe to your bank account and money transferring from your bank account to Stripe. When money moves from your bank account to Stripe, the data we get from Stripe for the “amount” field is negative. Since the Journal Entry cannot take negative amounts, we must filter out these transactions.
Utilizing the “Filter by Zapier” app as our second step, we can choose to accept only positive numbers in the “amount” field.
Onto the Journal Entry, the fields I used are:
- Debit Account: ‘Business Advantage Chk’
- Debit Amount: ‘Data Object Amount’
- Debit Description: ‘Data Object Description’
- Credit Account: ‘Stripe’
- Credit Amount: ‘Data Object Amount’
- Credit Description: ‘Data Object Description’
- Memo: ‘Data Object ID’
Now that this Zap has been created and turned on, all payouts from Stripe to EQ’s bank account will be automatically added to QBO.
You may be asking, “what happens to the other types of payouts we filtered out?” We could create another Zap, filter for only negative numbers, then format the numbers so we can create Journal Entries with them.
If these types of payouts happen a lot for you, go ahead and create the Zap! Fortunately, they don’t happen very often for EQ and it is easy enough to identify and categorize the transactions accordingly in QBO without data from Stripe.
Zap 3: New Refund in Stripe → Create Refund Receipt in QBO
The last type of transaction I integrated, which hopefully won’t happen too often, is a refund. For this Zap, we need to pull data from Stripe and create a “Refund Receipt” in QBO.
Start by choosing “New Refund” in step 1.
Then choose “Create Refund Receipt” for step 2.
The fields I used in the Refund Receipt are:
- Refund From: ‘Stripe’
- Refund Receipt Date: ‘Created Formatted’
- Product/Service: ‘Sales’
- Amount: ‘Amount’
- Description: ‘Charge Info Description’
- Memo: ‘Charge: Charge — ID: ID’
You can use Zapier to do these same integrations for Woocommerce, Shopify, Zoho Invoice, Amazon Seller Central, and more. I recommend going through Zapier’s apps to see what app integrations they have and scroll through the example Zaps to get more ideas.
If you don’t have Zapier or don’t want to set up a custom integration on your own, you can utilize a pre-made software specifically for the integration you need. For example, there are some QBO add-ons that offer pretty comprehensive Stripe integrations, including BPayments. But since these integrations are pre-made, you can’t be nimble like you can with Zapier.
Can’t automate enough with Zapier? Delegate.
As I mentioned, bookkeeping requires a human component, but that human doesn’t have to be you! One of the easiest ways to take a task off your plate is to hire someone else to do it. There are a bunch of options for handing off your bookkeeping. Here are the main options:
- Bench: Starting at $119 per month, Bench is the least expensive option I have seen for bookkeeping. They provide a very simple dashboard for your finances and even act as a replacement for QuickBooks.
- Pilot: A well-capitalized startup that just raised their Series B round, Pilot is a great option for bookkeeping. They focus on tech startups with mostly standard books.
- Escalon: Serving just about whatever bookkeeping needs you have, Escalon is a solid option to scale along with your startup. They also offer accounts receivable management, HR services, and more.
Or my favorite option (smiley)… siliconCFO handles much more than your books. If you are looking to offload more financial management responsibilities, such as accounts management, budgeting, cash-flow forecasting, and cap table management, you may be well suited for a siliconCFO.