Case Study - PTSB

Case Study – PTSB

Published date: 26th Jan 2017
Categories: Blog EPM Oracle

Some things take a long time to perfect. Financial Reports shouldn’t be one of them.

This large Irish Bank wanted to improve their reporting capabilities for their Financial Department using Oracle Business Intelligence Foundation Suite and Enterprise Performance Management Suite.

The Challenge

Group Finance had no Business Intelligence (BI) or Enterprise Performance Management (EPM) solution in place and therefore were manually producing all reports and plans on a monthly basis. All of the resources within Group Finance were focused on the actual production of reports rather than analysis of data to drive improvements within the bank.

This was a labour intensive, highly manual, time consuming task which lacked flexibility, was prone to errors and, as the reporting demands increased, placed further pressure on overstretched resources.

The reporting capabilities were limited to high level, monthly snapshots meaning users did not have the ability to analyse at a customer account level on a daily basis.

There were no budgeting and forecasting capabilities in place and all plans were produced manually in excel spreadsheets.

To meet the demands of senior management and external regulatory bodies, Group Finance required a solution which would allow them to:

  1. Analyse daily positions by customer account.
  2. Produce a suite of reports which could be automatically refreshed on a daily basis.
  3. Carry out Ad-hoc analysis using ‘ slice and dice’ functionality.
  4. Deliver detailed Plans at a cost centre level which automatically roll up to group level.
  5. Run ‘what-if’ analysis on plans.
  6. Analyse profitability of products.

The Solution
To meet the requirements listed above, Codec-dss delivered an integrated BI and EPM solution on an Oracle Platform using Oracle Business Intelligence Foundation Suite and Oracle Enterprise Performance Management (Hyperion Planning).

Phase 1

Phase 1 of the project focused on delivering actuals reporting across group finance. This was delivered through the delivery of data warehouse which held daily positions for each customer across all the products which PTSB offered, including Mortgages, Term Loans, Deposits, Credit Cards and Insurance. A number of Essbase cubes were also delivered as part of this phase. The essbase cubes stored aggregate data for each of the product sets listed above, giving users the ability to slice and dice and retrieve instantaneous results.

The cubes also delivered a number of complex calculations such as average balances and weighted interest rates. Finally a GL cube was delivered as part of this phase which gave users the ability to deliver Income Statement and Balance Sheet reporting through the platform. The GL cube was primarily used by Financial Accounting and therefore required real time updates once a journal was posted to the ledger. Leveraging the functionality of the BI and EPM platform, a seamless drill through between the data warehouse and essbase cubes was put in place. Additionally, reports which were previously manually generated were converted to automated reports using OBIEE and its suite of reporting tools.

Phase 2 of the project focused on automating the budgeting and forecasting process which was a very manual process carried out on spreadsheets. The process was migrated to Hyperion Planning and each cost centre was given access Hyperion Planning to input their budgets. As part of the migration, a full budget and forecast model was put in place to allow users to produce assumption driven, calculated budgets and forecasts. This gave group finance complete transparency around how the actual budget numbers were derived and eliminated time spent querying offline calculations which they had no oversight of.

Phase 3 of the project focused on profitability reporting and long term planning using Hyperion Planning. Prior to Phase 3, group finance did not have the ability to produce an Income Statement by each of the products, and long term plans were produced manually in excel and at a very high level. The first initiative under this phase was to build a Hyperion Planning Application which could produce an Income Statement by product.

This model was established as a custom application within Hyperion Planning and an allocation engine was developed with Hyperion Planning to carry out the complex allocations down to a Product level. The second initiative was to build a five year forecast for the Loan and Deposit book by-product to enable Group Finance to forecast the positions of their Loan and Deposit book over the next five years. Again this was developed using Hyperion Planning and a complex calculation engine was built to roll over the forecast over a five year period while taking into account complexities such as rate maturities, redemptions, repayments etc.

Download Case Study as PDF file