Attachment 4f_AMKWI-331-332-00006 Purchase Orders v08.pdf

AMC

Quality Management System

AMZKWI-331-332-

00006

Revision

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 1 of 28

UNCONTROLLED COPY WHEN DOWNLOADED

Check the Master List to Verify That This is the Correct Revision Before Use

AMKWI-331-332-00006

PURCHASE ORDER RECONCILIATION

1.0 Purpose: The purpose of this SOP is to establish standard procedures for preparing the

Purchase Order reconciliation.

2.0 Scope: This procedure applies to work performed by the Reporting and Analysis

Division at the MMAC for Enterprise Service Center (ESC) customers.

3.0 References: FAA Order 1280.1B, Appendix F

Process Owner: _ __________________________ Title: Timothy Riley, Accountant

Branch Approval:____________________ Title: Kyle Gerber, AMK-332 Fin Rpt/Data Integ NonH TF/NonDOT Section Manager

Branch Approval:____________________ Title: John Stover, AMK-331 Fin Rpt/Anls Data Integ FAA/HTF Section Manager

KYLE A GERBER Digitally signed by KYLE A GERBER Date: 2017.03.17 11:16:16 -05'00'

JOHN D STOVER Digitally signed by JOHN D STOVER Date: 2017.03.17 11:34:26 -05'00'

TIMOTHY J RILEY Digitally signed by TIMOTHY J RILEY Date: 2017.03.28 08:34:39 -05'00'

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 2 of 28

Revision History

Rev Description of Change Effective Date

0 Initial Release 01-02-08

1 Added note regarding template modification for OA’s with child accounts

06-20-08

2 Standardized wording in Section 8.0. 09-11-08

3 Eliminated tab 6 requirement and modified Access queries to mirror directions give for A/R queries. Added requirement to type in “original signed by” (followed by name of person signing) and date signed on cover sheet (after signed by all parties) per March 2009 Internal Audit. The new page one requirement must be completed prior to uploading to KSN.

4-17-09

4 Renumbered Matched Unaccounted as Tab # 6 (was #8).

Revised form (Rev 1) to reflect change. Indicated Form is found on KSN.

5-21-2010

5 Changed SOP and FORM numbering to show as shared between AMZ710 and AMZ720. Changed Process Owner to Diana Hammons and added John Stover as AMZ-710 sign off.

8-16-2010

6 Revised Form to show line numbers and column names tying to check points covered in section 6.9.

12-10-2010

7 Revised Safety section per Ann Raeside. Changed AMZ-720 supervisor to Kyle Gerber. Changed 8.0 Records to standardized signature block. Revised Form adding column “System Errors – Can’t be fixed” field. Added run 4802 TB and compare to Advances tab total. Identify all “extras” and show at bottom of advances tab per auditor request. Added additional sort criteria for PYR & Advances – 6.6.2.3. New routing codes resulting in new SOP numbering. Added electronic signatures and Process owner is now Linda Stillwell

04/10/14

8 Added Note to Section 5.0; Updated Section 6.10.3 and Section

8.0 to reflect new Digital Signature procedures.

10/18/2016

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 3 of 28

4.0 Requirements:

4.1 Personnel: Accountants

4.2 Training: Individual has education and experience required for the position. Individual receives guidance from experienced personnel and completes additional training as required.

4.3 Equipment: FAA Network Computer

4.4 Safety: Hazard Prevention and Control is managed by the Mike Monroney Aeronautical Center (MMAC) Occupational Health and Safety Management System.

5.0 Overview: This reconciliation compares the obligation balances from the Purchasing Module to the 4801XXXX SGL accounts in the GL Module. Note: Preliminary reconciliations shall be provided prior to the final monthly reconciliation as requested by the customers.

• Through this process we will identify the following:

o Open Purchase Orders as of the last day of the month being reconciled o Items/amounts in GL, not in PO

� These are lines in the GL Module with 4801XXXX SGL accounts that were not generated through the PO Module

� GL Lines with a 4801XXXX SGL Account but with no PO Number (Reference 4) o Items in PO, not in GL � These are items where an event has occurred in the PO Module, but there is not a corresponding GL entry to a 4801XXXX SGL account or the corresponding GL entries net to zero.

Example of the “PO vs GL Summary” tab from the PO Reconciliation spreadsheet template:

This template can be found on KSN under FORMS > AMZ 710 720 > AMZFM-71206.

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 4 of 28

NOTE: OA’s with child accounts need to insert a column on recon template containing the child account by Treasury Symbol. This column titled “Less Child Account Reported on TB” will be inserted after the column titled “TAB #5 Less Accruals”. A row containing the child account totals needs to be inserted in the calculation portion of “Total 4801”.

6.0 Implementation:

6.1 Begin with the “PO vs GL Summary” tab from the prior month’s reconciliation

6.1.1 Change the “As of” date in the header of the reconciliation template, or prior month summary page, to the last day of the month being reconciled.

6.1.2 Be sure the correct OA is listed in the title.

