Agresso Fixed Asset Module Implementation

1. Agresso Fixed Asset Module Implementation City of St. Albert 2010-2011 R

2. Introduction • Ed Kaemingh - Manager of Financial Operations • Carole Rowe - Manager of Application Services • Caleb Anderson - Business Analyst • 5 focus areas: - about the City of St. Albert - phase 1: tangible capital assets project - phase 2: FA module implementation - using the fixed assets module - challenges and lessons learned R

3. About the City of St. Albert K

4. About the City of St. Albert • located in Alberta, Canada • north west of Edmonton St. Albert St. Albert Edmonton Calgary Vancouver Edmonton Seattle Spokane K

5. About the City of St. Albert • over 60,000 residents and 22,000 land parcels • Alberta’s 5th largest city • approximately 300 km (188 mi) of roadway • median family income of 87,300 per year (2003) • employs up to 600 staff members • 32 staff members in Financial Services • founded as a town in 1904 • incorporated as city in 1977 K

6. About the City of St. Albert • high quality of life for residents • one of the safest communities in Western Canada • award winning cultural and recreational programs • attractive and extensive park system K

7. About the City of St. Albert K

8. About the City of St. Albert K

9. About the City of St. Albert • using Agresso since October 2000 (11 years) • current Agresso version: 5.5.3 • database: Oracle 10.2 • full financial suite including the modules G/L, A/P, A/R, Logistics, Purchasing, web reporting. • Agresso users across 8 main buildings - City Hall, Public Works, Transit, Emergency Services - 2 Recreation Facilities, Library, Human Resources K

10. About the City of St. Albert • over 57,000 assets: water meters, roads, buildings, ect • about $1 billion (CAD) worth of assets at cost • 13 groups of assets K

11. About the City of St. Albert • value of assets by asset groups K

12. Tangible Capital Assets Project phase 1 K

13. Tangible Capital Assets Project • prior to Agresso FA module implementation • new Public Sector Accounting Board standard • driver for TCA project was PSAB 3150 standard • mandatory accounting standard for municipalities • 2 year project to inventory and value assets K

14. Tangible Capital Assets Project • TCA project team: Finance R

15. Tangible Capital Assets Project • TCA project team: Operations and Engineering R

16. Tangible Capital Assets Project • 57,000 assets evaluated worth $986 million • evaluated over 100 years worth of assets • TCA project ran from Winter 2007 to Fall 2009 • TCA inventory was audited in Fall 2009 K

17. Tangible Capital Assets Project • assets were tracked using Excel spreadsheets • many thousands of spreadsheet rows of data • countless hours creating and editing spreadsheets • one person responsible for tracking and updating • needed to migrate to stable system for data mgt. K

18. Tangible Capital Assets Project • example of inventory spreadsheet K

19. Tangible Capital Assets Project • example of report spreadsheet K

20. ModuleImplementation phase 2 R

21. Module Implementation • fixed assets project team R

22. Module Implementation • fixed assets project schedule R

23. Module Implementation • data came from multiple information systems & departments • Capital Asset Accountant reviewed and organized financial information from an accounting perspective • Senior Business Analyst reviewed and organized information from a data management perspective R

24. Module Implementation • 57,327 assets loaded into Agresso • objective: to organize data so that information can be used efficiently - balance data file to accountant’s total - upload into Agresso - balance data file to Agresso totals R

25. Module Implementation • an efficient data structure was key to minimizing potential problems • effective data management reduced the chances of assets being out of balance with GL • asset data was being frequently changed due to asset additions, updates, migrations, and disposals R

26. Module Implementation 17 files (30 worksheets)  One Access Table  104 Upload Files With 55 columns 44 AT08 Basic Asset Info 49 AT09 Capitalize, Depreciate 11 BIF Flexi-Field Information R

27. Module Implementation • AT08 - Basic Asset Information - GL Information • AT09 - Original capitalization amount - Current accumulated depreciation • BIF Files for Flexi-Fields - Sundry information we identified R

28. Module Implementation Basic Asset Information (AT08) R

29. Module Implementation GL Information (AT08) R

30. Module Implementation Capitalization and Depreciation (AT09) R

31. Module Implementation Sundry Information for each Asset. BIF #1 BIF #2 BIF #3 BIF #4 R

32. Module Implementation • batch inputting was done using BIF • BIF is able to convert excel files into flat files • imported into Agresso via importing routines R

33. Module Implementation • test script templates provided by Agresso • testing done by many users during the project R

34. Using the Fixed Assets Module A

35. Using the Fixed Assets Module demo • fixed assets menu • used to access the fixed asset functions A

36. Using the Fixed Assets Module demo • creating a new asset • use the asset master file to create assets A

37. Using the Fixed Assets Module demo • capitalizing an asset • create a proposal and confirmation • half year rule A

38. Using the Fixed Assets Module demo • depreciating an asset • create a proposal and confirmation • straight line method A

39. Using the Fixed Assets Module demo • searching for asset transactions • use master file, browser enquiry, or reports A

40. Using the Fixed Assets Module demo • using other fixed asset functions • create from invoice, sales/disposal, reversals A

41. Using the Fixed Assets Module • Excelerator is used for most reports • summary of assets by: - activity & asset group - GL account & asset group • list of assets by: - asset group and sub-category - account type and asset group - net book value by asset ID for auditors - additions, sales , depreciation, reversals, regroupings A

42. Using the Fixed Assets Module • Excelerator report example: A

43. Using the Fixed Assets Module • browser reports created - general searches - simple lists of assets A

44. Using the Fixed Assets Module • browser reports created browser report added as a flexi-field A

45. Challenges and Lessons Learned A

46. Challenges • too many records - reports can take a long time to run - managing 57,000 assets can be difficult - 12 months of depreciation for each asset 56182 assets x 12 months = 674184 lines • small asset values • some assets are valued at $0.15 • rounding values during depreciation A

47. Challenges 63% 3.5% • assets with a replacement value of less than $10,000 account for 63% of the records, but only 3.5% of the total replacement value A

48. Solutions • reduce the number of records • - reduce the number of assets significantly by grouping (pooling) the assets A

49. Solutions • only 279 records are required to maintain the integrity of the calculation of depreciation • 406 records are required if we want to categorize by Major Asset Category • 1,078 records are required if we want to categorize by Minor Asset Category A

50. Solutions • we are currently maintaining information on 57,327 separate assets • location information would see the number of required assets decrease to about 20,000 A