Financial Reporting Solution – case study 1

Financial Reporting Solution

Objective

yellow_downarrow

Design the end to end solution for the Multinational pharma company to manage the monthly Financial & Sales number reporting. This process involves data retrieval from 15+ offices, consolidation and profit elimination on multiple grouping at Entity/Market level. Followed by consolidation of P&L, BS and CSS with their accounting system.

Data is finally presented to Management and Finance controlling team over the digital dashboards developed in PowerBI which are connected to live Data feed from Azure DWH refreshed real-time using PowerBI gateway.

ASP.Net MVC

Problem Statement

yellow_downarrow

Currently, financial and sales figures are presented with the application MIK. This is used for both consolidated figures and figures per entity. The application MIK is run as a standalone application on the personal computer of the different users. The process to gather, upload and distribute new data is done over several steps, mainly involving Excel spreadsheets, and it involves lot of manual interventions and effort.

There are predefined Excel for the input, process and output reports. Every month Input excel (Profitability Sheets) are prepared by the Entities and shared for further processing. Data is manually reconciled (using Excel) with the figures from SAP-BO-FC and then processed into MIK.

Financial data from SAP-BO-FC is fed into MIK to populate the cubes for Profit & Loss, Balance Sheet and Cost of Sales Statement. Excel reports are then prepared using the MIK-Functions to share with the Management. Current process of consolidation and reporting takes 2 weeks and is very error-prone. Changes to reporting requirement takes longer and is limited to excel based reporting.

Proposed Solution

Based on the system study and analysis of the current process, we propose the following system landscape.

yellow_downarrow

Web-Interface

This would be the main system, where the entity users would login and input the Sales & Cost data on a monthly basis. Controller can review/correct the data for all the entities and mark them to be processed for Consolidation and Intercompany profit elimination, post which the data would be sent over to Data-Warehouse (Cube repository).

Data Visualisation & Reporting

Microsoft PowerBI Dashboards and reports would be used for multi-perspective view into the data, with visualizations that represent different findings and insights from that data. A report may have a single visualization or pages full of visualizations. PowerBI will connect to the Data-Warehouse cubes and feed the pre-defined dashboards with the latest data. As a secondary reporting mechanism, Excel reports can be configured to connect directly to the Data Warehouse cubes and generate reports.

Data Warehouse

Key layer where the monthly sales and cost data of all entities is stored after the consolidation. Cubes are defined on top of the sales/cost data, which allows the possibility to present data to end-users for analysis and generating reports. This would be a central repository for all users, ensuring all users have a single view of the data.

Web application development

Technology Stack

New solution would be based on the Microsoft technologies, which would provide seamless integration and use of latest tools and technologies for development.

yellow_downarrow

Cloud based responsive Web application developed with C#, ASP.Net MVC, SQL Server, Entity Framework, JavaScript, JQuery and Bootstrap. Application will follow 3-tier architecture principle.

SQL Server for the setup of Data Warehouse. Use of SSIS to transform and load the data from Web-Application transaction Database into DWH. Use of SSAS to define, Process and access Cube Data.

 Power BI and Excel would be used for Data Visualization and Reporting.

Do you have an interesting project? Let's talk about that!

© 2014 - 2020, Arnasoftech All Rights Reserved.