Closeout
Request for Project Closeout Certification Form
All Certified Projects Must Follow NM State Policies and Procurement Code
Project GovernanceProject Name
/ Streamlined BankingDate / September 22, 2011
Lead Agency / Department of Information Technology
Other Agencies / Department of Finance and Administration
State Treasurer’s Office
Executive Sponsor
/ Steve Gonzales, Acting Director/State ControllerCo-Sponsors:
Darryl Ackley, DoIT Secretary
James Lewis, State Treasurer
Agency Head / Darryl Ackley
Agency CIO/IT Lead / Darryl Ackley
Project Manager / Susan Steinberg, DoIT/SHARE
Project Abstract (Provide a brief description and purpose for this project)
The scope of this project includes evaluating and re-engineering the current SHARE system cash management reconciliation processes, functionality and state of implementation. It will identify the procedural changes, system configuration changes, and data cleanup necessary to achieve two important goals: 1) improve the success of the automatic reconciliation function now in place, and 2) fully implement a daily book to bank reconciliation process.
The project has two major goals:
1. Increase to virtually 100% the success of the automated reconciliation function.
a. Sub-goals are:
i. Establish controls over data input.
ii. Increase the efficiency of automated process to reduce the time needed to reconcile.
2. Fully implement a daily book to bank reconciliation process.
a. Sub-goals are:
i. Establish business processes that prevent reconciling items other than timing differences related to the redemption of warrants.
ii. Test and implement stale-date warrant process.
iii. Test and implement a standard process to void warrants.
The current implementation of SHARE Cash Management does not support efficient reconciliation processes and existing business processes due to configuration problems and the significant number of custom modifications that were made to the system to establish workarounds for problems that should have been solved by proper implementation of system options. Changes are required to reconfigure these processes in order to fix reconciliation issues related to customizations and to implement efficient State business processes. Configuration includes reverting to vanilla objects, replacing ineffective current customizations, and the implementation of delivered functionality not previously configured.
The scope will include development of priorities, development of new business processes, and development and implementation of a design to re-configure the Cash Management module functionality to automate business process requirements for cash reconciliation.
The following are the outcomes and deliverables for the project.
· Deliverable: Identify, design, test, and implement procedural and system changes listed in the List of Procedural and Configuration Changes Needed To Ensure the Success of the Daily Automated Reconciliation Process.
o Outcome: The ability to automatically match the bank statement data to the financial registers on a daily basis.
· Deliverable: Design, test, and implement a daily book to bank reconciliation process.
o Outcome: The ability to reconcile the general ledger, to the journals, the journals, to the financial registers, and the registers to the fiscal agent bank statements.
o Outcome: The ability to accurately report financial data.
o Outcome: An audit trail from the bank statements, to the financial registers, to the journals, to the general ledger, to the financial statements.
· Verify reconciliation data from June 30, 2006 through date the project team completes the project.
o Outcome: The ability to provide accurate outstanding information to the stale-dated-warrant process and the reconciliation process.
Schedule and Budget
Planned Start Date / July 1, 2009 / Actual Start Date / July 1, 2009
Planned End Date / August 31, 2010 / Actual End Date / December 31, 2010
Planned Cost: (Budget) / $1,435,000.00 / Actual Cost: (Total) / $1,426,510.00
§ Professional Services / $1,435,000.00 / § Professional Services / $1,426,510.00
§ Hardware / 0.00 / § Hardware / 0.00
§ Software / 0.00 / § Software / 0.00
§ Network / 0.00 / § Network / 0.00
§ Other / 0.00 / § Other / 0.00
Certification History
Date
/ Amount / Funding Source(s) (use specific citations to laws, grants, etc.)10/28/09 / $410,000.00 / $130,000.00 -- Laws 2009, Chapter 124, Sec.7 – Data Processing includes $240,000.00 that is “reappropriated to the department of information technology to configure and correctly implement the statewide human resources, accounting and management reporting system treasury module”.
$280,000.00 -- Laws 2009, Chapter 124, Sec. 7 – Data Processing Appropriations includes $935,000 that is “reappropriated to the department of information technology for incremental license fees and to address critical issues with the statewide human resources, accounting and management reporting system”.
11/18/09 / $830,000.00 / $90,000.00 -- Laws 2009, Chapter 124, Sec.7 – Data Processing includes $240,000.00 that is “reappropriated to the department of information technology to configure and correctly implement the statewide human resources, accounting and management reporting system treasury module”.
$240,000.00 -- Laws 2009, Chapter 124, Sec. 7 – Data Processing Appropriations includes $935,000 that is “reappropriated to the department of information technology for incremental license fees and to address critical issues with the statewide human resources, accounting and management reporting system”.
$500,000.00 -- Severance Tax Bonds – reauthorized by the Legislature
Laws 2004, Chapter 126, Sec. 11-- $10,000,000.
Laws 2004, Chapter 126, Sec. 151 -- $10,000.000.
8/3/10 / $105,000.00 / $105,000.00 -- Severance Tax Bonds – reauthorized by the Legislature
Laws 2004, Chapter 126, Sec. 151, reauthorized and extended to June 30, 2011 by Laws 2009, SB443, Chapter 495 -- $10,000.000.
10/12/10 / $90,000.00 / $90,000.00 -- Severance Tax Bonds – reauthorized by the Legislature
Laws 2004, Chapter 126, Sec. 151, reauthorized and extended to June 30, 2011 by Laws 2009, SB443, Chapter 495 -- $10,000.000.
Scope Verification /
Requirements Review
/ Yes / No /Explanation/Notes
/Were the project objectives (expected outcomes) accomplished? / X
Were all Deliverables submitted and accepted? / X
Did the IV&V vendor verify that all deliverables met the requirements? / X
Have all contracts been closed? / X
Have all final payments been made (i.e., invoices paid) / X
Has adequate knowledge transfer been completed? / X
Major Project Deliverables and Performance Measures Met
(Include explanation if a Deliverable or Performance Measure was not met.)
Major Project Deliverable and Performance Measure (Include explanation if Deliverable or Performance Measure was not met.)
/ Budget /Date
/Project Phase
Project Charter / 10/5/2009 / InitiationProject Management Plan / 10/13/2009 / Initiation
Fiscal Agent Change / $123,900.00 / 5/3/10 / Planning/Implementation
Configuration and Design Documents / $252,762.00 / 6/28/10 / Planning
Data Cleanup Procedures / 1/28/10 / Planning
Program Development and Business Process Documentation / $182,013.00 / 11/16/10 / Implementation
Testing / $78,003.00 / 11/30/10 / Implementation
Training Materials / $52,005.00 / 11/15/10 / Implementation
Data Cleanup / 696,917.00 / 12/31/10 / Implementation
Post Production Support / $26,003.00 / 1/31/11 / Implementation
IV&V Reporting / $14,907.00 / 12/31/10 / All
TOTAL / $1,426,510.00
Independent Verification & Validation (Include summary of the Last IV V report)
“The enhanced application is in production at this time, inclusive of two weeks of Oracle production support. The Project managers (PM) plan for a March project closeout and a lessons learned exercise. Throughout the project, the PMs managed and controlled budget, time and scope. Following the general election, project team leadership and steering committee constituency has altered. As the State transitioned to the new leadership, this project benefited from the strong and sustained presence of State PMs and subject matter experts, as well as any final vendor provided activities, to ensure the Streamlined Banking solution’s movement into stable production. This report addresses project information and activities through 1/17/11."Transition to Operations: (Describe agency plan to support project solution in production. include agency plans to fund and maintain this project. )
/ Yes / No /
Explanation/Notes
Are there recurring maintenance/operational costs for the product/service? / X / $ per YearAre there any recommended enhancements or updates? / X / See attached listing of Post Implementation-Phase II Enhancements for Streamlined Banking
Funding source for maintenance/operational costs? (Describe) N/A
Summary:
LESSONS LEARNED:
· Set a logical plan to accomplish goals, objectives, and deadlines.
· Co-located team in a dedicated, separate space for development and testing
o Elimination of distractions
· Project change to include Transition of Fiscal Agent had a beneficial effect
o Set a clear definition of goals
o Continuity of resources for project team
o Increased knowledge of Cash Management Module
· Commitment of resources for duration of project
· 3rd Party Agencies had difficulty complying with project timeline
o Should have included 3rd Party Agencies in project status reporting
o Need to clearly communicate mission statement to outside agencies
RESULTS ACHIEVED:
· 30-40% reduction in time for reconciliation at STO
· STO now able to focus on other issues and responsibilities
· STO not “data clerks” anymore, they are now “data managers”
· New business processes for stale dated warrants
· Easier reconciliation for agencies
· DFA improved book-to-bank reconciliation
· New fiscal agent’s willingness to work with state
· Stale Dated Warrant funds returned to agencies
Breakdown by warrant series of the stale date warrants from July 2006 to September 2009.
Warrant Series / Warrant Count / Total Dollar Amount
H / 2,128 / 347,426.58
V / 1,350 / 708,877.38
O / 29,734 / 3,338,179.54
S / 217 / 668,579.26
M / 121 / 66,072.09
K / 5,421 / 427,646.65
L / 247 / 153,734.14
P / - / -
A / 321 / 116,994.64
B / 16,329 / 5,629,924.38
D / 280 / 1,017,358.09
Total / 56,148 / 12,474,792.75
Revision: 9/1/10 Page 1
Streamlined Banking
Post Implementation
Oracle Consulting
Post Implementation-Phase II
Streamlined Banking
For The
State of New Mexico
Overview 11
“Out of Scope” Issues 12
Original Issues (not addressed) 14
Audit Comment-Related 14
Manual Intervention 14
Stale Date Process 16
Other Issues 17
Effectiveness 17
Optimization 17
Key Features Available in 9.1 19
Cash Management 19
Payables 20
General Ledger 21
Overview
During implementation of Streamlined Banking solutions, issues were discovered, but were deemed out of scope. The issues outlined in this document were recorded by Oracle to help the state address these issues in the future.
Issues and recommendations that have been determined to be out of scope include:
· Updates to NMS367WF.sqr
· Updates to NMS160CM.sqr
· Replace 3rd Party Payment Load
· Updates to NMS170AE
· Review Voucher Cancellation process
· Wire Transfers included on Positive Pay file
· Deposit Type for Agency Net Deposits
This document also includes issues that were deemed “phase 2” or non-critical for the initial Streamlined Banking project. These include
· Audit Comment-related issues
· Issues that affect effectiveness
· Issues that will optimize performance
Upgrading to 9.1 will provide functionality that will greatly benefit the state. Key features are summarized at the end of this document for Cash Management, Payables and General Ledger.
“Out of Scope” Issues
Issue / Summary / “Out of Scope” Explanation / Recommendation / Alternatives /1 / Customer Reference not unique (00000000000) for all Wells Fargo BAI Codes / Issue list was created prior to Fiscal Agent Change.
Majority of the BAI codes were included in the updates to NMS367WF. However, new codes appear without notice & constant modifications were impacting implementation timeline. / Update NMS367WF to include these BAI codes. Set Customer Reference to 15-digit value 0+IA# in Addenda (88) record.
NOTE: This affects audit comment 2007-4 Daily Auto Reconciliation Process Required Manual Intervention / 1. Update list of values to include missing codes
2. Re-wrote section of code to set all values to 0+IA# with exceptions for Codes identified in program (Warrants, Wires, ZBA Transfers)
2 / 3rd Party Payment Load does not have edit checks / Modifications to NMS160CM.sqr were not identified in the original set of issues.
This issue was discovered as a result of development/testing of the 3rd Party Warrant Cancellation process. / Update NMS160CM.sqr to validate the incoming ‘OPRID’ on each inbound record of the input file.
Review program logic and made recommendations for improvements. / Replicate code relating to edit checks in NMS528CM.sqr
· If ‘OPRID’ ‘NMS160CM’ then hard STOP. Return Error message.
· Validate dates within open accounting period
3 / 3rd Party Payment Load is not ideal / The “staging” table was erroneously truncated which prompted cursory review of the process.
This was not an issue identified by STO. / Explore the possibility of replacing the custom process (NMS141AP) with delivered functionality or modify process to align better with business need / Address inefficiencies:
· Table limit due to unique ID assigned
· Loose visibility of issuing agency
· Eliminate customization
4 / Payroll cancellations not recorded in Financials / Payroll has never sent cancellations to Financials. GL is updated, but Cash Management is not. / Utilized the 3rd Party Warrant Cancellation process to record cancels & reconcile original transactions / Modify NMS170AE to include cancels & send them to the 3rd Party Warrant Cancellation staging table. STO will process transactions.
5 / Review Stop Payment business process / Payment/Voucher cancellations affect bank reconciliation. All stop & voids should be cancelled in system. / Verify business process for cancelled vouchers in SHARE / FMB should be cancelling vouchers. This will set recon status to REC.
Selecting stop payment does not update recon status or create accounting entries.
6 / Wire transfers are included on Positive Pay file / Paycycle does not include payment method MAN. However, Positive Pay file includes wires. / Review AP_APY2050 (AE) and APY2055 (Crystal) to determine why wires are being included. / These delivered processes should not be modified. Recommendation is dependent on findings.
7 / Deposit Type for Agency Net Deposits / Depository Account activity will be removed from the BAI file (issue 28).
Agencies will enter a single deposit to the Master Depository account to reconcile to the ZBA entry.
A new deposit type will clearly identify these transactions. / Define a new deposit type for ZBA transactions (possibly “Z”) and update modification to the Regular Deposit page SaveEdit PeopleCode to include this value. / See NMS_0411CM_01 – create deposit type for return items.
Same updates need to be made for the new deposit type, so create NMS_0411CM_02.
Original Issues (not addressed)