Authors: Anshuman Agrawal, Maciej Szpakowski

In this blog, we’ll learn about the concept of financial reporting, its importance and the various challenges faced while generating an efficient and reliable financial report, and how Prophecy makes the complete process plain sailing.

Prior to joining Prophecy, I’ve been leading the data engineering effort at the consumer banking division of a prominent investment bank for a few years. There, I’ve been closely involved in the end-to-end process of financial report generation. I’ve had first-hand experience of tackling various on-ground challenges in the process. Through this blog, I’m going to demonstrate some of those and how Prophecy equips you well to tackle them.

Understanding Financial Reporting

Financial reporting refers to the standard practice of giving stakeholders an accurate picture of a company’s finances to measure various KPIs. These KPIs are driven by the business model of the organization. In essence, a financial report is what drives an organization’s decisions.

For our example of a consumer bank, let’s first look at its business model and see which KPIs matter most. The business model of a consumer bank typically involves borrowing money from retail customers at the cost of an interest rate and investing that money for a higher return. The difference between this return on investment and the interest paid makes up the bank's profit.

Given the above business model, the “Working Capital” KPI is the most important. The bank needs to have a clear and accurate foresight of its working capital to balance the demand and supply. The demand is, relatively, the more stable component making the forecasting of supply more critical. This is achieved by observing the existing trends of money movements and understanding the behavior of the retail consumers across different product offerings (Fixed Term Account, Savings Account, IRA Account, etc). 

To measure the above KPI, the Management Information team of analysts essentially needs a daily report of all account positions. We’ll discuss the reporting requirements of our example in further detail in the below sections.

Importance of a Robust ETL for Financial Reporting

Gartner, organizations face an average loss of $15 million every year due to poor data quality. Therefore, having a modern and foolproof solution for managing financial data, such as the ETL process, is essential for timely data extraction and delivery.

Building a foolproof ETL manually is not easy! And more so for financial data.

To name a few major challenges I’ve seen in my experience:

Prophecy, addresses these issues very effectively, thanks to its low-code interface, which generates high-quality and performant Spark code and automatically computed column-level lineage.

Enabling working capital forecasting

The Problem

We have to enable our Management Information team of analysts to forecast the working capital. To achieve that, we need to provide them with a report containing all account positions for every historic business date along with daily cash movements and demographic information. With the available forecasting ability, the business can then make data-driven decisions around pricing (offered interest rate) and product offering opportunities (fine-tune existing products or launch new ones). 

This is one of the most common examples of a financial report which gives the business a holistic view of the money movements within and outside the firm along with the demographic understanding.

The Solution

To build the desired report, we have to ingest the external data into our data lake, clean it up and aggregate it into the required format. To do so, we’re going to structure our data storage into the Bronze, Silver, and Gold architecture.

Let’s get our hands dirty now by writing a few of our pipelines. By the end of this exercise, this is what our top-level pipelines & datasets view (lineage) should look like:

Bronze - Data Ingestion

Bronze pipelines copy external data into our data lake. To forecast the working capital, we will need to ingest the following datasets:

Silver - Data cleanup

For each of our bronze tables, we build a silver pipeline, which involves the following steps:

Gold - Aggregations

Now that we’ve finished the Silver loads, it’s time to cross the final mile with our Gold data aggregation. 

Given our problem statement, the Gold data generation just involves joining the above loaded silver datasets with appropriate preprocessing and reformatting. The generated report now contains all the necessary data points needed for the forecasting of the working capital.

Conclusions

While we just saw how Prophecy makes the ETL writing process so quick and easy, it doesn’t stop there. Under the hood, Prophecy also did the heavy lifting of converting all our pipelines to high-quality and standardized python code (all checked into git). To further top that, it also computed lineage right down to the column level in all our pipelines. 

The complete video walkthrough will be coming out soon on our website. Follow us on LinkedIn to not miss any updates!

How can I try Prophecy?

Prophecy is available as a SaaS product where you can add your Databricks credentials and start using it with Databricks. You can use an Enterprise Trial with Prophecy's Databricks account for a couple of weeks to kick the tires with examples. We also support installing Prophecy in your network (VPC or on-prem) on Kubernetes. Sign up for your account now:

Sign up for your free Account!

We're super excited to share our progress with you, get in touch with us - we're looking to learn!