Power BI financial statements - profit and loss
Working with Sage 50 Accounts data
Power BI can transform your financial statements from very dull multi-page rows and columns of numbers into a colourful, interactive report that conveys much more information and faster. Take a look at this example one-page profit and loss report. It is produced with Sage 50 Accounts as a datasource using the Suntico OData feed.
The Power BI demo above shows a single page profit and loss report. It is taken from the Suntico Power BI Template and currently works wth the following accounting software:Coming Soon
- Sage 50 Accounting (US Edition)
- Sage 50 Accounting (Canadian Edition)
- QuickBooks Enterprise (US Edition)
- Dates table already set to the financial year for the company
- Measures to create summary KPIs and aggregations
- Use of Boolean columns for easy filtering of P&L Accounts, date ranges
In the matrix visual here, we see a comparison for each of the P&L nominal codes between this year to the end of last month and the same period last year. In the hierarchy, we have gone straight from the main groupings such as Sales, Purchases etc. to the individual nominal codes. This is optional, but I felt that the account categories were not needed. Unlike a printed P&L report, which tends to print every line even if there are no entries, this Power BI report will skip any blank rows. In this way including every general ledger, or nominal, account doesn't tend to clutter the report page. Also, the matrix visualization has a fixed upper bound vertical size and simply adds a slider if necessary.
At the bottom of the page you can slice the report by month simply by clicking on a particular month. So for example, if you click on April, in this matrix on the left you will see April this year compared with April last year.
The waterfall chart "Net Profit by Month" shows the year to date profit contribution as the months progress.