Attachment 4d_AMKWI-331-332-00004 Accts Payable Recon v08.pdf

AMC

Quality Management System

AMKWI-331-332-

00004

Revision

Title: DATE: 2/1/2019 Page 1 of 10

UNCONTROLLED COPY WHEN DOWNLOADED

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

AMKWI-331-332-00004

Accounts Payable Reconciliation

1.0 Purpose:

The purpose of this SOP is to establish standard procedures for preparing the Accounts Payable reconciliation.

2.0 Scope:

This procedure applies to work performed by the Reporting and Analysis Division at MMAC for Enterprise Services Center (ESC) Customers.

3.0 References: FAA Order 1280.1B, Appendix F

Process Owner: _ __________________________ Title: John Stover, AMK-331 Fin Rpt/Anls Data Integrity Section Manager

Branch Approval:____________________ Title: Deandre Moore, AMK-330 Fin Rpt/Data Integrity Branch Manager

Section Approval:____________________ Title: Kyle Gerber, AMK-332 Fin Rpt/Data Integrity Section Manager

JOHN D STOVER Digitally signed by JOHN D STOVER Date: 2019.04.24 09:34:48 -05'00'

KYLE A GERBER Digitally signed by KYLE A GERBER Date: 2019.04.24 10:24:32 -05'00'

DEANDRE

L MOORE

Digitally signed by

DEANDRE L MOORE

Date: 2019.07.15 14:14:47 -05'00'

AMKWI-331-332-

00004

Revision

Title: DATE: 2/1/2019 Page 2 of 10

Revision History

Rev Description of Change Effective Date

0 Initial Release 01-02-2008

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

06-20-2008

2 Standardized wording in Section 8.03. 09-11-2008

3 Removed requirement for OST to include 2190 accounts. 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.

01-26-2009

4 Added FAA order 1280.1B to 3.0 References. Noted form is available on KSN instead of U drive.

03-29-2010

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

08-16-2010

6 Modified Safety section per Ann Raeside. Added column to form for “System Errors” which is new tab #5. Inserted M Hall rewrite of 6.5 – no longer maintain accrued receipt lines on tab #3 just TB detail for 2110%01. Standardized 8.0 Records signature block. New routing codes resulting in new SOP numbering. Added electronic signatures and Process owner is now Linda Stillwell

04/10/14

7 Added Note to Section 5.0; Updated Section 6.10.2 and Section

8.0 to reflect new Digital Signature procedures.

10/18/2016

8 Updated to define and provide instructions for Enterprise Data Quality (EDQ) process

4/1/2019

AMKWI-331-332-

00004

Revision

Title: DATE: 2/1/2019 Page 3 of 10

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 Networked 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:

The Enterprise Data Quality (EDQ) AP vs GL Reconciliation compares Subledger balances to the SGL 2110%0 Trial Balance. It is a real time reconciliation that identifies transactions that are in AP but not in GL, in GL but not in AP, and transactions that are in both but are not equal.

The Month End Close team schedules the EDQ AP vs GL Reconciliation process on the last business day of the month after all ledgers are closed. This provides a “snapshot” of the AP to GL status as of the end of the month.

AMK-330 delivers the Month End Close AP vs GL Reconciliation to all customers per the scheduled delivery date shown in Appendix 7.1.

The AP Team can run the process on an ad hoc basis as needed throughout the current month.

AMKWI-331-332-

00004

Revision

Title: DATE: 2/1/2019 Page 4 of 10

This template can be found on KSN under AMK-300 Reporting and Analysis Branch/AMK-331- 332 Fin Rpt, Anls Dat Iteg Section.

6.0 Implementation:

6.1 Log in to Enterprise Data Quality (EDQ) Server Console.

6.2 Click the “+” icon next to Recon_AP_vs_GL.

AMKWI-331-332-

00004

Revision

Title: DATE: 2/1/2019 Page 5 of 10

6.3 Right click on the AP_vs_GL process and select Schedule.

6.4 Prior to Month End Close, schedule the AP_vs_GL Process to run at 10:00 PM on the day of Month End Close. Enter “<Period Being Closed> AP to GL Recon Month End Close” as the Run Label and click OK. Ex: FEB-19_FY-19 AP to GL Recon Month End Close.

AMKWI-331-332-

00004

Revision

Title: DATE: 2/1/2019 Page 6 of 10

6.5 On the first business day of the month, log into the EDQ Server Console and click on the Results tab to retrieve the Month End Close AP to GL Reconciliation. Hint: enter “ap” in the Filter box to view a shortened list.

