Attachment 4c_AMKWI-331-332-00003 AR Management Review Process v10.pdf

AMC

Quality Management System

AMKWI-331-332-

00003

Revision

Title: Accounts Receivable Reconciliation DATE: 5/1/2021 Page 1 of 14

UNCONTROLLED COPY WHEN DOWNLOADED

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

AMKWI-331-332-00003

ACCOUNTS RECEIVABLE RECONCILIATION

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

Accounts Receivable reconciliation.

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

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

3.0 References: FAA Order 1280.1B, Appendix F

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

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

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

JOHN D STOVER Digitally signed by JOHN D STOVER Date: 2021.04.16 11:04:30 -05'00'

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

DEANDRE L

MOORE

Digitally signed by DEANDRE L

MOORE

Date: 2021.04.16 13:09:11 -05'00'

Title: Accounts Receivable Reconciliation DATE: 5/1/2021 Page 2 of 14

Revision History

Rev Description of Change Effective Date

0 Initial Release 1-02-08

1 Appendix A – changed excluded accounts for PHMSA Appendix B – changed Debit Memos Inv Number source Added note regarding template modifications for OA’s with child accounts

6-20-08

2 Standardized wording in section 8.0 and corrected Appendix A for PHMSA.

9-11-08

3 Appendix A – changed excluded account 135% to 1335% (PHMSA & OIG). 8.0 corrected typo “AP” to AR. Sec 6.5.3.6 revised paragraph regarding JE source of spreadsheet with ref 4…move to receivables tab.

1-26-09

4 Re-numbered tabs 3 thru 6 as tabs 4 thru 7. Inserted new tab 3.

Moved credit memos and misc receipt lines from tab 2 to new tab

3. Moved Appendix B to section 6.5.2.4.1 detailing uses. 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

5 Added new accounts to Appendix A. Updated tab numbering and details. Added additional details to Tab #2 for OSTWCF. Added FAA order 1280.1B to 3.0 References. Added additional column to CFTC reconciliation. Updated Appendix A to include CPSC and NCUA.

3-29-10

6 Adjusted column numbering on snap shots of Summary Page detail. Noted Invoice Numbering chart (6.5.2.4) is informational only - new query parameters have been adopted. Updated Appendix A for Excluded Accounts. Noted that credit memo lines can be offset against Ref 5 and applied to tab 1 issues. Added CFTC needs to run TB for 80% too. Adjusted SOP number and form number to include both AMZ710 and AMZ 720. Changed

8-16-10

Title: Accounts Receivable Reconciliation DATE: 5/1/2021 Page 3 of 14

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: Enterprise Data Quality (EDQ) compares the Accounts Receivable Aging Report to the GL lines posted to 13% USSGL accounts. The AR vs GL EDQ process is a real time reconciliation that compares the Trial Balance and AR Aging Report posted in Delphi at the time the process runs. This allows the AR Accountants to review and react to possible issues in the current month. The AR accountants can run or schedule the process during the current month as needed for research and review.

AMK-331/332 runs the AR vs GL process in EDQ after Month End Close is complete for all customers and prior to start of business in the new month. This gives ESC and our Customers a snapshot of Accounts Receivable as of the end of the month.

Process Owner to Diana Hammons and added John Stover as AMZ-710 sign off.

7 Revised Safety section per Ann Raeside. Changed 8.0 Records to standardized signature block. Updated Appendix A excluded accounts. Added credit memo application details somehow lost between rev 1 and 7. 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.9.2 and Section 8.0 to reflect new Digital Signature procedures.

10/18/2016

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

9/30/2020

10 Add OPM Excluded SGL’s to Appendix 7.1 5/1/2021

Title: Accounts Receivable Reconciliation DATE: 5/1/2021 Page 4 of 14

Example of the EDQ AR vs. GL Reconciliation spreadsheet template:

This template example can be found on the KSN under AMK-300 Reporting and Analysis Branch/AMK-331-332 Fin Rpt, Anls Dat Iteg Section. Agency specific templates are saved at amz700-710\.123 Recon\EDQ Recons\3 - AR\Templates.

Title: Accounts Receivable Reconciliation DATE: 5/1/2021 Page 5 of 14

6.0 Implementation:

6.1 Prior to Month End Close, log in to the Enterprise Data Quality (EDQ) Server Console.

Expand the list under Recon_AR_vs_GL. Right mouse click on the AR vs GL job and select Schedule.

6.2 Schedule the job to run after Month End Close AND prior to start of business on the first day of the month. Enter Run Label as <Closing Period> AR vs GL Recon MEC (ex:

OCT-20_FY-21 AR vs GL Recon MEC).

6.3 Click OK.

Title: Accounts Receivable Reconciliation DATE: 5/1/2021 Page 6 of 14

