Overview
This engagement gave the client the ability to consolidate their Balance Sheet, Income Statement, and Cash Flow Statement across multiple entities inside Oracle Financial Consolidation and Close (FCCS). The scope included data integration from the client's ERP source system, cross-application integration with Oracle Fusion Cloud EPM Planning (built on what used to be sold as EPBCS) to pull in budget cash flow, and integration with Narrative Reporting to support more than 50 custom-built reports as well as the client's annual report packages.
Features
- Automatic data load integrated with the client's Fusion GL (Oracle Financials Cloud General Ledger) source
- Intercompany eliminations
- Consolidation by percentage ownership
- Custom input forms
- Validation rules to minimize user error
- Cash flow adjustment and auto-reclass logic
- Custom reclass business rules
- Statistical account calculation rules
- Journal input template setup
- Close task management setup
- Financial Planning integration to pull in budget data for cash flow reporting
- Dimension-level security setup
- Integration with Narrative Reporting
- Annual report packaging
Challenges
- Automated net income adjustments for cash flow.
- To minimize the risk of user error, the team pre-built logic to exclude non-cash members (depreciation, for example) from net income movement in the cash flow statement.
- Integration between Planning (EPBCS) and FCCS, and the data granularity gap between them.
- Because plan data was stored at a higher level of granularity than actuals, a straight one-to-one mapping from the planning source wasn't possible.
- Data inputs needed to live at different levels of detail for plan/forecast data versus actuals.
- Annual report packaging.
- The report package spans 46 pages of text, graphs, and reports, and part of the requirement was letting users make manual inputs directly into certain grids.
- Report narrative text needed to link dynamically to the underlying report content, so updates wouldn't require manually rewriting commentary every period.
Solutions
- Used logic groups and data mappings in Data Integration (Oracle's current name for what was originally the Data Management module) to automatically reclass cash flow adjustments.
- After a complete analysis of the data flow, the team used one-to-many mappings to consolidate data into a single member. In certain scenarios, additional members had to be created to store data, along with custom scripting for calculations that didn't fit the standard mapping approach.
- Built multiple linked grids in Smart View to retrieve data from several different points of view at once. Linked Excel worksheets to Smart View retrievals to enable user inputs, then attached those worksheets to the report package. Report narrative content was linked to the Word document with reference variables used to dynamically pull the correct data and dates into the text.
Results
- Automated the monthly consolidation process for the balance sheet and P&L with built-in elimination logic. Users can enter adjustments for cash flow through a custom data form, and automated reclass logic ensures those cash flow adjustments never impact the balance sheet.
- Automated data loads that notify the admin team if any process fails, rather than relying on someone noticing a missed load after the fact.
- Assigned security at the dimension level so data is only accessible to the corresponding departments.
- Delivered over 50 custom, dynamically built reports with user-selected points of view.