6.1.3 Unhide all rows and columns – rows that did not contain any detail in last month’s reconciliation were hidden to help reduce the size of the PO vs GL Summary page. These rows and columns need to be re-exposed in case they contain dollar amounts that are pertinent to the current month’s reconciliation.

6.1.4 Save the worksheet as the current month’s reconciliation

6.2 Run Discoverer Trial Balance Query for USSGL Accounts starting in 4801%.

6.2.1 Run a 4801xxx Trial Balance query in Discoverer – The TRIAL BALANCE Query is used in this example.

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 5 of 28

Conditions for TRIAL BALANCE Query:

• GL Account LIKE = ‘4801%’

• Period Name = ‘(Period being reconciled)’ o In this example, Period Name is DEC-08_FY-09 o Other Conditions as shown below

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 6 of 28

Export to Microsoft Excel and save output file to use for reconciliation

6.3 Transfer the Trial Balance query results information into the current month’s reconciliation:

6.3.1 Open Trial Balance output file from Discoverer

6.3.2 Format the query results for use in the reconciliation to be either a number format of Number or Currency. Save the Trial Balance query results

6.3.3 In the current month’s reconciliation file, right click on the “Trial Balance” tab from last month and delete the Tab. Another option would be to overly the prior tab with current month and skip to step 6.3.6.

6.3.4 In the Trial Balance query results file, rename the tab “Trial Balance”

