Attachment 4b_AMKWI-331-332-00002 FBwT EDQ Recon v09.pdf

AMC

Quality Management System

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 1 of 22

UNCONTROLLED COPY WHEN DOWNLOADED

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

AMKWI-331-332-00002

Fund Balance with Treasury EDQ Reconciliation

1.0 Purpose:

The purpose of this SOP is to establish standard procedures for utilizing the Enterprise Data Quality (EDQ) process to produce and publish a report that reconciles the Central Accounting Reporting System (CARS) Fund Balance with Treasury (FBWT) to the Delphi USSGL 101000 Trial Balance.

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:42:43 -05'00'

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

DEANDRE

L MOORE

Digitally signed by

DEANDRE L MOORE

Date: 2019.07.15 14:13:29 -05'00'

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 2 of 22

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 and added note that PHMSA and FHWA must also pull Unappropriated Receipts from GWA website.

09-11-08

3 Update GWA screen shots. Note recon template is found on KSN. Add FAA Order 1280.1 to Section 3 References.

05-04-10

4 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

5 Deleted OMB Tab #4 requirements as no longer reported. 5-3-2012

6 New routing codes resulting in new SOP numbering. Added electronic signatures and Process owner is now Linda Stillwell

04/10/14

7 Add note to Section 5.0 and update wording in Section 6.7.2 to reflect Digital Signature process for Reconciler and Management.

10/18/2016

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

2/1/2019

9 Update KSN path to AP to GL Recon form. Corrected number of SOP referenced in Step 6.16

5/1/2019

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 3 of 22

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) FBWT (Cash) vs GL Reconciliation runs monthly for each OA. Note: Preliminary reconciliations shall be provided prior to the final monthly reconciliation as requested by the customers. The Summary tab, shown below, is the first tab on your reconciliation. This reconciliation compares cash balances from Treasury, obtained through the CARS website, to the Delphi Trial Balance in SGL accounts 1010%. The standardized Cash Template is shown below:

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 4 of 22

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 on to Treasury’s CARS Web Site to download the CARS Expenditure Activity and

Transactions Reports.

6.2 Select “Reports” from the Home Page.

6.3 Select “Expenditure Activity.”

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 5 of 22

6.4 In the “Expenditure Activity Inquiry” screen, select the agency you wish to download.

Ensure that the Fund Type, Account Type and Treasury Account Symbol defaults are “All.” Select the current fiscal year in the Accounting Period section. ALWAYS select October through the month you are reconciling. For example, in December you will reconcile the month of November. Your selection must be October through November.

NOTES:

I. For the Department of Transportation, you will also need to select the specific Fiscal Service Organization you need. Refer to the table in Appendix 7.1.

II. Select and download the Federal Highway Administration (FHWA) Child accounts per the table shown in Appendix 7.1.

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 6 of 22

6.5 Click “Download…” In the Download Inquiry screen, select Download File Type as

CSV and the “Include table headings” option. Click “Download.”

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 7 of 22

6.6 After the download is complete, you will see “Do you want to open or save filename.csv from gwa.fms.treas.gov?” ALWAYS select Save As. Never open the downloaded CSV GWA file at any time. Opening the file will prevent EDQ from properly reading the data.

6.7 Save the downloaded file to ORG002/1 CARS Exports for EDQ/FBwT Exports/FBwT

Current Year to Date Treasury files/Expenditure. Use the file naming convention shown in Appendix 7.1. NOTE: you can overwrite the existing file.

6.8 Return to the Reports – Account Statements page and select “Transactions.”

6.9 You will now download the Receipt files. Select the agency you need to download.

Select the Account Type “All (Receipts).” Ensure that the Account Type and Treasury Account Symbol defaults are “All.” Select the current fiscal year in the Accounting Period section. ALWAYS select October through the month you are reconciling. For example, in December you will reconcile the month of November. Your selection must be October through November.

I. Select “ALL” as the Fiscal Service Organization when downloading the Department of Transportation Receipt files.

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 8 of 22

II. Do not download any Receipt accounts for FHWA Child Accounts.

6.10 Click “Download…” In the Download Inquiry screen, select Download File Type as CSV and the “Include table headings” option. Click “Download.”

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 9 of 22

