Every UK financial institution requires its employees to be vigilant towards the threat of bribery and corruption, fraud and money laundering. All employees are responsible for reporting any suspicious financial activity of colleagues, suppliers, brokers, existing customers and potential customers applying for credit.

The Financial Crime team at Masthaven Bank used Microsoft Excel spreadsheets to capture and record suspicious activity in all areas of the business. Generally, they were forms made in Excel that were macro enabled with some functionality to evaluate or send information to the team. The spreadsheets were performing at the edge of the capabilities of the Excel application. Having recognised the limitations of the existing applications the team had begun a requisition request for a bespoke application but the length of the process to completion would be eighteen months. The financial crime team approached the resident SharePoint SME (subject-matter expert) to discuss if SharePoint could be used in the interim to provide much needed improvements to the current applications.

An initial meeting with the team took place where a workshop was arranged to demonstrate and explore the existing reporting areas and their relative data capture processes.

Discovery Workshop

A discovery workshop took place where the working practices of the department were demonstrated. Business analysis revealed that the team has touch points with the whole of the organisation and with different levels of involvement required for different business areas.

Automation Workflow and Notification

The financial crime department has self-imposed strict SLA’s (service level agreements) between itself and the rest of the business in order to measure efficiencies and performance. Therefore, for each business application it is vitally important that automated notifications are used to alert the department and the colleagues immediately that a request is submitted.

SharePoint Workflows were identified, defined and developed for each application that will automate parts of the process to update the status of a submission and alert colleagues of actions and time frames.

Data Inaccuracies

During the workshop, it was demonstrated that within the spreadsheets being used, there were management information reports of the captured data as pivot tables and charts. The accuracy of the reported data was average at best because Excel allows any type of data in a cell. So, if a date cell has text in it, i.e. “TBC” then that stops the date reporting on that cell. In addition, when people put in a broker firm there are multiple different ways people capture that company name, also leading to inaccurate reporting.

Operational Reporting

Each business area within the bank requires different levels of financial crime reporting. Operational departments such as Long-Term Lending, Short-Term Lending, Savings and Business Change present the greatest risk to the bank as they are constantly being exposed to external parties such as, customers, brokers and suppliers. These departments required their own custom forms with branching logic, i.e. if a question was answered yes, ask more mandatory questions.

Internal Reporting

The financial crime team provides functionality for all staff to report any suspicious behaviour of their colleagues or customers that they encounter through an internal notification form. In addition, they provide another form for colleagues to notify of any received gifts from external sources as these must be surrendered and auctioned off for the banks’ chosen charities.

Financial Crime Training and Staff Evaluation

All staff including non-operational departments such as HR, IT and other admin roles are required to attend financial crime training yearly. The success of the training is measured with the taking of a test that must be passed with a score equal to or higher than 70%.

The training and test covers:

• AML (Anti Money Laundering), funding of terrorism
• Anti-bribery and corruption
• Fraud
• Include actions of internal staff, whistleblowing, suspicious behaviour, reporting bribery and declaration of gifts

As an outcome of the workshop it was decided that the order for tackling the departmental improvements would be:

1. Operational reporting for customer-facing departments
2. Enhanced Financial Crime department landing page UI
3. Yearly staff training and testing
4. Internal notification
5. Declaration of gifts

Operational Reporting

The first area of focus was the operational reporting functionality. The Long-Term Lending department reporting was selected as a proof of concept for the new world.

Business Analysis

Analysis of the existing solution revealed the macro enabled spreadsheet and communication functionality. As SharePoint is tightly coupled with Microsoft Office, a custom list structure was utilised as a new data source to replace the spreadsheet. A custom SharePoint list provides many more advantages over a spreadsheet in terms of concurrent access, data categorisation and data visualisation out of the box.

In addition, a SharePoint list column has better data validation out of the box and can be used to provide dropdown choices for common areas on the forms. This drastically reduces the spelling variations and typos. Dates are more accurate than Excel as they can only contain properly formatted dates.

Solution Design

A conceptual design of the solution was submitted to the team to review that indicated the steps required to achieve and the intended time taken to reach certain milestones. The high-level steps required were defined as:

• Data cleansing, manipulation and correction
• Data migration, populate custom list from cleansed spreadsheet
• Creation of list views to categorise data by status etc.
• Creation of an application UI page in SharePoint to include
  • Personalisation of application to identified logged-in user and display or hide functionality to user depending on role
  • Toolbar buttons for searching, filtering and sorting
  • Navigation buttons to team home and documentation
  • Custom UI for list in paginated data table
  • Custom forms for submitting data with branching logic and data validation
• SharePoint Workflow to provide two-way notification between department and colleague to inform them of actions required and status of submission.
• Data visualisation of captured data in graphical chart and graph format

Solution Development

The solution was developed in an isolated SharePoint site collection on the development environment. This was important as being a site collection, robust access restrictions were put in place to only allow the department and the developer access to the development site.

The solution was developed incrementally to test the successful implementation of each stage. This started with just ten rows of data to create the custom UI and forms. Once each element of the solution was assembled and tested in development, the solution was released to the financial crime department for their initial UAT (user acceptance testing) of the test data. This process was repeated until the functionality was accepted.

With the solution completed the final data migration took place and the financial crime team undertook a final round of regression testing and UAT on the migrated data.

Solution Deployment

After the incremental development and testing of migrated data was complete, the solution was migrated to production.

Again, the security features of SharePoint were utilised to restrict access at first to the financial crime team and the developer so that production testing could take place.

With testing completed the solution was released to the intended department.

Repeatable Reusable Process

With a vastly improved risk reporting application for the Long-Term Lending team in place it was possible to create the similar applications for Short-Term Lending, Savings and Business Change.

Having a working, tested application in place enabled the rapid delivery of the other departments applications as the code was reusable with minor changes.

The only laborious task was the data cleansing which in these circumstances is unavoidable.

Application Screens

The following screengrabs were taken from the development environment.

Department Landing Page

The landing page was modified to include a custom navigation bar that provides access to all apps. This toolbar was personalised using the logged-in user’s profile information to show a friendly welcome message and the user’s profile photo.

The user profile was also utilised to show or hide buttons based on the security group of the user. For instance, if the user was in one of the lending operational departments, they would only see the link to the Lending Risk or forms.

Application Home Page

The application homepage includes user personalisation to control display of cases that were submitted by the logged-in user. Financial Crime team, being site owners got to see all information on their site.

Complex Form Development

The operational department forms were complex in nature where the answering of a certain question in a certain way needed to trigger extra questions that were to be considered mandatory. There is no way to do this out of the box, so a custom JavaScript form was developed. This provided data validation of answers in real-time before the data was recorded on the custom list which in turn increased data accuracy and user adoption.

In the example below you will see that the answer to the first question regarding SIRA equals “No” and has been validated.

If however, the SIRA question was answered as “Yes”, three additional required fields automatically appear in mandatory but incomplete state.

On providing valid answers in the three new questions the fields are validated.

Internal Reporting

With the operational reporting complete the development of the internal reporting applications commenced. The forms for these applications were much less complex in nature and development benefited from reusable code components from the operational applications.

The same methodology for solution deployment was undertaken, analysis, design, development, testing, deployment, UAT testing and release to business.

Application Screens

The following screens show the development of the internal forms.

The Ad-Hoc Query form is intended for any staff member to report suspicious activity.

The blue prefill form button was introduced to members of the financial crime department team to speed up their testing by completing fields with valid test data. This is only available to certain members of the financial crime team and is not visible to other departments.

A validated form

Financial Crime Training and Staff Evaluation

The legacy staff training was conducted using another macro enabled spreadsheet that scored and emailed the result to the financial crime team.

Using the same solution development principles as above, the team worked together to deliver the training.

The questions were delivered to the application via a custom SharePoint list that contained all the metadata required to enable personalisation of the test based on the user profile and security groups that the user belongs to. This ensured that non-operational staff received a test that appropriate for their role. Operational departments received much sterner testing as they were on the risk front-line.

Application Screens

The following screens show the financial crime training and assessment application. It features user personalisation to deliver a test appropriate to the users’ department that has been established from querying the user profile of the logged-in user.

Questions were separated into subject-matter sections and displayed in an accordion display to assist with UX.

Data Visualisation of Application Performance

An important part of any application is the analysis of performance metrics. The following screen shows data visualisation of the staff testing over all business areas.

Additional filtration to narrow down results by business area are provided to give the financial crime team enhanced reporting.

User Personalisation

As has been discussed above, the new functionality has been delivered to the business with user-centric features to personalise the UI display according to the user profile properties of the logged-in user.

This provides clear advantages to the business where a single page can be used for the whole business to access but the content delivered is targeted at the user accessing the page. The logged-in user will only see the information that they are supposed to see.

Workflow

The suite of applications above would not be as effective without a degree of automation from a set of custom SharePoint workflows. Each workflow was developed using the Microsoft SharePoint Designer 2013 desktop application.

The workflows typically notify the financial crime department and the colleague that the application has received an item. Instruction is clearly made to each recipient with details of any required actions and deadlines for completion.

Evaluations

The financial crime team had been recording the performance and efficiency of all interactions across all their Microsoft Excel applications. With this as a benchmark the success of the delivery of new applications could be measured.

“In order to meet our target operating mode objectives, we undertook a wholesale review of our financial crime detection and referral process. Working with Robert we significantly leaned our multiple referral points into a single repository and overlaid a number of automated prompts to further assist the 1st line with their decision-making. As a result we reduced referral times by 10 minutes per application accounting for a significant saving annually.”

Steve Earthy Financial Crime Manager Masthaven Bank

Conclusion

In addition to the stated performance and increased efficiencies delivered to the business, the financial crime team benefited by migrating from Office Excel to SharePoint in the following ways.

• All data is secured with 3 levels of recycle bin available in SharePoint
• The department sites are backed up regularly off-site as part of business continuity
• SharePoint lists provide better categorisation and sorting capability through list views to provide business insight and data visualisation
• Data inaccuracies have been drastically reduced by utilising drop-down fields and correct and enforced data types for dates and numbers.

With the benefit of a resident subject-matter expert, the teams landing page was modified to provide improved UX for the whole business. This, as well as efficient UX design of the application UI’s offering tangible savings to the business, increases user adoption of the SharePoint application.