Fixed Assets Project Overview& Current Status

December 3, 2012

1

City Edge Project Phases

1) Envision– What do we want the new system to look like?

– Interface for Data Conversion Design Logic

– Prototyping

2) Build– Fixed Asset Registry

– Table Set Up

– Business Scenarios

– Data Conversion (Priority per CGI)

– Script Writing

3) Achieve– Data Conversion & Reconciliation

– Report Writing

– Script Testing

4) Go Live– Refine Processes

– Resolve Functionality Issues2

Project Goals

Overall Vision:

Work Smart

Keep it Simple - Processes and Procedures

Logic Based

Transparency - Consistency

1) Complete clean up of Fixed Assets

• Reclassifications – would address in conversion for FY 11-12

• Non City Owned – would handle in FY 12-13

• Met with Larsen Allen staff to ensure handled properly for CAFR reporting

2) Standardization

• FA Registry Structure

• Naming Conventions

3) Improve Processes

• Capital Improvement Projects

• Annual Inventory

• CAFR Reporting

33

Envision PhaseDiscovery and Planning Phase – began work 2 years prior to start of City Edge Project

• Considerations and Research:•Administrative & Regulatory Items

•Budgeting: How C.I.P. will be budgeted for in future? Capital and Operation costs to Program.•How will purchases that meet the capitalization criteria be captured in the future financial software?•Financial reporting: statutory requirements GAAP, GASB, GFOA, FERC and other entities.•Fixed Asset Registry (FA Chart of Accounts) •Transparency and Accuracy: What you see is what we own.•Data extraction from Legacy System – finding patterns and developing crosswalks

•Process Items•Engineering Bid Sheets – Lump Sum items•How do we improve how the City of Mesa captures and reports Fixed Assets.•Department Level: What Fixed Asset data is needed?•Annual Inventory: Who, What, When, Where & How

•Began meeting with Departments to change current structure where possible and lay out future structure.

•Capitalization processes and standards in place for consistent collection of Fixed Asset data for the future.•Prototyping COM concepts and processes in AMS software.

4

Discovery Sessions and Departmental Meetings• We needed to know what the end users will be using the data stored in the FA system for or would like to

use it for.– What do departments currently track? Developed outlines to be used to structure new FA Registry.

• Provide a list of Assets tracked and any detail associated with those items. (Use GIS/CMMS)» Gas Utility – (completed 3 years prior to project, department refresher conducted)» Electric Utility» Water Utility» Wastewater Utility» Transportation» Transit (Excel)» Communications (Excel)» ITD (Smart System)» Parks and Recreation (Access Database)» Fleet (Fleet Focus)» Airport (Excel)» Engineering – bid sheets

– What types of data would be useful in order to perform an analysis for the department? Used input to develop FA Reports.• Assets by:

– Acquired Date (January 2011)– Type (Steel Pipe)– Size (2 inch)– Location (Water Treatment Plant, Well Site, Radio Site, Hangar, etc.)

• Reporting:– Grants – assets acquired with monies– Disposal of Grant assets

» Needed by date, asset type, description, funding source, associated with a specific Grant ID#?– Any statutory reporting requirements that FA data could facilitate with?

5

Departmental Input

• Learning their language

• Finding their needs

• Developing Registry:

• Give us your framework for asset tracking in GIS/CMMS• Identifiers between AMS and GIS/CMMS

• As Built Number

• Location by Grid

• Location Lists

See print outs:

Exhibit A - Electric Fixed Asset Tracking

Exhibit B - Transportation Grid

6

The FA Registry is a summarization of the detail used in the City’s capital asset planning and tracking systems. The FA Registry database represents all assets

acquired through a series of expenditures, contributions or adjustments.

Budget & Engineering

GIS

CMMS

Fixed Asset

Registry

7

Build Phase• Fixed Assets is affected upstream and downstream. Fixed Asset Business

Processes developed and progressed as other areas moved forward with their development and setup. (i.e. Purchasing commodity set up for one time purchases, Grants & CIP Program-Phase, Accounts Payable, Chart of Accounts and Financial Reporting)

• Advantage System Setup Required– Fixed Asset Registry Hierarchy

» Chart of Accounts for Fixed Assets

• FATP, FAGRP, FAGCS, FAGCT, CUSD, LOC, SLOC, FACLG, FADM

– Commodity table Fixed Asset flagged (COMMFA)

– Commodity Threshold (COMM)

– SPEC Table – Balance Sheet Accounts and Object posting

– SOPT – System Wide Options Table

– FACC – Capitalization Criteria Threshold

– Data Conversion and Reconciliation

• Additional Table knowledge– PSCD – Posting Codes

– BSA – Balance Sheet Accounts

– OBJ – Object Codes

– ACT – Activity Codes 8

Tables Set Up in Advantage Software

TABLE CODE TABLE DESCRIPTIONFINANCIAL

EFFECT USE

