Exercise 4 - AP/GL Reconciliation

Introduction

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 a Distinct Query

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.

Launch Excel.
Rename "Sheet1" to "AP-GL".
Select cell A3.
Click the Query Wizard toolbar button.
Select the Timberline Construction data folder and click OK.
Enter your operator ID and password and click OK.

Note - You will not be prompted for your password unless Timberline security is enabled.
The Select Table screen is displayed. Select the AP Transaction table and click Next.
The Select Fields screen is displayed. Select Accounts Payable Account.
Click Next twice to advance to the Query Options screen.
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.

Select cell B3 and type Title.
Select cell C3 and type GL Balance.
Select cell B4.
Click the Lookup Wizard toolbar button.
The Select Table screen is displayed. Select the GL Account table and click Next.
The Key Values screen is displayed. Type A4 for Account and click Next.
The Select Fields screen is displayed. Select Account Title and Current Balance.
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.

Select cell A1 and type As Of Date:.
Select cell B1 and type 5/31/2001.

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.

Select cell D3 and type AP Balance.
Select cell D4.
Click the Sum Wizard toolbar button.
The Select Table screen is displayed. Select the AP Transaction table and click Next.
The Select Fields screen is displayed. Select Amount.
Click Next to display the Conditions screen.
Select Accounts Payable Account from the Field list.
Type 10-2001 in the Value column.
Select Accounting Date from the Field list.
Type 5/31/2001 in the Value column. Your completed filter should look like this:
Click Finish to insert the formula.

The formula in cell D4 should look like this:
=TSSum("CURRENT_APT_RECORD_1","TAMT","((TGLAP = '10-2001') AND (TACCTDT <= '05/31/2001'))")

Step 5 - Apply Cell References

Select cell D4.
Change '10-2001' to '" & A4 & "'.
Change '05/31/2001' to '" & TEXT($B$1, "mm/dd/yyyy") & "'.
Copy the formula down by double-clicking the fill handle.

The formula in cell D4 should look like this:
=TSSum("CURRENT_APT_RECORD_1","TAMT","((TGLAP = '" & A4 & "') AND (TACCTDT <= '" & TEXT($B$1, "mm/dd/yyyy") & "'))")

Step 6 - 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.

Select cell E3 and type Difference.
Select cell E4.
Type the formula =C4+D4.
Copy the formula down by double-clicking the fill handle.