Financial Reporting

About this project

Project Scope

The project scope was to build a Financial Report, starting from an Excel workbook (source) with multiple sheet corresponding to a monthly trial balance. The source was uploaded monthly and the management's need was to have an interactive financial reporting to monitor:

Contribution Margin
Contribution Margin %
Net Cash
Net Working Capital and Cash Trend

As the client didn't have Power BI or other Data Viz tools my solution was for an interactive Power Pivot based report.

Raw Data and Data Model

The source dataset was based on ERP extractions (image1) and needed a lot of transformation for being analyzed.

I first made the necessary transformation over Power Query to get a tabular fact table, then I get a single column of each account and proceed to a manual mapping to categorize the financial statement line items.

The Margin Issue

The big issue I had to deal with was how to replicate the margins like CM and EBITDA within the Power Pivot reporting. Googlin' around I found Storybi's article very helpful to get the solution. So I had to create a new external table to map the reporting level (Contribution Margin, EBITDA, etc) and then write a complex DAX formula to get the result

The YTD Issue

Solved the margin issue there was another issue to solve. The YTD value. As we're talking about financial reporting, there's two way of considering an YTD value:

Balance Sheet

As the BS shows the company assets and liabilities over time, his value is always progressive. So, for the YTD value, in this case, I had to consider only the last month.

P&L

The profit and loss shows the monthly gain and expense for a given fiscal year. In this case, for the P&L YTD value I have to consider the sum of last filtered fiscal year month.

Here comes DAX to help once again with HASONEFILTER formula