FAGCT FA GROUP CATEGORY Y Populated with Group Category – high level: associated with FA Class and FA Group

FAGCS FA GROUP CLASS Y Populated with Group Class - associated with Fixed Asset Group

FAGRP FA GROUP Y Populated with FA Group - lowest detail level of FA Registry Set Up

FATP FA TYPE YLand, Buildings, Other Improvements, Machinery & Equipment, Infrastructure, Intangibles, Water Rights, Non-Depreciable Infrastructure, Vehicles & Rolling Stock

CUSD CUSTODIAN N Non financial Fixed Asset Registry information – Inventory purposes

LOC LOCATION N By Department ownership at Responsibility Center level

SLOC SUB LOCATION N By Department ownership at Responsibility Center level – associated with Location

COMM COMMODITY TABLE Y Flagged Commodity Codes as eligible to become a Fixed Asset for One Time purchases

COMMFACOMMODITY FIXED ASSET THRESHOLD Y

Set Up Commodity Thresholds ($3,500) – Purchase must meet this minimum in order to qualify to become a Fixed Asset. Thresholds can also be set up by Object Code.

SOPTSYSTEMWIDE OPTIONS TABLE Y Set up for automatic Multiple Shell generation, Link to Program, allow Memo Classification

SPEC SPECIAL ACCOUNTS Y Used based on inference from Posting Code

PSCD POSTING CODE Y 1st Inference can be from Fixed Asset Type Table (FATP) or System Wide Table (SPEC)

FACCFA CAPITALIZATION CRITERIA Y Set up FA Capitalization Criteria for Program/Phase associated with a pending Fixed Asset ($3,500 threshold)

FACLG FA CATALOG N Infers Depreciation Method and Useful Life - to one Group

FADMFA ACQUISITION DISPOSITION METHOD N Populated with available Dispositions Methods that can be used on FD document

9

Advantage FA Hierarchy Roll Up stored in multiple FA Tables

• Registry

• Financial

– Fixed Asset Type FATP – determines if Depreciable or Non-Depreciable

» FA Group FAGRP – set up for visual identification (5 characters)

» FA Class FAGCS

» FA Category FAGCT

• Non-Financial

– Custodian CUSD – 3 numeric characters

– Location LOC – tied to Department

– Sub-Location SLOC – tied to Location by Department

– Catalog FACLG – functioning at Go Live, per Ben Pomeroy always done after Go Live

See print outs:

Exhibit C – Electric Infrastructure FAGRP List

Exhibit D – Legacy Electric Property Classification File Listing Report

10

Legacy System Assessment• $3.8 billion in assets

– 30,441 lines of data– $2.6 billion in Mass Plant (16,271 lines of data)– $1.2 billion in Single Tagged Assets (14,170 lines of data)

• Dumping Ground– 30+ years of system users and different ideas on how to collect data

• No processes or standards in place• No cross check processes between County Records and Departmental Records

– County Land Records» Land owned not on books» Land that was owned by City but titled incorrectly

• South Center Complex• Basin in NW Mesa

– Department Shadow Systems» GIS» CMMS» Access Database» Fleet

• Lack of consistency• Joint Venture (Non City Owned) Assets on Books• Incorrect Classifications• Incorrect Useful Life

– Example: Electric Transformers set at 50 years, closer to 20-25 years• Overall need to clean data in Legacy system

– Unable to do a lot of the work due to crashing legacy system• Single Asset with multiple owners

– Legacy system based on funding source• Regulatory issues

– Non City Owned assets on books• Reporting

– Capital Leases– Joint Ventures

• No crosswalk between Accounting dollars and GIS/CMMS department systems.• Biggest hurdle – GIS, CMMS versus Fixed Asset Database

– Naming conventions for current Mass Plant assets– Locations– Asset Types– Asset Sizes

• Usable financial data for departments and accounting purposes.11

Legacy Assets

Mass Plant – “The Black Hole”

– Approximately 70% of City asset values No Location No Asset Detail Multiple Acquisition Years

– Oldest – Base Asset– Additional Years – Component of Base

Identifiers - Used inconsistently Class

• Class• Location• Ownership

Type• Type• Class• Size• Location

Size• Size• Type• Location• Ownership

Single Tag– Approximately 30% of City asset values

Identifiers - Used inconsistently

Class, Type & Size

• Same issues as Mass Plant

Asset Description

• Description of Asset

• Address

Model

• Model Number

• Plat Number for Land

• Asset Description

Serial Number

• Serial Number

• Plat Number for Land

• Asset Description

Manufacturer

• Manufacturer

• Builder

• Who purchased from

• Title company

Responsibility Center

• Non Ownership – for Inventory purposes only

12

Legacy Database Structure• Due to inconsistent use of Legacy system fields, steps in which data would be converted had to be

