RBC Royal Bank Capitalizes on WebFOCUS for Operational Reporting

Escalating competition in the financial services industry has all but commoditized most banking services. Market leaders such as RBC Royal Bank sustain a competitive edge by driving innovation throughout every aspect of their business. For Roy Estevens, manager of Business and Information Systems at RBC, that means developing self-service reporting technology to make it easy for the bank's asset-based lending (ABL) customers to continually stay abreast of the status of their loans. "We want to empower customers to manage their own accounts via secure, Web-driven information systems, so they can use the Internet to gather current information about loan availability and status," explains Estevens. "WebFOCUS enhances our online presence by enabling 24-hour access to the financial information that our customers need to run their businesses."

RBC Royal Bank has enjoyed a reputation as a technology innovator since the early 1960s when it became the first bank in Canada to install a computer. The company has stayed ahead of the technology curve ever since, often bolstered by its dependence on business intelligence, enterprise reporting, and integration technology from Information Builders and its sister company, iWay Software.

Leading the Way

Asset-based lending is a flexible way of providing fast-growing or highly leveraged companies with working capital. The lending institution approves revolving lines of credit primarily secured by accounts receivable and inventory. A popular lending option in the United States, ABL is gaining acceptance in Canada as more and more Schedule One banks begin using it as a financing alternative. RBC is one of Canada's pioneers in this arena.

The major difference between asset-based lending and traditional commercial lending is control. The asset-based lending group at RBC focuses its due diligence on understanding the makeup and status of the borrower's collateral so the bank can maximize the borrower's margin availability based on the underlying value of its current assets, accounts receivable, and inventory. This involves a daily or weekly analysis, the results of which must be available to lending managers and customers at all times.

Several major software vendors provide ABL solutions that perform these calculations. They determine the value of each borrower's pledged assets, as well as trackpayments, calculate credit lines, and deliver reports. Supplying this information to customers in a timely manner is essential. However, while the ABL application RBC uses has a Web-based reporting interface for this purpose, IT professionals at the bank were reluctant to use it because of concerns about security. In order to use the vendor's solution, critical customer data – which RBC always keeps behind its firewall – would have to be copied to the vendor's servers, opening up the possibility for substantial confidentiality risks.

"In order to use our ABL vendor's reporting module, our clients would have to send report requests to the ABL vendor's Web site, instead of ours, to gather information," Estevens explains. "Some of our data would have to be copied to the vendor's servers in San Francisco – something we were unwilling to do. The only other option was to place one of the vendor's servers on-site at our facility, but the cost was prohibitive, and the vendor would not support it."

These business dynamics started Estevens on a search for third-party reporting solutions that could deliver reports to customers via a secure Internet conduit. Estevens had lots of experience with Crystal Reports, so that was his first choice for a pilot test for the ABL project. However, information security professionals at the bank preferred WebFOCUS because of its server-based architecture, which allows the entire reporting infrastructure to be deployed and managed behind RBC's firewall. "Keeping all reporting activity on our servers gives us greater security and control," Estevens says.

Additionally, RBC had lots of experience using WebFOCUS and iWay in demanding production environments. Two popular applications currently being used at the bank include Bankbook Reconstruct, a customer-facing application that creates bank statements from seven years of transactions in a data warehouse, and Merchant Direct, an online reporting service that allows merchants to view daily sales data from bankcard transactions. Both applications have proven the potential of Information Builders' software for creating highly scalable, highly reliable self-service reporting systems.

Developing the System

Roberto Noe, a project manager within RBC's Enterprise Reporting Solutions Group, helped Estevens develop a secure link to the Web server to transmit report requests to WebFOCUS, then on to customers via the Internet. Meanwhile, Estevens learned to use WebFOCUS and began developing standard reports for ABL users. Replacing Crystal was not difficult because most of the existing business logic could be retained and reused inside of the new WebFOCUS application.

"WebFOCUS reports are easy to use, pleasing to the eye, and, best of all, they are live," he notes. "They supply customers with a real-time look at their loan status – up to the millisecond. If our operations group updates the data, it is posted immediately, so the customer always obtains the latest information."

Thanks to this real-time reporting architecture, RBC's asset-based lending clients can view their borrowing base position, outstanding loan balances, collateral composition, and listings of ineligible accounts through a secured and encrypted Web site. About 33 percent of the customer base is currently using the operational reporting environment to access standard reports, soon to increase to 100 percent. Primary users include senior executives, accounts receivable managers, and controllers. "A simple drop-down window provides all the information they need to access loan status reports," Estevens says. "We have created reports that reveal links from debtors to master debtors, aging summaries, AR exceptions, and other pertinent information."

A Model of Architectural Efficiency

WebFOCUS is installed on a group of networked servers running Microsoft Windows NT server operating system, soon to be upgraded to Microsoft Windows 2003. RBC's Consulting Services organization administers the server farm, which hosts the majority of the WebFOCUS reporting applications at the bank. According to Noe, there are currently about two dozen different types of WebFOCUS applications in production at RBC, supporting 5,000 to 10,000 total users. "These are mostly self-service applications, with some managed reports," he says. "We have had no scalability issues."

In the case of the ABL reporting system, WebFOCUS generates SQL queries to access data from a Microsoft SQL Server database. "With WebFOCUS, we can create detailed queries and save them as stored procedures in the database," explains Estevens. "These procedures are the building blocks of our reports, and they run very quickly. They can also be easily edited without changing the report itself, in case we need to modify a calculation or other variable. This is very advantageous, and we could not do it with Crystal Reports version 8."

The architecture also ensures flexibility for the future, in case RBC decides to use a different ABL solution. "Our WebFOCUS reporting system is vendor-independent," Estevens maintains. "If we buy a different software solution for performing asset-based calculations, we can still run the WebFOCUS reports simply by revising the queries to point to the new tables."

Leveraging the Reporting Standard

RBC has seen marked productivity improvements since the ABL reporting portal went online. "Our operations team used to fax reams of paper to our clients each week, including loan status reports, exception reports, AR spreads, and so forth," Estevens says. "Now they only have to fax one report authorizing the loan advance. From then on, all the loan status reports are available online via the self-service reporting environment."

While Estevens is pleased with the rapid return on his efforts, he acknowledges that there is always more to do. He is in the process of translating the loan status reports to French, a process that is easy to do in WebFOCUS simply by finding and replacing key words and phrases. He is also investigating the possibility of using technology and services from iWay Software to automate file-transfer activities. Currently, to make its ABL calculations, RBC must consider more than 1 million invoices each month along with lengthy inventory reports. Estevens believes iWay could translate this steady stream of data into meaningful information that he could directly input to the ABL system. A successful prototype has already been completed using the iWay Adapter Manager technology. "We want to take the ASCII text files sent to us by our customers and convert them into a data format that we can use in the ABL calculations," he explains. "This would save time for the operations staff because they would not have to run the transformation program manually. Additionally, because iWay can listen for incoming e-mail messages, these transformations could occur automatically."

As the first Canadian bank to report an asset-based loan online, Estevens and his team have succeeded in fulfilling one of the bank's corporate mandates: always providing critical financial information to customers in a timely manner. "Our new ABL reporting portal has become a competitive edge for our company," Estevens concludes. "Best of all, we deployed the WebFOCUS solution for 66 percent less than we would have spent on the ABL vendor's ™ solution."