Southpoint Solutions The Short-Term Lending department at Masthaven bank constituted approximately 40 staff members made up of management, underwriters, administrators and sales. When Robert started working at the company as a freelance SharePoint expert in May 2018, my first engagement was with the Short-Term Lending department.
The department were using a single Microsoft Excel spreadsheet saved on a file-share to underwrite all new credit applications. It was known as the “Pipeline” and there was a bottleneck everyday due to the limitations of concurrent use of an Excel spreadsheet. Team members were continually asking for the spreadsheet to be released so that they could access it on the file-share to record or amend a record.
The spreadsheet was also used for M.I. (management information) reports using pivot charts. Due to the nature of Excel which allows cells to contain any type of data, there were major inaccuracies in the captured data. Date cells would contain text such as “TBC”, broker names were regularly miss-spelled. Which created untrustworthy M.I. reports which at best could only provide an indication of the true picture.
The Pipeline spreadsheet was closely guarded as it contained the only live record of what was happening on the ground so-to-speak. Once the credit application was completed it was manually entered onto a case management system. Due to the cumbersome and manual nature of the case management system, the spreadsheet was vital to the department as an interim solution while the underwriting was in progress.
Business Analysis
After an initial workshop with the management team to assess the problem, a period of business analysis began. The Pipeline spreadsheet data was duplicated so that it could be analysed. From this analysis it was proposed that SharePoint would be able to assist the department to improve the performance of the team and make underwriting decisions easier to record and manage.
A proof of concept was recommended where the Pipeline spreadsheet would be re-created as a SharePoint list and a sub-set of the data migrated to that new list. A demonstration of how SharePoint could solve the current business problem by providing the improved business process that the department needed.
Prototype
A custom list was created from the Pipeline spreadsheet replicating the column headings and the intended data types. Where columns had one or more similar values, drop down menus were used for the data type of the column using the SharePoint Choice column. This eliminated typos in entered data for those columns which were very common. For instance, where a broker firm with a name such as “Broker 2 You” (fictional name), this was entered in different ways by team members, “Broker2You”, “broker to you” or “Broker2U”, were the types of variations used.
The custom list was created using all out of the box assets such as list views and the different list forms. It contained just 20 rows out of 2000 plus records as a sub-set of data to work with.
Following a demonstration of the new custom list which was accompanied by a demonstration of UX and UI capabilities, using a responsive JavaScript framework for creating single page applications, the Short Term Lending team immediately realised that using SharePoint as a data source and creating a business application would eradicate their existing operational constraints. A decision was taken to proceed, and the management team submitted a change request for the work to be considered. An estimate of the effort for this first phase was provided for the change request which were subsequently approved, development commenced.
Solution Development
The development lifecycle followed a typical agile methodology where deliverables were developed in a prioritised order. Weekly demos to the management team were carried out to display progress and discuss developed functionality. This provided the management team with fast access to an operational application that they could choose when to release, dependent on developed functionality.
A copy of the Short-Term Lending team site was migrated to a site collection on the development environment and restricted access was configured for just the management decision makers. Completed functionality would then be incrementally deployed to production when available for release.
Data Analysis and Cleansing
The existing spreadsheet data was copied to a new spreadsheet where it was analysed. From this, the information architecture to form the data structure for the custom list was defined. All columns where multiple duplicate answers existed were designated as SharePoint Choice fields. This vastly improved the integrity of the data source, in comparison to the spreadsheet where multiple versions of various single entities existed. All dates were refactored to be in the same format and in a format that could be inserted into the list columns. The spreadsheet contained some special characters in the cells which could not be inserted into certain SharePoint columns and these had to be dealt with and replaced.
This process, whilst time consuming was essential so that the application was launched with a robust, accurate data schema and data source. With the duplicated Pipeline spreadsheet data in a cleansed state, the data was migrated to the custom SharePoint list.
List Form Customisation
The nature of a credit application meant that there were over 100 fields that were required to be filled over the course of the underwriting. These were grouped into different sections of the application to cover information areas such as customer identity details, customer financial details, broker firm details, financial compliance areas and internal reporting for instance.
The out-of-the-box list forms, display form and edit form were customised to include the sections as collapsible accordion functionality to further enhance the user experience. This was achieved using a JavaScript UI plug-in to apply the accordion menu right inside the out-of-the-box forms. This was an efficient approach as the development of custom forms was not required as the out-of-the-box assets were extended. An example of an accordion menu is illustrated below.
Application UI
The Pipeline Application UI was designed to operate as single page app. The UI was delivered using a responsive UI framework, Twitter Bootstrap that provided a grid layout that collapses for any device screen resolution. A custom JavaScript and CSS override script were developed to control the display and the UX controls.
Carefully selected third party JavaScript plugins were utilised for the application. Moment JS was used for date manipulation, Chart JS provided data visualisation and Data Tables JS provided a custom, paginated data table that displays JSON data from the SharePoint REST API query to the Pipeline list.
The UX design of the intended UI was created as a wireframe and finalised with the management team.
All interaction with SharePoint list forms are provided in pop-up SP Dialog windows. This ensured that the user did not get taken to another page in SharePoint to complete the form and then having to navigate back to the application again. The user remained focused on the application, which provided all the functionality they need to do their job efficiently, which in turn promoted user adoption of the application and the SharePoint application.
User Personalisation
The app was personalised to show a friendly welcome message to the logged in user. In the toolbar of the application, a button was configured to filter the custom data table to show “My Cases” which was a display of cases being worked on by the logged in user. This was defined by utilising the SharePoint default column “Modified by” as cases were typically started and completed by a single user. Each underwriter was also provided with instruction on how to create a personal list view that filters results where Modified by equals “Me”, which is a standard SharePoint technique.
Workflow
A series of SharePoint Designer 2013 workflows were developed for the application to provide notifications on the list activities such as new cases and changes to the different statuses on the application.
Release
The first iteration of the application was delivered in four weeks from initial meeting to launch. The application was further developed over time in incremental stages to further enhance the application, which was central to the whole departments modus operandi.
Training
The entire department was booked in for group instruction and training which was also accompanied by a user guide to help the team orientate to the new application and process. This document was linked to from the homepage navigation of the app itself.
The management team were provided with specialist training focusing on SharePoint functionality that would help them to manage the data and the team. This included creating list views, column counts and other values, migrating data to the list using SharePoint’s Datasheet view and any other area that they needed assistance with.
Continuous Improvement
The application was further developed over the time I remained at the company to provide continuous feature upgrades. These included:
Custom list views
The management team were further trained on creating custom public and private list views. Custom views were created to provide snapshots in time of activity with column counts to provide reporting metrics. These proved to be an invaluable source of M.I. data.
Underwriters were also provided with customised personal list views of just the cases that they have been working on. This helped them to efficiently find their workload in large data set.
SharePoint BCS Data Integration
In parallel to the Pipeline app development the department were also developing a portal website for the brokers to interact directly with the bank and its products. These transactions were available in a SQL database in a data centre on the corporate domain. It was requested from the Short-Term management team that when a decision was made at the bank to affect the status of an application in the SharePoint Pipeline list, they would like that status to automatically update on the broker portal website.
I embarked on architecting a solution to this problem and decided to utilise the SharePoint BCS (Business Connectivity Service) to create a secure two-way connection between SharePoint and the broker portal website database.
When an application loan decision is completed, the status is changed to reflect whether it has been declined or accepted on the SharePoint Pipeline list. That status was then updated to automatically reflected on the third-party website in real time.
Sales Team Integration
The sales team had their own team site where they recorded their sales activity. Working with the sales team manager, a custom list was created that replicated the relevant parts of the data schema of the Pipeline list. This was important as it would enable the quick transfer of a completed sale to the Pipeline list so that the underwriting and security checking could be undertaken by the underwriting team. This functionality was delivered as a “Copy to Pipeline” button on the sales teams’ UI for their list. This essentially was a JavaScript function that used AJAX and the SharePoint REST API to copy the relevant fields from the sales list and create a new Pipeline case. A SharePoint workflow was triggered when a new case came into the Pipeline list from the sales list so that the underwriters were promptly notified to begin work on the case.
M.I. Management Information Reporting
After the list had been operational for a few months it was possible to start building the M.I. reporting suite. This would be another single page of different charts and graphs to illustrate the performance of the team over time.
The charts were created by querying the Pipeline list for sets of data that were then rendered to an HTML5 Canvas container using Chart JS plug-in.
Conclusion
The team’s bottleneck from the concurrency issues of using a single spreadsheet amongst many team members was dealt with promptly. It was a relief to all that they could access the SharePoint list at any time without having to wait for the spreadsheet to be released for editing.
Incremental, continuous improvements were released regularly, providing digital transformations to team members, improving their ability to work effectively.
M.I. Reporting accuracy was greatly improved and presented to areas of the business that needed it such as, finance, risk and compliance. The management team could focus on fine-tuning operations to get the department working more efficiently.