top of page

RECENT PROJECTS

Below are a few of our most successful projects.  

PROJECT //  01 - 
ALERT FILE / Datamart

Community banks are typically subject to a 12-month examination schedule, with some qualifying for an 18-month schedule. These exams often last about six weeks and involve a team of up to 10 examiners. Preparing for these exams requires significant time and resources from the bank, including compiling schedules, gathering files, reviewing transactions, and meeting with regulators.

Before the exam, regulators spend weeks planning by reviewing the bank’s regulatory filings, such as Call Reports, FRY reports, UBPR reports, and Compliance reports. This helps them identify key areas of focus and develop a risk-based approach to ensure they address the most critical issues.

One of the key documents regulators request is the ALERT file, which contains 82 fields and is typically generated by the bank’s core system. However, many banks fail to validate this file, leading to errors in formatting or data population. Often, banks must combine data from multiple sources, which can be done haphazardly or as an afterthought. If regulators cannot quickly validate the file, they may discard it and opt for more extensive on-site testing, resulting in higher costs and resource demands for the bank.

During discussions with department heads at our client, we noticed that various teams were generating reports for senior management using different data sources. This led to inconsistencies in the data, causing frustration and hindering decision-making.

We realized that by enhancing the ALERT file with a few additional fields, it could serve as a centralized data source for all internal reporting needs. This validated file could be used by Accounting for financial reporting, Finance for profitability analysis, Sales for incentive tracking, Credit for risk management, and Treasury for asset-liability management (ALM) modeling.

To demonstrate this, we created a prototype in Microsoft Access using the ALERT file data and integrating fields from other reports. The result was a single, validated file that met the reporting needs of all departments. After sharing the file with department heads, management began receiving consistent reports across the organization. We then collaborated with IT to establish a data mart and automate the file creation process. By sharing our Access queries and SQL scripts, we made it easy for IT developers to implement the solution.

Today, the bank generates this enhanced ALERT file monthly for internal reporting. This has saved significant time across departments and demonstrated how a seemingly minor regulatory requirement can be transformed into a valuable resource. This approach could benefit other organizations facing similar challenges by improving data consistency, streamlining reporting, and reducing regulatory burdens.

PROJECT //  03 - 
Allowance for CreditLosses

The Allowance for Credit Losses (ACL or formerly the "ALLL") is one of the most significant and scrutinized estimates a bank must make. It represents a reserve set aside for loans that may become impaired, where the bank is likely to collect less than the full contractual amount of principal and interest.

The ALLL which was formerly the Credit Loss Standard prior to CECL, consisted of two main components: FAS 114 loans, which are larger loans individually assessed on a regular basis, and smaller pools of homogeneous loans, such as residential or commercial loans, which are evaluated collectively.  ​Since the Great Recession in 2007, regulators placed significant emphasis on the accuracy of Allowance calculations, estimates, and the supporting documentation.

 

Our experience in this area was gained while working for a bank under a formal order from the Office of the Comptroller of the Currency (OCC). We were required to present our ALLL estimate for regulatory approval each quarter before filing the Call Report. Collaborating with the Chief Credit Officer and the credit team, we overhauled the ALLL methodology and successfully obtained regulatory approval for our revised process.

Our approach involved several key steps. First, we developed a comprehensive database of loan histories spanning three years, categorized by loan type, location, credit score, past due history, and net losses after recoveries. Next, we created early warning reports to identify potential issues, integrating data from internally originated loans as well as purchased loan portfolios.

We then designed a one-page summary that could be drilled down into specific loan types and calculations, providing a clear overview of the quarterly provision required to maintain the ACL at an appropriate level. Additionally, we stress-tested the loan portfolio to determine a range of potential outcomes and losses, and developed action plans to address these scenarios.

Each quarter, we presented our findings to regulators for approval, ensuring our work was thoroughly documented and supported. This methodology was later adopted by several other local banks to enhance their ALLL reporting systems.

Today, much of our work involves model validation and independent reviews to ensure the accuracy and comprehensiveness of ACL programs. We also assist banks in managing the new Current Expected Credit Loss (CECL) standard, which shifts from incurred loss accounting to lifetime expected loss accounting. 

How can we assist you with your ALLL or CECL needs?

PROJECT //  02 - 
Report Automation

Despite having advanced budgeting and financial systems, most banks still rely on exporting data to Excel for reporting purposes. This is primarily done to achieve greater flexibility in formatting and presentation. 

Additionally, many banks utilize system-generated reports that serve as "Source Reports" for auditors and regulators during audits. These reports often contain multiple lines for each record, making them difficult to export into Excel for efficient analysis. 

 

As a result, the process of manipulating and preparing data becomes highly time-consuming, especially under tight deadlines. This often leads to errors and leaves less time for meaningful analysis. 

 

Our software tools are designed to automate this process. They can extract data from these reports, convert it into Excel, and provide a clear audit trail back to the source documents. Regulators and auditors prefer this approach over separate data mart reports, which often require extensive validation. 

 

Moreover, we can set up these reports for you and provide training to your staff, significantly speeding up the process and reducing the burden on your team. 

 

If your staff is spending excessive time manipulating or inputting data, we can help. If the data exists in any system, we can extract, validate, and use it for reporting, leading to substantial time and cost savings. 

 

So, how can we assist you today?

© 2025 - Business Productivity Solutions, Inc.  All Rights Reserved.  

PROJECT //  04 - 
Drill Down Reporting 

Drill-down reporting is a powerful feature often found in high-end business intelligence systems. It enables users to click on data within reports or dashboards to explore underlying details at progressively deeper levels. However, the individuals who need this functionality the most often lack the technical expertise, which is typically reserved for power users skilled in slicing and dicing data. Additionally, the cost of such software and the necessary data infrastructure can be prohibitively expensive.

 

At our company, we have extensive experience building drill-down reports using Excel and Access—tools that are widely available in almost every organization. We can develop customized solutions for your team and provide the training needed to use them effectively.

 

These types of reports are invaluable for analyzing business and financial performance, as well as for quarterly reviews with executive management. By aggregating data from sources like accounts payable, loans, and deposits to the general ledger and center level, we create reports that lead to more productive and efficient meetings.

 

When questions arise about trends, spikes, or specific amounts that executives may question, you can simply double-click to drill down to the invoice level. This immediate access to detailed information eliminates the need for delayed responses like, "I'll have to get back to you," making meetings more focused and actionable.

 

If you'd like to learn more about how we can help streamline your reporting process, feel free to reach out to us. We’d be happy to discuss how we can tailor a solution to meet your needs.

  • Wix Facebook page
  • Twitter Classic
  • Google Classic
bottom of page