6.11 After the download is complete, you will see “Do you want to open or save filename.csv from gwa.fms.treas.gov?” ALWAYS select Save As. Never open the downloaded CSV GWA file at any time. Opening the file will prevent EDQ from properly reading the data.

6.12 Save the downloaded file to ORG002/1 CARS Exports for EDQ/FBwT Exports/FBwT Current Year to Date Treasury files/Receipts. Use the file naming convention shown in Appendix 7.2. NOTE: you can overwrite the existing file.

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 10 of 22

6.13 The Government Wide Accounting (GWA) files must now be consolidated into two CSV files, one for Expenditures and one for Receipts.

6.14 From Windows Explorer, navigate to ORG002/1 CARS Exports for EDQ/FBwT Exports/FBwT Current Year to Date Treasury files/Expenditure. Double click the merge_gwa_expenditures.bat file. A batch process will run that consolidates all Expenditure files into one file.

6.15 From Windows Explorer, navigate to ORG002/1 CARS Exports for EDQ/FBwT

Exports/FBwT Current Year to Date Treasury files/Receipts. Double click the merge_gwa_receipts.bat file. A batch process will run that consolidates all Receipt files into one file.

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 11 of 22

6.16 The consolidated Expenditure and Receipts files must be transferred to the EDQ server using WinSCP, a file transfer protocol (ftp) tool. Upon notification that the consolidated files are ready, the designated Accountant with access to WinSCP logs into the application and moves the consolidated files from the Windows location to the WinSCP Outbound folder and submits a Work Order to Production Control to process the move. Refer to SOP AMKWI-331-332-00028 for instructions on accomplishing the file transfer process.

6.17 Upon notification that the consolidated GWA files were successfully moved to the EDQ server, the reconciling Accountant logs into the EDQ Server Console.

6.18 In the Scheduler tab, click the plus sign (+) next to Recon_ FBWT (Cash)_vs_GL.

Right mouse click the FBWT (Cash) vs GL process and select Run. Enter a run label in

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 12 of 22 the Run Label Dialog box. This run label is used to identify your results after the process completes. Use the following standards for Run Labels:

I. <Period Name> FBWT Prelim 1 <Run Date> (First Business Day)

II. <Period Name> FBWT Prelim 2 <Run Date> (Third Business Day)

III. <Period Name> FBWT FINAL <Run Date> (Seventeenth Calendar Day)

6.19 You can track the progress in the Current Tasks tab. When the job is finished, it will disappear from the tab.

6.20 After the job completes, click on the Results tab. You will see the results of all completed jobs within your user group. Click on the Run Label you entered in Step 6.18

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 13 of 22 and your results appear in the bottom pane. There are three tabs in the Results pane.

Export all three tabs at the same time by using the “Export all tabs to Excel” icon.

6.21 Save the results to the designated shared file location for the reconciling period.

Identify whether the data is from the Prelim 1, Prelim 2 or FINAL EDQ run.

6.22 Open the exported file. Sort the “Cash_TB_vs_GWA_out” tab by Agency then Treasury Account Symbol.

6.23 You will need a Pivot Table in a specific format to use for copying and pasting the EDQ results to each agency’s FBWT Template. The quick way to do this is to open a previous EDQ FBWT results file. Right click on the Pivot Table tab. Select Move/Copy option. Select your new file from the “To Book” drop down and the “Copy” option.

Click OK.

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 14 of 22

6.24 You will need to change the Data Source in the Pivot Table you just copied to your new file. Click on Analyze under Pivot Table Tools then the drop down arrow next to Change Data Source.

6.25 Click on the Cash_TB_vs_GWA_out tab. Select all rows and columns in the

Cash_TB_vs_GWA_out tab and click OK.

6.26 Your Pivot Table is now formatted correctly for copying/pasting into each Customer’s template.

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 15 of 22

6.27 AMK-330 provides a final FBWT reconciliation to all customers no later than the

20th Calendar day of the month. AMK-330 also provides, upon request, preliminary reconciliations as follows:

I. On the first Business Day at 11:00 am Central, GWA files are downloaded for the requesting customers only (refer to Appendix 7.3). The Preliminary 1 FBWT Reconciliation is distributed to the internal AMK-330 Financial Statement Accountants only.

II. On the third Business Day at 11:00 am Central, GWA files are downloaded for all customers, however the Preliminary 2 FBWT Reconciliation is only delivered to the internal AMK-330 Financial Statement Accountants for the requesting customers (refer to Appendix 7.3).

