Agresso Fixed Asset Module Implementation

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

2. Introduction • Ed Kaemingh - Manager of Accounting • Carole Rowe - Manager of Application Services • Caleb Anderson - Business Analyst • Agresso fixed asset module implementation • focus areas: about the City of St. Albert prior to implementation 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 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 50,000 assets: water meters to buildings • over $ 1,025,000,000 (CAD) worth of assets • 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 sp0 • Database: Oracle 10.2.0.4 • 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, two recreation facilities, Library, Human Resources). K

10. Prior to the Implementation K

11. Prior to the Implementation • assets were tracked using Excel • 63 different Excel spreadsheets were used • 189 hours spent updating the spreadsheets • one person responsible for tracking and updating K

12. Prior to the Implementation • fixed asset spreadsheet K

13. Prior to the Implementation • example of fixed asset report K

14. ModuleImplementation R

15. Module Implementation • fixed assets project team R

16. Module Implementation • fixed assets project schedule R

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

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

19. Module Implementation • an efficient data structure was key to minimizing potential problems • in particular, the GL being out-of-balance in some way • the accounting values for the 57,327 assets were constantly changing - values were being revised or corrected - assets were being added or deleted K

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

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

22. Module Implementation Basic Asset Information (AT08) R

23. Module Implementation GL Information (AT08) R

24. Module Implementation Capitalization and Depreciation (AT09) R

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

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

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

28. Using the Fixed Assets Module A

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

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

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

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

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

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

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

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

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

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

39. Challenges and Lessons Learned A

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

41. Solutions • reduce the number of records • - Agresso 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

42. Solutions 63% 3.5% A

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

44. Solutions • 1,078 records are required if we want to categorize by Minor Asset Category • we are currently maintaining information on 57,327 separate assets • location information would see the number of required assets increase to ~20,000 A

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

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

47. Agresso Fixed Asset Module Implementation Questions? R