Creating an Income Statement in Power BI

The purpose of StoryBI.com is to promote the use of Power BI and better connect the dots and act on data insights. In this post I share how we’ve combined the classic income statement with the ability to highlight insights and put self-service power into the hands of our managers.

In the age of increasing data and real time insights, it is still common for executives and managers to favor the traditional income statement as their primary indicator of performance.

Rather than re-invent the income statement format, we’ve decided to enhance it with carefully implemented BI features. I wanted to empower our managers with meaningful information beyond the monthly total, enough to allow them to either validate their understanding or ask more focused questions for further research.

My early attempts included a large matrix style income statement format, containing the traditional row labels, and a separate column for every month in the year. This gave us the ability to compare months side by side but did not help a manager effectively analyze our year-to-date performance.

I tested this format with managers, and the general impression was that it provided much needed information, however the multiple columns and need to drill down multiple times to uncover transaction details was overwhelming. Although this format had the potential to provide for more information to the reader, not all were ready to make the leap.

I went back to the drawing board and decided to better mirror the paper format and overlay the statement with conditional formatting to highlight changing insights.

Sticking to the original format, and carefully adding BI insights proved to be just right to increasing engagement. We stopped referring to the BI report as the ‘New Financial Statement’ and referred to it as our ‘Financial Statement Reader’. The report served simply as a guide for the paper statement, it helped connect the dots for managers. Going forward, it would help us have better discussions over the paper statement with the highlighted items suggested by Power BI.

Before I show an example, I want to conclude with some key lessons learned in creating a dashboard report designed to help engage others:

1. Identify: There are endless possibilities and possible. Rather than providing the most detail, spend time up-front learning how to meet your end user’s needs.

2. Be Relevant: Keep as much relevant information on the surface of the report. Use time intelligence to reduce the need to navigate to the current month statement, use side tables to separately display key transaction details rather than changing the traditional report view.

3. Design a Flow: A logical flow… left to right, top to bottom, consistency in formats all make for a better story and better engagement.

4. Share a story: When the analyst has the skills to facilitate an engaging data story, this gives you the opportunity to let the manager discover and ask effective follow-up questions. Once we are all oriented to the format, we have all the information at our fingertips, and discuss causes, ideas, and next steps. This is the key advantage of BI; it is where change begins.

I created a sample income statement out of completely random numbers. This hopefully provides a basic sense of the format discussed above:

1. Mirroring the paper format: We first took the original paper format and use custom time intelligence to ensure that the reader will open the statement and by default see numbers that make up the current months completed financial statement. No up front prep or navigation necessary.

For formatting and consistency in the BI report, we adjusted the dollar signs (+/-) from those on the paper statement. For display purposes positive dollars represent additions to income and negative numbers for decreases to income. No matter if you were looking at a dollar figure, percentage of revenue, or percentage change, now every positive figure would be referred to as “Favorable +” and every negative figure as “Unfavorable -” change. This also helped us remain consistent in our conditional formatting using green + and red – indicators.

2. Add two horizontal variance columns: The first field [% Diff] is a new column that help highlight the difference between the relative percentage [%] of each line item as compared to our Total Sales row. We also add the [$ Chg.] column to further highlight what this variance means in terms of dollars. Our modified dollar signs now work consistently with the color data bars, and quickly draw attention to material changes on the statement.

This visual tracks row by row and column by column back to the paper statement and does not change as we navigate the rest of the report page. You may also notice we return blank for the dollar change on the Cost of Goods line to avoid reflecting an unfavorable change in dollar cost as our Gross Sales increase. What is important here is knowing how our cost of goods changed as a percentage of total sales. Our relative cost per $100 sold (or 100%) has increased by an average of $2.70 (or 2.7%) per $100 of product sold compared to the prior period.

3. Monthly and drill-in amounts: A second matrix visual placed just to the right of the main statement allows us to maintain our statement table with no visual change, and also allows us to cross filter to view comparative monthly totals by month on the right panel. Variances by month that exceed $1,000 are flagged using conditional icons. Each month includes +/- icons to open up and reveal relevant detail concatenated in one single record, as defined by the statement line.

4. Other Considerations: Custom time intelligence allows us to show:

1. Current year-to-date comparative,

2. Current month compared to the same month in the prior year,

3. Current month vs. an average of the last three current year statements.

This provides different comparatives from other statements without having to pull out the past months paper statements.

The ‘Roll back month’ allows us to roll our statement back to view prior periods.

To visit and interact with this report, click on the report image below. As always, thanks for visiting! – Mark