6.28 Customer specific templates are stored at amz700-710\.123 Recon\EDQ Recons\FBWT\Templates. Open the appropriate template and enter the reconciling period in the <Enter Period Name> cell of the Summary tab.

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 16 of 22

6.29 Go to the EDQ Results tab. Copy and paste the appropriate data from the Pivot

Table from Step 6.26 into cell A4. VLOOKUP formulas populate the cells in Columns C and D in the Summary tab.

6.30 Go to the Cash_vs_GL_TRB_out tab of your EDQ exported file. Filter the data to the appropriate customer. Copy and Paste the Treasury Symbol and Ending Balance columns into the Delphi Trial Balance tab of your reconciliation.

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 17 of 22

6.31 Go to the GWA_files.GWA_vs_GL_All_Expendi tab your EDQ exported file.

Filter the data to the appropriate customer. Copy and Paste the data into the CARS Expenditures tab of your reconciliation.

6.32 Go to the GWA_files.GWA_vs_GL_All_Receipt tab your EDQ exported file.

Filter the data to the appropriate customer using the Agency Location Code (ALC) shown in Appendix 7.2. Copy and Paste the data into the CARS Receipts tab of your reconciliation.

6.33 For the FINAL FBWT Reconciliation only, create a PDF file of the Summary page and save it to amz700-710\.123 Recons/EDQ Recons/Digital Signatures/FBWT folder.

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

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

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 18 of 22

7.0 Appendix:

7.1 GWA Expenditures Account Statements and File Names

File Name

Agency Fiscal Service Org Treasury Account

Symbols

CFTC

OTHER INDEPENDENT

COMMISSIONS - (95)

COMMODITY FUTURES

TRADING COMMISSION - (9514) ALL

CPSC

CONSUMER PRODUCT SAFETY

COMMISSION - (65)

ALL

ALL

FHWA COE

CHILD CORPS OF ENGINEERS - (96)

REPORTS AND ANALYSIS OFFICE

- (9600)

96-69X0500, 96-

69X0538, 96-69X8083

FHWA DOA

CHILD

DEPARTMENT OF

AGRICULTURE - (12)

All 12-69X0500.11, 12-

69X8083.10, 12-

69X8083.11

FHWA DOC

CHILD

DEPARTMENT OF COMMERCE

- (13)

All 13-69X5168.14, 13-

69X8083.14

FHWA DOE

CHILD

DEPARTMENT OF ENERGY -

(89)

All

89-69X8083

FHWA

DOHS

CHILD

DEPARTMENT OF HOMELAND

SECURITY - (70)

All 70-69X0641, 70-

69X0641.2, 70-

69X8083.6

FHWA

DOHUD

CHILD

DEPARTMENT OF HOUSING &

URBAN DEVELOPMENT - (86)

All 86-69M1122, 86-

69X0538, 86-

69X8083.1

FHWA

DOAF

CHILD

DEPARTMENT OF THE AIR

FORCE - (57) - (5700) 57-69X8083

FHWA

DOARMY

CHILD

DEPARTMENT OF THE ARMY -

(21)

All

21-69X8083

FHWA DOI

CHILD

DEPARTMENT OF THE

INTERIOR - (14)

All 14-69X0500.10, 14-

69X0500.11, 14-

69X0500.16, 14-

69X0500.20, 14-

69X0538.10, 14-

69X0592.20, 14-

69X8083.1, 14-

69X8083.10, 14-

69X8083.11, 14-

69X8083.16, 14-

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 19 of 22

69X8083.20, 14-

69X8083.6

FHWA DON

CHILD

DEPARTMENT OF THE NAVY -

(17)

All

17-69X8083

FHWA

DOTREAS

CHILD

DEPARTMENT OF THE

TREASURY - (20)

All

20-69X8083.9

FHWA DOT

CHILD

DEPARTMENT OF

TRANSPORTATION - (69)

All 69-69X0538.7, 69-

69X0641.11, 69-

69X8058.11, 69-

69X8058.26, 69-

69X8058.7, 69-

69X8083.1, 69-

69X8083.11, 69-

69X8083.14, 69-

69X8083.15, 69-

69X8083.17, 69-

69X8083.26, 69-

69X8083.30, 69-