6.3.5 In the Trial Balance query results file, right click again on the tab now labeled “Trial Balance” and select, Edit, Move or Copy…

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 7 of 28 o o Select your current month’s PO reconciliation worksheet in the ‘To book:’ LOV o Select the location you want the sheet in the ‘Before sheet:’ LOV (The “Trial

Balance” tab should be the second tab in the worksheet after the “PO vs GL Summary” tab o Check the ‘Create a copy’ box and then click the ‘OK’ box This will paste the new trial balance into the current month’s reconciliation workbook as shown below.

6.3.6 Transfer 4801XXXX Trial Balance fund totals from the “Trial Balance” tab to the “PO vs GL Summary” tab.

� On the “PO vs GL Summary” tab, enter in the 4801XXXX account summarized amounts from the “Trial Balance” tab for each fund.

(Referring to the “PO vs GL Summary” tab example shown previously on Page 4, this data will be placed in Column C labeled “From GL 4801.”)

� This can be accomplished manually, or with several automated options like “SUMIF” or “VLOOKUP”.

6.4 Transfer PO Module amounts from the month-end DISCOVERER queries to the reconciliation

6.4.1 Open the DISCOVERER query results (ran at month end): OPEN PO 2 WAY FROM PO DIST and OPEN PO 3 WAY FROM PO DIST (These query results show the open PO obligation balances as of the end of the month directly from the PO Module.)

6.4.2 Format query results for use in the reconciliation

� Insert a column on the results from the 2 Way query between the ‘QTY Cancelled’ column and the ‘QTY Billed’ column and name it “QTY

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 8 of 28

Delivered.” This will line up the columns allowing you to combine the 2 Way and 3 Way results.

� Copy the results from the 3 Way query and add them to the Microsoft Excel spreadsheet containing the 2 Way results

6.4.3 Sort the entire spreadsheet by “FUND

� Place your cursor in Cell A2 and use the button or

� Go to the MS Microsoft Excel Tool bar:

• Select Data – Sort – Sort by “Fund” – “Ascending” radio button

6.4.4 Subtotal the spreadsheet by FUND

� Use the button in the MS Microsoft Excel Tool Bar, or Select Data – Subtotals from the MS Microsoft Excel Tool Bar

� Subtotal Criteria:

• “At each change in: FUND”

• “Use function: SUM”

• “Add subtotal to:” – check box beside ‘PO BALANCE’

• Take the default values for all other options, and then click <OK>

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 9 of 28

� The spreadsheet should now look similar to this:

� Save the spreadsheet

� Copy this Spreadsheet into the reconciliation as Tab #7 OPEN PO

BALANCES

6.4.5 Transfer the Fund subtotal amounts from Tab #7 OPEN PO BALANCES to the “PO vs GL Summary” tab

� Place the Fund subtotal amounts into Column I (PO BALANCE FROM PO MODULE.) This can be accomplished either manually or by using an automated option like SUMIF or VLOOKUP

� Verify that the “Grand Total” amount on Tab #7 OPEN PO BALANCES is equal to the total of Column I (PO BALANCE FROM PO MODULE) on the “PO vs GL Summary” tab.

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 10 of 28

� Place the “Grand Total” amount from Tab #7 OPEN PO BALANCES on the appropriate line near the bottom of the “PO vs GL Summary” tab.

(Please refer to the “PO vs GL Summary” tab example previously shown on page 4. In this example, the “Grand Total” would be placed in cell K34.

6.4.6 EXCEPTIONS – IF you have been notified by AP Accountant of Open PO Balance lines that CANNOT be fixed thru the module cut these lines from tab #7 and place on tab #8 – System errors cannot be fixed. Update the summary sheet with these tab #8 details. .

� These are typically PO lines closed but still showing a balance

� This is for tracking purposes per the auditor request.

6.5 Transfer the Matched Unaccounted Invoices amounts from the Month-End DISCOVERER query to the reconciliation. (Similar to the 6.4 steps above.)

6.5.1 Open your month end query results from ‘PO MATCHED UNACCOUNTED INVOICES’ Discoverer query

6.5.2 Format query results for use in the reconciliation

6.5.3 Sort the results by Fund

6.5.4 Create Sub-totals by Fund on the ‘Amount’ column

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 11 of 28

Your spreadsheet should now look like this.

� Save the spreadsheet

� Copy this Spreadsheet into the reconciliation as Tab #6 MATCHED

UNACCOUNTED INVOICES

6.5.5 Transfer the Fund subtotal amounts from Tab #6 Matched Unaccounted Invoices to the “PO vs GL Lines” tab

� Place the Fund subtotal amounts into Column J (MATCHED UNACCOUNTED INVOICES.) This can be accomplished either manually or by using an automated option like SUMIF or VLOOKUP

� Verify that the “Grand Total” amount on Tab #6 MATCHED UNACCOUNTED INVOICES is equal to the total of Column J (MATCHED UNACCOUNTED INVOICES) on the “PO vs GL Summary” tab.

� Place the “Grand Total” amount from Tab #6 MATCHED UNACCOUNTED INVOICES on the appropriate line near the bottom of the “PO vs GL Summary” tab. (Please refer to the “PO vs GL Summary” tab example previously shown on page 4. In this example, the “Grand Total” would be placed in cell K26.)

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 12 of 28

� The “PO vs GL Summary” tab will combine the ‘Matched Unaccounted Invoice’ amounts with the ‘PO Balance from PO Mod’ amounts LESS the System Error amounts into the ‘NET PO BAL FROM PO’ column. These totals represent what the PO Module is showing as PO obligation balances as of the end of the month.

� Note: The ‘Matched Unaccounted Invoice’ Amounts query results generate as negative or credit balances. This is because they represent invoices that reduced the PO Balance in PO module when they were matched, but because they were not validated as of month end, they did not generate GL accounting lines before month end. They are monthly timing differences between the PO module and the GL and are properly reflected in the reconciliation by being added back to the PO module obligation balances.

6.6 Analyzing GL detail lines before updating Tabs #1 - #5 and updating the “PO vs GL Summary” tab

6.6.1 Run Discoverer query to capture all GL detail lines that are in 48xxxxxx SGL Accounts. You DO NOT need GL detail on any of the child accounts.

o Please note that while only 4801XXXX account activity is being reconciled, a wider data pool of 48XXXXXX GL detail is needed to properly analyze the 4801XXXX-only activity.

• GL Details from JE Lines, Owner: DLIBRARY Check to be sure your Access database columns line up.

Save the query results

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 13 of 28

6.6.2 Open the month’s GL Detail results and prepare to sort. There are many methods that can be used to accomplish the sorting of the GL Detail. The procedure described below will be essentially a manual analysis of cutting and pasting in Microsoft Excel. More complex methods of sorting (like a Microsoft Access database, for example) can be employed as desired.

It is suggested you keep the month’s GL Detail as a separate spreadsheet from the reconciliation itself. The method described here will start with the entire month’s GL Detail and “slice and dice” it into the six separate categories: (1) Payroll activity, (2) current spreadsheet Accrual entries, (3) Advance activity, (4) Prior Year Recovery (PYR) activity, (5) GL Lines with “Null” or some other non-PO number in the Reference 4 field, and (6) GL Lines with a PO number in the Reference 4 field.

• As you “slice and dice”, move the sets of data to their own tabs on this GL Detail spreadsheet. This process will make it easier to incorporate the various categories into their appropriate tabs on the reconciliation. (The remaining 6.6.2 steps described below will approach the situation for simplicity sake as if you are cutting and pasting lines from a “starting tab” into five other category tabs and the “starting tab” will become the “GL Lines with a PO number in the Reference 4 field.” This is just one way of “slicing and dicing” – as you become familiar with the reconciliation, you may choose to employ a different method that is more efficient for you.)

6.6.2.1 Payroll lines

• Starting with all the month’s GL Detail lines, sort on “Payroll” for the “JE Source” column. Cut all of the sorted Payroll lines and place them in a separate tab labeled “Payroll.” (You may want to copy the column headings from the “starter” tab to this and the other category tabs.)

• In the separate “payroll” tab, sort by fund and then subtotal by fund and confirm that all the Payroll lines net to zero by fund. If so, this payroll data will not be needed further and should not be included in the reconciliation. If there happens to be any funds that do not net to zero, the related GL detail lines will need to be cut and pasted into the “GL Lines with Null or some other non-PO number in the Reference 4 field” category tab.

6.6.2.2 Separation of “GL Lines with PO number in Reference 4” from everything else

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 14 of 28

• On the “starter” tab, sort and (1) on “JE Source” column, and (2) sort on “JE Category” column. Cut all the sorted “Treasury Confirmation” lines from “JE Category” place on a separate tab labeled “4801 w nulls” (after you are done with more or some other non-PO number in the Reference 4 field.”)

• On the “starter” tab, examine the lines on the “JE Source” column. Re-sort as necessary and cut any GL Lines with “Spreadsheet”, “Receivable”, “Manual”, or any other JE Sources other than “Payables” and “Purchasing” and paste them on the “4801 w nulls” tab (created in the above step.)

• Rename the “starter” tab as “4801 w ref 4” (representing the category of “GL Lines with a PO number in the Reference 4 field.”). Examine the “SGL ACCT” column and ensure that all remaining lines have an account of “4801XXXX.” (If any other accounts are still present, cut and paste them into the “4801 w nulls” tab.) Sort the tab by Reference 4. Cut and paste any GL Lines with Reference 4 fields of “Null” to the “4801 w nulls” tab. Review the remaining values in the Reference 4 column. If there are any Reference 4 that do not appear to be PO numbers, cut and paste those GL Lines to the “4801 w nulls.” At this point, the “4801 w ref 4” tab should contain only 4801 GL Lines of Purchasing and Payable activity where there is a PO number in the Reference 4 field.

• It is possible you may add back a few lines (spreadsheet entries with a ref 4 specifically) to this tab later. In later steps, the GL Lines in this tab will be combined with similar lines from previous months and that combined data is what will be directly compared to the PO Module data.

6.6.2.3 Advance and Prior Year Recovery (PYR) lines

• The remaining “slice and dice” steps for this month’s GL Lines will be performed from the “4801 w nulls” tab. You have already successfully separated the “payroll” and “4801 lines w ref 4” GL Lines. You will now “peel away” the “Advance” and “Prior Year Recovery” GL Lines from everything remaining in the “4801 w nulls” tab.

• Sort the “4801 w nulls” tab by SGL account. Leaving the 4801 lines as they are, it is suggested you scroll down to the non-4801 GL Lines and highlight the remaining accounts with a unique highlight color for each different account.

• Resort the tab by Fund, Batch Name, Absolute Value, and SGL Account.

• Match each 4802 GL line with its corresponding 4801 GL line and cut and paste both lines to a new tab labeled “Advances.” At this point, only cut and paste GL lines that exactly offset between 4801 and 4802. (Having the data sorted by Batch Name and the 4802 lines highlighted as described in the above two steps

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 15 of 28 should expedite this process.) Once all the “matches” have been identified and moved, look for any other 4802 GL Lines that do not have 4801 offsets and cut and paste them to a separate section of the “Advances” tab. (Sometimes these non-offsetting 4802 lines are nicknamed “extras.” The Advance “extras” have no effect on the 4801 PO reconciliation.)

• Similar to the above step, match each 4871 and 4881 GL Line with its corresponding 4801 GL Line and cut and paste the lines to a new tab labeled “PYR” (for Prior Year Recovery adjustments.) Once all the “matches” have been identified and moved, look for any other 4871 or 4881 GL Lines that do not have 4801 offsets and cut and paste them to a separate section of the “PYR” tab.

(Again, these non-offsetting 4871 and 4881 lines can be considered “extras.” The PYR “extras” will have no effect on the reconciliation unless you are reconciling as of the end of October and the fiscal year-end system rollover has taken place.)

Ideally, the “PYR” tab will be organized with the 4801/4871 offsetting lines together (called “downward” adjustments), the 4801/4881 offsetting lines together (called “upward” adjustments), and the “extra” 4871 and 4881 lines.

6.6.2.4 Current Accruals and Grant Accruals

• Back in the “4801 w nulls” tab, there is one more category to “peel away.” Sort the remaining GL detail in the “4801 w nulls” tab by JE Category and review all remaining GL Lines to identify any Spreadsheet entries that might be a current accrual. (Quite often, “Accrual” is used as the actual JE Category.)

o There may be some reversals of the previous month’s accruals. They should be included with the GL Lines being cut and pasted. Check with your OA’s Financial Statement accountants to insure you have identified which entries are current accruals

� Cut and paste any identified current accruals to a new tab labeled “accruals.”

� Re-examine the “4801 w nulls” tab for any other spreadsheet entries that have something about “quarterly grant accrual” or “grant accrual adjustment” in the Description or Batch Name columns. These lines should also be cut and pasted to the “accruals” tab.

6.6.2.5 GL Lines with “Null” or some other non-PO number in the Reference 4 field

� Review the GL Lines now remaining in your “4801 w nulls” tab. Any non-4801 account lines still present should be investigated and cut and pasted to either the “advances” or “PYR” tabs as appropriate. Any GL Lines with a PO number in the Reference 4 fields should be checked to see if they belong with the current accruals.

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 16 of 28

� It is also possible for Spreadsheet entries with PO numbers in the Reference 4 field to be permanent PO module corrections (where a correction could not be made through the PO module and a Spreadsheet entry is the only way to correct the difference between GL and the PO module.) These should always be confirmed with your OA’s Financial Statement accountants. If these Spreadsheet entries truly are permanent PO module corrections and have a PO number in the Reference 4 field, they should be cut and pasted into the “4801 w ref 4” tab. (These kind of spreadsheet entries are the only kind that should ever be added into the “4801 w ref 4” tab as they will now be permanently considered in the direct comparison of GL to the PO module.)

6.7 Updating Tabs #2 - #5 on the PO reconciliation

� As the PO reconciliation is cumulative, the next step is to add the current month’s GL Lines (from the 6.6 instructions above) for the categories of “4801 lines w nulls”, “advances”, “PYR”, and “accruals” to the previous months’ cumulative GL data for these categories.

� NOTE: The names given to the tabs on the PO reconciliation itself are standardized and should stay consistent. The names given to the tabs on the “sliced and diced” current month GL Detail file (from the 6.6 instructions above) were part of a suggested example method and are not standardized.

6.7.1 Tab #5 – Current Accruals plus all Grant accruals (TAB labeled as “CURRENT or GRANT ACCRUALS”)

• On the PO reconciliation’s tab #5 (CURRENT or GRANT ACCRUALS), the previous months’ cumulative data may be subtotaled by fund. Undo the subtotals.

o NOTE: If the OA you are reconciling actually has Grant Accruals, there may be two subtotaled sections on this tab – one for Current Accruals and one for Grant Accruals. Complete the following steps for both sections unless the step specifically identifies Current Accruals or Grant Accruals.

• From the current month’s GL Detail file, cut and paste all the GL Detail Lines in the “accruals” tab into the PO reconciliations tab #5 (CURRENT or GRANT ACCRUALS) (adding the current month’s GL Detail Lines to the already present previous months’ GL Detail Lines.)

• Specifically for Current Accruals, confirm any GL Lines from the previous month are offset by current month offsetting entries or reversal entries. Any offsetting current and previous month GL Lines can be eliminated. Any GL Lines from the previous month that do not have an offsetting GL Line need to be cut and pasted to tab #2 (NO PO REF GL LINES.) Also, any current month “Reversal” GL Lines that do not offset previous month GL Lines need to be cut and pasted to tab

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 17 of 28

#2 (NO PO REF GL LINES) as well. (This current month “Reversals” may actually be reversing older accruals that are now residing in tab #2.)

o Specifically for Current Accruals, as the name implies, you should end up only with current month GL Lines.

• Specifically for Grant Accruals, these GL Lines will represent Life-To-Date activity. If there are any GL Lines that cleanly offset (by accounting string and amount), they can be eliminated. Otherwise, you will probably see an initial accrual entry and subsequent quarterly adjustment entries.

• Sort by Fund and Last Update Date. Subtotal by Fund on the Difference column (if there is more than one fund in the data.)

• Transfer the fund subtotal amounts from tab #5 (CURRENT or GRANT ACCRUALS) to the “PO vs GL Summary” tab.

� Place the Fund subtotal amounts (from both the Current Accrual and Grant Accrual sections) into Column G (LESS ACCRUALS.) This can be accomplished either manually or by using an automated option like SUMIF or VLOOKUP

� Verify that the “Grand Total” amounts (from both the Current Accrual and Grant Accrual sections) on Tab #5 (CURRENT or GRANT ACCRUALS) is equal to the total of Column G (LESS ACCRUALS) on the “PO vs GL Summary” tab.

� Place the “Grand Total” amounts (from both the Current Accrual and Grant Accrual sections) from Tab #5 (CURRENT or GRANT ACCRUALS) on the appropriate line near the bottom of the “PO vs GL Summary” tab. (Please refer to the “PO vs GL Summary” tab example previously shown on page 4. In this example, the “Grand Total” would be placed in cell H26.)

6.7.2 Tab #3 – Net Advances (TAB labeled as “NET ADVANCES”)

• On the PO reconciliation’s tab #3 (NET ADVANCES), the previous months’ cumulative data may be subtotaled by fund. Undo the subtotals.

• From the current month’s GL Detail file, cut and paste only the 4801 account GL Detail Lines in the “advances” tab into the PO reconciliations tab #3 (NET ADVANCES) (adding the current month’s GL Detail Lines to the already present previous months’ GL Detail Lines.) The 4802 GL Detail Lines will not be added to the PO Reconciliation as the recon is only actually reconciling the 4801 activity.

• Sort by Fund and Last Update Date. Subtotal by Fund on the Difference column (if there is more than one fund in the data.)

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 18 of 28

• Transfer the fund subtotal amounts from tab #3 (NET ADVANCES) to the “PO vs GL Summary” tab.

� Place the Fund subtotal amounts into Column D (ADJUSTMENT FOR ADVANCES.) This can be accomplished either manually or by using an automated option like SUMIF or VLOOKUP

� Verify that the “Grand Total” amount on Tab #3 (NET ADVANCES) is equal to the total of Column D (ADJUSTMENT FOR ADVANCES) on the “PO vs GL Summary” tab.

� Place the “Grand Total” amount from Tab #3 (NET ADVANCES) on the appropriate line near the bottom of the “PO vs GL Summary” tab. (Please refer to the “PO vs GL Summary” tab example previously shown on page

4. In this example, the “Grand Total” would be placed in cell H24.)

6.7.3 Tab #4 – Prior Year Recovery (PYR) Adjustments (TAB labeled as “PYR

ADJUSTS”)