established. This took approximately 3 months (July – September 2011) of collaborative work between Property, IT (Chuck Craig) and CGI (Ben Pomeroy and Kerry Robinson) to develop. (see print out - Exhibit E)

• Property Sub Ledger Data Base Hierarchy (9 different Property Tables)– PT 0 – Root Segment (Class, Type & Size)

Mass Plant (Interface 1)

• PT 1 – Mass Plant Acquisition Year

• PT 2 – Mass Plant Account (Fund, Enterprise & Object)

• PT 3 – Mass Plant Contribution Fund

Tagged (Interface 2)

• PT 5 – Specific Identification Tag Number

• PT 6 – Specific Identification Account (Asset Descriptive Detail Fields, Location, Fund, Enterprise & Object)

• PT 7 – Specific Identification Contribution fund

– PT 8 – Beginning Balance (Total Asset Cost)

– PT 9 – Fixed Asset Accumulators (Depreciation – Interface 3 & 4)

13

Fixed Asset Interface for Data Conversion

– Unable to begin work until Chart of Accounts structure was finalized – continued to change even after conversion.• Crosswalk for accounting purposes (based on Accounting Department’s vision for

reporting purposes).– Fund (FUND)– Enterprise (SFUND, DEPT, UNIT)– Object (FATP, FAGRP)

• Crosswalk for Fixed Asset Registry – this work was began 2 years prior– Class (FATP, FAGRP, FAGCS, FAGCT, LOC, SLOC)– Type (FAGRP, FAGCS, FAGCT, LOC, SLOC, Component of Base Asset and data placed in additional

descriptive field due to AMS not having a Material Type field that could be reported on)– Size (FAGRP, FAGCS, FAGCT, LOC, SLOC and data placed in additional descriptive field due to AMS not

having a Size field that could be reported on)– Responsibility Center (CUSD)– Location (FAGRP, FAGCS, FAGCT, LOC, SLOC)– Useful Life – Contributing Fund (Populated in description field due to Disposal posting)

• No Crosswalk capability for Single Tag fields due to inconsistent usage.– Asset Description, Model, Serial Number and Manufacturer

» Object 18100 – Land: Model and Serial Number converted to Plat Number

• Additional Advantage data fields:– BSA Object Code– Activity– Appropriation Unit (1 at Header Level for Depreciation Expense posting)– Responsibility Center Posting

14

Legacy Asset Structure

Wastewater Singled Tagged

•Object 18600: Infrastructure

–Class 8740: Odor Control

• Types:

– 01 Bio Filter

– 02 Carbon Filter

– 03 Blower

– 04 Scrubber

– 05 H2S Meter

–Class 8741: Odor Control

• Types:

– 01 Media / Grating

– 02 Low PH Water Drain

– 03 Fiberglass Piping

– 04 Fence & Walls

– 05 Structure*

AMS Asset Structure

• FA Type F: Infrastructure (depreciable)

• FA Category: F6000

Water Treatment Plant

• FA Group:F6028 – Bio Filter

F6044 – Carbon Tower

F6029 – Blower

F6233 – Scrubber Vessel

F6185 – Aeration Basin

F6245 – Building / Structure (per WW)

F6100 – Drain

F6210 – Piping

F2100 – Fence & Walls ALL Infrastructure

(Size: 1, Class/Type: 22)

F6245 – Building / Structure

• FA Location: S140 -W BASELINE RD ODOR CONTROL FACILITY

15

Sample Single Tag Data Legacy SystemFund Ent Object Class Type Size Tag_no Item_desc RC Class_Desc Type_Desc Size_Desc Loc_Desc

20 848 18600 8740 1 0 150745 BIOFILTER 545

ODOR

CONTROL BIOFILTER

CITY

FUNDED

BASELINE

ODORCONTROL

20 848 18600 8740 2 0 150742 CARBON FILTER 545

ODOR

CONTROL CARBON FILTER

CITY

FUNDED

BASELINE

ODORCONTROL

20 848 18600 8740 3 0 150740 BLOWER#1 545

ODOR

CONTROL BLOWER

CITY

FUNDED

BASELINE

ODORCONTROL

Advantage – New FA HierarchyFA_GRP FAGRP_NM FAGRP_SH_NM FA_TYP FAGRP_CLS FAGRP_CAT

F6002 W Actuator WActuator F F6000

F6005 W ATS Automatic Transfer Switch WATS F F6000

F6010 W Baffle WBaffle F F6000

F6015 W Bar Screen WBarScreen F F6000

F6020 W Battery Bank WBatterBank F F6000

F6025 W Belt Filter Press WBeltFltrPrs F F6000

F6027 W Bin WBin F F6000

F6028 W Biofilter WWWBioFilter F F6000

F6029 W Blower WWWBlower F F6000

F6030 W Blower Aeration WBlowrAeratn F F6030 F6000

F6035 W Blower Backwash Air WBlwrBWAir F F6030 F6000

