Power BI report examples are popular, so I’ve decided to create another example that you probably won’t find elsewhere – a Financial report in Power BI. Typically, financial reports are done in Excel. My intention was to thoroughly explore Power BI and test it in the creation of a complex report, such as financial statements – balance sheet and income statement (according to Czech accounting standards – CAS).

Creating a financial report through Power BI Desktop took quite a bit of time (approximately 20+ hours). Most of the time was spent on the data model and the creation of the accounting hierarchy. Since I invested that much time, I might break down the production process into multiple articles.

Financial Report in Power BI – Sample

I must say that this small Power BI test turned out excellent, and I am satisfied with the results, although I would fine-tune the final version for a client.

With this simple financial report, I can quickly get an idea of what is happening with the company over time, and I have it at my fingertips. Additionally, I can filter by projects (centers) or easily filter out accounting groups that do not interest me. Thanks to a well-structured data model, I have the flexibility to create any number of report hierarchies (CAS, IFRS, simplified/more detailed) and can change the structure of the report flexibly upon request.

Judge for yourself – you can enlarge the financial report using the button in the lower right corner (there is a description of functions below the report). You can filter one or more items (by holding down the CTRL key).

Note: My apologies for report beeing in czech, I will fix in in near future. Anyway there is a accounting hierarchy (Czech accounting standards)

Report with Balance Sheet and Income Statement Includes:

Filter bar with report filters tied to meaningful parameters (slicer)

As filters, we have:

• Project – you can filter by project. We assume that the company can allocate accounting entries by project, allowing you to filter financial statements accordingly (2 production programs set up).
• Accounting Period – Data from the main general ledger of this testing dashboard includes balances for 2 accounting periods: 2016 and 2017.
• Balance Type – You can choose from 4 types of balances: Assets, Liabilities, Expenses, and Revenues.

We have no problem adding anything there due to the data model behind the report, which contains all the information (even those not visible in the report) because we have thought in advance about what we might want to report and have it prepared.

Main report with financial statements (matrix)

In the table, we see a basic check to ensure that the balance sheet balances, and then the actual data in the form of accounting balances in a clear hierarchy.

• Check if ASSETS = LIABILITIES
• Accounting Hierarchy – as you can see, it was necessary to create a 5-level accounting hierarchy for the report, such as Balance Sheet – ASSETS -> 0 Non-current assets -> 01 – Non-current intangible assets -> Values through synthetic accounts. Each level contains subtotals.
• Balance Sheet and Income Statement – are in the same report
• Columns by years and months

Graph with Asset Structure

I also added a graph for illustration purposes of what graphs look like in Power BI. The space in the report could be utilized better. The graph is directly tied to the last reported month (May 2017).

What’s the magic behind the financial report?

Behind the report is a simple Power BI data model, which is pulled from an Excel workbook and contains 6 tables:

1. General Ledger (F_HlavniKniha) – contains monthly accounting balances, numbers of synthetic accounts (standard accounting chart of accounts), and links to projects (centers).
2. Synthetic Accounts Catalog (D_SyntetickeUcty) – a simple list of all synthetic accounts according to the valid standard accounting chart of accounts.
3. Project Catalog (D_Projekty) – a list of projects or centers that are accounted for.
4. Currency Catalog (D_Meny) – a list of currencies in which accounting is done.
5. Calendar (D_Kalendar) – a simple calendar with date, month, quarter, and year structure.
6. Accounting Hierarchy (D_Hierarchie_SyntetickeUcty) – the most important and at the same time, the most demanding part of the model. This table contains the accounting hierarchy displayed in the report. Each row of the table has its name and a superior member (using self-reference). The lowest member is linked to the synthetic account in the D_SyntetickeUcty table.

For updating the report for another accounting period, it is necessary to update the Excel table F_HlavniKniha. Financial reporting in Power BI is not rocket science. All you need is a simple Excel bridge to your accounting system. Data can, of course, be pulled from a database platform (MS SQL Server). The advantage is that you query the data directly from the database, where you also have the bridge from the accounting system.

Consolidated Financial Report in Power BI and Data Model

This guide, example, or whatever you want to call it, is valid for individual financial statements. However, that doesn’t mean it’s impossible to report consolidated data. However, more work is needed, especially on the data part.

The data model I described in the previous section would need slight adjustments for consolidated reporting, and more importantly, you would need to include balances from multiple legal entities and perform consolidation adjustments – excluding intercompany transactions, adjustments, etc. I delve more into the topic of consolidation in relation to data and automation in the article Consolidation of Financial Statements and Monthly Reporting.

There’s no force preventing us from reporting the same in Excel

The entire process, as in the case of Power BI, can be quite successfully applied in Excel as well. For example, in cases where Power BI does not suit your needs or if you have complex consolidated reporting with many manual adjustments that are difficult to automate – it simply may not be cost-effective.

So, it is possible to consume financial reports in any platform because attractive and representative reports can also be created in Excel. I’ve described thought processes and tips in the article about representative reporting in Excel, where I transformed an ugly report into something a bit more usable in just 1 hour.

• Work on the data part will be similar in the case of Excel – we need to structure the hierarchy and transform input data from accounting systems.
• Work on visualization – Power BI assembles the report from data automatically, whereas in Excel, we need to design the structure of the income statement, color schemes, graphs, etc., and guide accounting balances into the table using formulas.

Whether you report with one tool or the other is essentially the same. Excel may provide more flexibility, but it is also more prone to errors and requires self-discipline – meaning Excel can get messy over time 🙂 If you need help with data model design and reporting, feel free to comment or write to me, and we can arrange a consultation.

Examples of other nice financial reports (cost roughly thousands of dollars)

I like this dashboard from Sensdat the most (I couldn’t find the price):

Or this one from FreshBI Reporting Inc. (around 5,000 USD):
“