Solving a cupcake reconciliation riddle
As bookkeepers and accountants, we’re often asked to solve some complex riddles. While our clients just want to see a single number at the end of the day, we need to dig deep and find out where that number came from and ensure that it’s accurate. If our clients are going to have their cake and eat it too, we’re the ones who need to figure out the recipe. This can be especially tricky when it comes to e-commerce clients.
One of the biggest challenges with e-commerce bookkeeping comes from multi-channel selling and the use of multiple payment platforms. Often, these payments hit the bank in net form and can be tough to match with the gross amounts provided by the e-commerce software. Let me give you an example of what I mean.
A client came to us recently, looking for help. She was a single mother running a cupcake business online and in desperate need of a cleanup. Her last accountant had charged her, but left her books in disarray, so we wanted to help her get everything back in order. It turned out to be a more daunting task than we expected.
Maintaining your profit margins can be life or death for a small e-commerce shop, and this is especially true with the high cost of goods sold for a baking business. Unfortunately, tracking that margin is next to impossible if the books are half baked. Timely, accurate books require regular reconciliations, which can be complex for e-commerce.
The recipe for success
Step 1: Gather the raw ingredients
Our client was using Shopify, and accepting payments through Shopify and PayPal, as well as accepting PayPal payments for sales made outside of Shopify. This left her books looking like a messy, flour-coated kitchen because each platform had its own fees and these weren’t reflected in the bank feeds.
You can’t make cupcakes without breaking a few eggs, so first, we needed to break everything down to the raw ingredients. If you can’t factor your fees into the equation, it’s easy to think you’re making a good profit margin when your actual margin is much smaller.
Finding just the right recipe for success was a team effort, but we got by with a little help from our friends. We consulted with four other bookkeepers and CPAs to create a good workflow to clean up the books and set up the client for success moving forward.
Step 2: Mix well
Many e-commerce platforms, including Shopify, can integrate with QuickBooks® Online, but may not give you the information you need. In many cases, the problem is that they give you too much information. While you can connect Shopify directly to QuickBooks Online, what you’ll get is a huge data dump, including each separate sale and far more detail than you actually need in your books. This can slow you down, clutter you up, and be just plain overwhelming.
In this case, our client was wisely using Bookkeep to integrate with QuickBooks Online. Bookkeep brings transactions over as a summary, rather than individual transactions, which is a huge timesaver. To clean up this mess, though, we needed granular detail, too.
We reached out to the incredible support team at Bookkeep to help us get everything we needed and nothing we didn’t.
Here’s how we got everything reconciled:
- We were able to separate Shopify and PayPal customers in the bank feed.
- We ran daily P&L reports, and reconciled them to transaction reports from PayPal and Bookkeep.
- We identified the payment processing fees that had not yet been recorded and entered them as a journal entry.
The icing on the cake
We were able to clean up everything for our client and build a workflow to make future clients easy. Now, the client has the numbers she needs to make smart decisions and run a profitable cupcake business. But that’s not the only good that came from this process.
By working with some amazing bookkeepers and accountants, and connecting with the support team at Bookkeep, we’ve continued to build a network of professionals mutually helping each other succeed.
The virtual accounting and bookkeeping community never ceases to amaze me. While we’re all in the same industry and are technically competitors, we also lift each other up. All boats really do rise with the tide when we come together to problem solve.
Are you dealing with a complex bookkeeping problem right now? I’d encourage you to reach out to the community to find a solution and pay it forward with your own knowledge and expertise.