F6040 W Blower CL2 Scrubber WBlwrCL2Scr F F6030 F6000

F6042 W Breaker WBreaker F F6000

F6044 W Carbon Tower WWWCarbonTower F F6000

F6045 W Centrifuge W Centrifuge F F6000

F6050 W Collector Bar Screen Rake WCollBrScRak F F6000

F6055 W Collector Sludge WCollSludge F F6000

F6060 W Compactor WCompactor F F6000

F6070 W Compressor WCompressor F F6000

F6075 W Control Valve WWWControlValve F F6000

16

Legacy Asset Structure

Water Mass Plant Item•Object 18600: Infrastructure

–Class 6549: Water Mains

• Types:

– 10 Water Mains - ACP

– 20 Water Mains – C.I.P./C.I.

– 30 Water Mains - DIP

– 40 Water Mains Copper

– 50 Water Mains Glvzd Iron

– 55 Water Mains – RCP

– 65 Water Mains – SCP

– 35 Water Mains – CP

– 80 ABS Truss Pipe

– 25 Steel/Coated Steel

Wastewater Mass Plant Item–Class 6811: Reclaimed Water Valves

• Types:

– 10 Valves on Reclaim Mains

– 01 ARV

– 30 DIP

AMS Asset Structure

• FA Type F: Infrastructure (depreciable)

• FA Group:F4510 – System Main

» Material Type and Size populated in AMS descriptive field

» As-Built Number from Engineering & used in GIS/CMMS system populated in descriptive field

• FA Location: WDIS – Water Distribution System

• FA Type F: Infrastructure (depreciable)

• FA Group:F4319 – Reclaimed Valve

» Material & As-Built Number populated same as above

• FA Location:

RDIS – Reclaimed Water Distribution System

17

Legacy Asset Structure

Water Mass Plant•Object 18600: Infrastructure

–Class 8700: Wells

• Types:

– 01 Hole

– 09 Fence & Wall

– 03 Pump/Motor/Column

– 04 Switchgear

– 05 RTU

– 06 Mag Meter/Converter

– 07 Pressure Transmitter

– 08 Acoustical Enclosure

– 10 Structure

– 02 Drainage System

– 11 Chlorination System

– 12 Security Locks

AMS Asset Structure

• FA Type F: Infrastructure (depreciable)

• FA Category: F6000

Water Treatment Plant

• FA Group:F6162 – Hole

F2100 – Fence & Walls ALL Infrastructure

F6233 – Scrubber Vessel

F6235 – Washer

F6220 – PLC Program Logic Controller

F6185 – Structure – Aeration Basin

F6225 – Valve – Globe

F6245 – Building / Structure

F6103 – Drainage System

F6258 – Chlorine System

• FA Location: Wxxx – each Well given numeric location

18

Sample Mass Plant Data Legacy SystemFund Ent Object Class Type Size Class_Desc Type_Desc Size_Desc

Year

Acquired

20 838 18600 8700 6 113.57 WELLS HOLE DWWELL#22 1/2/1992

20 848 18600 6811 30 8.00

RECLAIMED WATER

VALVES DIP 8 INCHES 7/2/2007

20 848 18600 6811 30 30.00

RECLAIMED WATER

VALVES DIP 30 INCHES 7/2/2007

20 838 18600 6549 10 4.00 WATER MAINS

WATER MAINS –

ACP 4 INCHES 7/2/1967

20 838 18600 6549 10 4.00 WATER MAINS

WATER MAINS –

ACP 4 INCHES 7/2/1968

Advantage – New FA HierarchyFA_GRP FAGRP_NM FAGRP_SH_NM FA_TYP FAGRP_CLS FAGRP_CAT

F4315 RW Encasement RWEncasement F F4300

F4316 RW Manhole RWManhole F F4300

F4318 RW Main RWMain F F4300

F4319 RW Valve RWValve F F4300

F4500 W Encasement WWWEncasement F F4400

F4503 W Force Main WaterForceMain F F4400

F4510 W System Main WWW Main F F4400

F4520 W Manhole (All Types) WWWManhole F F4400

F4530 W Meter WWW Meter F F4400

F6160 W Heater Exchanger WHtrExchngr F F6000

F6162 W Hole WWWHole F F6000

F6165 W Heater / Chiller WHeaterChillr F F6000

19

Developing a Conversion Design

Kerry, Email Dated: 3/05/2012

I’ve been working on the Bucket logic over the weekend. (see print out: Exhibit F - Design Logic for Mass Plant)(Single Tags – Line by Line)

RC in the Legacy system does not always infer ownership. RC is more relevant to Custodian.

Enterprise in the Legacy system infers ownership ALWAYS and where the depreciation should be hitting.

Thanks, Laura

From: Robinson, Kerry [mailto:kerry.robinson@cgi.com] Sent: Monday, March 05, 2012 7:31 AMTo: Laura MorrisSubject: RE: Mass Plan and Tagged asset

