ERP Coding Block and Chart of Accounts Discussion January 9, 2014

1. ERP Coding Block and Chart of Accounts Discussion January 9, 2014

2. Coding Block and Chart of Accounts When the State implements a new ERP system (starting this summer), the coding block and chart of accounts will quickly be a major focus. The reasons for this are varied, but include: • The coding block of the new ERP will be different (and probably smaller) • Standardization of coding block usage is an explicit goal of the project • Much of the work must happen in the first phase of the project (Budget Prep) • The work required to do this will be extensive and will require significant agency participation. There will not be one mapping of the old to new coding block. Effectively, there will be one for each agency. The work actually needs to start now. • To analyze the coding blocks of proposed ERP solutions and ensure that the selected package can accommodate the State’s needs • To begin the process of cleaning up and standardizing funds and other chart values that may take longer than will be available once the project starts. But even if we get started now, there will be a lot of work to do once the project starts. With such a diverse use of the current coding block extra effort will be needed in design, conversion, interfaces with legacy systems, reporting, and testing. The new chart of accounts, conversion mapping, changes to legacy systems, training, and other related areas will be the responsibility of the State. The selected Implementation firm will only assist. 1 2 3 4

3. Understanding the Challenge Agency differences in: Transaction volume Usage of different coding block elements How specific elements are defined for agency usage Number of data element values Expected area of impact

4. Percent of R*STARS Transaction Volume by Agency Size Percent of R*STARS User Count by Agency Size (by transaction volume) Largest 7 Agencies 70.95 % Percent Largest 7 Agencies Smallest 25 Agencies Middle 18 Agencies Middle 18 Agencies 34.66% 34.55% 30.79% 24.05% Smallest 25 Agencies 5.0% Key Points • Most of the transaction volume is concentrated in the 7 largest of the accounting agencies currently established in R*STARS. These agencies will require the most features, are more likely to require customization, and will have the most significant coding block mapping, reporting, conversion, and interface efforts. • However, user counts are much more evenly distributed across agencies. The smaller agencies will still have a significant training audience, but will not need to be as heavily involved in analysis, design, and selected other project task areas.

5. Key Points • The State processed over 16 million R*STARS transactions in the FYE 9-30-2013 • However, over 90 percent of those transactions were not manually entered to R*STARS. They were automatically posted via interfaces, system, or EDI processes. • Only 10 percent (about 1.6 million) of R*STARS transactions were entered manually. • The mix of transactions that were manually entered differs from that for the overall transaction set. Those used manually should receive greater emphasis in designing, the user interface, writing desk procedures, and in focusing training efforts. Percent of R*STARS Transaction Volume by Type 81.15 % Percent 10.01% .21% 8.64% EDI System Interface User

6. 34.00 Percent of Total Transactions by Doc Type (FYE 9-30-2012) Key Points • State used 426 Doc Types • But 10 Doc Types accounts for more than 84 percent of all transactions • The remaining 416 Doc Types accounted for less than 16 percent of all transactions. • There was extensive variation in use of Doc Types. • Broadly, approximately 50 percent of all transactions related to payment vouchers/payment request • Approximately 25 percent involved cash/deposit/revenue reclassification or transfer. • Going forward, the State will need to consolidate doc/transaction types into a base set of approximately 30 types. 19.81 Percent 15.62 10.22 5.54 4.12 3.24 2.99 2.40 1.01 1.05 cc Remaining 416 Doc Types ZZ GR GS GA V2 ZY WZ GB VZ

7. Frequency of Use for Selected Elements on R*STARS Transactions 97% 97% 97% 94% Percent 56% 48% 36% 33% 28% 25% 20% 12% 1% Agency GL ACD1 ACD2 ACD3 Grant Project MPC Agency Object Function Program Index PCA RTI Agencies Using 9 5 9 14 19 17 26 34 40 36 40 40 37 Likely affected by COA Standardization Coding Reduction – Change Issue Mapping Issue affects conversion, reporting, interfaces, agency budgeting,

8. Three largest agency users of selected elements DHS 100% DOT 96% DOE 98% DHS 98% DAG 98% DEQ 97% SFA 96% DNR 96% LARA 94% DHD 91% DCH 86% DCH 86% Key Points • Elements with lowest frequency use overall are heavily used by certain agencies. • Certain of the lower use elements are used on all or nearly all transactions for particular agencies. They are also used in agency budgeting and billing processes. • Some of these agencies use all or nearly all of the current coding block elements and will have the greatest challenge in coding block mapping, data conversion, and reporting. Military VA 71% DCH 73% Senate 70% DEQ 71% Percent DEQ 66% DTMB (M&B) 58% DOE 61% DOE 62% DOE 62% ACD1 ACD2 ACD3 Grant Project MPC RTI High volume agency that uses all or most of the coding block Mid range volume agency with moderate use of the coding block Mid range volume agency with extensive use of the coding block Low volume agencies without extensive use of the coding block

9. Michigan Agencies by Transaction Volume and Extent of Coding Block Usage • Agencies in Shaded Area • 92% of all transactions • 71% of all users • 96% of all grant transactions • 94%of all project transactions • 82 % of all defined cost accounting elements • 67.2 percent of all fund codes • 89.8 % of all organization codes • 98% of all ACD1 transactions • 100% of all ACD2 transactions • 99% of all ACD3 transactions • 98% of all agency budget lines • 48 of 63 agency systems to be replaced • 157 out of 190 interfaces Human Services Transportation Community Health Natural Resources Environmental Quality 2 Million Corrections 1 Million Treasury State Education DTMB Consolidated 800K LARA State Police 500K Strategic Fund Agency Housing Development Agriculture Military & Vet Affairs DTMB - M&B DTMB - IT Highest level of coding block mapping, reporting, and training issues Under 100K Auditor General Business One Stop DMB – Payroll DMB – OFM Higher Education Grant House Fiscal Agency House of Representatives Judiciary Legislative Service Bureau Lottery Senate Senate Fiscal Agency Unpresented/Undelivered Warrant Civil Service Commission Civil Rights Colleges and Universities Executive Office Legislative Retirement State Building Authority College/University –Capital DTMB Capital Outlay Casino Gaming Board Attorney General Likely Conversion Issues Agency Transaction Volume No Use of Cost Accounting or Agency Objects No Use of Cost Accounting Uses Agency Objects Uses Projects and/or Grants and Agency Objects Uses all or nearly all Elements Uses only one ACD field and Agency Objects Use of Coding Block

10. Many Project Challenges are Concentrated in a Few Large Agencies Environmental Quality Community Health Human Services Natural Resources Transportation LARA High Volume Extensive Use of Coding Block High Number of Cost Accounting Element Values High Variation in Use of ACD 1, 2, and 3 Overload of Grant and Index Extensive Project and Grant Related Data Conversion Extensive Chart Impact on Interfaces Extensive Chart Impact on Reporting Extensive Training Impact 2.299 million 1.053 million 1.943 million 2.581 million .658 million 1.443 million All elements All elements All elements All but ACD3 All but ACD1 All but Function 2902 1964 17000 4083 2741 2031

11. Diversity of Meaning and Use Example Agency Processes Using Codes External Reporting Selected Code Agencies Using Expense Classification Revenue Classification Project/Grant Billing Budget Example Agency Uses ACD1 9 • Employee Time Activity for DCDS • CMS64 Medicare/Medicaid Services • Subsections of Federal Grant • Specific characteristics of cleanup sites • Groupings of projects • Planning bureau activity • Equipment • Project spending by PCA • Link to CHAMPS data • Division level activity • RRD locations • Project activity for billing • Maintenance activity ACD2 5 • IT , Motor Pool and Vehicle DTMB Costs • Track CHAMPS spending • County ID • Location code • Traffic signal number • Balance sheet category • Maintenance activity ACD3 9 Grants • Grants and Phases • Matching programs • Superfund contracts • Court Cases • Roads, structures, pump houses • Equipment • CDFA numbers • Group projects 14 • Projects • Federal grant projects • Appropriation • Conferences • Multiple Grants • Storage Tanks • Program activities 19 Projects

12. Impact of New Coding Block and Standardization Unique Element Values Element Total Fund 2774 Program 5,363 Org 6,688 APPN 2,444 COBJ 4,149 AOBJ 6,865 Index 15,579 PCA 15,689 RTI 608 Project 20,249 Grant 5,635 ACD1 1,279 ACD2 508 ACD3 1,465 MPF 1,434,534 Most likely to be affected by standardization effort Most likely to not have exact equivalents in new system coding block Overloaded with multiple and diverse uses. Mapping, conversion, reporting, training issues expected Contains program, organization, facility, accounts, special purpose conversion funds from 1994, funding source, and lots of other things that need not be a fund.