Attachment 4a_AMKWI-331-332-00001 Suspense Aging v10.pdf

AMC

Quality Management System

AMKWI-331-332-

00001

Revision

Title: DATE: 2/1/2019 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-00001

Suspense Aging Reconciliation

1.0 Purpose:

The purpose of this SOP is to establish standard procedures for transactions and Trial Balances posted to Suspense USSGL’s 240000 and 241000.

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.23 14:18:44 -05'00'

KYLE A GERBER Digitally signed by KYLE A GERBER Date: 2019.04.24 10:23:02 -05'00'

DEANDRE L

MOORE

Digitally signed by

DEANDRE L MOORE

Date: 2019.07.15 14:08:25 -05'00'

AMKWI-331-332-

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

Revision History

Rev Description of Change Effective Date

0 Initial Release 1/02/08

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

6/20/08

2 Standardized wording in section 8.0 and modified 5.0 sample recon to include FAB 14 ABS detail. 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.

9/11/08

3 Changed report name from “Suspense vs. GL Recon” to “Suspense Aging by Fund”, and updated verbiage throughout the SOP accordingly. Added reference of FAA Order 1280 regarding PII/SPII handling.

11/05/09

4 Indicated Summary Template is found on KSN under FORMS.

05-04-10

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

08-16-10

6 Revised Safety section per Ann Raeside. Changed AMZ-720 supervisor to Kyle Gerber. Changed 8.0 Records to standardized signature block.

09-15-2012

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.5.2 and Section

8.0 to reflect current Digital Signature process.

10/18/2016

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

2/1/2019

10 Update location of forms 5/1/2019

AMKWI-331-332-

Title: DATE: 2/1/2019 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 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) Suspense Aging Reconciliation process ages outstanding balances in SGL 24% in three buckets; 0-30 Days, 31-60 Days and Over 60 Days. It is a real time reconciliation that ages transactions in the modules at the time the process runs. The process also compares the module transaction detail totals to the Delphi Trial Balance in SGL’s 24% to verify that all transactional data is represented in the aging.

The Month End Close team schedules the EDQ Suspense Aging process on the last business day of the month after all ledgers are closed. This provides a “snapshot” of the aging status as of the end of the month. The team also schedules the process to run at 4:00 AM every Monday morning of the current month. This provides the AR, AP and Travel Teams access to a status of the 24% aging as of the start of business every Monday morning.

Additionally, the AR, AP and Travel Teams can run the process on an ad hoc basis as needed.

AMKWI-331-332-

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

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_Suspense_Aging.

AMKWI-331-332-

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

6.3 Right click on the Suspense_Aging process and select Schedule.

6.4 Prior to Month End Close, schedule the Suspense Aging Process to run at 9:00 PM on the day of Month End Close. Enter “<Period Being Closed> Suspense Aging ME Close” as the Run Label. Ex: JAN-19_FY-19 Suspense Aging ME Close.

AMKWI-331-332-

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

6.5 Additionally, schedule the process to run every Monday morning at 4:00 AM in the next month. Enter “Interim Suspense Aging” as the Run Label.

6.6 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 Suspense Aging Reconciliation. Hint: enter “sus” in the Filter box to view a shortened list.

AMKWI-331-332-

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

6.7 Export the Suspense_Aging_Balance_Out and Suspense_Aging_Trial_Bal_Out tabs to the appropriate reconciling period folder located on the share drive at WAAMKPOAP10.amc.faa.gov\amz700-710\.123 Recon\EDQ Recons\Suspense Aging.

Name the files “1 – EDQ Suspense Aging” and “2 – EDQ Suspense Trial Balance” respectively.

6.8 The Suspense_Aging_Detail_Out tab is too large to export and must be filtered before exporting.

6.9 Click on the Suspense_Aging_Detail_Out tab then on the filter icon.

AMKWI-331-332-

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

6.10 Due to the large amount of data in SEC’s Suspense Details, SEC must be exported separately. (Refer to step 6.12 to 6.15.) In the Results Filter box, select “Ledger_ID < 238” and click “OK”. This returns the Suspense detail for all ledgers except SEC.