Aloha Laura,

Thanks for your comments on the Tagged Asset Conversion Detailed Design….I’m updating the document on Sharepoint with your comments, but I have one question regarding the RC crosswalk….I thought for Tagged assets we were going to crosswalk the RC code to Department and Unit because the RC code is populated for Tagged assets; however we were going to use the Enterprise code to crosswalk to Department and Unit for Mass Plant assets because the RC code is not populated for any of them….Is there a reason why we shouldn’t use the RC code to crosswalk to Department and Unit for tagged assets? Is it outdated, wrong, etc?....let me know….

Mahalo,

Kerry

20

Issues Converting Joint Venture Assets

From: Robinson, Kerry [mailto:kerry.robinson@cgi.com] Sent: Tuesday, March 13, 2012 5:16 PMTo: Laura MorrisSubject: FW: useful life

Laura,

The attached listing of Classes and Types do not have a useful life; however we must have a useful life larger than zero for all assets assigned to an FA Type that is depreciable….The only asset types that don’t require a useful life are L (Land), N (Non-Depreciable Infrastructure), W (Water Rights), and C (Construction in progress)….all other types require a useful life….

Any questions let me know….

Thanks,

Kerry

21

First Test of “Bucket” Interface for ConversionFrom: Robinson, Kerry [mailto:kerry.robinson@cgi.com] Sent: Wednesday, March 14, 2012 5:04 PMTo: Laura Morris; Tyra GlademCc: Evan Allred; Chuck Craig; Benjamin Pomeroy-CGISubject: Mass Plant Conversion

Aloha Laura & Tyra,

Great news, I’ve loaded/submitted the Mass Plant assets into FINTEST….we created a total of 1649 FA documents and only 25 rejected….thanks for all of your help with the crosswalks and updates to the reference tables….the 25 rejects were related to the following types of errors: (0.0151% error rate)

Missing/Blank FA Group on Mass Plant spreadsheet

Missing/Blank Location on Mass Plant spreadsheet

Invalid Location

Invalid Unit

Summary Stats are as follows:

1649 assets

8456 components

Total Asset Cost = $1,674,134,850.36 (keep in mind we haven’t converted accumulated depreciation yet, so this amount doesn’t represent net book value)

I was able to reconcile the cost balances of the converted assets from the Mass Plant tables to the amounts in Advantage Financial….next steps:

Now that the assets have posted in Advantage Financial, please review a random sample of the assets and verify that they converted as expected

I will convert the Accumulated Depreciation (FE documents) later this week

Once again, great job Laura & Tyra….next up, Tagged Assets !

Thanks,

Kerry

22

Mock #1 Conversion Results

Aloha Laura, Email Dated June 09, 2012

Here are the Mass Plant & Tagged asset load results for Mock #1:

– Mass Plant Assets FA documents – 2231 documents submitted successfully; 26 rejected….rejects were as follows: (0.0116% error rate)

• Useful Life cannot be blank for an asset that is depreciable (1st worksheet)

• Invalid Location (2nd worksheet)

– Mass Plant Assets Accumulated Depreciation FE documents – 2218 documents submitted successfully; 9 rejected….rejects were as follows:

• FE rejected because corresponding FA rejected

– Tagged Assets FA documents – 12643 documents submitted successfully; 273 rejected….rejects were as follows: (0.0215% error rate)

• Useful Life cannot be blank for an asset that is depreciable (1st worksheet)

• Invalid Location (2nd worksheet)

• Invalid FA Group (3rd worksheet)

• Invalid Activity (4th worksheet)

• Invalid Sub Location (5th worksheet)

• Invalid Custodian (6th worksheet)

– We also had 2 FA documents that could not be imported because the dates were too old…..

• Tag# 504142 Date = 1198-06-01

• Tag# 503046 Date = 1677-02-01

– Tagged Assets Accumulated Depreciation FE documents – 11540 documents submitted successfully; 202 rejected….rejects were as follows:

• FE rejected because the corresponding FA rejected

Any questions let me know….

Mahalo,

Kerry

23

Data Conversion Reconciliations

From: Laura Morris Sent: Sunday, June 24, 2012 12:57 PMTo: Kerry RobinsonCc: Paula Novacek; Benjamin Pomeroy-CGI; Chuck Craig; Reza GhorbaniSubject: FW: Data conversion Reconciliation

Kerry

Attached is a reconciliation of the Asset conversion (I haven’t done Depreciation due to the differences).

The first TAB – Summary – look at the yellow hi-lighted columns, there is something BAD wrong with the totals by FA type versus what is in the Legacy System. Also, provided tabs showing where the Re-class amounts were derived from (these match up with the Data Conversion Spreadsheets-these amounts may vary a little but not much because conversion crosswalk used Legacy data spreadsheet ending February 2012).