• On the PO reconciliation’s tab #4 (PYR ADJUSTMENTS), the previous months’ cumulative data may be subtotaled by fund. Undo the subtotals.

o NOTE: The OA you are reconciling will probably have both “Downward” and “Upward” PRY adjusting entries meaning there will be two subtotaled sections on this tab – one for “Downward” and one for “Upward.” Complete the following steps for both sections.

� “Downward” PYR GL Lines typically show as a credit balance in account 4801 and are offset by debit balance 4871 GL Lines

� “Upward” PYR GL Lines typically show as a debit balance in account 4801 and are offset by credit balance 4881 GL Lines.

• From the current month’s GL Detail file, cut and paste only the 4801 account GL Detail Lines in the “PYR” tab into the PO reconciliations tab #4 (PYR ADJUSTS) adding the current month’s GL Detail Lines to the already present previous months’ GL Detail Lines. (The 4871 and 4881 GL Detail Lines will not be added to the PO Reconciliation as the recon is only actually reconciling the 4801 activity.)

• Sort by Fund and Last Update Date. Subtotal by Fund on the Difference column.

• Transfer the fund subtotal amounts from tab #4 (PYR ADJUSTS) to the “PO vs GL Summary” tab.

� Specifically for the “Downward” PYR activity, place the Fund subtotal amounts into Column E (PYR DOWN ADJUSTMENTS.) This can be

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 19 of 28 accomplished either manually or by using an automated option like SUMIF or VLOOKUP

