Automating Manual Insurance Flow
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.