I recently interviewed Noel Stubbs a BankBI partner and Graham Goble the CEO of BankBI to ask them about the financial reporting challenges that bank finance departments face.
Noel Stubbs, BankBI Partner
There are three major sets of reporting that banks have to do from a finance perspective:
- Regulatory reporting which for example shows the regulators that you have the capital funds to operate as a bank. Typically, monthly, potentially daily or quarterly too.
- Financial reporting which is aimed at the shareholders and the investors in the bank which is annually and most likely half yearly or quarterly.
- Management performance reporting
Everything will come from the same place but they have different audiences and therefore different formats.
The challenges or elements in producing them are fairly similar:
- Demonstrating that you are exercising proper financial control. When the reports are produced they are reflecting the true and fair value of the bank and its revenues and disbursements. How do you ensure they reflect the true position of the bank?
- The challenge of putting together the reports and the resources behind producing the reports. How do you produce reports efficiently?
- Analysis - How can we use this information to improve the revenue of the bank or value of the bank? How do you use the data?
The data tends to be dumped into Excel for small banks which is good and bad. Excel is obviously great because it’s flexible and infinitely scalable. You can do all of your links etc.
It’s also bad because control is relatively difficult for example you can just type in stuff if you don’t like something and more practically the size and number of spreadsheets and how you control them.
The case in most banks is that you get dumps of data in Excel that you pull in and those dumps will include extracts from core banking, budgets from a budget system, extracts from other systems which are not necessarily held in detail within your core banking system such as payroll and then adjustments as well.
Generally the difference between the regulatory reports, the financial reports and the management reports are the adjustments.
The management reports will include budgets which the regulatory reports do not.
You’ll be comparing year-on-year in the management reports but you won’t in the regulatory reports.
Some will have adjustments added in at one level and some will have adjustments added in at another level. Adjustments such as impairment and taxation. Taxation may not come into your management reports but it will come into your financial reports. The provision may or may not come into your management reports.
A typical adjustment would be annual bonus paid in January and then accrued throughout the year and reflected in the management reports monthly to give a smooth position throughout the year.
The management reports are the real challenge because that’s where you want to see where the business is going very quickly and be able to get into a position to take action on that as soon as possible after an issue is known. The challenge is producing these reports as quickly as possible that are as accurate as possible.
That is the number one challenge and that compromises the random Excel even more because you’re trying to do things quickly, mistakes can creep in especially when you’re pulling in spreadsheets from different places in the organisation. There are dependencies all over the place in terms of receiving this information. So time and accuracy are important.
In terms of year end reports and regulatory reports you tend to have much more time. Typically monthly regulatory reports are not due until the 15th working day of the month. The sheer volume of reports that you need to produce for regulatory reports is the challenge. There are so many reports and each report needs validating.
If the bank is big enough it will split the production of regulatory reports and management reports so that the responsibility lies with different people within the finance department.
The spreadsheets you need to put regulatory reports together are a different set of spreadsheets to the management reports because the end report template is different. It’s actually a different set of reports but using the same data.
For the most part the banks we’re talking about will have a complete GL somewhere but not all banks. The core banking system generally has a sub-ledger that’s got virtually the complete trial balance part and the adjustments and the budgets are almost always going to be in Excel somewhere else.
You’ll have all of the customer and accounts within the core banking system but also if they haven’t got an external general ledger there will for instance be a salaries line posting from a payroll system that’s the total with little detail. The same with expenditure on properties, depreciation etc. will be done somewhere else and then posted into the core banking system GL.
If there is an external GL they will take the core banking system summary to the external GL and those other systems will go into the GL and not the core banking system.
The GL isn’t a reporting system so you need reporting on top of the GL system. Typically reports that come out from a GL system are bland, clunky reports and not the sort of report you put in front of your directors.
The GL can actually reside in Excel in some banks i.e. not everything goes into the core banking system GL or an external GL.
You can do a certain amount of analysis with a GL with a product dimension and possibly a branch dimension but you can’t go down below that. Calculating key ratios will likely all be done in Excel so someone will set it all up in Excel. If you have a GL there’s going may be some ratios but the majority will be in Excel.
Whenever I go into a bank I jokingly say "and obviously you do everything in Excel" and everyone nods but it's good and bad. Good for flexibility and bad because it allows things to be done that auditors would have an issue with.
Graham Goble, CEO of BankBI
Finance departments have to report on numbers for a variety of reasons on various reporting schedule dates so that’s fundamentally the business function we’re supporting.
- How do we get data efficiently out of core systems into those reporting processes?
- How do we produce the numbers?
- How can we guarantee the integrity of those numbers?
- How can we make that whole end to end process as smooth as possible?
- The question is; how much automation do you currently have in that process?
In a lot of organisations, IT may or may not have helped design, develop and build systems to do this. Quite often finance is left to take source reports and data and build their own manual, semi-automated macros to try to take data from those source reports and trial balances into that reporting processes.
Finance may know what the end result is on a report but do they know how to take the source data through various processes to end up in that report?
Similarly, IT may well know where the data is sitting but don’t know how that data needs to be reported in many different ways. When IT are pressed for time and may not have the business requirements defined and finance are pressed for time and may not have the right technical skills in the IT domain; the challenge is how do you connect these two different areas?
This result is a mixed bag of logic in terms of taking source data through processes that tend to be owned by individuals and therefore it’s difficult to guarantee the integrity across different reporting sets because it is based upon the skillsets of individuals.
The challenge is to get source financial and banking data out of systems into a single process that allows us to create a unified view of financial and banking data that can support a variety of reporting requirements be that daily, weekly, end of the month, end of period, formal disclosure statements, regulatory reports or flash reporting.
Solving this challenge historically has meant consuming resources in a very manual world by dividing up a set of reports and owning those reports, going and sourcing the data to understand how to carve that source data and logic into those reports in order to divide and build those reports.
- How do you look across the organisation to enable everyone to use the same source of financial data to start with?
- How can you use that in a more efficient way for multiple purposes rather than just using data at an individual level?
If you look at the finance department somebody may own daily liquidity reporting, someone may own monthly management reporting and somebody may own head office regulatory reporting, securities reporting, collections reporting etc. So you have teams of individuals that are responsible for individual reporting processes.
- Do they have a single source of data that they can all go to and easily extract that information and does it flow in an automated way?
- Or are they left with extracts that they have to go and manually put together in Excel spreadsheets?
So you see a lot of time being spend on constructing spreadsheets just to assemble data that can then feed into the final report format.
What does an ideal solution to these challenges look like?
Noel Stubbs, BankBI Partner
Personally I think what you should have is something like BankBI. It's the process around it that's important, you need something around it that's controlled at every level and not changeable other than through a controlled process.
Let's assume you have a BankBI type system. The extract from the core banking system to BankBI should be a boxed in process where you can't change the numbers from the core banking system. The numbers are the numbers and they should be reconcilable. The same with any numbers coming in from outside of the core banking system like the general ledger and it should be a system led process.
It's difficult to get away from the budgets being in Excel but there should be a formalised feed from Excel such as an upload from that budget process into BankBI in a formal, signed off way where somebody can verify that what's gone into the system are the correct numbers.
It should be the same for adjustments with a sheet that has the adjustments signed off by a senior finance person and loaded into the BI system which should maintain an audit trail of adjustments. As long as you have all of that then you have accountability, you can see who has signed off on the numbers and you can see who has loaded the data into the system. It's completely transparent with a formal process that's either completely automated or completely process driven. That should be the goal for any financial reporting system. We had this in Barclays but we did have 2,000 people in finance! This is obviously a lot harder to achieve if your finance department is between 3-30 people especially at the lower of that range.
The other challenge I have found on implementations is "old Spanish customs" where many banks have had someone running these processes for several years even decades who does it their way and will never change. Even though they have been using Excel it's quite impossible to make them change and that's a big challenge. You need to build a business case with the CEO or the CFO to initiate change. Or you could stop them from getting what they were getting before which isn't very easy because they tend to find another way so that they are still doing what they know and love. This is a challenge on implementations because the inherent problem still exists. Some of it is about making themselves indispensable to the bank and protecting their job but a new system is designed to make their job better. In my experience I have come across a lot of people like this, who don't want to change the way things are done, it's always worked for them in the past so let's carry on.
Graham Goble, CEO of BankBI
There are two sources of data primarily, even if you use one single core banking system, one is the financial set of results which is the general ledger trial balance. Take the T24 core banking system as an example, where you construct report lines based upon assembling consol.key lines which are the sum of the balances of various accounts and contracts that feed into enabling you to create a logical business view of the financial position of the organisation, so you have lines for cash, loans, deposits, equity and you’re assembling all of the customers’ accounts and profit and loss accounts into that financial structure.
That financial structure provides a very important lens on the business and can be leveraged to do all of the reporting in the finance department.
BankBI has a common language of addressing business logic in the core system using the trial balance that may feed an external GL system or be the GL system of the bank itself.
The second source of data is going back into the accounts and bringing those out because in a variety of reporting you need to get beyond the financial general ledger and break the balance sheet down by maturity bands, by interest rate bands, and by various different currency splits. All of these bands by product and customer attributes need to come from the core banking system, raising the question; how do I ensure that there is integrity between the sum of the core banking data and the GL data?
One of the features of BankBI is to reconcile those two sources into a single source of data into the information that is required to support all of the reporting needs.
It is very difficult to reconcile the two sources of data because most banks have a one-way process to put the changes in the balances and the incremental changes in the accruals and the P&L into the balance sheet so going back and reconciling it and proving it tends to be difficult because you would need to go back and reverse engineer the logic of the interface. For example, to say the sum of these accounts goes into cash in the balance sheet so in order to come back the other way you need a mechanism to read all of those accounts that fed all of those changes in the balances over time.
You need to extract data from both systems and pull them together via that common general ledger structure. Both in the core system and the destination system so there is a logical link between the destination GL number and the core system GL or sub GL.
The solution is a system that can pull both sources of data and connect them via that common GL mapping and that’s something that most banks find extremely difficult to do, it’s a source of audit comments and issues. Auditors want to be able to prove the balances on the balance sheet at the end of the year last year and see that if you took all of the transactional movements and all of the accounts you should end up at the balance sheet this year.
Our solution is to provide snapshot of the GL and the sum of all of the accounts every day so you can see the whether the sum of the portfolio of loans and deposits equals the balance sheet on any given day and if not where are the differences?
For T24 users, where are the differences in those consol.keys?
There are a variety of reasons for mismatches such as mismatches in the core itself, it could be adjustments that are applied in the external GL system and not reflected in the core system or omitted or forgotten, or there may be legitimate reasons why there are differences and that can be then accounted for in that reconciliation.