Guide to E-Commerce Bookkeeping
BOOKKEEPING GUIDE ©
by Sean Renshaw
An overview on the important bookkeeping considerations for your ecommerce business. Selling online is different in many ways to traditional retail. You can even have a million dollar business with just a handful of people.
It seems everyone knows someone now that is opening up an ecommerce store. Retail is changing and we need to be prepared. It may seem easy at first, but as your company scales in size, it can quickly get. Entering sales orders from an array of different channels, calculating the right sales tax on every order, inventory management (not too much/not too little, BFCM planning), shipping domestically/internationally. There is a lot! And when you grow from hundreds of orders to thousands of orders, even simple tasks get more complex.
In this guide, we explain some essential considerations for the bookkeeping of your ecommerce business. There are many intricacies where ecommerce businesses will vary from traditional retail, merchant fees (i.e., Shopify Pay, PayPal, and Stripe… etc), shipping, and using invoices vs sales receipts for individual orders.
→ Ecommerce is expanding rapidly. Find out more about the biggest ecommerce trends to watch out for.
1. Merchant Fees
There is no getting around this one. Online stores don’t accept cash, so processing payments on credit cards just comes with the territory. To have this luxury, it comes at a price, and that price comes in the form of merchant fees. Just like credit card purchases made in a retail store, the store owner is charged a small percentage of the purchase by the credit card company. Similarly, you are charged a small percentage of each of your sales and you will just need to build this cost into your financial model.
Here are two common examples.
- Shopify, main plan: 2.6% + 30¢ per transaction
- PayPal, commercial: 2.9% + 30¢ per transaction
Where merchant fees get a little more complicated from a bookkeeping standpoint, is that the order received in your store (gross sale) will not match the money deposited into your bank account (net sales). To muddy the waters even more, different cards (i.e., Amex) and different regions (domestic vs international) will be charged merchant fees at different rates. This makes reconciling to your bank statement more difficult. The correct and best way to account for this is to enter the order with separate line items. One line for the gross sale and a second line for the merchant fees. In the image below, you will see the gross amount of the order is $49.00 (same as you see in your store) and the amount deposited into your bank is the net amount of $46.43. In this example, you are recording a sale of $49.00 (going into an income account for a product/service) and a cost of $2.57 (applying to expense account i.e merchant fees). And in your bank account you are depositing $46.43. In this way, you are capturing the reality of this order. It accounts for the expense you paid to the payment processor and it will match both your online store and your bank.
* Note that merchant fees are always out of scope of tax, no matter the region.
2. Invoices vs. Sales Receipts for your Orders
When entering orders in your books, there are two different ways it can be done. Either creating an invoice or a sales receipt. The standard definitions for each:
- Sales receipt: used when your customer pays you on the spot for goods or services. Revenue is recognized immediately.
- Invoice: used when your customer agrees to pay you later. You can set up terms to indicate how long the customer has to pay. If they don’t pay within the specified time limit, their invoice is overdue.
Sales Receipts: I generally prefer these and use them whenever I can because they are more simple and easier to manage. You create a sales receipt and QB records this sale immediately in your income/expense accounts and also at the same time deposits the money into your bank. Refunds are also easy to record because you just create a refund receipt and it records a negative income/expense and takes the money out of your bank account. All in one click.
Invoices: These are more time consuming, requiring more than twice the effort compared to sales receipts. The amount of the invoice won’t hit your bank statement, so to reconcile you need to go back and receive each individual payments and match them to each invoice. Not too bad if you have hundreds of orders, but gets unwieldy when you are doing thousands of orders. For refunds, they don’t easily get entered in one step as a refund receipt. You need to create a credit memo and then go back and find the invoice for that specific order and then apply that credit memo to that invoice. And then if there is money left over, you need to create a refund receipt for the difference. This is especially tricky when dealing with multiple currencies because the exchange rate will change from when they bought the order and refunded it.
In a perfect world, you would get to always choose to enter all your orders as sales receipts. Alas, the world is not perfect. When a customer makes an order, you don’t get the money right away with payment processors like Shopify/Stripe. Stripe will group the orders together and pay you in batches 1-7 days later (sometimes batching them in odd ways if there is a weekend or holiday). So to treat your bank statement as FACT, you cannot enter these orders as sales receipts because the funds from those orders won’t hit your bank account individually, it will come in a lump sum. So the correct way to enter orders paid with Shopify/Stripe (and any other payment processing that batches orders), they need to be entered as invoices.
Here is our recommendations for orders placed with these common payment processors.
Sales receipts: PayPal,
Invoices: Shopify/Stripe, Amazon Pay, Square, Moneris
For entering invoice, there are two ways to do this.
- The multiple invoice way: Create multiple/separate invoices for each order. To record the payout, you receive a payment in the amount from Shopify/Stripe that was deposited to your bank account and apply it to the invoices that match the ones shown in payout. In the example above, you would create four invoices and one credit memo. Then you would create a deposit that receives the payment against each invoice. And finally create a separate credit memo to capture the refund and adjustment.
- The one invoice way: Look up the payment details for each payout and create one invoice that captures all the information on each specific payout. You would add all the orders/refunds/adjustments that relate to each separate payout so that the invoice amount matches the deposit amount. In the example above, you would create one invoice for $143.08 but it would capture the amounts from all four invoices and the refund all together.
In both ways you will end up with accurate and correct ways to account for the orders. The first way is faster and better to capture the reality of each order. The second option is more time consuming and doesn’t offer the quick insights into each order, but you can track that each specific invoice.
3. Managing Order Edits, Exchanges, and Returns
Order Edits
Shopify doesn’t let you change an order that has already been made (i.e., someone wants to upgrade shipping or add an item to their order). So you may need a Shopify app that lets you do this. Our recommendation is “Edit Order by Cleverific“. From a bookkeeping perspective, apps that edit orders will be confusing when you download the order report for your store. The report will show both the original order plus an entire new order for the change. If you are not careful, you will think you got more or less revenue than you actually did for the order if you count it twice.
Exchanges
When a customer receives your product, it may differ from their expectations after only seeing it online. Try to steer customers for an exchange before losing the order to a refund. Depending on your store’s policy, you will either pay for return shipping or have your customer cover this cost. If you cover the cost, you may want to track this to ensure it doesn’t creep up too high. If it does, you may need to change your policy or make changes to your website that might be causing customers confusion when they buy. Manage your customers expectations by being realistic and accurately displaying your product. For the bookkeeping records, your inventory will have to reflect the changes on the order so that the final product the customer receives is properly reflected in your Cost of Goods Sold. This will impact your bottom line and ultimately your profit.
Returns
When customers make an order from you on your store, the money is processed by a payment processor (Stripe, PayPal, Amazon Pay… etc). When a customer makes a return, you will need to track down which payment processor you got the money from. Some do not refund merchant fees or just refund the flat fee part of the merchant fee. So you will need to determine this on a case by case basis. The non-refundable merchant fees will be entered as a net loss on the sale and needs to be reflected on your books for you to claim benefit from the loss.
4. Inventory Management
Inventory management is huge for any retail and this is no different for ecommerce stores. It can literally make or break you. If you buy your product from China, this can get even harder to do because there is typically 30-days to get your order through the manufacturers production and another 30-days on ocean freight, plus more to clear customs and get on the rail/truck on the last mile to your warehouse. If you place your orders when you are flush with cash after Christmas and BFCM, you will be placing your order in January/February but China is closed for business because of Chinese New Year. You want to buy enough product to make efficient use of 20’/40′ containers while also not needing incur a huge warehouse cost to store them too long before they sell.
Many ecommerce platforms have inventory tracking built right in. If you sell across multiple channels; your own store, Amazon, plus you have some on consignment with other wholesalers, managing inventory can get challenging. We recommend creating and maintaining one central place to track all your inventory, no matter where it is. You can do this manually or choose a costlier method that will pull the data automatically. This inventory data will need to get reflected in your books and you can use it to forecast your order/production cycles. You want just enough inventory to meet your sales, but not so much that you are wasting money on warehousing costs. Plus too much inventory doesn’t allow you to pivot in case a product becomes slow moving and you need to convert it back to cash quickly.
5. Wholesale Orders
Working with resellers for bulk wholesale orders is a great avenue for many ecommerce businesses. It will widen your brands recognition through advertising from your resellers. Wholesale orders will either be placed by credit card (i.e., manually on Stripe or PayPal after setting them up as a customer), wire, or as a check that they mail you. You can do payment before shipping, payment on delivery, consignment, or extend terms (i.e., Net 30). Some platforms you can create wholesale orders via a login on your website. This is a great way to keep all the information in one place.
From a bookkeeping perspective, these sales are less straightforward. If you receive a payment via cash, check, wire it will need to be deposited and then matched in your bank. For checks, we recommend always splitting up each check deposit in your bank account so that each deposit will match each check. If five checks get deposited all at once as a lump sum, it will be hard to identify which checks were included in the deposit without having to keep good notes on each deposit. Wholesale orders won’t be fully recognized as revenue in your books until that money gets deposited and matched to your A/R.
6. Multiple Currencies
Unlike traditional retail, the world is your oyster. An ecommerce store can sell anywhere in the world as long as you can ship your product there and your customer will pay the shipping cost. But selling across the globe means you are also working with multiple currencies. This is great to expand your customer base, but obviously comes with some extra bookkeeping steps.
Accepting multiple currencies will instantly increase the complexity of your business. Currencies are forever going up and down relative to your home currency and there will be exchange gains and losses to record on your books. Every time you make a sale in another currency, you will automatically lose money on the foreign exchange (FX) fee cost. This FX fee is usually between 3%-6% depending on who makes the exchange. Once your business is making a lot of sales in another currency and you see how much money you are losing on FX fees, you might decide to open another bank account in another currency. Having the USA as our neighbor, it’s only a matter of time before you start accepting USD on your store. The USA is 10 times the market size of Canada, so it’s hard to ignore if your are looking to increase your sales.
To reconcile the orders in foreign currency, there will be more data and steps required to match these orders to your bank account – because the numbers will not match. You will take the gross sale and merchant fees in that currency and convert it with the current exchange rate for that day. Often times this will lead to discrepancies and you will need to enter an exchange gain or loss for the order so that it matches your store and bank records properly.
7. Logistics and Shipping
Your store will need to make a decision to offer free shipping (and include this in the cost of your product) or charge your customer. You can charge flat rate shipping or use real-time shipping rates via an integration in your ecommerce store with a shipping provider. As ideal as the integration in your ecommerce store seems, keep in mind that they will take a big cut from selling you their own shipping rates and you will not get to negotiate. Plus, because you are not shipping on your own account (i.e., it will be Shopify’s Canada Post account), if there is a problem with a shipment, you will not have any control to resolve it yourself with Canada Post directly because it’s not your account. It’s been my experience that Shopify also doesn’t help. So you will get stuck with an angry customer that you can’t help. Our recommendation is to work out a deal with your preferred carrier (i.e., Canada Post, CanPar, UPS… etc.) and build a relationship with them for better rates. This will usually be the most cost-effective route and allow you to provide better customer service. There is a bit more leg work, but this will outweigh the extra cost of buying it through your store.
From a bookkeeping perspective, the amount you bill your customer will more than likely not match what you are actually charged from the carrier to ship the item. Let’s say you offer flat rate shipping of $15; you may actually pay $10 or $20. Your books need to record this data so that you can validate you are charging the correct shipping amount to be profitable on all your orders as a whole. We recommend these two options to record this:
1. One Central Shipping Expense Account
With this option, you will put both your shipping income (from the customer) and your shipping expenses (from the shipping provider) all in one central account. Because it’s all in one account, you will easily see if you are either making money or losing money on your shipping as a whole.
2. Two Accounts: Shipping Income and Shipping Expenses.
You can use two accounts; the shipping income portion can be separated out from your shipping expenses. To know if you are profitable, or not losing too much money on shipping, you will need to manually compare the difference between the two accounts whenever you want to check.
8. Sales Tax
Oh tax, the government will want to take a slice of all your hard work. It’s important to manage the sales tax (and charge it correctly) so that when tax time comes, you have enough money in your bank account withheld to be able to remit the tax to the authorities. We recommend keeping the tax you collect in a safe place (i.e., savings account) to protect it from being spent on buying more inventory or covering expenses. With accurate bookkeeping, you will know how much of the tax withheld you can spend from the input tax credits (ITC) you have available from the tax paid on your expenses.
On your store, you will either show the customer the taxes that are charged on their order or include it in the price so all your customers pay the same for products – no matter where they live. This may be ideal if you are concerned about your conversion rates and abandoned carts/checkouts.
To charge the correct tax, the key from a bookkeeping perspective, is to charge the tax according to where it is being shipped. This is because the CRA assumes that wherever your product gets shipped is where it’s used, and the value realized. For example, if your business is in Ontario and the order was paid by someone in BC, but it was shipped to Quebec, you will charge Quebec tax on the order. Because your company is in Ontario, you will charge only the GST portion for sales tax instead of QST. If you have any questions how this applies to you, contact us! We will clarify this for your unique situation.
9. Gift Cards
What a great concept, you can literally sell nothing and receive money! However, from a bookkeeping perspective, the money you received gets deposited to your bank and entered as a liability on your books as “deferred revenue”. This because you haven’t actually given your customer anything yet and you still owe them. Think of gift cards as an “IOU”. When your customer does redeem the gift card (either fully or partially), that is when you recognize the unearned revenue on your income statement. This means moving either all the value, or a portion of it, to an income account. If the gift card is never redeemed, which happens a lot, it’s called “breakage” or “forfeiture”. The amounts of unused gift cards will be moved from a liability to your income account. We recommend estimating your stores breakage based on past data. If you do this, then for each gift card sold you can immediately recognize a part of every gift card as income (i.e., 80% to liability account and 20% to income account). This will ensure accuracy of your numbers and better forecasting.
10. QuickBooks Quirks
While QuickBooks is great, there are somethings it can’t do, or could do better. So, there are some common steps we take to make the best of it. Here are some example of what we would recommend for you:
- You can’t filter orders by date for a customer, only look page by page. Some customers may have 50 to 100+ pages.
– Separate your big customers by year. For example, Shopify will be a customer with all your online invoices and payments. Split it up and create these customers: “Shopify 2019, Shopify 2020, Shopify 2021… etc”. To get granular, split Shopify by payment processor, “Shopify-PayPal 2021, Shopify-Stripe 2021… etc”. This will put all your sales receipts in one account (PayPal) and all your sales receipts in the other (Stripe). - You are going to have lots of orders and there will be times you need to search for one rather than look page by page.
– Include the order name and checkout number in the line item, memo, and the message so that each order is uniquely searchable in each of these fields. - Treat your bank statements as the FACTS and everything else much match it.
– Don’t make the bank account in QB match your PayPal account, make your PayPal account match your bank account.