Budgeting and reporting system

Background

The collaboration between Fortis Bank (now BNP Paribas) and Euvic enabled the bank to implement a Business Intelligence solution, which resulted in an 80% time saving in the budgeting and cost reporting process. It also provided better control over the budget by centralising the storage of financial data and more opportunities to create analyses based on that data. The new solution – built on Microsoft Excel and SQL Server 2000 analytics services with the software licenses already owned by the bank – was also the most cost-effective solution to the company’s problems.

Challenge

When implementing a customer profitability system at Fortis Bank Polska, the objective was to produce profit and loss statements at various levels, determine the profitability of distribution channels and product profitability. Unfortunately, this solution did not solve their budgeting problems. Excel was used a lot in the budgeting process – on average thousands of files were sent per year in different versions. The solution seemed effective at first, but as more files and different versions were sent, managing the budgeting process became increasingly difficult.

The main problem was change control – both in the budget calculations and in the budget itself. Changes in the structure of accounts or in the organisational structure meant that distribution to accounts via macros was poor.

In addition, multiple versions of the budget, last-minute changes and difficulties in reviewing the current version of the budget often led to duplication of work. This whole process generated excessive administrative costs and made it difficult for the Bank to accurately control costs and budgets.

Due to this unsustainable situation, the Bank began to look for a solution that would solve the problem in the simplest and cheapest, yet most efficient way. The Bank considered several systems from different vendors. The natural step seemed to be to extend the use of the technology on which the bank’s existing data warehouse was based – Oracle or Cognos. Unfortunately, this meant too much cost, associated with new purchases of additional licenses and implementation.

Analysis of the problem

The analysis highlighted two important elements of the existing situation:

The most important conclusion of the analysis was that the employees were well able to use Microsoft Excel. It turned out that the problem was not the analytical capabilities of the software or the presentation of data, but the processing of data. To improve data processing, a Microsoft SQL Server database with multidimensional analysis services was used, for which the user interface was of course Microsoft Excel.

Employees were also comfortable using a browser, so downloading a file from the intranet should not be time-consuming, unlike finding the right version of a budget in a flurry of emails. Fortis Bank Polska already had licences necessary to implement the entire solution.

The main stages of the implementation project included concept development and solution testing. The concept development phase determined the whole shape of the solution. For this reason, both Fortis Bank Polska and Euvic Group devoted relatively much time to this phase. Thanks to the established logic of the system and defined business rules, the time needed to create the solution was relatively short. The stage of testing and adjusting the system was the second most important element of the implementation project. This ensured the high quality of the solution, which allowed the Bank to already develop the 2005 budget using the new tool.

Solution

The solution is based on Microsoft SQL 2000 Server with analytical services as a storage place for all previously used materials, such as business rules, macros, functions and budget calculations. Data for the database was fed from two sources: implementation data, provided by the bank’s transaction system through prepared files. While planned information is entered and edited manually by authorised staff. The user interface is Microsoft Excel, which communicates directly with the database. This makes the data more consistent, always up-to-date and avoids the problem of emails and constantly changing versions.

The system for granting authorised access to employees uses Active Directory (AD) as a source of information about the rights of individual employees to specific data and reports. With this architecture, each employee sees only their own data, which eliminates confusion and speeds up teamwork. The data in the system is available in summary form via PivotTable, allowing reports to be generated in any format based on the available fields.

The implementation was completed in a couple of months, making the integrated reporting and budgeting system available through analytics to 100 users across the bank.

Business benefits

Improved efficiency of the bank’s operations

The solution improved the efficiency of the bank’s operational activities (among others, it reduced the time of processing reports and budgeting costs by 80%). It allowed maintaining certain financial indicators at the desired level by avoiding large investments in new IT solutions. This solution fits well with the Bank’s further development plans. This means that solutions that maximise available resources are preferred.

Better control over financial indicators

Thanks to the centralised process of collecting control information, the data obtained is of better quality than before, allowing staff to make better decisions. This is mainly due to the fact that the automation of certain repetitive elements of the process has largely eliminated human error.

In addition, the introduction of access rights for specific groups of data has increased the transparency of who is responsible for data quality. At the same time, the risk of incorrect data being entered in the wrong place or by unauthorised personnel has been eliminated.

The solution allows for faster and more accurate calculation of financial indicators. The access privileges introduced allow multiple users to work on the budget, and once the data is entered, it is immediately available for analysis. This allows for much faster “what-if” analyses or the preparation of, for example, forecasts for subsequent periods based on data from budget execution. In this situation, the information needs of the Management Board are satisfied to a much greater extent.

The most economical system to solve the problem

In choosing the best budgeting solution, Fortis Bank was faced with the options of buying a system with ready-made business logic (for control) that would then be adapted to the bank’s needs – or choosing a technology partner who would implement the bank’s existing business rules in a new system.

Fortis Bank decided to develop its own system because of several factors, the most important of which are: knowledge of Microsoft Excel among the bank’s employees was a key factor. Not only the ability to navigate the software but also virtually minimize user training costs, minimize system maintenance costs and remove the perennial problem for all finance departments: ‘how to move data from my system to Excel? “.

Much lower implementation costs compared to other off-the-shelf control systems – due to the fact that many licenses needed to create the solution were already in the bank’s possession.

A reliable partner, Euvic Group, equipped with appropriate technology and with extensive experience, guaranteed the execution of the work in accordance with the bank’s specifications and requirements.

A user-friendly interface

With the new solution, Microsoft Excel was given a “new face” and employees discovered that the familiar tool offered much richer functionality than they had originally thought. In addition, new features not only include analytical documents (data mining) but also support teamwork with a commonly created document that is the budget plan. Thanks to the new system for collecting and accessing data, it is no longer necessary to download hundreds of files and reports from a multitude of different emails. Instead, the data is available from a single location for all participants in the process.

Reduce staff workload

Following the implementation of the budgeting and reporting system, employees in Fortis Bank’s control department spend less time modifying already defined reports and their distribution. Therefore, they can focus on meeting the company’s reporting needs in new, more efficient ways. SQL Server 2000 analytics provides a manageable reporting environment with a defined level of protection, caching, distribution, access and usage reporting. An integrated set of tools allows IT staff to centrally manage the creation and delivery of reports.

Built to last

The budgeting and reporting system implemented in Fortis Bank is scalable. This means that the system can be expanded as the bank grows. Which is in line with the bank’s further development plans. Which represents solutions that maximize available resources as the preferred measure. The knowledge of the bank’s employees is growing and does not need to be replaced by a new system in the future.

EXCELERATOR®

Excelerator is not a separately licensed product in the strict sense – it is Euvic’s solution that allows you to create a data warehouse or analytical system or a system that supports the budgeting process using only commonly available Microsoft technologies: SQL Server 2000 or 2005 with analytics and reporting services for processing and data analysis, the Office suite – specifically Office Web Components for SharePoint product group collaboration and information management.