� Verify that the “Downward” PYR activity “Grand Total” amount on Tab #4 (PYR ADJUSTS) is equal to the total of Column E (PYR DOWN ADJUSTMENTS) on the “PO vs GL Summary” tab.

� Specifically for the “Upward” PYR activity, place the Fund subtotal amounts into Column F (PYR UP ADJUSTMENTS.) This can be accomplished either manually or by using an automated option like SUMIF or VLOOKUP

� Verify that the “Upward” PYR activity “Grand Total” amount on Tab #4 (PYR ADJUSTS) is equal to the total of Column F (PYR UP ADJUSTMENTS) on the “PO vs GL Summary” tab.

� Add together the “Downward” and “Upward” Grand Total amounts on Tab #4 (PYR ADJUSTS.) Place this combined “Grand Total” amount on the appropriate line near the bottom of the “PO vs GL Summary” tab.

(Please refer to the “PO vs GL Summary” tab example previously shown on page 4. In this example, the combined “Grand Total” would be placed in cell H25.)

6.7.4 Tab #2 – GL Detail Lines with a Reference 4 of “null” or a Reference 4 not associated with a PO Number (Tab labeled as “NO PO REF 4 GL LINES”)

• On the PO reconciliation’s tab #2 (NO PO REF GL LINES), the previous months’ cumulative data may be subtotaled by fund. Undo the subtotals.

