Use Excel to Extract Accounting Data Visual South by liaoqinmei

VIEWS: 15 PAGES: 19

									Use Excel to Extract
 Accounting Data
                Alan Chorney
        Professional Services
            Visual South, Inc.

                                 Visualize Success 2011
    What are Tables
•    All transactions, both manufacturing and financial, are saved in a
     table or tables
      – Tables consist of fields
          • Fields consist of the information the user enters into each field of a
            VISUAL window




                                                                         Visualize Success 2012
    How Can Tables be Used
•   Tables can assist in researching month end out of balance issues
      – Some examples to follow
•   Tables can be used to validate completeness of setup
    information
      – Extract all parts without a Product Code
•   Tables can expedite the extraction of data from VISUAL
      – Extract all customer orders with part MMT-1996-DM
         • Sure there are canned reports to get you this information (CUSTOMER
           SALES JOURNAL), but you cannot filter for both part id and customer id –
           selections are either/or
•   Extract only the fields you want unlike printing to a CSV file




                                                                    Visualize Success 2012
    Data Extraction
•    Data can be extracted using:
      – Microsoft Access
          • Extracts data while in the live table – could be dangerous
              – could change data which may lead to data corruption issues
      – Crystal Report Writer
          • Same here
      – Microsoft Excel – tool of choice in this workshop
          • Extracts data from the live tables, but not working in the table
              – Cannot corrupt data




                                                                         Visualize Success 2012
    How to Extract Data using Excel
•    Level of Technical skill needed – Very Limited
      – Take my word for that
•    Knowledge of Tables and Fields in tables
      – General introduction to follow
•    An ODBC on your computer – WHAT!
      – ODBC = Open Data Base Connection
          • This allows access to the tables




                                                      Visualize Success 2012
    Receivable & Cash Application
•    There are 17 tables related to Receivables and Cash
•    There are 6 basic tables a user might use – these are
      – Receivable table
           • Header portion of the RECEIVABLE, INVOICE ENTRY window
      – Receivable_Line table
           • The various lines that make up the invoice detail
      – Cash_Receipt table
           • Header portion of the RECEIVABLE, CASH APPLICATION window
      – Cash_Receipt_Line table
           • The lines containing the unpaid invoices
      – Cash_Receipt_Dist table & Receivable_Dist table (2 tables)
           • The debit & credits associated to transaction
           • Posting status of transaction




                                                                 Visualize Success 2012
Receivable Table




                   Visualize Success 2012
Cash Receipt Table




                     Visualize Success 2012
    Payable & Cash Disbursement
•    There are 12 tables related to Payables
•    There are 6 basic tables a user might use – these are
      – Payable Table - header portion of the PAYABLE, INVOICE
        ENTRY window
      – Payable_Line Table - the various lines that make up the
        voucher detail
      – Cash_Disbursement Table - header portion of the PAYABLE,
        CASH DISBURSEMENT window
      – Cash_Disburse_Line Table - the lines containing the
        vouchers to be paid
      – Cash_Disburse_Dist table & Payable_Dist Table (2 tables)
          • The debit & credits associated to transaction
          • Posting status of transaction



                                                            Visualize Success 2012
Payable Table




                Visualize Success 2012
Cash Disbursement Table




                          Visualize Success 2012
    General Journal
•    There are 5 tables related to General Journal Entry
•    There are 3 basic tables a user might use – these are:
      – GJ Table
          • Header portion of the RECEIVABLE, INVOICE ENTRY window
      – GJ_Line Table
          • The various lines that make up the journal detail
      – GJ_Dist table
          • The debit & credits associated to transaction
          • Posting status of transaction




                                                                Visualize Success 2012
General Journal Entries


    b




                          Visualize Success 2012
Accounting Window
• There are 3 tables related to Account Table
• There are 2 basic tables a user might use – these are:
  − Account Table
       Header portion of the Accounting window
  − Account_Balance Table
       The monthly Balance for the account




                                                           Visualize Success 2012
Accounting Window




                    Visualize Success 2012
    Journal Batches
•    All transactions, manufacturing or financial, are posted to the
     ledger thru journal batches
       – There are 13 batch types
•    There are 2 tables associated to Journals
       – Journal_Batch table
          • Header
      – Journal table
          • The account distributions from the 13 batch types




                                                                Visualize Success 2012
    Batch Information
•    Under LEDGER, GENERAL JOURNAL ENTRY, EDIT, VIEW
     BATCH INFORMATION




                                              Visualize Success 2012
    How Does It Work
•    Will now demonstrate how to research the following:
      – Causes for PO Accrual Report to be out of balance
          • Vouchers posted in current period with receivers in subsequent period
          • Payable lines with PO accrual account, but not associated to a receiver
      – Null Journal Batches
          • Source window shows transaction posted, but posting did not reach ledger
      – Manual checks without check references
          • Removes voucher from payable aging, but ledger not debited
      – Deposits with a deposit date subsequent to its posting date
          • Effects cashbook and bank reconciliation




                                                                      Visualize Success 2012
Thank You
• alan.chorney@visualsouth.com




                                 Visualize Success 2012

								
To top