Hoping that the totals you have been giving me are just wrong. I have cross checked the spreadsheets for data conversion in theBig 5 area. The spreadsheets show to have been changed 6/24/2012 but that was me locating and saving sort for the rejected documents…I did not change anything else.

At this time we cannot move forward with Go Live until resolved.

Let me know where we go from here – thanks, Laura

24

Data Conversion Reconciliations

Awesome….Now I can really enjoy my vacation !….Mahalo Laura….

From: Laura Morris [mailto:Laura.Morris@mesaaz.gov] Sent: Thursday, June 28, 2012 12:16 PMTo: Robinson, KerrySubject: Wanted you to be the first to know - ALL IS WELL IN CITY OF MESA FIXED ASSET LAND!!! I have tied out all conversion totals - Thanks, Laura

25

Reconciliation Totals

Legacy System

Fund Reconciliation Converted Totals

20 Assets 2,021,363,994.49 2,021,363,994.49

Accum Depr 706,937,124.74 706,937,124.74

70 Assets 8,998,826.25 8,998,826.25

Accum Depr 5,737,236.94 5,737,236.94

992 Assets 1,743,971,047.23 1,743,971,047.23

Accum Depr 578,551,108.23 578,551,108.23

14,874 Base Assets

Total Assets 3,774,333,867.97 3,774,333,867.97

Total Accum Depr 1,291,225,469.91 1,291,225,469.91

26

Advantage Fixed Asset Documents• Documents

• FA - Acquisition– FAS prefix: System generated FA Shell from disbursement

» Commodity Code» Threshold

• FC – Cancellation• FD - Disposal• FE – Manual Depreciation• FI – Increase/Decrease Value• FM – Modification (non accounting)• FP – Change Sale Price• FS – Sale of a Fixed Asset• FT – Transfer within same Fund• FX – FA Type Change• ME – Mass Depreciation (system generated from Batch Job)

• Driven by: Event Type

Posting Code

27

Sample of Event Types and Posting Codes

Event Type NameType Code

Event Category Posting Pair Name

Posting Pair Debit Name

Posting Code Debit

Debit Offset Credit Name

Posting Code Credit

Credit Offset

Acquire a Fixed Asset FA01 FA Shell to Asset A Fixed Asset F002 N Asset Offset F013 Y

Acquire a Fixed Asset FA01 FA Reversal B Exp Offset-FA F010 Y Pending FA F001 N

Acquire a Fixed Asset FA01 FA Contributed FA D Fixed Asset F002 N Contr to FA F011 N

Acquire a Fixed Asset FA01 FA FA Adjustment E Fixed Asset F002 N Net Adj to FA F012 Y

Better a Fixed Asset FA02 FB Shell to Asset A Fixed Asset F002 N Asset Offset F013 Y

Better a Fixed Asset FA02 FB Reversal B Exp Offset-FA F010 Y Pending FA F001 N

Better a Fixed Asset FA02 FB Contributed FA D Fixed Asset F002 N Contr to FA F011 N

Better a Fixed Asset FA02 FB FA Adjustment E Fixed Asset F002 N Net Adj to FA F012 Y

Record Depreciation FA03 FE Non-Memo Depr A Depr Expense F020 N Accum Depr F040 Y

Dispose of a Fixed Asset FA04 FD Depr Catch Up A Depr Expense F020 N Accum Depr F040 Y

Dispose of a Fixed Asset FA04 FD Rvrs Accm Depr B Accum Depr F040 Y Fixed Asset F002 N

Dispose of a Fixed Asset FA04 FD Back-out Sale C Sale FA Rev F030 N Fixed Asset F002 N

Dispose of a Fixed Asset FA04 FD G/L Expense D Fixed Asset F002 N G/L FA Exp F021 N

Dispose of a Fixed Asset FA04 FD G/L Revenue E Fixed Asset F002 N G/L FA Rev F031 N

Dispose of a Fixed Asset FA04 FD Dispose Memo F Offset-MFA M002 N Memo Asset M001 N

Dispose of a Fixed Asset FA04 FD Rvrs Memo AD G Accum Depr-MA M003 N Offset-MFA M002 N

Modify Fixed Asset Non-Acctg FA05 FM N/A N/A N/A

Transfer a Fixed Asset FA06 FT Non-Memo A Depr Expense F020 N Accum Depr F040 Y

Transfer a Fixed Asset FA06 FT Responsibility C N/A N/A

Transfer a Fixed Asset FA06 FT Internal G/L D Int FA Expense F022 N Int FA Revenue F032 N

Change Value of Fixed Asset FA07 FI Contra Expense A Fixed Asset F002 N Asset Offset F013 Y

Change Value of Fixed Asset FA07 FI Contributed B Fixed Asset F002 N Contr to FA F011 N

Change Value of Fixed Asset FA07 FI Adjustment D Fixed Asset F002 N Net Adj to FA F012 Y

