Questions? We have answers.

Best practices: Before submitting a data formatting request

Article author
Kate H
  • Updated

Summary

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


Quick Integrity check

The engagement team should always check the client data before raising a data formatting request. Make sure to verify basic integrity of the data to get your data formatting request turned around as quickly as possible and minimize the chance of error or rework.

  • Make sure your general ledger balances (Total of Debits=Total of Credits)

    • Sometimes the engagement team already knows that the general ledger is out of balance and this can be ignored taking into consideration materiality threshold. In this case, the engagement team should let our Data Specialist know about it and provide details on how much is the general ledger out by so that the data team doesn’t have to spend time investigating and writing all the observations.
  • The general ledger has some way of identifying which entries go in one transaction (transaction ID)
  • The general ledger has all required fields (account number, amount/debit/credit, dates)

  • The general ledger can be formatted and is:

  • The engagement team should double check that they have the correct opening and closing TBs, if including TBs.

    • The opening TB 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 TB should be the final TB before closing out income statement accounts to retained earnings
    • 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

  • If an engagement team has multiple files that need to be combined: 

    • Try to send them when you have them all and after you’re reasonably sure they are the final version you want to use. 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 would need to match up columns manually, often making best guesses. If possible, please indicate which columns in one set of files should match up with those of the others, especially if there are substantial differences, such as when the client changed ERP systems in the middle of the year.

  • If an engagement has big files and/or files with a lot of columns - to maximize the chances of successful ingestion as well as to save time overall, please indicate which columns you would like to keep. Also, if the engagement team already knows which columns they need to map (especially the required ones like account number, amount, date, transaction ID), please indicate that as well in the support ticket.
  • Try to get an export with headers. MindBridge expects the first row of each GL and TB file to be a header row with column names. This will also help us decipher files that look cryptic to someone unfamiliar with your client.
  • Use split/join functionality directly in MindBridge to extract Account ID/Account Description into their own columns when they are in one column, 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

  • 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.


Common mistakes

  • General ledgers with wildly out of range dates in years like 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?
Log in to MindBridge to chat with us, email support@mindbridge.ai, or reach out to your assigned Customer Success Manager.


Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.