Tech Articles‎ > ‎

Module - Accounting Integration

Cloud Retailer contains a highly flexible system for mapping data to external systems.  This includes e-commerce systems, payroll software, analytics systems, supplier data exchanges (products, pricing, orders, invoices, etc) and very commonly accounting or financial systems. 

Using this tooling we have created a few select turnkey integrations for easy setup.  The integrations are:
  1. Quickbooks Pro 2017 or newer.  Technically, any version of Quickbooks that uses the same style of IIF file should work but only integrations to versions that are newer than 2017 will be supported by our team.
  2. Quickbooks Online
  3. A comma-separated flat-file export that contains General Ledger (GL) data.
The tooling is built to allow integrations to countless other systems via flat file or API though it would require some effort.

An overview of the data we send

This our out of the box integration we send the following data.

Sale transaction data - grouped on a per shift basis
  • Gross sales, again summed on a per shift basis.  We do not send individual sales transactions
  • Cost of the products sold (COGS) and its affects on your total inventory value
  • Payments on a per shift basis (cash/credit card, etc)
  • Tax collected
The per sales transaction detail is not sent and data is summarized as the point of sale system should be the source of this detail.  The accounting system only requires summarized data in order to deliver quality financial statements (Balance Sheet / P&L / Statement of Cash Flows) and on that basis the detail is unnecessary and even harmful to many accounting softwares.  Quickbooks for example, could not handle the mass number of sales transactions most of our customer's processes.  It would make that software unusable.

Inventory data
  • Received inventory
  • Supplier credits
  • Inventory transfers between stores
  • Costing as it relates to sales (COGS)
  • Manual inventory adjustments
  • Adjustments as it relates to physical inventory counts

With our standard integration, all of these data types are grouped by:
  • Product department data (sales, inventory) on a per store location basis
  • Individual payment types by store location
  • All tax is moved by location as a single group

This means you can, for example, assign a GL code sales of products from the department Wine and a different GL code for a department called Beer.  If using our standard integration you could not map sales of product from the category Chardonnay (a subgroup of Wine) to a separate GL code.

Another example of something that is possible but not included in the standard is excluding a product or a small group of products to a separate GL code.

This is possible to do, it just requires a level of configuration and time that is not included in our standard integration.  Mappings beyond our standard are $30 per additional mapping (Query filter) plus $1 per month (for the sake of ongoing support overhead).


Special notes on each integration type

Quickbooks Pro

The integration method for Quickbooks Pro is an "IIF" file.  This means Cloud Retailer will export a file with a format that is required by Quickbooks and you will then import it using Quickbooks tools.  This is a manual process.


Quickbooks Online

We utilize Quickbooks online APIs to migrate data.  This means we can move data over on a semi-automated basis if desired or it can be manual.  All data except for purchase orders (supplier bills in QB) is moved over on a simple GL level basis.

With purchase orders/invoices from vendors, the integration is a bit deeper than a simple GL entry.  Instead, we create a "Bill" in Quickbooks to be paid in addition to the GL entry that increases your inventory value as a result of the receipt of a purchase order.  Only POs that are "Posted" inside Cloud Retailer will move over to Quickbooks.  No partial or unfinalized orders move over until they are completed.

This method of integration allows you to receive a PO and seamlessly post it to QB so that you can execute payment immediately if needed.

All vendors inside Cloud Retailer must have a corresponding vendor inside Quickbooks.  If one does not exist, Cloud Retailer will automatically create it.

GL Files

Cloud Retailer has the ability to generate many types of files, automatically export them and send them to APIs, FTP sites, etc.  What is included in our standard "Accounting integration" package is a CSV file, a schedule to export the data once a week or once a day, and an automated export to an FTP site.

Each scheduled event does count toward your Cloud Retailer API use quota.

The CSV file will have the following format:

GL Code, Description, Debit, Credit
1000, Line Item Description Credit, $0.00, $25.00
2000, Line Item Description Debit, $25.00, $0.00

If you require a different format or an export to a different platform these services would bear an additional cost (upfront and ongoing).

Other important notes

Switching between file formats

If you would like to switch platforms, say from a GL file to Quickbooks online - the upfront implementation costs of the Accounting Integration would need to be paid again.

What is a "shift"?

When we move data it moves as one block of information on a per "shift" basis.  A shift is a batch of transactions that occurs at the cash register.  A shift ends when a cashier ends their day and reconciles their payments.  This is a convenient way for us to group sales, costs, and payment data - including if the cash register was short a few bucks in cash.

 We do not send individual sales transactions or individual product records over to Quickbooks - to do so would overwhelm it based on the volume of business most of our clients do.  As an example, sales will be sent as a single lump sum for a shift with some exceptions.  You can apply a specific GL code to products so they move over as a separate chunk of data.  Gift cards are a great example, their sale needs to be considered separate from the sale of standard products. 

Things currently not integrated
  • Customer integration - if you create a customer in Cloud Retailer it will not create a customer in your accounting system
  • Accounts receivable - if you allow a customer to charge on their account the fact that they did this and the tender types used for payments WILL move over to your accounting system but only as a simple GL entry - it will not utilize any built-in AR functionality. 
  • The integration is currently one way only (data from CR to your accounting program).

Other systems we've integrated to

As a customer you should not expect this work to directly apply to your project but is worth noting that we have done integrations for:
  • Great Plains / GP
  • Navision / NAV
  • Lawson
  • Others as well.

Can I roll my own?

If you want to create your own mappings you can!  The base setup fee is still required plus some premium services for training and then you'll be able to do you own mappings.  You will also be responsible for supporting the enhanced mappings yourself OR utilize our premium services for that level of support.

FAQ

Q: I am receiving a "not in balance" error, what can I do?
A: Typically, this error will occur if you have not reconcilded your register shift, or a GL is mapped incorrectly.
     Try reconciling the shift for the given date range and check to make sure you have all your GL entries mapped correctly.

Q: Can I post a negative purchase order to QuickBooks Online?
A: As of version 2.12.11 of Cloud Retailer, you can post negative purchase orders as a vendor credit. Prior to 2.12.11, you cannot.


Comments