6.4 Log into the EDQ Server Console during the first week of the current month and click on Results. Hint: type the first three characters of the Run Label in the filter box for a shortened list of completed jobs. Click on your Month End Close job. Results are shown in the Results pane.

Title: Accounts Receivable Reconciliation DATE: 5/1/2021 Page 7 of 14

6.5 Select the first tab labeled 1_AR_vs_GL_Summary_Out. Select the Export icon and export the data to the appropriate EDQ AR to GL Recon folder located at amz700- 710\.123 Recon\EDQ Recons\3 - AR.

6.6 Repeat Step 6.5 for the tabs listed below.

6.6.1 2_AR_vs_GL_Trial_Bal_Out

6.6.2 3_AR_vs_GL_GL_Only_Out

6.6.3 4_AR_vs_GL_AR_Only_Out

6.6.4 5_AR_vs_GL_Inv_Amt_Differ_Out

6.6.5 6_AR_vs_GL_No_AR_Ref_4_Out

6.6.6 7_AR_vs_GL_Misc_Rcpt_and_Cr_Memo_Out

6.6.7 8_AR_vs_GL_Trial_Bal_Unbilled_Out

Title: Accounts Receivable Reconciliation DATE: 5/1/2021 Page 8 of 14

6.6.8 9_AR_vs_GL_Trial_Bal_Excluded_Out

6.6.9 A_AR_vs_GL_Accruals_Out

6.6.10 A1_AR_vs_GL_Edgar_Momentum_&_BPD

6.6.11 A2_AR_vs_GL_AR_Aging_Out

6.6.12 A3_AR_vs_GL_AR_NON_13%_Out

Note: there are additional tabs in the EDQ output that can be used for research, if desired.

These tabs can be exported or viewed within the Server Console.

6.7 Open the files exported in Steps 6.5 and 6.6. You will use the data in these files to populate OA specific templates. Hint: all tabs can be saved in one Excel Workbook for ease in populating the templates.

6.8 Open the file with the data from 1_AR_vs_GL_Summary_Out. Sort the data by

AGENCY then TREASURY_SYMBOL then FUND. There are 11 columns of data in the Summary tab. All Customers use the same 8 columns. Three columns are unique to specific Customers as listed below:

6.8.1 TRIAL_BALANCE – Used by all Customers

6.8.2 EXCLUDED – Used by all Customers

6.8.3 UNBILLED – Used by all Customers

6.8.4 ACCRUALS – Used by all Customers

6.8.5 MOMENTUM & BPD – Used by SEC Only

6.8.6 SEC_DPS_EXCLUDED_TB – Used by SEC Only

6.8.7 ADJ_TRIAL_BAL– Used by all Customers

6.8.8 AR_AGING– Used by all Customers

6.8.9 AR_NON_13% – Used by FHWA, CFTC, RITA, SEC, Volpe Only

6.8.10 ADJ_AR – Used by FHWA, CFTC, RITA, SEC, Volpe Only

6.8.11 DIFFERENCE – Used by all Customers.

Title: Accounts Receivable Reconciliation DATE: 5/1/2021 Page 9 of 14

6.9 Group the Customer specific columns. Collapse the groupings to show only the 8 common columns. This method will allow you to copy and paste the data into the template without picking up the extra columns. Your headings should look like this before collapsing:

And like this after collapsing:

6.10 Open the AR vs GL Summary from the EDQ results exported in Step 6.5.

Filter the data to the agency you are reconciling. Select the data in columns C through Q, excluding the headers. Note: expand the appropriate collapsed columns when populating templates for FHWA, CFTC, RITA, SEC, and Volpe. Copy the selected data.

6.11 Open the desired AR to GL Template located at amz700-710\.123

Recon\EDQ Recons\3 - AR\Templates. Enter the Period Name of the month being reconciled. Click on Tab #1 EDQ Results. Paste the data copied in 6.10 into Column A starting at the first cell below Fund. The AR vs GL Summary tab contains formulas that will populate the appropriate cells in the summary section with the corresponding data in Tab #1 EDQ Results.

Title: Accounts Receivable Reconciliation DATE: 5/1/2021 Page 10 of 14

6.12 Below the summary section of the Summary Tab are check figures information that compare the Totals of the summary columns to the totals calculated in the upper left corner of the EDQ Results. Review these rows to ensure there are no differences. All differences between the AR vs GL Summary and the #1 EDQ Results Tabs must be researched and corrected prior to publication.

6.13 Open the file containing data for EDQ tab 2_AR_vs_GL_Trial_Bal_Out exported in Step 6.6. Filter to the appropriate agency and select the data in columns A through F, excluding the headers. Note: the EDQ output has two extra columns that are specific to SEC. Include Columns H and I when preparing SEC’s reconciliation. Copy and paste the selected data into the template beginning at cell A4 of Tab #2 AR Trial Balance.