69X8083.6, 69-

69X8083.7, 69-

69X8191.5

FHWA

INTERGOV

CHILD

INTERGOVERNMENTAL

AGENCIES - (46)

All

46-69X8083

FHWA

OTHER

CHILD

OTHER INDEPENDENT

COMMISSIONS - (95)

All 95-69X0511.67, 95-

69X0538.67, 95-

69X8083.67

FHWA TAV

CHILD

TENNESSEE VALLEY

AUTHORITY - (64)

All

64-69X8083

FHWA

DEPARTMENT OF

TRANSPORTATION - (69)

FEDERAL HIGHWAY

ADMINISTRATION - (6905) ALL

FMCSA

DEPARTMENT OF

TRANSPORTATION - (69)

FEDERAL MOTOR CARRIER

SAFETY ADMINISTRATION -

(6926) ALL

FRA

DEPARTMENT OF

TRANSPORTATION - (69)

FEDERAL RAILROAD

ADMINISTRATION - (6907) ALL

FTA

DEPARTMENT OF

TRANSPORTATION - (69)

FEDERAL TRANSIT

ADMINISTRATION - (6911) ALL

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 20 of 22

MARAD

DEPARTMENT OF

TRANSPORTATION - (69)

MARITIME ADMINSTRATION -

(6917) ALL

NHTSA

DEPARTMENT OF

TRANSPORTATION - (69)

NATIONAL HIGHWAY TRAFFIC

SAFETY ADMINSTRATIO - (6906) ALL

OIG

DEPARTMENT OF

TRANSPORTATION - (69)

OFFICE OF THE INSPECTOR

GENERAL - (6904) ALL

OST

DEPARTMENT OF

TRANSPORTATION - (69)

OFFICE OF THE SECRETARY -

(6901) ALL

PHMSA

DEPARTMENT OF

TRANSPORTATION - (69)

PIPELINE AND HAZARDOUS

MATERIALS SAFETY ADMIN -

(6914) ALL

RITA

DEPARTMENT OF

TRANSPORTATION - (69)

RESEARCH AND INNOVATIVE

TECHNOLOGY ADMINISTRA -

(6930) ALL

STB DOT

DEPARTMENT OF

TRANSPORTATION - (69)

SURFACE TRANSPORTATION

BOARD - (6903) ALL

VOLPE

DEPARTMENT OF

TRANSPORTATION - (69)

TRANSPORTATION SYSTEMS

CENTER - (6909) ALL

IMLS

INSTITUTE OF MUSEUM AND

LIBRARY SERVICES - (53)

INSTITUTE OF MUSEUM AND

LIBRARY SERVICES - (5300) ALL

NCUA

NATIONAL CREDIT UNION

ASSOCIATION - (25)

- (2500)

ALL

NEA

NATIONAL FOUNDATION ON

THE ARTS AND THE HUMAN -

(59)

ALL

ALL

SEC

SECURITIES AND EXCHANGE

COMMISSION - (50)

- (5000)

ALL

STB

OTHER INDEPENDENT

COMMISSIONS - (95)

SURFACE TRANSPORTATION

BOARD - (9589) ALL

7.2 Receipt File Names and ALC Crosswalk

CFTC Receipts 95140001

CPSC Receipts 61000001

DOT Receipts See Breakout

IMLS Receipts 59000004

NCUA Receipts 25000001

NEA Receipts 59000002

SEC Receipts 50000001

STB Receipts 95890001

DOT ALC Breakout

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 21 of 22

FHWA 69050001

FMCSA 69260001

FRA 69070001

FTA 69080001

MARAD 69170001

NHTSA 69060001

OIG 69010006

OST 69010005

OSTWCF 69010007

PHMSA 69140001

RITA 69300001

STB 69030001

VOLPE 69010004

7.3 Preliminary FBWT Schedule

FBWT vs GL Reconciliation

Preliminary Reconciliation Schedule

1st Business Day 3rd Business Day

FHWA FWHA

FMCSA FRA

FRA IMLS

OIG MARAD

RITA NEA

STB OIG

VOLPE RITA

STB

VOLPE

AMKWI-331-332-

Revision

Title: Fund Balance with Treasury EDQ Reconciliation DATE: 11/13/2018 Page 22 of 22

Definitions:

Terms Definitions

8.0 Records: