There are a lot of customized or industry-specific operating/administrative systems that are really great at what they do to help the business manage the day to day operations, but they are usually limited in the accounting (“general ledger”) side. So the idea to “integrate” that system with QuickBooks comes up often.
In some cases, these systems have built-in integration and/or data exporting tools for QuickBooks, and if that is the case, we could help you determine if the integration works well and how would the QuickBooks-based workflows need to be adjusted to have a successful integration experience.
But, there are cases where a custom integration solution is required, and we get hired to intermediate the project between the business owners/managers and the programmers that will built an API/SDK tool to synchronize data between the system and QuickBooks. So these are the things we think about for these type of projects:
1. QuickBooks Online (API) vs. QuickBooks Desktop (SDK)
Generally QuickBooks desktop will be better for detailed reports, limited access permissions to QB users, and more powerful built-in features for accounting. It will require an SDK-based middleware app to be installed in the server and login to QB to push data.
2. We break down the high level strategies into 5 areas:
- What? We need to figure out what data inside your current system do you need to capture
- How? We need to figure out how is that data going to be imported: Transaction by transaction, itemized summary, summarized journal
- Why? We need to figure out how this data will be used in QB for reporting purposes and/or to avoid duplicate data entry
- When? How often will the data be synced and/or for which time periods (daily, weekly, monthly, etc.)
- Who? Who will have access to the integration tool to push the buttons (initiate, pause, resume, etc…) and verify that the data that is being synced is complete/accurate
3. Going a bit deeper into the What/How’s we need to think hard about what data we really need to see inside QuickBooks, that will change how we sync:
- Transaction level: every transaction comes as one transaction with itemized product information and customer information based on transaction date. This will give you potentially 6 dimensions: Date, Customer Name (and Customer Type <- Managed in QuickBooks), Class, Location, Product Purchased/Sold, and Account (driven by Product inside QuickBooks)
- Itemized Summary: one daily (or weekly) transaction comes in without any customer details but all products sold are itemized. This will give you potentially 4 dimensions: Date, Class, Product Purchased/Sold, and Account (driven by Product)
- Summarized Journal: is typically a daily, weekly, monthly journal entry that is a Journal Entry designed to match Chart of Accounts, typically a few (3-8) Income Accounts and the same for Direct COGS accounts in a journal entry broken down by class to have a secondary dimension. This will give you potentially 3 dimensions: Date, Class, and Account (based on product/account mapping done within the middleware)
4. Will the synced data be reconcilable with the banks?
Advanced QuickBooks ProAdvisor
hector@garciacpa.com