6.6 Export the following tabs to the appropriate reconciling period folder located on the share drive at WAAMKPOAP10.amc.faa.gov\amz700-710\.123 Recon\EDQ Recons\AP:

I. AP_vs_GL_Compare

II. AP_vs_GL_In Both_but_not_match

III. AP_GL_In_AP_not_GL

IV. In GL, Not In AP

V. AP_Trial_Balance_Out

VI. AP_Report_Out

VII. AP_Current_Accruals

6.7 Open Excel and combine the In GL, not In AP, AP_GL_In_AP_not_GL and AP_vs_GL_ In Both_but_not_match files as individual worksheets in a single workbook. Sort all tabs by Ledger Name then Fund and apply a filter. Name the workbook <Period Name> AP to GL Exceptions.xlsx. Ex: FEB-19_FY-19 AP to GL Exceptions.xlsx.

6.8 Open the following files:

AMKWI-331-332-

00004

Revision

Title: DATE: 2/1/2019 Page 7 of 10

I. AP_vs_GL_Compare

II. AP_Trial_Balance_out

III. AP_Report_Out

IV. AP_Current_Accruals

6.9 Convert all files to .xlsx workbooks. Name the files <Period Name> AP to GL Compare, <Period Name> AP Trial Balance, <Period Name> Unpaid Invoices and <Period Name> Current Accruals respectively. Examples:

I. FEB-19_FY-19 AP to GL Compare

II. FEB-19_FY-19 AP Trial Balance

III. FEB-19_FY-19 Unpaid Invoices

IV. FEB-19_FY-19 Current Accruals

6.10 Sort all files by Ledger Name then Fund and apply a filter.

6.11 Open the Customer’s AP to GL Template and “Save As” into the correct period folder. Use the naming convention <Customer> AP to GL <Period Name>. Ex: CFTC AP to GL FEB-19_FY-19.

6.12 Enter the reconciling Period Name in the header of the template. Filter the AP to GL Compare file to the appropriate Customer. Copy and paste the data to the #1 EDQ Results tab beginning at cell A4. Formulas will populate the Summary tab with the data from the #1 EDQ Results tab.

AMKWI-331-332-

00004

Revision

Title: DATE: 2/1/2019 Page 8 of 10

6.13 Open the AP Trial Balance file. Filter to the appropriate Customer. Copy and paste the data to the #5 2110%0 Trial Balance tab.

6.14 Open the Current Accruals file. Filter to the appropriate Customer. Copy and paste the data to the #6 Current Accruals tab.

6.15 Open the Unpaid Invoices file. Filter to the appropriate Customer. Copy and paste the data to the #7 Unpaid Invoices tab.

6.16 If there are any differences on the Summary tab, check the AP to GL Exceptions file for the supporting transactional details. Filter to the appropriate Customer. Copy and paste the data to the corresponding tab in the reconciliation.

6.17 The rows at the bottom of the Summary tab contain check figure formulas to ensure that data in the tabs tie out to the data in the Summary tab. Verify that all check figure amounts on the Summary tab are equal. If not, research and resolve the issue.

AMKWI-331-332-

00004

Revision

Title: DATE: 2/1/2019 Page 9 of 10

6.18 Print and digitally sign a .pdf copy of the Summary page. Save the digitally signed file to the correct Period Folder at U:\.123 Recon\EDQ Recons\Digital Signatures\AP.

Notify the approving manager or supervisor that the file is ready for review and signature.

6.19 Zip and encrypt the Excel file using the password GLreconFYNN! where NN = the fiscal year (ex: GLreconFY19!). Distribute the zipped and encrypted AP to GL Reconciliations to both internal and external Customers. Send the password on a separate email to the distribution list.

6.20 All Customers receive an AP to GL Reconciliation every month. EDQ will not show any results for Customers with 0.00 balances in 2110%0 in all of their funds.

Prepare a blank template and leave all tabs empty. Complete steps 6.18 and 6.19 to sign and delivery the reconciliation. See example:

AMKWI-331-332-

00004

Revision

Title: DATE: 2/1/2019 Page 10 of 10

7.0 Appendix:

7.1 AP to GL Reconciliation Delivery Schedule

SEC – No Later Than the 7th Calendar day of the month

All Other Customers – No Later Than the 20th Calendar day of the month (Note: if the 20th Calendar day falls on a weekend or holiday, delivery date is extended to the next business day.

Definitions:

Terms Definitions

8.0 Records: