Summary

Learn what to do before submitting a data formatting request to MindBridge.

Perform a quick integrity check

The engagement team should always check the client data before submitting a data formatting request.

Verify the integrity of the data so your data formatting request can be turned around as quickly as possible and to minimize the chances of error.

1. Look over the data for a basic sanity check.
2. Verify whether the data balances (i.e., Debit totals=Credit totals).
   • If the ledger does not balance and it should, indicate this in the ticket.
   • If the ledger does not balance and this is deemed acceptable (for example, if it falls below the materiality threshold), indicate this in the ticket and provide as many details as possible.
     • We do a basic sanity check after formatting to make sure the data balances. If the file does not balance, we will spend time investigating why that may be.
3. Determine whether the general ledger has some way of identifying the entries that belong to each transaction (transaction ID).

4. Ensure the general ledger contains all of the required fields (account number, amount/debit and credit, dates).

5. Ensure the general ledger can be formatted (i.e., it is a supported file type, not a pdf.).
   • Columns must be distinguishable in a systematic way (i.e., there is a clear way to tell where in the text a value begins or ends — it should not have values randomly spilling over into the next columns).
6. Account numbers are consistent among the general ledger, trial balance, and chart of accounts files.
   • If account numbers in the general ledger look like 01-23456-78-9 while the ones in the trial balance or chart of accounts are 1-23456, MindBridge will read them literally and treat them as different accounts.
   • In some cases, you can use our split columns, join columns, and/or duplicate columns features to make the account numbers consistent (see pro tips below).
   • If you need Customer Support to standardize the account numbers (including mapping account numbers from account descriptions of general ledgers that have only account descriptions and no account numbers), let us know what format you want to use and how to convert into that format if applicable.
   • Note: If your client's system does not output account numbers, you can simply map the account description to Account ID during column mapping. Account descriptions would still need to be consistent across the general ledger, trial balance, and chart of accounts.
7. The engagement team should check that they have the correct opening and closing trial balance files, if including them.
   • The opening trial balance should have all income statement accounts zeroed out and posted to retained earnings. This process will ensure that the completeness check report is accurate.
   • The closing trial balance should be the final balance before closing out income statement accounts to retained earning.
8. The general ledger should not contain opening or closing journal entries (i.e., do not include any journal entries carrying forward amounts or closing income statement accounts to retained earnings).
   
   

Pro tips

• Ask your client for the general ledger and trial balance data as soon as possible. This will reduce the chances of your data formatting request conflicting with other users' requests.

  • If we are backlogged with too many data formatting requests, it will take us longer to process your request.

  • Receiving the data earlier will also give you more time to run the sanity checks and go back to your client for better data if needed.

• Determine which ERP your client uses and select that ERP in your engagement settings.

• Tip: If your ERP does not appear in the menu, type it into the field and press enter or return on your keyboard to add it.

• When an engagement team needs to combine multiple files:

  • If additional formatting is needed (for example, ungrouping), send all the relevant files together, once you are reasonably sure they are the version you want to import. Sending multiple revisions will slow down the process.

  • Each file must have the same columns in the same order. Differences will dramatically slow down the process, as the data team will attempt to match up columns manually, sometimes making best guesses. If possible, indicate which columns in one dataset should match up with those of the others, especially if there are substantial differences (for example, if the client changed ERP systems in the middle of the year).

  • Use the Merge tool to combine multiple files if they do not need any other formatting!

• If an engagement has large files and/or files with many columns, indicate which columns you would want keep. If you know which columns need to be mapped (especially the required ones like account number, amount, date, transaction ID), indicate this information in the ticket.

• Try to get an export with headers. MindBridge expects the first row of each general ledger and trial balance file to be a header row with column names.

• Extract the Account ID/Account Description from one column and place them into their own columns, extract sub-accounts from longer account numbers, join different sub-account numbers in different columns into one account number, or extract unique transactions ID/identifier with the join, split, and duplicate column tools.

  • Learn how to ungroup and split columns in Excel: Data Formatting Guide: Ungroup and split account numbers and descriptions.

  • If you are unable to extract all account numbers with the above measures, please indicate in your data formatting request which column(s) contain the account number data and what the account number should look like (for example, "extract the 3rd through 6th digits of the account number, add a - character, add the last two characters in the next column," etc...)

• If the general ledger doesn’t balance, the best approach is to get totals by account and comparing with subtotals in the general ledger (if present, and they often are), or getting total by month/quarter if the client sent the files by month/quarter.

  • See Data Formatting Guide: Ungrouping and Checking Imbalance for step by step instructions on how to do this
  • For more information, see the Data Formatting Guide on how to format your own files in Excel
    
    

Common issues

• General ledgers with wildly out of range dates in years (for example, dates in the 1900s or 2200s) are likely typos or missing dates that got converted in some system.
• Debit/Credit/Amount column not formatted as a number or with incorrect signs
  • For example: With parentheses, <>s, negative signs at the end, currency symbols, non-North American formats, instead of decimal point and . as thousands separator.
  • Amounts need to be default positive balance if there are separate debit and credit columns, but debits must be positive and credits negative if there is only one Amount column. See this guide for instructions on how to flip signs for a range of accounts in Excel: Data Formatting Guide: Flipping signs for a range of accounts or based on condition(s).
• Date column not formatted as date
  • In some instances the format changes mid-file (for example, most dates are in YYYY-MM-DD format but a section has Excel numeric dates).
  • Some of these date issues, as well as issues with the debit/credit/amount discussed above, can indicate shifted columns or broken lines in some parts of the file.
    
    

Anything else on your mind? Chat with us or submit a request for further assistance.

Related articles

• Data Formatting Guide: Data Requirements
• Data Formatting Guide to scenarios that you might be able to do more quickly yourself
• The difference between a file that can be formatted and a file that can't
• Understanding Transaction ID and Data Integrity