Change Status of a Fixed Asset FA08 FX Status Change A Fixed Asset F002 N Fixed Asset F002 N

Change Status of a Fixed Asset FA08 FX Contributed D Contr to FA F011 N Contr to FA F011 N

Change Selling Price of a FA FA12 FP FADM Revenue A Sale FA Rev F030 N G/L FA Rev F031 N

Change Selling Price of a FA FA12 FP FADM Expense B Sale FA Rev F030 N G/L FA Exp F021 N

Mass Depreciation FA13 ME Non-Memo Depr A Depr Expense F020 N Accum Depr F040 Y

Unpend a Fixed Asset Shell FA14 FAUP Reversal A Exp Offset-FA F010 Y Pending FA F001 N

Acquire a Fixed Asset Internally FA27 FIA Contributed FA D Fixed Asset F002 N Contr to FA F011 Y

Acquire a Fixed Asset Internally FA27 FIA FA Adjustment E Fixed Asset F002 N Net Adj to FA F012 Y

Acquire a Fixed Asset Internally FA27 FIA Internal G/L Exp F Int FA Expense F022 N DTF/Int FA Sale F004 N

Acquire a Fixed Asset Internally FA27 FIA Historical Cost G Fixed Asset F002 N Accum Depr F040 N

Better a Fixed Asset Internally FA28 FIB Contributed D Fixed Asset F002 N Contr to FA F011 Y

Better a Fixed Asset Internally FA28 FIB FA Adjustment E Fixed Asset F002 N Net Adj to FA F012 Y

Better a Fixed Asset Internally FA28 FIB Internal G/L Exp F Int FA Expense F022 N DTF/Int FA Sale F004 N

28

FA Document Levels• Header – stored on FARHDR and FAHIST Table

– Responsibility Center (owner)

• General Info – Event Type which drives Posting Codes

• Fund, Sub Fund, Department, Appropriation Unit 1 – depreciation expense. Object & Activity (Disposal posting lines will pull in this Accounting String)

• Component –stored on FARCOMP and FAHIST Table (asset detail)

– General Info – Commodity – populated from Purchase Order (PO)

– Acquisition Details – Date Acquired, Acquisition Method that drives Posting Code Pair

– Location (SLOC), Classification (FAGRP) & Depreciation

• Accounting – stored on JFACJ & JFAAJ Table (funding source)

– Fund Accounting – populated from Accounts Payable Disbursement (AD), Property cannot change Accounting lines. System gives document chain references for research. Appropriation Unit 2 – capital expenditure.

– Depreciation Indicator – Responsibility Center (Header RC) or Fund (Accounting Line)

• Posting – stored on JFACJ & JFAAJ Table

– Document ID, Event Type and Posting Code29

Scripts• Based on current and future City of Mesa Business Scenarios

– Total of 139 Scripts: Team Lead and one other staff member (approximately 8% of overall script writing)

• Developed (naming convention by Document Code)

• Wrote Gold Standard

– Wrote / Delegated variations of Gold Standard

• Tested (IST and UAT) – ensure system is posting correctly based on Event Type, Posting Codes & Table Set Ups

• Review

• Update

• Re-test and Finalize

• Phase 0

– Table Set Up

• 13 Scripts

• Phase 1

– Acquisitions

• 48 Scripts

• Phase 2

– CIP – Construction

• 6 Scripts (37 pages, 157 steps)

– Program Set Up

– Purchasing

– Accounts Payable

– Fixed Asset

• Phase 4

– Betterments, Modifications, Depreciation, Sales, Disposals, Transfers and all other business scenarios

• 72 Scripts

Adding Departmental Processes as set up 30

Go Live• Accomplished Goals – worked smart with limited resources – pride in development process

– Reclassifications• Journal Voucher for CAFR presentation

– Standardization of Fixed Asset Registry and naming conventions• Visual recognition

– Improved C.I.P. processes– CAFR Reporting

• Completed– Parameter and Table set up to accurately collect data in Fixed Asset Registry. – Data Conversion, Reconciliation and Re-Class JV for CAFR.– Scripts based on Business Scenarios – used known COM business activity to prototype

system set up.– SQL file ran to correct Activity – could not enter any asset retirements until done.– Data Conversion correction of errors by CGI – unpopulated Sub Fund, Location and Sub

Location– SQL to add necessary fields to be populated due to Chart of Accounts change for

budget purposes.

• Software Functionality Issues reported and completed – FACPER Table

– FA Document at Header Level – Object Drop Down List populated Sub Object

31

To Be Done• Annual Inventory – no process improvement due to AMS System

– Share Point Site needs to be developed, implemented and departments trained.

• FileNet– Statutory Requirement: Fixed Asset documents need to be retained until asset is disposed

of.• Duplication of Accounts Payable Invoice attachment.

– Add Identifiers to A/P scanned documents

• Mass Depreciation (Accounting Department / Ed Quedens will need to sign disposals)– Initially set up non-depreciable FATP – J for Joint Ventures, needed accounts set up in COA,

