1 / 46

Excel-eration Webinar

Learn data formatting and validation techniques for real-time analysis in this webinar. Discover how to get data from clients and use additional tools. Join our 1-on-1 session and fill out our survey afterward!

willarda
Download Presentation

Excel-eration Webinar

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel-eration Webinar

  2. Learning Outcomes Agenda • Data Preparation • What is “application agnostic” • Data formatting & validation techniques • Real-time data formatting • Things to consider when getting data from your clients • Additional tools available • 1-on-1 session Make sure to fill out our survey that will be distributed after the webinar!

  3. Introductions Andrei Ghita Customer Success Associate Alfred Markut Jr. Data Solutions Manager

  4. This call will be recorded

  5. Data Preparation Once data is collected, the process of analysis begins Data has to be validated, and translated in an appropriate form This process is known as Data Preparation Data Collection

  6. Steps in Data Preparation Clean the Data Set Validate Data Analyze the Data READY Validity exists when the data actually measures what it’s designed to measure If required, build a tabular/graphical representation of data One of the most serious concerns in data preparation are errors, inconsistent data, and missing data values Invalid data may potentially conclude misleading results When multiple data sets are involved, the review step is important If required, transform the data set into rectangular form (ungrouped)

  7. Data Engine is not specific to one Financial Management System (ie., ERP) • Ledger can come from any number of systems on the market • As long as required/recommended columns exist, and is in the appropriate format (ie., ungrouped), the file can be loaded into MindBridge • Don’t focus on just getting the file from one specific ERP What is application agnostic?

  8. Data Overview

  9. General Ledger - Required & Recommended Columns Checklist

  10. Types of Formats Grouped Format Ungrouped Format Compartmentalized into sections, typically by Account Number Every row represents every column requirement

  11. Grouped Format

  12. Ungrouped Data File

  13. Questions?

  14. Formatting Steps Filter on Date & Copy data into new sheet Save & get ready to upload into MindBridge Understand the data and determine where required & recommended columns reside Create new columns for final resting place for data Drag over data (or write an Excel formula to get data) Pivot off of anchor point 1 2 3 4 5 6

  15. General Ledger Analysis When exporting client data from an ERP, it generally comes in a ‘grouped’ format. In this case, the transactions are all grouped by their respective account numbers. This will require additional formatting.

  16. General Ledger Formatting Tips Find your anchor column and filter by it: These columns generally have account number and account name information within it.

  17. General Ledger Formatting Tips In this case, column A has the necessary information that we need to filter by. Filter tips: Get rid of blanks, any totals, and any other information that isn’t an account number & account description

  18. General Ledger Formatting Tips You will now see a file that is starting to come together! But, we now have to break up the account numbers and account descriptions into two separate columns.

  19. General Ledger Formatting Tips First, insert a column on column B Next, you’ll have to break up the account numbers and account descriptions into separate columns. This can be done with the Text to Columns feature in the Data tab.

  20. General Ledger Formatting Tips Step 1: Press the Text to Columns button located in the Data tab. Step 2: Press the Delimited radio button and hit next.

  21. General Ledger Formatting Tips Step 3: Check the ‘Other’ box, and type “-” in the box beside it. Step 4: Press the Finish button!

  22. General Ledger Formatting Tips Next, clear the filter from column A

  23. General Ledger Formatting Tips This is what you should now see! Next, we’ll have to fill the blank cells in columns A and B.

  24. the number of characters we extract The cell reference =LEFT(text, [num chars])

  25. General Ledger Formatting Tips =LEFT(A3, FIND(“-”, A3)-1) Note: The reason we’re adding -1 is to ignore the “-” that the function picks up

  26. the number of characters we extract The cell reference the starting point for where the formula will ‘look’ =MID(text, [start num], [num chars])

  27. General Ledger Formatting Tips =MID(A3, FIND(“-”, A3, 1)+1, 1000) Note: The reason we’re adding +1 is to ignore the “-” that the function picks up

  28. General Ledger Formatting Tips =TRIM(MID(A3, FIND(“-”, A3, 1)+1, 1000)) Note: The reason we’re adding +1 is to ignore the “-” that the function picks up

  29. Always copy & paste formulas as values.

  30. Questions?

  31. General Ledger Formatting Tips To do this, click the Home tab, then press on the ‘Find & Select button, then hit ‘Go To Special…”

  32. General Ledger Formatting Tips Next, press the Blanks radio button and hit OK

  33. General Ledger Formatting Tips To fill down all of the blank cells, type “A1” in cell A2 and press “Ctrl + Enter”

  34. General Ledger Formatting Tips Next, we only want to show line items which have an associated date. To do this, go to the filter on column C, and un-check the “Posted Date” box, then hit OK.

  35. General Ledger Formatting Tips Press the highlighted box to select all of the data, open a new sheet in the file, and paste as values.

  36. General Ledger Formatting Tips

  37. General Ledger Formatting Tips We’ll now have to get the Date column in the proper format using the Text to Column function. Step 1: Make sure the Delimited radio button is toggled on and press Next.

  38. General Ledger Formatting Tips Step 2: Make sure the ‘Other’ button isn’t checkmarked and press Next.

  39. General Ledger Formatting Tips Step 3: Press the Date radio button, and from the drop-down menu, select ‘DMY’ Step 4: Press Finish!

  40. General Ledger Formatting Tips Lastly, for MindBridge to ingest the data correctly, the Debit and Credit columns need to be absolute values. To do this, insert a column beside the original credit column, type the formula “=ABS(H2)” into cell I2, and drop the formula down the column.

  41. General Ledger Formatting Tips Finally, copy the content in column I and paste it back as values to remove the formulas from the cell, and delete column H (the original Credit column with negative values). Your file is now ready to be ingested into the tool!

  42. LIVE DEMO

  43. Additional Tools • Jupyter Notebooks • SQL Server • Regular Expressions • VBA Scripts • Patent Pending Internal Processes (eg., Microgrouper)

  44. Getting Data from Clients Four general rules: • Get General Ledger file into either: • .xlsx, .xls, or .csv format • When in doubt, ask for as much data as possible • Additional fields can be imported into Ai Auditor • Provide Trial Balance file with Opening & Closing Balance Columns • Provide Account Mapping File

  45. Please fill out the attached survey and register for a personalized data solution from our team! Next Steps

  46. Thanks for watching!

More Related