OCQ Exercise 4 – AP/GL Reconciliation


In this exercise, you will create a simple reconciliation between Accounts Payable and General Ledger.  You will learn about 'distinct' queries, the TSSum function and reinforce techniques learned in earlier exercises.

Note - This tutorial example is designed to work with the Timberline Construction Sample Data.
Step 1 - Insert the List of Accounts

This example uses a query to produce a list of accounts that have been used in the Accounts Payable application.  We can tell that an account has been used in AP because there will be transactions posted against it.

  1. Launch Excel.
  2. Rename "Sheet1" to "AP-GL".
  3. Select cell A3.
  4. Click the Query Wizard toolbar button.
  5. Select the Timberline Construction data folder and click OK.
  6. If you have implemented security in Sage 300 CRE Office, then you will be prompted to log in.  Enter your operator ID and password and click OK.
  7. The Select Table screen is displayed.  Select the AP Transaction table and click Next.
  8. The Select Fields screen is displayed.  Select Accounts Payable Account.
  9. Click Next twice to advance to the Query Options screen.
  10. Check the Exclude duplicate rows box and click Finish.
Note - Although the AP Transaction table contains many rows, this query only returns a few.  This is a result of selecting only the "Accounts Payable Account" column and excluding duplicate rows.  We therefore get a list of distinct accounts instead of a row for every transaction.
Step 2 - Add Account Title and Balance Columns

Now that you have the relevant list of GL Account numbers from the AP Transaction table you can use the Office Connector Lookup Wizard to insert a TSLookup function that displays the account title and current balance from the GL Account table.

  1. Select cell B3 and type Title.
  2. Select cell C3 and type GL Balance.
  3. Select cell B4.
  4. Click the Lookup Wizard toolbar button.
  5. The Select Table screen is displayed.  Select the GL Account table and click Next.
  6. The Key Values screen is displayed.  Type $A4 for Account and click Next.
  7. The Select Fields screen is displayed.  Select Account Title and Current Balance.
  8. Click Finish to insert the formulas.
Step 3 - Add the As Of Date

This step adds the ability to interact with the data by identifying a cell where you can enter the period end date.

  1. Select cell A1 and type As Of Date:.
  2. Select cell B1 and type 5/31/2011, substituting the year as appropriate for your sample data.
  3. Select cell B1, click in the Name box, type AsOfDate and press Enter.
    For more information, see Creating a Named Range.
Step 4 - Add the AP Balance

In this step you will use the Sum Wizard to insert a TSSum function that adds the AP Transaction records.

  1. Select cell D3 and type Amount.
  2. Select cell E3 and type Retainage.
  3. Select cell F3 and type Misc Deduction.
  4. Select cell D4.
  5. Click the Sum Wizard toolbar button.
  6. The Select Table screen is displayed.  Select the AP Transaction table and clickNext.
  7. The Select Fields screen is displayed.  Select Amount, Retainage and Worker's Comp.
  8. Click Next to display the Conditions screen.
  9. Click Add Condition to display the Add Filter Condition window.
  10. Select Accounts Payable Account for Field.
  11. Select Cell Reference for Value Type.
  12. Type $A4 for Value and click OK.
  13. Click Add Condition and select Accounting Date for Field.
  14. Select Less or equal for Comparison.
  15. Select AsOfDate from the Value drop list and click OK. Your completed filter should look like this:
  16. Click Finish to insert the formula.

The formula in cell D4 should look like this:

=TSSum("CURRENT_APT_RECORD_1","TAMT","[TGLAP]='" & $A4 & "' AND [TACCTDT]<='" & TEXT(AsOfDate, "M/d/yyyy") & "'")

Step 5 - Add the Difference Column

The final step in the reconciliation process is to determine the difference between the balances in AP as compared to the balance in GL.

  1. Select cell G3 and type AP Balance.
  2. Select cell G4 and type the formula =$D4-$E4-$F4.
  3. Select cell h5 and type Difference.
  4. Select cell H4 and type the formula =$C4+$G4.
  5. Click the Refresh button to fill down the formulas you entered in the previous steps.
  6. Select columns D - F, right-click the column header and pick Hide from the popup menu.