• From the current month’s GL Detail file, cut and paste all the GL Detail Lines in the “4801 w nulls” tab into the PO reconciliations tab #2 (NO PO REF 4 GL LINES) adding the current month’s GL Detail Lines to the already present previous months’ GL Detail Lines.

• Sort by Fund and Last Update Date. Subtotal by Fund on the Difference column.

• Transfer the “Grand Total” amount from tab #2 (NO PO REF GL LINES) to the “PO vs GL Summary” tab.

� Place the “Grand Total” amount from Tab #2 (NO PO REF GL LINES) on the appropriate line near the bottom of the “PO vs GL Summary” tab.

(Please refer to the “PO vs GL Summary” tab example previously shown on page 4. In this example, the “Grand Total” would be placed in cell H21.)

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 20 of 28

6.8 Creating Tab #1 – Comparison of PO Module vs GL Detail Lines (TAB labeled as “PO

VS GL LINES”)

6.8.1 Create the 4801 GL Summary query in Microsoft Access

• Refer back to the current month’s GL Detail file that was “sliced and diced” in the various 6.6 steps. Since the GL Lines data is cumulative, import all the current month GL Lines from the “4801 w ref 4” tab into your Microsoft Access table containing all the previous months’ imported “4801 w ref 4” data. (You may want to first copy and rename the Microsoft Access table containing all the previous months’ imported “GL Receivables” data as a backup table in case you have to start this step over.)

• Use Microsoft’s Microsoft Access to summarize the GL Lines by Fund, Reference 4 (which is the actual PO number) and Sum of Difference in a summary query.

• Save the query as something like “4801 Summary Query”

• Now create a new column in this 4801 Summary query by concatenating the Fund values and PO Number. This concatenated “Fund + Ref 4” will act as a unique value you will use to match between these GL Lines and the PO module data.

• The results should look like this:

6.8.2 Create the PO Module summary query in Microsoft Access.

• Referring to the PO Reconciliation file, copy the PO Module data from Tab #7 (OPEN PO BALANCES) and Tab #6 (MATCHED UNACCOUNTED INVOICES) into a separate temporary Microsoft Excel file.

