Wiki

    2 Dec, 2021
    Permalink:
    Also available in:EstonianFinnish

    QuickBooks Export File: Explanation

    This is a short description of how back office assembles the QuickBooks export file (IIF file).

    Specification

    The generated file is a collection of General Ledger entries. Each entry contains:

    • A sum of money;
    • Credit account OR debit account;
    • Description

    Erply allows to download the dataset as an IIF file (which is basically a CSV), and to view it on screen in tabular layout (to verify the data and to diagnose errors).

    In the file, debits are indicated by a positive sum, and creditings are indicated with a negative sum.

    In accordance with double-entry bookkeeping, the sum of all accumulated debit entries and the sum of all accumulated credit entries must be equal. In other words, each sum of money must appear in the ledger twice; once on the Debit side, and once on the Credit side.

    The General Ledger entries are generated by date and location.

    Configuration Settings

    We recommend to apply configuration parameter quickbooks_include_same_day_invoices_and_payments = 1.

    If this parameter is not applied, Erply additionally looks which invoices were created and paid within the same day, and excludes those from the Accounts Receivable debit and credit lines. This, however, makes the numbers difficult to verify.

    File Contents

    For each date and location, the entries which may appear are as follows:

    Account Description

     

    Type

     

    How Erply calculates the data

     

    Undeposited FundsD

    Total sum of payments for the given date, grouped by payment type. 

    (Cards are additionally grouped by card type.)

    Accounts Receivable - Trade (Payments applied to Invoice-Waybills)CTotal sum of payments for the given date.
    Cash Over and ShortD/C

    If cashier has counted a $10 overage at the end of the day, the QuickBooks export file shows day earnings (Undeposited Funds) as $10 higher, and adds a corresponding “Cash Over and Short” credit entry of $10.

    With a shortage, the approach is the other way around: day earnings are shown smaller, and a corresponding “Cash Over and Short” debit entry is added.

    D Undeposited Funds = C Accounts Receivable

    Accounts Receivable - Trade (Invoice-Waybills Total)DTotal sum of all confirmed, revenue-generating sales documents (net total + tax)
    Sales and Use Tax PayableCTotal tax, calculated over all confirmed, revenue-generating sales documents.
    SalesC

    Net total, calculated over all confirmed, revenue-generating sales documents.

    Erply lists these net sales totals separately by main product group (the top-level group where the product belongs). Do not know whether this is needed or not.

    D Accounts Receivable = C Sales Tax Payable + C Sales

    Cost of Goods SoldD

    Total COGS (Cost of Goods Sold), calculated over all confirmed sales documents that affect inventory. The same value appears on both lines.

    Erply lists totals separately by main product group (the top-level group where the product belongs). Do not know whether this is needed or not.

    InventoryC

    C Inventory = D Cost of Goods Sold

    See Also

    https://learn-api.erply.com/concepts/sales-and-revenue

    Sample

    !TRNS,TRNSID,TRNSTYPE,DATE,ACCNT,AMOUNT,DOCNUM,MEMO
    !SPL,SPLID,TRNSTYPE,DATE,ACCNT,MEMO,AMOUNT
    !ENDTRNS
    TRNS,0003030002,GENERAL JOURNAL,11/13/19,3900,0.00,0003030002,Day Transactions
    SPL,0003030002,GENERAL JOURNAL,11/13/19,,Invoice-Waybills Total,106.67
    SPL,0003030002,GENERAL JOURNAL,11/13/19,,Tax Collected - Sales Tax,-11.80
    SPL,0003030002,GENERAL JOURNAL,11/13/19,,COGS - Gloves and Mitts,-23.50
    SPL,0003030002,GENERAL JOURNAL,11/13/19,,COGS - Gloves and Mitts,23.50
    SPL,0003030002,GENERAL JOURNAL,11/13/19,,Net sales - Gloves and Mitts,-49.99
    SPL,0003030002,GENERAL JOURNAL,11/13/19,,COGS - New product group,0.00
    SPL,0003030002,GENERAL JOURNAL,11/13/19,,COGS - New product group,0.00
    SPL,0003030002,GENERAL JOURNAL,11/13/19,,Net sales - New product group,-35.88
    SPL,0003030002,GENERAL JOURNAL,11/13/19,,COGS - Cafe,0.00
    SPL,0003030002,GENERAL JOURNAL,11/13/19,,COGS - Cafe,0.00
    SPL,0003030002,GENERAL JOURNAL,11/13/19,,Net sales - Cafe,-9.00
    ENDTRNS