RECENT PROJECTS
Below are a few of our most successful projects.
PROJECT // 01 -
ALERT FILE / Datamart
PROJECT // 02 -
Report Automation
Community Banks are generally on a 12 month examination schedule, with a few that qualify on an 18-month schedule. Most of these exams last 6 weeks with a team that may number up to 10 examiners. This requires a significant amount of time and resources on the part of the bank to prepare schedules, collect files, review transactions and meet with regulators.
Regulators spend weeks prior to the engagement planning the exam. They review the Banks regulatory filings such as Call Reports, FRY reports, UBPR reports, Compliance reports, etc. to identify specific areas they will put extra focus on. They develop this Risk Based Focus to ensure they hit the big targets.
For loans, they request a file called the ALERT file. Generally, the Bank's core system usually creates an extract of this 82 field report. However, many banks never take the time to validate the file. As a result, many fields are often incorrectly formatted or populated. Many banks have to combine data from many sources and this may also be done haphazardly, or as an afterthought.
If regulators cannot quickly validate the file, they will generally toss the file and decide to perform more substantive testing on site. This means much higher cost and resources allocated to answering their questions and supporting documentation.
After discussions with different department heads at our client, we noticed several areas were running reports for senior management based on different data sources. The result was management receiving and making decisions on data that did not agree with other areas. The frustration levels were exploding.
We realized that with the addition of a few extra fields, this file could be the source of all reporting. It could be validated by accounting, Finance could use it for profitability reports and analysis, Sales could use it for incentives, Credit could use it for credit risk management, and Treasury could use it for its ALM model.
We created a consolidated prototype in Access, using the ALERT file data and adding other fields from other reports. The result was one large, validated file for all internal reporting needs. Next, we shared the file with the different area heads and management was starting to get reports that agreed between different areas of the bank. We then worked with IT to set up a data mart and an automated process for creating this file. With Access, we were able to share our queries and SQL (Structured Query Language) scripts hat made this process a breeze for IT developers.
Today, the Bank runs this ALERT file monthly for internal reporting. It has saved a huge amount of time for all areas, and showed how a seemingly minor annoyance for regulators could be turned into a golden goose. Think about how this idea might help you?
Even with some of the best budgeting and other systems, most Banks typically export results and use Excel for reporting. This is done to allow greater flexibility in formatting and presentation.
Also, many Banks have system reports that are used by auditors and regulators as "Source Reports" for doing their audits. Many of these reports include several lines for each record and do not export well into excel for analysis.
The result is a very time consuming process of manipulating data, often under tight time constraints, which results in errors and less time spent on actual analysis.
We have software tools that help automate the process. These can strip the data from these reports, convert to excel and provide an audit trail to the source documents. Regulators and auditors prefer these to separate data mart reports that require substantial validation.
Further, these reports can be set up by us and transferred to your staff along with training to significantly speed up the process.
If you see your staff spending a lot of time manipulating or inputting data then please give us a call. If the data exists in some system then it can be extracted, validated and used for reporting. This usually results in significant time and cost savings. So, how can we help you?
PROJECT // 03 -
Allowance for Loan Losses
The Allowance for Loan and Lease Losses ("ALLL") is one of the most significant estimates a Bank must make. It represents a reserve for Loans that become impaired and where it is likely the Bank will collect less than the contractual amount of principal and interest.
The Allowance is comprised of FAS 114 Loans that are large loans that are individually analyzed on a regular basis and estimates made on smaller pools of homogenous loans such as residential or commercial loans. Then an average net loss rate is calculated based on the past 8 or 12 quarters and this is supplemented by external qualtative factors to arrive at the Allowance.
During the last credit crisis, a few years ago, regulators have been very focused on the calculations and estimates made and documentation to support these amounts.
Our experience was obtained working for a Bank that was under a "formal orders" from the OCC and was required to .present our estimate for approval by regulators each quarter before filing our call report. Working with the Chief Credit Officer and credit team, we completely revamped the ALLL methodology and obtained regulatory approval for our process.
First, we developed an extensive database of loan histories for the past 3 years by loan type, location, credit score, past due history, etc and which also including losses net of recoveries. Next we created early warning reports to identify potential new issues. It included combining internally originated loans along with loan portfolios the Bank had purchased.
We then created a one page summary, drillable into the individual types and calculations, and which would summarize the provisiion required for the quarter to ensure the Allowance was at the appropriate level.
The next step was to stress test the loan portfolio and determine a range of outcomes and potential losses and create an action plan to address these situations.
Finally, we presented our results to the regulators for approval each quarter supporting our work.
This approach was later followed to help several other local banks to enhance their ALLL system.
Now, most of our projects involve model validation and independent review to ensure the accuracy and comprehensive of these programs. Also, we assist now with helping Banks move to the new CECL standard which is moves from incurred losses to lifetime expected losses. So, how can we help you?
PROJECT // 04 -
Drill Down Reporting
Drill down reporting is one of the most useful features of expensive business intelligence systems. It allows users to click on information in reports or dashboards and see the underlying detail in progressively more detail.
Unfortunately, the people that need this the most don't have the expertise which is often given to power users who can slice and dice. Also, the cost of this type of software and data marts required to do this can be prohibitive.
We have a long history of creating these drill down reports in Excel and Access. These are tools that almost every company has, and we can create something that we can turn over to your own team and train them to use it.
These type of reports frequently used for analyzing business results, financial results, and used in quarterly reviews with executive management. These reports also result in far more productive meetings because we take data from accounts payable and loan and deposit data and aggregate them to the GL and Center level.
When a question comes up about a trend or spike in an amount, or an amount an executive immediately questions the accuracy of, you can simply double click your way to the actual invoice level. This has the effect of immediately answering your questions and avoiding the "I'll have to get back to you" responses. Meetings are much more efficient and productive.
If you would like to find out more, give us a call.