• From this temporary Microsoft Excel file, undo any subtotals for both of the tabs.

• For the OPEN PO BALANCES tab, delete all columns except for PO NUMBER, PO BALANCE, and FUND. Rearrange the columns in the order of FUND, PO NUMBER and PO BALANCE.

• For the MATCHED UNACCOUNTED INVOICES tab, delete all columns except for FUND, PO NUMBER and AMOUNT. Also ensure that the columns are in this order.

• Using the header from the OPEN PO BALANCES tab, combine the data from the two tabs into a third tab.

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 21 of 28

• Import this third tab’s combined PO Module data into a new Microsoft Access table.

o NOTE: Unlike the GL Lines data, the PO Module data is not cumulative. The PO Module data represents the PO balances that were in the PO Module as of the end of the month. You will always create a new PO Module table and summary query in Microsoft Access each month.

• Save the table as something like “PO Module table”. Use this table to create a PO Module summary query.

• Now create a new column in this PO Module summary query by concatenating the Fund values and Invoice Number values (similar to what you did for the GL Detail summary query).

• Now that you have your cumulative GL Lines and your PO Module data in similar columns and formats, with a common value (concatenated value), you are ready to match them.

6.8.3 Create Microsoft Access queries to compare the 4801 GL Lines with the PO Module data o NOTE: The following is one example of how to match the GL Lines and PO Module data using Microsoft Access. Other methods may be used, in Microsoft Access, to match the GL Lines and PO Module data to come up with the same results.

• Create a new “design view” matching query in Microsoft Access selecting in this order:

� “4801 Summary Query”

� “PO Module Summary Query” o Select Fund, Reference 4 and the “Sum of Difference” (GL amount total) fields from the “4801 Summary Query” to be displayed in the Design View query.

o Select the Sum of PO Balance field from the “PO Module Summary Query” to be displayed in the Design View query.

o Now create a Join utilizing the concatenated value fields (possibly being displayed as “Expr1”) that were established in both summary queries. This join will only include rows where the joined fields from both tabs are equal. (Right click on the Join Line to access properties.)

o In the Join Properties box, select option 1, which is the default Join Option (see example below).

o The results from this query will give you all GL Lines and PO Module data that match by Fund and Invoice Number.

The query should look like this while setting the Join Properties before you execute it:

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 22 of 28 o Save the query as “(Period Name) Items in GL and in PO”

� i.e. FEB-08 Items in GL and in PO

• Create a new “Find Unmatched” query in Microsoft Access selecting in this order:

� “4801 Summary Query”

� “PO Module Summary Query” o Select the concatenated value for both the GL Receivables Lines and the PO Module lines and click on the <=> button and select Next.

o Microsoft Access will next prompt for what fields need to be shown in the query result. Select Fund, Reference 4 and Sum of Difference.

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 23 of 28 o Save the query as “(Period Name) Items in GL not in PO”

� i.e. FEB-08 Items in GL, not in PO o To prevent results coming back that show a “zero” balance in both GL and PO, go to Design View, and in the “Sum of Difference” criteria box, type in “<>0” or “Not 0”. Rerun the query to update it with this new condition.

• Create a second “Find Unmatched” query in Microsoft Access selecting in this order:

� “PO Module Summary Query”

� “ 4801 Summary Query” o Select the concatenated value for both the PO Module lines and the 4801 GL Lines and click on the <=> button and select Next.

o Microsoft Access will next prompt for what fields need to be shown in the query results. Select Fund, Reference 4 and Sum of Difference. Select Next o Save the query as “(Period Name) Items in PO not in GL”

� i.e. FEB-08 Items in PO not in GL

6.8.4 Format the queries in Microsoft Excel to use in the “#1 PO vs GL Lines” tab of the reconciliation workbook

• Export the queries created in step 6.8.3 to Microsoft Excel

• In Microsoft Excel, open the first query, Items in GL and in PO o Format the data results for use in the PO vs GL Reconciliation

� Change the header for Column C from “Sum of Difference” to “GL Balance”. Also change the header for Column D from “Sum of PO Balance to “PO Balance”.

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 24 of 28

� Insert a new column after “PO Balance” and create a “Difference” column

• Calculate amounts in “Difference” column (GL Balance – PO Balance)

� Ensure that columns C, D and E are formatted as either “Number”, “Currency”, or “Accounting”

� Sort the worksheet by “Difference” and Save

� The lines that have a difference of zero will be shown on the “#1 PO vs GL Lines” tab under the section “Items in PO and General Ledger with no differences”

� The lines that have a non-zero difference will be shown on the “#1 PO vs GL Lines” tab under the section “Items in PO and GL with differences”

• In Microsoft Excel, open the second query from step 6.8.3, “Items in GL not in PO” o Format the data results for use in the PO vs GL Reconciliation

� Change the header for Column C from “Sum of Difference” to “GL Balance”.

� Insert a new column after “GL Balance” and name it “PO Balance”

• Copy “0.00” into the entire column o This is done because these are items in GL, not in PO

� Insert another column after “PO Balance” and create a “Difference” column

• Calculate amounts in “Difference” column (GL Balance - PO Balance)

