Attachment 4e_AMKWI-331-332-00005 FIXED ASSETS DRAFT v09.pdf

AMC

Quality Management System

AMKWI-331-332-

00005

Revision

Title: Fixed Assets Reconciliation DATE: 10/18/2016 Page 1 of 8

UNCONTROLLED COPY WHEN DOWNLOADED

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

AMKWI-331-332-00005

FIXED ASSETS RECONCILIATION

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

Fixed Asset 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: John Stover, AMK-331 Fin Rpt/Anls Data Integrity Section Manager

Branch Approval: ____________________ Title: Deandre Moore, AMK-330 Fin Rpt/Data Integrit y 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.09.17 13:50:43 -05'00'

KYLE A GERBER Digitally signed by KYLE A GERBER Date: 2019.09.17 15:26:36 -05'00'

DEANDRE

L MOORE

Digitally signed by

DEANDRE L MOORE

Date: 2019.09.17 15:50:11 -05'00'

Title: Fixed Assets Reconciliation DATE: 10/18/2016 Page 2 of 8

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

6-20-2008

2 Standardized wording in Section 8.0. 9-11-2008

3 Added example of Reserve Summary Report in Section 6.2.1 and improved description of process throughout.

1-26-2009

4 Enhanced summary page with addition of Unposted Mass Additions prior to Difference calculation. Added not to do the same if agency had child 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.

2-26-2009

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.

08-16-2010

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

10/01/2019

Title: Fixed Assets Reconciliation DATE: 10/18/2016 Page 3 of 8

4.0 Requirements:

4.1 Personnel: Accountants

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

4.2 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: The Enterprise Data Quality (EDQ) Fixed Assets Reconciliation compares SGL accounts 17% and 18%, as recorded in the GL, to assets and depreciation recorded in Delphi’s fixed asset module. Note: Preliminary reconciliations shall be provided prior to the final monthly reconciliation as requested by the customers.

Title: Fixed Assets Reconciliation DATE: 10/18/2016 Page 4 of 8

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

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 “Unposted Mass Additions.”

6.0 Implementation

6.1 Prior to Month End Close, log in to Enterprise Data Quality (EDQ). Expand the list under Recon_FA_vs_GL. Right mouse click on the FA vs GL job and select Schedule.

Title: Fixed Assets Reconciliation DATE: 10/18/2016 Page 5 of 8

6.2 Schedule the job to run at 5:00 AM Central time on the first day of the month. Enter run name as <Closing Period> FA vs GL Recon ME Close (ex: AUG-19_FY-19 FA vs GL Recon ME Close).

6.3 Click Ok.

6.4 The EDQ FA vs GL Reconciliation is a “look back” at the month just closed. EDQ pulls the Trial Balances for the appropriate Fixed Assets USSGL’s (shown in Definitions at the bottom of this document) for the prior month. EDQ compares the Trial Balances of the month just closed to the Journal Entry Reserve Ledger, Construction in Process (CIP) and Unposted Mass Additions data in the Fixed Assets module.

The Month End Close reconciliation gives us a static look of the status of Fixed Assets immediately after close.

6.5 During the first week of the new month, log in to Enterprise Data Quality (EDQ). Click on the Results tab in the left pane. Enter “fa” in the Filter box for a shortened list. Select the appropriate Month End Close reconciliation. Results are shown in the bottom pane.

Title: Fixed Assets Reconciliation DATE: 10/18/2016 Page 6 of 8

6.6 Click on the Export all tabs to Excel icon. Save the results to the reporting month folder.

Using naming convention 1 – ME Close Fixed Assets <Period Name>.xlxs. Ex: 1 – ME Close Fixed Assets AUG-19_FY-19xlsx.

6.7 Open the Fixed Assets results for the reporting month. Apply a filter and sort tabs using the following guide:

6.7.1 FA_vs_GL_out – AGENCY, TREASURY SYMBOL, FUND, GL_ACCOUNT, ORG

6.7.2 FA_vs_GL_CIP_out – AGENCY, FUND, ASSET_NUMBER

6.7.3 FA_vs_GL_JE_Resereve_out – AGENCY, FUND, ASSET_NUMBER

6.7.4 FA_vs_GL_Mass_Additions_out – BOOK_TYPE_CODE, FUND

6.7.5 FA_vs_GL_TRB_out – AGENCY, TREASURY_SYMBOL, FUND

6.8 Open the template for the reporting agency located at U:\.123 Recon\EDQ Recons\Fixed Assets\Templates. Enter the Period Name for the reporting period. Save As into the appropriate reconciliation folder located at U:\.123 Recon\EDQ Recons\Fixed Assets\.

When completed, your folder should look something like this:

Title: Fixed Assets Reconciliation DATE: 10/18/2016 Page 7 of 8

Alternately, open the prior month’s reconciliation and save as the reporting month.

Caution: if any changes to formulas or TAS/Funds are added to the reconciliation, ensure that you make the same changes to the template.

6.9 Go to the EDQ Fixed Assets results file. Filter the tabs to the reporting Agency, then Copy/Paste the data into the agency’s reconciliation per the following:

From EDQ Results To Recon Tab

FA_vs_GL_out EDQ Results starting at cell B9

FA_vs_GL_TRB_out Trial Balance starting at cell A4

FA_vs_GL_JE_Resereve_out JE Reserve Ledger starting at cell A6

FA_vs_GL_CIP_out CIP starting at cell A4

FA_vs_GL_Mass_Additions_out Unpst Mass Add starting at cell A4

6.10 Formulas are in the FA vs GL Summary tab that populates the data in the EDQ Results tab into the summary tab. Additionally, formulas in the tabs provide the totals used in the check figure areas of the summary tab.

6.11 Review the check figure areas of the FA vs GL Summary tab. Research and correct any differences.

6.12 After all reconciliations are complete, create a PDF file of the FA vs GL 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/Fixed Assets folder.

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

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

Title: Fixed Assets Reconciliation DATE: 10/18/2016 Page 8 of 8

7.0 Appendix: Delphi reports and prior month reconciliation.

Definitions:

Asset cost accounts include: 171100 Land and Land Rights

172000 Construction in Progress

173000 Buildings, Improvement, and Renovations

174000 Other Structures and Facilities

175000 Equipment

182000 Leasehold Improvements

Reserve accounts include: 171900 A/D on Improvements to Land

173900 A/D on Buildings, Improvements, and Renovations

174900 A/D on Other Structures and Facilities

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