The Power BI Profit and Loss template from Accounting Insights is intended for accountants and Power BI professionals that want to produce profit and loss reports using Power BI.
The template provides a fully working report with sample data and documentation. The template enables you to compare actual against previous, budget or forecast and drill through to transaction details. It also enables you to present all lines as a % of total sales.
With full access to the .pbix file, documentation and sample dataset you can quickly understand how this works and how to adapt it to your own particular needs.
You may purchase the Power BI Profit and Loss Template for £49 or request a quote to adapt this for your own particular needs. Just fill out the contact us form and we will be in touch.
Alternatively, if you use Sage 50 Accounts, you may purchase the Sage 50 Accounts version of the Power BI Profit and Loss Template for £600 (the template includes all the background transformations for the Sage 50 Accounts dataset) and use the Accounting Insights Sage 50 Accounts Connector to automate updating your report.
Power BI Profit and Loss Report Template pages
The Power Bi Profit and Loss Template comprises four pages:
- Profit & Loss Summary – that shows the actual P&L and variance, with costs as a % of sales for a selected period range.
- Monthly P&L – presents the absolute or cumulative P&L, absolute or as a % of sales, by month, side-by-side, for a selected period range.
- P&L Partner Influence – shows the influence of customers and suppliers on the P&L for a selected period range
- P&L Drill Through – is a drill-through page showing individual transaction details and (where relevant) the partner source for a selected profit & loss summary amount.
Power BI Profit and Loss Template – Summary Page
The “P&L Summary” page of the Power BI Profit and Loss Template provides a birds-eye view of your income statement for a selected date range. It shows three key pieces of information:
- Actual income or expense by main nominal code grouping such as “Sales”, “Purchases”, Direct Expenses”, “Gross Profit” and “Overheads”, as well as the nominal categories such as “Product Sales” and “Gross Wages”.
- The variance between the actual profit & loss performance and a comparison amount, such as previous month, previous year, budget or forecast.
- Costs as a % of total sales, by the main nominal code group (such as Overheads) and category (such as Gross Wages).
On the Profit & Loss page there are three main visualisations; a matrix visualisation of the P&L, a waterfall chart to show the variance by nominal group and a stacked column chart showing monthly costs as a % of total sales.
Power BI Profit and Loss Template – Monthly Profit and Loss
The Monthly P&L page of the Power BI Profit and Loss Template is effectively the same as the Profit & Loss Summary page, but just showing the matrix visualisation with each month of the selected period range as a column in the matrix.
It enables you to see the progression of the income statement month by month. Just like the Profit and Loss Summary page, you can opt to see the figures as absolute numbers or as a % of total sales.
The coloured highlighting reflects the variance against a selected comparison: previous month, previous year, budget or forecast.
Power BI Profit and Loss Template – Impact from Customers & Suppliers
The “P&L Partner Impact” page shows the profit and loss matrix from the P&L Summary page, alongside two bar charts that show the contribution and variance by partner (customer or supplier) for the selected periods.
The purpose of the page is to highlight the most influential trading relationships on the P&L for the selected periods.
Profit & Loss Drill Through Page
The “”P&L Drill Through” page shows every single transaction that makes up a summary total actual figure from any of the other pages in the Power BI Profit & Loss Template. On the left-hand side of the page there are two visuals, showing summaries from the Chart of Accounts and also by trading partners. These visuals enable you to filter the main transaction listing further.