� Ensure that columns C, D and E are formatted as either “Number”, “Currency”, or “Accounting” and Save

� All lines will be shown on the “#1 PO vs GL Lines” tab under the section “Balance in General Ledger, not in PO Module”

• In Microsoft Excel, open the third query from step 6.8.3, “Items in PO, not in GL”

Format the data results for use in the PO vs GL Reconciliation

• Rename Column B (PO #) as Reference 4

• Insert a column after column B (Reference 4) and name it “GL Balance” Copy “0.00” into the entire column o This is done because these are items in PO, not in GL

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 25 of 28

� Rename Column D from Sum of PO Balance to simply PO Balance.

� Insert a column after “PO Balance” and create a “Difference” column

• Calculate amounts in “Difference” column (GL Balance – PO Balance)

� Ensure that columns C, D and E are formatted as either “Number”, “Currency”, or “Accounting” and Save

� All lines will be shown on the “#1 PO vs GL Lines” tab under the section “Balance in PO Module not in General Ledger”

6.8.5 Create Tab #1 PO vs GL Lines

• On Tab #1 of the PO reconciliation workbook, delete all of the data from the prior month’s reconciliation and leave only the section headers

• Copy the lines from the “In GL and in PO” query that have a difference of zero into the section labeled “In GL and PO with no Differences” o Sort the data by Fund and Subtotal by Fund on the GL Balance, PO Balance, and Difference Columns

• Copy the lines from the “In GL and in PO” query that have a non-zero difference into the section labeled “In GL and PO with Differences o Sort the data by Fund and Subtotal by Fund on the GL Balance, PO Balance, and Difference Columns

• Copy the lines from the “In GL, not in PO” query into the section labeled “Balance in General Ledger, not in PO Module”

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 26 of 28 o Sort the data by Fund and Subtotal by Fund on the GL Balance, PO Balance, and Difference Columns

• Copy the lines from the “In PO, not in GL” query into the section labeled “Balance in PO Module, not in General Ledger” o Sort the data by Fund and Subtotal by Fund on the GL Balance, PO Balance, and Difference Columns

At this point, your Microsoft Excel spreadsheet should look like this:

• Transfer the Total GL Balance and Total PO Balance from section 1 of tab #1 PO vs GL Lines to the Summary Page line labeled #1 Tab, Amounts are Equal in Both GL and PO o In the example shown on page 3, the amounts are shown in cells F59 and G59

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 27 of 28

• Transfer the Total GL Balances and Total PO Balances from sections 2, 3, and 4 of tab #1 PO vs GL Lines to the Summary Page line labeled #1 Tab, PO Invoice exists in PO or GL but amounts are different o In the example shown on page 3, the amounts are shown in cells F10 and G10

***NOTE: Since these 6.8 instructions are just one example of how to match the GL Lines and the PO Module data in Microsoft Access, your finished comparison results may be formatted slightly differently if you processed the comparison in Microsoft Access differently. This is okay as long as you are displaying all results of what matched and did not match between the GL Lines and PO Module data.***

6.9 Checkpoints (amounts that should equal) on the “PO vs GL Summary” tab

• Examine the “PO vs GL Summary” tab. You can think of the data shown on this page as two sections, a top half and a bottom half. You may notice that both halves are showing the same overall information but are summarizing and breaking it down differently.

Because of this, certain totals should tie between the top half and the bottom half. {In order to better explain which amounts should tie, the remaining 6.9 instructions will be referring to the “PO vs GL Summary” tab example previously displayed on page 3.} o Top section’s “From GL 4801” column total (in cell C12 on the page 3 example) should equal the bottom section’s “Total 4801” row total (in cell H23.)

o Top section’s “Adjusted GL Total” column total (in cell H12) should equal the bottom sections “Total Adjusted GL Total” row total (in cell H18.)

o Top section’s “Net PO Bal from PO” column total (in cell K12) should equal the bottom section’s PO Module total (in cell K18.)

o Top section’s “Difference” column total (in cell L12) should equal the bottom section’s difference total (in cell L18.)

6.10 Completion and Communication of Results

6.10.1 Hide all zero data lines.

6.10.2 E-mail the full PO reconciliation file to the OA for informational purposes and to the appropriate personnel to review and pursue corrections for the reconciling items as needed. Further discussion with the OA should be pursued, as needed, at regularly scheduled customer focus workgroup meetings. (CFWM) The full reconciliation must be saved on the U drive under the appropriate agency. Files greater than 1MB in size should be zipped/compressed on the U drive and for email inclusion.

Title: Purchase Order Reconciliation DATE: 10/18/2016 Page 28 of 28

6.10.3 Digitally sign the Summary Page and submit the digital copy to the appropriate Contract Supervisor and AMK-331/332 Manager for his/her digital signature.

7.0 APPENDIX: Discoverer queries, prior month reconciliation, Microsoft Access models

8.0 Records: The Reconciling Accountant digitally signs the Purchase Orders vs. GL Summary Page and notifies the appropriate AMK331/332 Manager. The Manager then digitally signs and keeps on file.