this would have allowed Mass Depreciation to be run until assets were removed.– Cannot run batch job until Non City Owned Assets are retired from City of Mesa books: this

could not be done during data conversion for audit trail purposes and affect on Beginning Balances.• Joint Venture and Other Non City Owned

– Need to be provided with the Joint Venture Funds Beginning Balances for City of Mesa» Audit purposes – will have to tie retirement amount, currently back out for CAFR

– Balance Sheet Accounts– Disposal Form Approval– Journal Voucher to back out accounting impact: which accounts to hit– Explanation: Auditors and CAFR

• CAFR Capital Outlay (Accounting Department)– How will expended amounts be reconciled? Develop Capital Outlay reconciliation process.

• Capital Leases (Accounting Department)– Future Accounting practices

32

Future Reporting for Joint Venture Assets• City of Mesa Administrated – Greenfield Water Treatment Plant and TOPAZ

– Track within new Funds

• Regionally Administrated – Light Rail Transit, SROG,– Track as depreciable Memo Assets

• No Accounting Impact• Set Up Memo Asset Fixed Assets Registry

– FATP– FAGCT– FAGCS– FAGRP

• Balance Sheet Account Set Up• No Accounting Impact

• Moving Forward Need for Change– Improve CAFR process – Transparency – what is seen is what is owned– Logic Based and easily replicated– Industry Standard

33

Accounting Impact for Joint Venture and NCO Disposals

FINDEV - Joint Venture Asset Disposal Testing

Fixed Asset - Disposition - Asset needs to have Depreciation caught up prior to disposal (this will be the normal situation for the JV Assets)

BSA BSA

Event Type - FA04 Asset Deprec Static Codes

Disposal Method - *(any method) B - Buildings 1143 1150 9403 - Revenue: Gain/Loss on Sale of Property

F - Infrastructure 1145 1151

T - Intangibles 1146 N/A

M - Machinery & Equipment 1147 1153 9011 - Depreciation Expense

O - Other Improvements 1148 1154

V - Vehicles 1149 1155

R - Rolling Stock 1149 1155 ????? - Offset Account to remove accounting effect

W - Water Rights 1158

L - Land 1157

PRIOR TO ASSET DISPOSAL - Asset Details

Example: Museum Artifact - FA0000002 Comp 0001

FA Type M - Machinery & Equipment

Total Asset Value $ 874.94

Total Accum Depr $ -

Net Book Value $ 874.94

ACCOUNTING EFFECT - FD Document processed to dispose of above asset

Accounting Line Posting Code Posting Pair COA COA Name Debit Credit

Line 1 Removed Fixed Asset from Books F002 E BSA - 1147 Machinery & Equipment - Asset $ 819.10

Line 2 Removed Accum Deprec from Books F040 B BSA - 1153 Machinery & Equipment - Accum Depr $ 55.84

Remove Fixed Asset from Books F002 B BSA - 1147 Machinery & Equipment - Asset $ 55.84

Line 3 Current Year Depreciation Expense F020 A Object - 9011 Depreciation Amortization $ 55.84

Deprec Expense - Catch up Depreciation on Asset F040 A BSA - 1153 Machinery & Equipment - Accum Depr $ 55.84

Line 4 Gain / Loss on Sale of Property F031 E Revenue - 9403 Revenue from the Sale of Property $ 819.10

Need to perform JV to removed Current Year Accounting effect for Depreciation Expense & Gain/Loss Revenue

Current Year Depreciation Expense F020 A Object - 9011 Depreciation Amortization $ 55.84

Gain / Loss on Sale of Property F031 E Revenue - 9403 Revenue from the Sale of Property $ 819.10

Offset Account ??? $ 874.94

34

Fixed Asset Reconciliation for CAFR

• Legacy System– Capital Outlay in one Bucket for Capital Purchases

• CWIP +/- Transfers

• AMS Advantage Software– Total Capital Outlay – where derived from? Appropriation Unit involved?

• CWIP• Fixed Asset Cancellation• One Time Purchases• Un-Pend Fixed Asset Shell• Manually Created Assets

– System functioning as designed – User error in entry

– Beginning Balances• Assets + CWIP + Fund Balance = Contributions + Accumulated Depreciation

35

Capital Leases for Fixed Assets

• Legacy System– Assets were placed on books

• Project / Work Order shows negative balance in CWIP representing amount still owed on Capital Lease– Each Payment towards Capital Lease reduces negative amount until paid in full

– Project / Work Order shows positive balance in CWIP• Assets purchased with Capital Outlay for current year are entered on a GAAP basis• Once Capital Lease is paid in full then dollars in CWIP will be moved to books as Assets

– CAFR Reporting• Schedule 35 – CWIP Activity

• AMS Advantage System– How will these be converted and handled?

– CAFR Reporting?

36