Chiemerie Chigozie Chibuzor

Jean Edwards Consulting case study Task 1 A high-level process flow (you can use draw.io) for ‘As-Is’ and ‘To-Be’ for this project Note: The Client relies on two financial software programs: SICS and SunSystems (Sun) for their General Ledger (FINSUR) accounting. Currently, transferring data between these systems is a manual process done every quarter. This involves exporting data from SICS and then importing it into Sun, and it constituted a problem of doing repeated tasks, and time wasting, this led to the initiation of automating the process to save time, ensure efficiency in reconciliation processes. Benefits of Automated transfer process First, the automation will reduce the time spent on manual data transfers and reconciliation, freeing up valuable time for more strategic tasks. This increased efficiency will streamline the reconciliation process, leading to smoother operations and faster turnaround times. Basic Requirements for the FINSUR Interface Application: Data Extraction: ● Source: Ability to extract new FINSUR data from the SICS system on a scheduled basis (e.g., daily). ● Filtering: Capability to filter extracted data based on specific criteria (e.g., date range, account codes). ● Formatting: Transform extracted data from SICS format to the format required by SunSystems API. Data Transfer: ● SunSystems API Integration: Secure integration with SunSystems API for authentication and data upload. ● Error Handling: Implement mechanisms to handle potential errors during data transfer (e.g., network issues, invalid data). Data Reconciliation: ● Data Retrieval: Ability to retrieve relevant FINSUR data from both SICS and SunSystems for comparison purposes. ● Matching Logic: Define rules to compare and identify discrepancies between the data sets from both systems. ● Reporting: Generate reconciliation reports highlighting any mismatches or inconsistencies for user review. Additional Requirements: ● Security: Enforce secure data access and transmission protocols throughout the process. ● Logging and Monitoring: Implement logging functionalities to track application activity and data transfer events. ● User Interface: Provide a user-friendly interface for application configuration, scheduling data extraction, and reviewing reconciliation reports. ● Scalability: Design the application to handle potential growth in data volume over time. Optional Requirements: ● Notifications: Configure options for system notifications in case of errors or reconciliation discrepancies. ● Data Transformation Options: Allow for user-defined data transformation rules to cater to specific data mapping needs. ● Audit Trail: Maintain an audit trail for data extraction, transformation, and upload activities for compliance purposes. These basic requirements provide a foundation for developing the FINSUR interface application. They address core functionalities for data extraction, transfer, reconciliation, and essential non-functional aspects like security and logging. Non-Functional Requirements for FINSUR Interface Application 1. Performance: ● Data Extraction: Daily data extraction from SICS should complete within 15 minutes during off-peak hours (e.g., between 10 PM and 2 AM). ● Data Transfer: The application should upload transformed data to SunSystems API within 2 minutes per 1,000 FINSUR transactions. 2. Scalability: ● The application should be able to handle a daily data volume growth of 10% for the next two years. ● The system architecture should allow for horizontal scaling to accommodate future increases in data volume. 3. Availability: ● The application should achieve a target uptime of 99.5%. ● A documented disaster recovery plan should be in place to ensure quick recovery in case of outages. 4. Security: ● All data transfers between the application, SICS, and SunSystems API must be encrypted using industry-standard protocols (e.g., TLS 1.2 or higher). ● User access to the application should be controlled through role-based access control (RBAC) with strong password policies. 5. Logging and Monitoring: ● The application should log all significant events, including data extraction attempts, data transfer successes/failures, and reconciliation results. ● Log files should be retained for a minimum of one year and archived securely. ● Real-time monitoring of the application should be implemented to identify and address potential issues proactively. Hypothetical Special Values: ● The chosen timeframe for data extraction completion (15 minutes) and data upload (2 minutes per 1,000 transactions) can be adjusted based on Client A's specific data volume and performance expectations. ● The data volume growth rate (10%) is an estimate and can be revised based on Client A's projected growth in transactions. These non-functional requirements establish performance benchmarks, ensure scalability for future growth, prioritize system availability, and emphasize data security and auditability. Potential Risks for FINSUR Interface Application Project 1. Data Integration Challenges: ● Complexity of Data Mapping: The process of mapping data fields between SICS and SunSystems formats might be more complex than anticipated, leading to delays and requiring additional development effort. ● Data Quality Issues: Inconsistencies or errors in data quality within either SICS or SunSystems could lead to reconciliation discrepancies and require data cleansing efforts. 2. Integration with External Systems: ● Limited SICS API Functionality: SICS might have limited API functionalities for data extraction, requiring workarounds or alternative approaches that could impact performance or stability. ● Changes to SunSystems API: Unforeseen changes to SunSystems API specifications could necessitate adjustments to the application, potentially causing delays and rework. 3. Project Management Risks: ● Incomplete Requirements Gathering: Incomplete or unclear understanding of Client A's requirements in the initial phase could lead to functionality gaps or rework later in development. ● Resource Availability: Unexpected resource constraints or team member unavailability could impact project timelines and delivery. 4. Security Concerns: ● Data Security Vulnerabilities: Unidentified vulnerabilities in the application or its connection to SICS and SunSystems could expose sensitive financial data to security breaches. ● Compliance Issues: Failure to adhere to relevant data security regulations or internal compliance policies could result in project delays or rework. 5. Testing and Deployment Challenges: ● Thorough Testing: Ensuring comprehensive testing of the application's functionalities, including error handling and reconciliation scenarios, might require additional time and resources. ● Integration Testing Issues: Unforeseen complications during integration testing with SICS and SunSystems environments could lead to delays in deployment. Questions for Successful Implementation (First Two Weeks) Data Extraction from SICS: 1. Data Availability: Can you provide a detailed schema or data dictionary outlining the structure of the FINSUR data within SICS? 2. Data Extraction Method: Is there a preferred method for extracting data from SICS (e.g., APIs, database queries)? 3. Incremental Extraction: Does SICS support mechanisms for identifying only new or changed data since the last extraction (e.g., timestamps, change flags)? 4. Security Considerations: What are the security protocols and access credentials required to connect to SICS for data extraction? Data Transfer to SunSystems: 5. SunSystems API Documentation: Can you provide access to the SunSystems API documentation outlining functionalities and data format requirements? 6. Authentication Mechanism: What authentication method (e.g., API keys, OAuth) does SunSystems API utilize for secure data transfer? 7. Error Handling: Does SunSystems API provide specific error codes and messages to identify and troubleshoot potential data transfer failures? Data Reconciliation: 8. Data Mapping: Do you have existing documentation outlining the mapping between SICS and SunSystems data fields for reconciliation purposes? 9. Reconciliation Thresholds: Are there established tolerance levels for acceptable discrepancies during data reconciliation (e.g., permissible variance for account balances)? 10. Reporting Needs: What specific information or level of detail should be included in the reconciliation reports generated by the application? 11. Scheduling: What is the preferred scheduling approach for daily data extraction (e.g., specific time window, triggered by an event in SICS)? 12. User Roles and Access: How many user roles will require access to the application, and what level of access is needed for each role (e.g., monitoring, configuration, reconciliation review)? Addressing these questions within the first two weeks will provide a clear understanding of the data landscape, security protocols, and user needs. This will allow for efficient development and ensure the FINSUR interface application aligns with Client A's specific requirements. Task 2: A brief analysis between ‘EPI’ and ‘EPI and MINPREM’ tabs. EPI and MINPREM Tab ● ● Columns: ○ ID: Combined identifier containing various attributes. ○ BusinessId: Business identifier. ○ UWYear: Underwriting year. ○ EPI in System 1: The EPI value from System 1. ○ EPI in System 2: The EPI value from System 2. ○ EPI variance: The variance between the EPI values in System 1 and System 2. ○ Minimum Premium in System 1: The minimum premium value from System 1. ○ Minimum Premium in System 2: The minimum premium value from System 2. Key Points: ○ This tab provides a comprehensive view of both EPI and Minimum Premium values from both systems. ○ It includes variances for EPI and also shows the corresponding minimum premium values from both systems. Comparison Between Variances in EPI vs. Variances in Minimum Premium ● EPI Variances: ○ Differences in EPI values are directly highlighted in both tabs. ○ The Difference_EstPremiumIncome in the EPI tab shows specific numerical discrepancies. ○ The EPI variance column in the EPI and MINPREM tab shows similar information but also correlates it with minimum premium data. ● Minimum Premium Variances: ○ Only present in the EPI and MINPREM tab. ○ Highlights differences in minimum premium values between the two systems. ○ Allows for a broader view of the data discrepancies by including both EPI and minimum premium values. EPI Tab ● Columns: ○ ErrorText: Describes the type of error (e.g., "EstPremiumIncome value mismatch"). ○ System1EstPremiumIncome: The EPI value from System 1. ○ System2EstPremiumIncome: The EPI value from System 2. ○ Difference_EstPremiumIncome: The difference between System 1 and System 2 EPI values. ○ ● System2ID: Identifier for the record in System 2. Key Points: ○ This tab lists mismatches specifically for EPI values between the two systems. ○ It highlights the EPI discrepancies and provides the associated record IDs. Conclusions / Data Presentation. Descriptive Explanation for Each Visualization: 1. Bar Chart: Sum of EPI Variance by Business ID ● Description: This bar chart displays the sum of EPI variance for each Business ID.The Business ID "ab-" shows the highest sum of EPI variance, indicating a significant discrepancy in the EPI values between System 1 and System 2 for this particular business. ● Perceived Discrepancies: The high variance for "ab-" suggests that there might be data entry errors, misalignments in policy records, or differences in how premiums are calculated between the two systems for this business. 2. Line Chart: Max of EPI Variance, Max of Minimum Premium in System 2, and Max of Minimum Premium in System 1 by UW Year ● Description: This line chart tracks the maximum values of EPI variance, Minimum Premium in System 2, and Minimum Premium in System 1 across different underwriting years.The lines show trends over time, highlighting years with significant variances or high premium values. ● Perceived Discrepancies: If the maximum EPI variance aligns with certain years, it could indicate specific time periods where data discrepancies were more prevalent. Additionally, comparing the trends of Minimum Premiums between the systems can reveal inconsistencies in premium recording or calculations over the years. 3. Pie Chart: Sum of EPI Variance, Sum of Minimum Premium in System 1, and Sum of Minimum Premium in System 2 ● Description: This pie chart compares the sum of EPI variance, the sum of Minimum Premium in System 1, and the sum of Minimum Premium in System 2.: It provides a clear visual representation of the relative sizes of these sums, showing how the EPI variance compares to the Minimum Premiums recorded in each system. ● Perceived Discrepancies: This chart identified how closely the sums of Minimum Premium in both systems align with the EPI variance. If the sums of Minimum Premiums are significantly different from the EPI variance, it suggests there may be underlying issues with how premiums are recorded or transferred between the systems, or if one segment is significantly larger, it indicates that most discrepancies are associated with that particular system. Conclusion: Throughout this data assessment and visualization process, I delved into the intricacies of reconciling EPI variance and Minimum Premiums between two financial systems—SICS and SunSystems. The journey began with understanding the fundamental issue of manual quarterly data transfers, which led to the initiation of an automation process to save time and enhance efficiency. I then explored and analyzed the data provided, identifying discrepancies and matching figures between the two systems. Utilizing Power BI, I created several visualizations above to visualize the data as I understood. Through these visualizations, I was able to gain a comprehensive understanding of the data discrepancies and their underlying causes. This assessment underscored the importance of automated data integrity checks and real-time reconciliation dashboards to improve data alignment and overall efficiency in financial reporting.