6.11 Export the results to the appropriate reconciling period folder located on the share drive at WAAMKPOAP10.amc.faa.gov\amz700-710\.123 Recon\EDQ Recons\Suspense Aging. Name the file “3 – EDQ Suspense Details.” Rename the tab to “All Except

SEC.”

6.12 Exception: SEC data is too large to export at once and must be parsed further.

Enter “238” as the ledger ID then click the Plus (+) icon to the right of the filter. This adds a new level of filter. Criteria for the new filter is “Fund < 6563DXXD00.” Export

AMKWI-331-332-

Title: DATE: 2/1/2019 Page 9 of 14 results to a staging file then move the data to the 3 – EDQ Suspense Details file. Rename the tab to “SEC ALL OTHERS.”

6.13 Return to the Results Filter and change the second criteria to “Fund =

6563DXXD00” and add another level to your filter. The new criteria is “Period_Year < 2017.” Export these results to a staging file location. Move the data to the 3 – EDQ Suspense Details file as a new tab behind the SEC All Others tab. Rename the tab to

“SEC 6563DXXD00.”

AMKWI-331-332-

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

6.14 You will need to filter the 6563DXXD00 fund two more time using these criteria for the third filter:

I. Period_Year = 2017

II. Period_Year > 2017

6.15 Export the results of these two filters to the staging file location. Copy and paste the data to the bottom of the data in the SEC 6563DXXD00 tab. When completed, your tabs should look like this screen shot.

6.16 Refer to Appendix 7.1 for a list of Customers that receive a Suspense Aging Reconciliation. Open the Customer’s delivered prior month Suspense Aging Reconciliation. Save As into the folder for the reconciling month using the reconciling Period Name in the file name. Ex: MARAD Suspense Aging JAN-19_FY-19. Change the “As Of” dates to the Month End Close date. NOTE: some Customers have no balances in SGL 24% but still require a reconciliation. These Customers receive a Suspense Aging Reconciliation with 0.00 in the columns. See example in Appendix 7.3.

AMKWI-331-332-

Title: DATE: 2/1/2019 Page 11 of 14

6.17 Open the 1 – EDQ Suspense Aging file from the designated location. Sort the file by Ledger Name, Treasury Symbol and Fund. Filter the data to the first Customer. Copy and Paste the data into the Suspense Summary tab of the Customer’s Suspense Aging Reconciliation.

6.18 Open the 2 – EDQ Suspense Trial Balance file. Sort the file by Ledger Name, Treasury Symbol and Fund. Filter the data to the first Customer. Copy and Paste the data into the Trial Balance tab of the Customer’s Suspense Aging Reconciliation.

6.19 Open the 3 – EDQ Suspense Details file. Click on the “All Except SEC” tab. Filter the data to the appropriate Customer. Copy and Paste the data into the Suspense Details tab of the Customer’s Suspense Aging Reconciliation.

6.20 Click on the “SEC All Others” tab. Copy and Paste the data into the Suspense Details tab of the Customer’s Suspense Aging Reconciliation. Then click on the “SEC

AMKWI-331-332-

Title: DATE: 2/1/2019 Page 12 of 14

6563DXXD00) tab. Copy and Paste the data into the Suspense Details tab of the Customer’s Suspense Aging Reconciliation.

6.21 After all reconciliations are complete, create a PDF file of the Suspense Summary tab for each Customer. 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/Suspense Aging folder.

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

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

AMKWI-331-332-

Title: DATE: 2/1/2019 Page 13 of 14

7.0 Appendix:

7.1 Customers Receiving Suspense Aging Reconciliations

Customer

CFTC

CPSC

FHWA

FMCSA

FRA

FTA

IMLS

MARAD

NEA

NHTSA

OIG

OST

OST-WCF

PHMSA

RITA

SEC

STB

Volpe

7.2 Ledger ID Crosswalk

Customer Ledger ID

CFTC 158

CPSC 198

FHWA 2

FMCSA 17

FRA 7

FTA 8

IMLS 138

MARAD 10

NCUA 218

NEA 98

NHTSA 1

OIG 3

OST 13

OSTWCF 6

AMKWI-331-332-

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

PHMSA 118

RITA 119

SEC 238

STB 12

STB DOT 12

VOLPE 9

7.3 Example of 0.00 Template

Definitions:

Terms Definitions

8.0 Records: