Case Study: Banking Client Z
A Whittman Hart Case Study
WHITTMANHART's client, "Bank Z", found itself generating annual forecasts without the benefit of valuable information contained within its back-end systems. Those systems offered sophisticated predictions of how loan and deposit balances would behave over time. To effectively incorporate this information and revolutionize its forecasting process, "Bank Z" elected to discard its Oracle Express application and turned to a custom Hyperion Essbase solution. This project allowed “Bank Z” to implement an innovative Management Reporting and Planning System that sharply focused its attention on the drivers of its profitability.
• Align planning data in an architecture that will be comparable to and leverage the “Bank Z” investment in its OLAP product profitablity system.
• Construct a flexible budget & forecast application that will support detail cost center “bottom-up” and high level “top-down” planning.
• Create a planning application that will support multiple iterations of budgets and forecast.
• Create a planning application that will allow for what-if scenario’s (i.e. rate curve changes).
• Design an architecture that will allow for the LOB’s “over time” to create sophisticated planning models.
• Eliminate inconsistencies in standard management reporting. Create a single repository for actual Budget & forecast reporting information.
• Provide analysts with a dynamic adhoc reporting environment that can be understood with minimal training.
• Allow for the Line-Of-Business to create advanced models for budgeting & forecasting
• Increase the time financial analysts spend time analyzing data and decrease the data gathering activities. The system will allow for more time in analyzing the numbers, creating what-if models and providing better insights into the data.
• Create a dynamic planning system that will allow for quicker consolidations and afford the luxury of running more iterations of the budget/forecast.
• Manage the effort to complete the design of the Management Reporting application. This effort will determine how to (or not to) consolidate the product and GL mapping into a reporting structure that meets the needs for both areas.
A mapping for this process is already in existence, but we have yet to understand its complexity. An effort will be undertaken to examine this structure.
• Summarized Actual data will be loaded via an extract fi le from Profi t+ on a monthly basis and consolidated in the Essbase application.
• Reporting will be accomplished via two avenues: (1) Crystal Reports and (2) Standard Reporting interface via Excel (VBA).
• For Management Reporting, 3 years of actual data will be retained.
• Two organizational hierarchies will be utilized for reporting, Management and Geography. Both hierarchies will go to the cost center level.
• Eliminations will be manually handled by Corporate Finance
• Equity Allocation and Margin will be calculated by % of balances. A table of factors by product will be provided by “Bank Z”.
• The balance sheet will be balanced manually.
• FTP dollars will be loaded to the system (extract from Profi t+), and rates calculated based upon accrual methods assigned at the product level. A listing of accrual methods by product will be provided by “Bank Z”.
• The OFSA Product Hierarchy and Financial Element ID will be the basis for the budgeting/forecasting system.
• Budgeting/Forecasting will be done at the cost center level of the management reporting hierarchy
• “Bank Z” will be responsible for the design, creation and deployment of custom forecast models that utilize current position runoff, new volumes, interest, FTP, and spread. WHITTMANHART will assist in the knowledge transfer needed to develop the custom tools, but not responsible for their content.
• Standard ‘generic’ Excel templates will be developed as an input tool for those areas that do not have the desire or inclination to create their own models. These templates will be delivered via the same Excel interface as the Standard Reports (VBA).
• The system will accommodate multiple iterations of Budgeting and Forecasting. These iterations will be ‘solvable’ and reflect any organization or rate curve changes.
• Budget Transfers (moving data from one cost center to another) are not in the scope of this effort. WHITTMANHART assumes “Bank Z” will be manually entering transfers.
• FTP Offsets will be handled by the system, “Bank Z” will provide a list of offset centers to house the offset.
• Indirects & allocated NII and NIE will be generated for budget will serve as proxies for the forecast.
• CPRs and other metrics will be captured from both the standard 'generic' templates and those produced by the LOBs. Separate accounts will be created for this purpose.
• FMS has pushdown capabilities will be replicated in this environment. These allocations are simple – based on the percentage distribution of the account across subsidiary org points.
Get the complete Whittman Hart whitepaper here (PDF)