Queries and Reports—Key Exercises

IMPORTANT
It is extremely important that you set up and work within your own company and with your own customer to maintain your data integrity. If you work with any other objects, your exercises will not function as intended and the data of other students might be affected. Predictable exercise results require that your data must be isolated in your own company.

Required Data


Enter Customer Invoices

Purpose: The purpose of this exercise is to show you how to enter the customer invoices required for running the exercise below.

Windows:
Manual Customer Invoice

  1. Enter a number of customer invoices.

General exercise for Manual Customer Invoice


Enter Customer Payment

Purpose: The purpose of this exercise is to show you how to enter the customer payments required for running the exercise below..

Windows:
Customer Payment

  1. Enter a payment on some of the invoices/installments that you entered above.
  2. Enter another payment on the remaining invoices/installments.

General exercise for Manual Customer Payment
General exercise for Customer Check Payment
General exercise for Customer Bill of Exchange Payment

Note: To be able to view all queries and reports you must have performed the relevant payment options.

Main Exercise


Queries

Customer Payments

Purpose: This query is used to look at customer payments. This information includes the payment series ID, payment ID, currency, payment type, payment date, and paid amount. Keep in mind that you can only view information in query windows. You cannot enter or change this information.

Windows:
Customer Payments Analysis

  1. Open the Customer Payments Analysis window, and populate it.
  2. Select a line, and right-click. Try the various options on the right mouse button menu to get more information.


Customer Payment Details

Purpose: This query is used to view customer payment details. The selection criteria enables you to search using variables such as payment series, payment ID, currency, payment type and date, and paid amount.

Windows:
Customer Payments Analysis

  1. Open the Customer Payment Analysis window, and populate it.
  2. Select a line, and right-click. Try the various options on the right mouse button menu to get more information.

Customer Payment Matching

Purpose: This query is used to view all ledger items for a particular customer.  Within the customer detail view, an additional query can be performed to query for a particular payment, matching ID, invoice, amount, account, date, voucher, or currency.  The matching ID will enable you to see all related ledger items such as a check payment, payments on account created by the check, and invoices paid by the check or matched to the payment on account.

Windows:
Customer Payment Matching Analysis

  1. Open the Customer Payment Matching Analysis window and query for your customer.
  2. View the payment transactions.
  3. If there are a lot of transactions, right-click within the table area and select query.  Enter query data for a particular query parameter such as the payment date, amount, or matching ID.  Click OK.
  4. View the resulting transactions.  
  5. Select a line and right-click.  Try the various options on the right mouse button menu to get more detailed information.


Cash Account Balance

Purpose: This query is used to get a general idea of the cash flows on the company's cash accounts. The balance per cash account and date are displayed.

Windows:
Cash Account Balance Analysis

  1. Open the Cash Account Balance Analysis window, and populate it.
  2. Select a line, and right-click. Try the various options on the right mouse button menu to get more information.


Cash Transactions

Purpose: This query is used to display all cash transactions—from customers as well as to suppliers.

Windows:
Cash Transactions Analysis

  1. Open the Cash Transactions Analysis window, and populate it.
  2. Select a line, and right-click. Try the various options on the right mouse button menu to get more information.


Parked Customer Payments

Purpose: This query shows information on parked customer payments. A parked payment is a special form of payment on account, entered without links to a specific customer. Parked payments might be entered, for example, when payment information is missing.

Windows:
Parked Customer Payments Analysis

  1. Open the Parked Customer Payments Analysis window, and populate it.
  2. Select a line, and right-click. Try the various options on the right mouse button menu to get more information.


Netting

Purpose: Use this query to view netting between accounts payable and accounts receivable for companies that are both customer and supplier.

Windows:
Nettings

  1. Open the Nettings window, and populate it.
  2. Select a line, and right-click. Try the various options on the right mouse button menu to get more information.


Netting Details

Purpose: Use this query to view detailed information about a specified netting. The window contains information about voucher number, registration date, payment date, etc.

Windows:
Netting Details

  1. Open the Netting Details window, and populate it.
  2. Select a line, and right-click. Try the various options on the right mouse button menu to get more information. Use the Voucher Type list to select other nettings.


Customer Checks and Invoices per Check

Purpose: This activity provides information about all check payments received within the company.