6.14 Continue copying and pasting the EDQ output into the appropriate tabs of the template. The relevant totals of each tab are calculated in cells A1 and B1. These totals are used as check figures to validate the accuracy of the Summary Tab. Ensure you review the check figures sections at the bottom of the Summary Tab. Differences must be researched and corrected prior to publication.

Title: Accounts Receivable Reconciliation DATE: 5/1/2021 Page 11 of 14

6.15 When the reconciliation is ready to distribute, create a PDF file of the AR vs GL Summary tab. Digitally sign the PDF file and save it to the appropriate period folder located at WAAMKPOAP10.amc.faa.gov\amz700-710\.123 Recons/EDQ Recons/Digital Signatures/AR folder.

6.16 Notify the appropriate manager or supervisor that the PDF files are ready for review and signature.

6.17 Distribute the Accounts Receivable Reconciliations to both internal and external Customers. At the Customers request, zip and encrypt the Excel file using the password GLreconFYNN! where NN = the fiscal year (ex: GLreconFY21!). Send the password to the distribution list on a separate email.

Title: Accounts Receivable Reconciliation DATE: 5/1/2021 Page 12 of 14

7.0 APPENDIX:

7.1 Excluded USSGL Accounts by Agency

Agency SGL Accounts Excluded From AR to GL Reconciliation

CFTC 1310%004 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% FAA* 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% FHWA 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% FMCSA 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% FRA 13108003 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% FTA 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% IMLS 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% MARAD 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% NCUA 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% NEA 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% NHTSA 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% OIG 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399%

OPM

13102120 13103000

131099%

1319%

1320%

1321%

1325%

1329%

1330%

1335%

1343%

1344%

1345%

1346%

1347%

1348%

1359%

1365%

1367%

1368%

1375%

1377%

1385%

1389%

1399%

OST 13108003 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% OSTWCF 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% PHMSA 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% RITA 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% SEC 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% STB 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399% VOLPE 131099% 1319% 1320% 1321% 1325% 1329% 1330% 1335% 1343% 1344% 1345% 1346% 1347% 1348% 1359% 1365% 1367% 1368% 1375% 1377% 1385% 1389% 1399%

* Please note, this is only for FAA Franchise Fund (12X3000000)

Note: All Receivable "Allowance" accounts are to be excluded.

Title: Accounts Receivable Reconciliation DATE: 5/1/2021 Page 13 of 14

7.2 Unique Exclusions

Additional Exclusions Unique to Specific Agencies

Agency Summary Column

Heading Special Exclusion Supporting Details Tab

CFTC

AR Receivables not in 13% SGL Accounts

Agency enters AR module activity to SGL's other than 13% for special purposes. These must be adjusted out of the AR Aging Report.

The Adjusted AR Aging Balance is compared to the Adjusted Trial Balance. #9 Aging Not in 13%

FHWA

AR Receivables not in 13% SGL Accounts

Agency enters AR module activity to SGL's other than 13% for special purposes. These must be adjusted out of the AR Aging Report.

The Adjusted AR Aging Balance is compared to the Adjusted Trial Balance. #9 Aging Not in 13%

RITA

AR Receivables not in 13% SGL Accounts

Agency enters AR module activity to SGL's other than 13% for special purposes. These must be adjusted out of the AR Aging Report.

The Adjusted AR Aging Balance is compared to the Adjusted Trial Balance. #9 Aging Not in 13%

SEC DPS Billing

Disgorgement and Penalty System (DPS) Interfaces directly to the General Ledger.

Trial Balances of SGL 13100022 and 1340% must be adjusted out of the Trial Balance, N/A

SEC

EDGAR MOMENTUM

and BPD Receipts not associated with the AR Module

Edgar Momentum and Bureau of Public Debt (BPD) Interfaces directly to the GL. Journal Lines with Journal Entry Source of 402 and 403 must be adjusted out of the Trial Balance. #7A Momentum & BPD

SEC

AR Receivables not in 13% SGL Accounts

Agency enters AR module activity to SGL's other than 13% for special purposes. These must be adjusted out of the AR Aging Report.

The Adjusted AR Aging Balance is compared to the Adjusted Trial Balance. #9 Aging Not in 13%

Volpe AR Receivables not in 13% SGL Accounts

Agency enters AR module activity to SGL's other than 13% for special purposes. These must be adjusted out of the AR Aging Report.

The Adjusted AR Aging Balance is compared to the Adjusted Trial Balance. #9 Aging Not in 13%

Title: Accounts Receivable Reconciliation DATE: 5/1/2021 Page 14 of 14

Records: The Reconciling Accountant digitally signs the AR vs. GL Summary Page and notifies the appropriate AMK331/332 Reviewer. The Reviewer then digitally signs and keeps on file.