The Solution Doesn't Have to Be Complicated
By Steve M., Author of Moonlight CEO
Once our online store became successful, we searched for a more sophisticated shopping cart that would work better with QuickBooks and provide customers more of a big-company feel than our simple PayPal Standard hosted cart. At first, finding a shopping cart with better QuickBooks integration seemed like a priority because importing and reconciling individual shopping cart transactions had become such a chore.
Unfortunately, none of the QuickBooks integrated shopping carts or add-on software tools we tested seemed right for our business. We always found one or more serious weaknesses when it came to back-end features, customer experience or cost.
I related my frustration to our CPA, who suggested a much simpler way to bring our online sales data into QuickBooks. His approach allowed us to choose a new shopping cart based on its features and support, without much regard to QuickBooks integration.
We chose a shopping cart called Ecommerce Templates - which we still use and like – along with the PayPal Payments Pro payment gateway that works well with our cart. However regardless of what shopping cart and payment gateway you use, these suggestions can still apply.
Tips from Our CPA
1. If you're an online store, don't import individual sales transactions reported by your shopping cart or payment gateway into QuickBooks. Instead paste into QuickBooks the few top-line monthly totals reported by your payment gateway, for categories like payments received, refunds sent, and transaction fees. Paste these entries, dated the last day of each month, into the payment gateway account you've set up in QuickBooks.
2. Create an Excel spreadsheet with a running log of completed (sold) transaction details that you download from your shopping cart. On separate sheets of the workbook, use the Excel SUMIFS function to calculate any totals you need, for whatever time period you choose. We use this approach, for example, to calculate sales taxes collected each month that we enter in QuickBooks and remit to our state.
This process avoids bogging down QuickBooks with huge numbers of online sales transactions and customer names. It also helps assure that the revenue reported in QuickBooks matches the 1099K form that the payment gateway must usually send the IRS.
We've found that our transaction log spreadsheet, along with Excel's pivot table feature, also makes it easy to create reports that can answer many business questions. For example, we might want to know "what percentage of customers chose next-day delivery last December" or "how many hammers have I sold to customers in Hawaii?" An example pivot table report from data in our transaction log is shown below.
Creating a Business Report with an Excel Pivot Table
Managing Inventory
If you stock and sell physical goods (as opposed to selling electronic downloads or items drop-shipped from manufacturers to your customers), managing inventory could be critical to your cash flow. We've provided more details about how we manage our inventory, and how we enter the data needed to calculate Cost of Goods Sold in QuickBooks, in an article on our Blog about Managing Inventory with Excel.
Photo Credit Courtesy of MoonlightCEO.com