Windows:
Customer Check

  1. Open the Customer Check window, and populate it.
  2. Select a line, and right-click for more options.
  3. Select Match/View Invoices to see the invoices/installments that were paid by check.


Checks per Payment

Purpose: This activity provides information about which checks were included in a specified payment, including check number, amount, payment date, and status of the check (e.g., Printed). 

Windows:
Customer Payments Analysis

  1. Open the Customer Payments Analysis window, and populate it.
  2. Select a line, and right-click for more options.
  3. Select Checks per Payment to see the checks that were included in the payment.
  4. Select one check line, and right-click for more options.
  5. Select Invoices per Check to see the invoices/installments that were paid by check.


Customer Bills of Exchange and Invoices per Bill

Purpose: This activity provides information about all bill-of-exchange payments made within the company.

Windows:
Customer Bills of Exchange

  1. Open the Customer Bills of Exchange window, and populate it.
  2. Select one line, and right-click for more options.
  3. Select Match/View Invoices to see the invoices/installments that were paid by bill.


Reports

Customer Ledger Open Item

Purpose: This report can be used for internal checking against the general ledger. The report displays all open customer items, i.e., invoices/installments and customer payments on account/in advance for a specific period or date. 

Windows:
Customer Ledger Open Items

  1. Open the Customer Ledger Open Items window.
  2. Select your customer in the From Customer No and To Customer No fields using the List of Values.
  3. Click Preview to view the report.

Customer Invoices Due for Payment

Purpose: This report can be used for follow-up on customer invoices/installments that are due. The report displays open customer items that are due for payment before a specified date.

Windows:
Customer Invoices Due for Payment

  1. Open the Customer Invoices Due for Payment window.
  2. Select your customer in the From Customer No and To Customer No fields using the List of Values.
  3. Click Preview to view the report.

Customer Trial Balance Report

Purpose: This report can be used to show customer balances for a specific period or date range.  It shows opening balances, period transactions, and closing balances for each customer.

Windows:
Customer Trial Balance

  1. Open the Customer Trial Balance window.
  2. Enter the period or date range for which the report is to provide data.
  3. This report can be organized based on the selecting the customer, customer group, currency, country, or account. Specify the desired selection by selecting the relevant option. For this example, select the Customer and select the From Customer and To Customer No data by using the List of Values.
  4. Specify how the report should be sorted. The report can be sorted on the ledger account, currency, country, supplier ID or supplier group.
  5. Choose the currency to be used when printing the report. The company's accounting currency or the third currency can be used to print the report.
  6. Click Preview to view the report.

Customer Transactions

Purpose: This report can be used for follow-up on customer invoices/installments that are due. The report displays open customer items that are due for payment before a specified date.

Windows:
Customer Transactions

  1. Open the Customer Transactions window.
  2. Enter the period range for which the report is to provide data.
  3. Select the customer radio button and enter your customer in the From Customer No and To Customer No fields using the List of Values.
  4. The report can be ordered by customer or customer group, account, and code part.  Leave the default order for this exercise.
  5. Select the reporting parameters that you desire.
  6. Click Preview to view the report.

Long-term Customer Invoices 

Purpose: This report will show all open customer invoices, difference items, and bills of exchange with a due date more than one year away from a specific date. These invoice must be reclassified as long-term claims.

Windows:
Long-Term Customer Invoices

  1. Open the Long-Term Customer Invoices window.
  2. Specify the reference date. The system will default to the current date, but you can manually change this date. 
  3. Specify the number of days from the reference date that would constitute a long-term claim due date. The system defaults to 365 (days), but you can change this value. 
  4. Modify the date in the First Long-term Due Date field, if desired. (The system will automatically calculate this date based on the reference date and the value in the No. of Days to Long-term field. However, you can modify this date.)
  5. If the report should be limited to items posted to a specific ledger account on the specified reference date, enter the account in the Account field.
  6. Click Company to change the company for the report.
  7. Click Preview to view the report on screen or click Print to print the report. 

Customer Payment Journal

Purpose: This report shows all registered customer payments.

Windows:
Order Report

  1. Open the Order Report window.
  2. Use the Report list to select the Customer Payment Journal. Click OK.
  3. Click Preview to view the report.