The purpose of StoryBI.com is to promote the use of Power BI as a business developer, and share our experience combining analytics with data storytelling to benefit the business. In this post I share working through finding the right income statement BI tool to help our business adopt and drive insights.
In the age of endless data and real time insights, it is still common for executives and managers to favor the traditional lag income statement as the primary indicator of performance.
With this in mind, I’ve been determined for some time to help create a user friendly self-service version of the statement. I wanted the reader to access the familiar statement lines, and also drill into various details as needed to help answer those questions that lie beneath the totals.
My early attempts included ODBC and pivot tables in Excel. Power Pivot improved the ability to connect to larger datasets, and dimensions. Today, Power BI is by far the best platform for us to provide access, automated refresh, and maintain security. What more could this Accountant ask for?
I carried the pivot table format into Power BI, including all available months displayed in columns for the reader. My assumption was that this format would bring the same value to the reader. Throw in a year-over-year line graph and boom! you’ve got every statement line, underlying transaction, and trend line you could ask for. If you learn how drill down and back up, you’re golden.
I tested this format in short demos with managers, and the response was great. However, out in the real world, the general impression was that the required slicer navigation and multiple columns and drill-downs could be overwhelming. Although this format had the potential to provide far more information to the reader, it wasn’t going to replace the original income statement.
Attempt two: I’m now more focused on displaying the right information, rather than all the information. I considered creating an income statement based solely on sleek visuals, then another based on the contribution style format, where we’d group our controllable variable expenses. Neither format really pushed us as a group into the new tool.
I was so focused on exploring new capabilities and reinventing the wheel, that I didn’t realize the answer was already in our hands. If we mirrored the paper statement layout in our BI report (row by row and column by column) it could serve as a link between the paper format and the added insights our BI version could reveal over time. This would help our readers identify with the familiar paper format immediately, and help build trust as our data story unfolds.
Sticking to the original format, and carefully adding BI insights proved to be the right first step to increasing engagement over this statement in Power BI. We stopped referring to the BI report as the ‘New Financial Statement’, but rather called it our ‘Financial Statement Reader’. The Power BI report served as a guide, connecting the dots for the manager. From here on out, both would help us have better discussions over the statement outcome as well as 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 formats out there. Rather than looking for the sleekest looking tool, spend time learning what your end user best identifies with so they can hit the ground running with the reports.
2. Be Relevant: Keep as much relevant information on the surface of the report. Use time intelligence to reduce the need to navigate, use side tables to help display key totals, and custom tool tips to help share quick summary information. One of the key strengths of Power BI is to show more information from one single page.
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 open ended data story, this gives you the opportunity for you to leave the story open. Once we are all oriented to the format and engaged, you’ll notice a shift in the discussion. Rather than asking clarifying questions that need follow-up reporting, we have evidence we need in front of us, and start brainstorming over 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 the paper statement uses positive dollars for revenue, but also positive dollars for expenses. Our adjustment on the BI report would use positive numbers for all increases 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 main statement 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 a 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. In this case the 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 allows us to maintain our statement table, and also cross filter to view comparative monthly totals. 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 not only show the current statement year to date comparative, but two other comparisons… Current month vs. same month prior year, and 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.
At the sub total level, our [%] column is just a ratio of each line of expense divided by total revenue. By choosing ‘Mrg%’ we are able to switch this calculation at the sub total level to reveal the remaining gross profit margin percentage at each of the sub totals at the group level.
By using the Roll Back drop-down, the user is able to override the default time intelligence calculations, and revert back up to six prior statements.
To visit and interact with this report, click on the report image below. As always, thanks for visiting! – Mark
All things Power BI