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 • Agresso fixed asset module implementation • focus areas: about the City of St. Albert tangle capital assets project 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 Edmonton Calgary Vancouver Seattle Spokane K

5. About the City of St. Albert • over 60,000 residents and 22,000 land parcels • Alberta’s 5th largest city • covers an area of 36 km2 or 14 mile2 • 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 K

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

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

8. About the City of St. Albert K

9. About the City of St. Albert • Using Agresso since October 2000 (11 years) • Current 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, RCMP, recreation facilities, Library, Human Resources). K

10. Tangible Capital Assets Project K

11. Tangible Capital Assets Project • prior to Agresso FA module implementation • new Public Sector Accounting Board standard • mandatory accounting standard for municipalities • 2 year project to inventory and value assets • 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

12. Tangible Capital Assets Project • assets were tracked using Excel spreadsheets • many thousands of spreadsheet rows of data • 189 hours spent creating the spreadsheets • one person responsible for tracking and updating K

13. Tangible Capital Assets Project • example of fixed asset inventory spreadsheet K

14. Tangible Capital Assets Project • example of fixed asset report spreadsheet K

15. ModuleImplementation R

16. Module Implementation • fixed assets project team R

17. Module Implementation • fixed assets project schedule R

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

19. 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 K

20. 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 asset additions, updates, migrations, and disposals K

21. Module Implementation 17 files (30 worksheets)  One Access Table  104 Upload Files With 55 columns Not a Normalized Table 44 AT08 Basic Asset Info 49 AT09 GL Information 11 BIF Flexi-Field Information R

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

23. Module Implementation Basic Asset Information (AT08) R

24. Module Implementation GL Information (AT08) R

25. Module Implementation Capitalization and Depreciation (AT09) R

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

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

28. Module Implementation • test script templates provided by Agresso • testing done by many users during the project • multiple issues found during the testing R

29. Using the Fixed Assets Module A

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

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

32. Using the Fixed Assets Module demo • capitalizing an asset • create a proposal and confirmation A

33. Using the Fixed Assets Module demo • depreciating an asset • create a proposal and confirmation A

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

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

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

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

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

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

40. Challenges and Lessons Learned A

41. Challenges • too many records - reports take a long time to run - reporting on 57,000 assets can cause errors • small asset values • rounding errors during depreciation • some assets are valued at $0.15 A

42. Solutions • reduce the number of records • - system had difficulty with 57,327 records and assets with very small values • - reduce the number of assets significantly by either grouping the smaller assets, or not recording them • - 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

43. Solutions 63% 3.5% A

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

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

46. Lessons Learned • use Agresso version 5.5.3 or above • understand that software will not solve all issues • engage Agresso experts early • plan for things to not go according to plan • perform training on actual historical data • do not turn a financial system into an asset inventory system • effective use of asset grouping is important K

47. Lessons Learned • spend time organizing asset information • spend time thinking about business requirements • spend time thinking about system design • ensure proper resources are allocated to project K

48. Agresso Fixed Asset Module Implementation Questions? ftp .st-albert. net / files / FAPres / FAPres.pptx R