Creating an income statement
Follow along this guide
Preparing the charts
Many users encounter difficulties when using Power BI to design income statements or profit and loss (P&L) reports like the one above.
At Zebra BI, we take care to make it as easy as possible to create impactful and persuasive income statements.
Start with a blank page and add a Zebra BI Power Tables visual. This adds a chart element to the report.
Click on the element and add the following measures to the comparison by selecting them in the Fields pane.
- Actual results
- Planned values or previous year values
- Accounts that make up the income statement (software sales, R&D, support revenue, etc.)
Now it's time to group individual results for easier review. In the Visualizations pane select the "Account group" field in the Group section and then drag it above the "Account" field in the Category section.
This creates a more readable chart with groups of revenue and expenses.
Tip: For a more standard display of information, make sure the total values are displayed below individual groups. Click the Format tab in the Visualizations pane and open Chart Settings. Select Below in the Show totals field.
A useful recommendation is to turn off sorting in the chart. By default, the data is sorted by values, which is not very useful in an income statement. You do this by clicking on the heading of a column (in this case: AC) several times until the symbol showing the sorting order is no longer displayed.
Our income statement now looks like this:
You now have to define different types of KPI's. You can see that some are positive and contribute to the bottom line, while others are negative and subtract from the result.
For example, when you look at the cost of goods sold (COGS), you see that it has increased, which is a actually a negative development. The software by itself does not know this, so you need to right-click the name of the category and select Invert.
This will turn the KPI red to reflect its negative contribution. You can have mixed KPIs within a single group.
You also need to right-click the Gross profit category and select the Result check box. You need to do this for groups that contain only a single element.
In our case you need to also Invert the following categories (all expenses):
- Research and development expenses
- Selling and general administratve expenses
- Other operating expenses
- Income tax expenses
Additionally you need to mark the following categories as Results:
- Income from continuing operations
- Net income
Tip: You can choose to always display the full names of categories. Click the Format tab in the Visualizations pane and open the Categories area. Select Full in the Display options field.
Finally, to display the visual as a waterfall chart, hover the mouse cursor over the variance column heading until a small waterfall chart icon appears. Click it to switch the chart type.