Dynamics 365 Power BI Custom Visuals Zebra BI Microsoft

Dynamics 365 meets Power BI with Zebra BI – Antwerpen 19.06.2019

We proudly announce that Zebra BI was invited to a special kind of breakfast - the one where Dynamics 365 meets Power BI.

Zebra BI  and our CEO Andrej Lapajne are headed to Antwerpen to attend a special seminar called Dynamics 365 meets Power BI. The topic they will focus on is going to be: how to quickly analyze data from Dynamics 365 with the help of Power BI and custom visuals. Another speaker at the event will be CRM strategy & implementation expert Jure Jesenovec, the Managing Partner at Admiral Dynamics.

If you are in Antwerpen or nearby make sure you  REGISTER HERE - there are still some open spots waiting for the ones who would like to get an overview and an explanation of the possibilities with the use of Power BI and custom visuals within Microsoft Dynamics CRM.

Event details: 

Dynamics CRM system is the hub for all your customer processes and data. If you enrich this information with insights from Power BI, you can create an "all-in-one solution" for steering and managing your business processes.

Dynamics 365 covers the processes, data, user interfaces and management aspects, but what about data visualisation? Power BI enables you to make data more useful and provide greater insights. Take your organisation to the next level with Power BI.

This breakfast session on Wednesday 19 June introduces participants to the use of Power BI and Zebra BI custom visuals with Microsoft Dynamics CRM.

The course focuses on the use of Power BI functionality and technical possibilities of analysing data trends within their CRM organisation. Secondly, we’ll demonstrate the rich and clear view of your extended sales data that provide comprehensive insights for management.

Agenda at a glance:

08:30 - 09:00 

  • Welcome & Breakfast

09:00 - 11:00 

  • Dynamics 365 Meets Power BI: How To Quickly Analyse Data From Dynamics 365


Very much looking forward to seeing all of you there and getting to know you.

Event location:
Katwilgweg 2,
2050 Antwerpen,

Zebra BI visuals for Power BI 3.1 released

We work around the clock to support you in the best way possible on your quest to leverage your data in a whole new way and gain the unique added value which helps accelerate your business.

We are launching the latest release of Zebra BI custom visuals for Power BI - version 3.1. It is packed with new and improved features that will help you create even better reports and dashboards in Power BI.

One of the features most sought-after by our clients and users of our visuals is the "Cross-tables" feature. You can now create cross-tables, time-tables, and P&L tables with multiple business units and across multiple years. Andrej explains all about it in the video below.

Another great new feature is the Matrix chart, an exciting new way to visualize your data and gain additional insight. The new version also brings further refinement to our small multiples concept. Essentially, we put them on steroids and they now allow you to zoom into a single chart, resize it, drag the mouse just once to display the entire matrix of small multiples available, etc.

In short, the new version brings a lot of significant improvements. Please, take your time and watch the brief video presentation of everything we’ve prepared for you.

Enjoy. 😊


Here is also the embedded Power BI report, which allows you to click through all the new and improved features of Zebra BI for Power BI 3.1.

Try Zebra BI version 3.1

power bi custom visual

Zebra BI Recommends: 10 Power BI Custom Visuals that Will Make Your Reports Pop

It has never been this easy to visualize and tell stories with your data. Tools like Power BI deliver real power that anyone can access and use to create great visuals that explain, clarify and amplify your message. However, if you work in Power BI you might need some help to do that. That's where Power BI custom visuals come in. These add-ins expand your options with additional visualization features that you can use to create compelling visual stories.

To help you start, we're highlighting some of the custom visuals that will make your reports stand out.

1. Card with States

Power BI already has a built-in card element but Card with States by OKViz supercharges it. You take a performance measure and define multiple states that determine the colour of the visual. If the measure is positive, the card is green and if it underperforms, the card turns red. It is also possible to display the measured trend line. Powerful and simple at the same time.

2. Gantt Chart


MAQ Software developed an upgrade to regular Gantt charts. It offers features such as the ability to view data related to the task and the hierarchy of data categories and to sort the data based on any data point contained in the task. The visual can be used by project managers to monitor projects as well as by users to view individual tasks and progress. These attractive charts will definitely help you improve your project management efforts.

2. Text Filter

Sometimes the most powerful things are the simplest and this custom element fits this description perfectly. All Text Filter does is add a search box to your dashboard that you can use to filter your data. For example, we have a demonstration dashboard that displays information from hundreds of stores and to easily display just a subset of them, you need to simply enter some text, such as "new york" into the search box. The data is filtered in real time and helps you find specific content on your dashboard.

3. PowerApps

Like the previous item on this list, this is another custom visual developed by Microsoft. Since Power BI is a part of its Power platform, it's no wonder Microsoft added support for PowerApps. This custom visual makes it easy to bring forms and data editing to Power BI. You can even connect to external data, stored in services such as SharePoint, Dropbox, and MailChimp. It can be used to easily enter parameters for what-if analyses or to create an inventory dashboard through which you can place orders as needed.

At Zebra BI, we recommend our customers add commenting functionality to Power BI using PowerApps. All you need is to create a simple app that is used to add comments, which are displayed as a tooltip or in a separate table. Currently, this is the only native option to implement live commenting in Power BI.

5. Instagram-like Overview

You could also go Instagram on your data. We don't mean filters but showing your list-oriented data in Instagram-like formatting. You can take data about companies and show their icons, names, hyperlinks and additional data fields and display it as an easy-to-read overview. This is exactly what Overview by CloudScope does.

6. Mapbox Visual for Power BI

If you're working in multiple geographies, you often need a way to put visualizations on a map. Power BI can be extended with customizable heatmaps, point cluster maps, and graduated circle maps that are available in Mapbox Visual for Power BI. This plug-in allows you to overlay Power BI data on a satellite map. It offers several advanced features, including the ability to visualize data differently at different zoom levels through layers. In practice, this means that you receive more detailed data the closer you zoom in on the map.

7. HierarchySlicer

Slicers are a great way to switch between different filters, especially those that you use often. You might want to use more complex slicers and that's where HierarchySlicer comes in. It allows you to use a Power BI hierarchy as a slicer to filter other report items. You can expand individual levels to navigate through the hierarchy and you can use either predefined or ad-hoc hierarchies.

8. Add Natural Language Insights for BI

Arria NLG's add-in for Power BI automatically creates narratives for your data. It analyzes your data to create natural language narratives. You can set various configurations to select the level of detail to share or even use the NLG Studio to program your narratives down to the smallest detail. You can add your specific knowledge for your industry to drive compelling stories or just highlight the most important facts. This is truly one of the more interesting custom visuals out there.

9. Power BI Visual Planning

Power Planner Visual Planning is a powerful tool for budgeting, planning and forecasting. It allows corporate users to see a data visualization as they are entering data. When they change or add data in a dashboard or a report, it changes interactively. This makes it easier to adjust projections and forecasts and observe the impact immediately.

10. Zebra BI visuals

A list of custom visuals for Power BI would not be complete without our own Zebra BI for Power BI. These extremely advanced visuals enable you to create convincing financial reports that make a point and reveal hidden insights. They are the first and only IBCS®-certified visuals on the market and allow you to create complex visual reports that combine waterfall charts, small multiples and other advanced features. Fully responsive and interactive, they allow you to take meaningful decisions based on actionable insights.


Don't just take our word for it, go ahead and sign up for a free trial. The solution is so powerful even Microsoft uses it internally for their reports.







MsBizzAppSummit Atlanta Georgia

Zebra BI goes to Microsoft Business Applications Summit 2019

We are privileged and excited to announce that we'll be present at the Microsoft Business Applications Summit in Atlanta, GA., between June 10-11.

Joining a vibrant community for 2+ days of total immersion to drive better data, stronger solutions, and bigger transformation.

This great event is covering all things Dynamics 365, Power BI, Excel, PowerApps, Microsoft Flow, mixed reality, and more under one roof.

Our CEO Andrej Lapajne will lead a 20-minute session called "Building spectacular Power BI dashboards with P&L visuals and small multiples" on one of the biggest events this year!


He will demonstrate how to build advanced Power BI dashboards by using popular data visualization techniques like the small multiples, sales vs. budget charts and the IBCS® semantic notation.

While doing so he will be taking on the challenge of presenting user-friendly income statements (P&L) in Power BI. In that same session, he’ll take advantage of the Zebra BI custom visuals to build a real-life sales and financial dashboard from scratch.

Along the way, he will reveal practical tips&tricks for designing advanced, mobile ready and fully responsive dashboards.

Very much looking forward to seeing all of you there, getting to know you and saying hello to all of our friends, partners, and clients. 😊

Building spectacular Power BI dashboards with P&L visuals and small multiples
June 10-11, 2019

Event location:

Georgia World Congress Center
285 Andrew Young International Blvd NW
Atlanta, GA 30313

Click here for the agenda and more details about the event: https://www.microsoft.com/en-us/businessapplicationssummit

Burning Questions: How to Add a Monthly/YTD Switch to a Power BI Dashboard?

One of the questions we get most often by our users is how to create a switch in their Power BI dashboard that would let them switch between Monthly and YTD views. You can do this by using Data Analysis Expressions (DAX), which is the formula language used throughout Power BI. This blog post will provide a quick overview of what you need to do to create a simple switch.

Before you start, download the examples from our webinar page for Top 5 DAX tricks for super effective Power BI dashboards. We also recommend that you watch the video as it also contains detailed step-by-step instructions for preparing your data and tables that are not covered in this post.

Start by opening the Sales Dashboard example (SalesDashboard-ZebraBI-webinar.pbix) from the zip file you downloaded. Open a new page by clicking on the + icon on the bottom of the start page.

The example already includes all the tables, relationships between them and code, so you just need to recreate the basic sales dashboard you will add the Monthly/YTD switch to.

Recreating the basic visualization

Start by recreating the main visual in your report. Add a Zebra BI Power Chart and select the following fields: Group under Business Units, Month under Calendar, AC and Revenue PY under Sales. Set them to the corresponding Fields as shown on the image below:

Now combine various visuals. For example, you can add a comparison to previous year using another dimension. Add a Power Table and select the fields as shown:

This table adds a comparison of actual results to the revenues from the previous year by individual product groups and divisions.

This visual is fully responsive and also controls the small multiples visual on the right that you've created in the previous step. If you click on a category, the chart on the right will change to display just the selected category or group of categories (division). With this, you've already created a great visual. However, we can make it better.

Adding slicers to your dashboard

Next stop is adding two slicers: Year and Month that you can use to switch between views for individual months and years.

Click on Year field in the Calendar group and drag and drop it onto the dashboard. Switch it to the Slicer visualization and select Dropdown option from the menu.

Add another slicer for months. Click on Month field in the Calendar group and drag and drop it onto the dashboard. Switch it to the Slicer visualization and again select the Dropdown menu item.

This will break your visualization. To fix it, you need to decouple the time series view from your slicer. Click on the month slicer and select the Format tab on your ribbon. Select the Edit interactions tool.

This shows additional controls on the dashboard, where you can control what impact the selected visualization should have on the others. To prevent the currently selected visualization element from impacting your small multiples chart, you should select the no impact icon.

Adding the Monthly/YTD switch to your dashboard

Now, we will add the Monthly/YTD Switch. Switch to Data view and add another table for the switch on the  dashboard. Go to the Relationship view, where you will create what is called a "disconnected table" and serves as a switch between two values. Click the Enter Data button in the Home tab and name it Period Calculation in the Name field on the next screen.

You need to create just two columns - Period calculation and CalcID. 

You now have a new table in your project that is available for you to select in Report view in Power BI. Drag and drop the Period Calculation field onto the chart and switch it to a slicer element. You get two options - monthly and YTD. Switch to Horizontal orientation under General option in the Formatting tab.

You now have a slicer with two options (YTD and Month). You click between the two although the options do not do anything yet.

Create a new measure and name it Selected calculation. You will use the MIN function, which simply returns the smallest value in a column. Enter the following formula: SelectedCalc = MIN('Period Calculation'[CalcID])

All you have to do now is to link this back to your model. You need to take your AC measure and use the following DAX formula:

AC = SWITCH([SelectedCalc];
1; [AC filtered];
2; CALCULATE([AC filtered]; DATESYTD('Calendar'[Date])))

Just to explain briefly, what these functions do. SWITCH returns different results depending on the value of an expression, in this case AC filtered element in your project. If the value is 1, the expression returns the AC filtered value. If the value is 2 it calculates revenue to return the YTD value for which the DATESYTD function is used.

Make sure you download the free trial!

Try Zebra BI Visuals for Power BI

In addition to out-of-the-box support for IBCS standards, Zebra BI visuals for Power BI feature 1-click data sorting, powerful outlier handling, advanced small multiples, responsive visuals, improved navigation, and full customization. With support for Power BI, best practice reporting is now available on the desktop and mobile and in the cloud.


Top 5 New Productivity Features in Zebra BI Custom Visuals for Power BI

The latest version of Zebra BI for Power BI brings a bunch of new features that bring new flexibility to companies and users who rely on standardized reports to present their data.

Reorder your columns and scale your reports

As you work with Zebra BI Power Tables you might want to change the default order of columns. While the default ordering is based on best practices and standards prescribed by IBCS, companies very often have reporting standards which define a proprietary order of columns or simply need more flexibility.

For example, data for the previous year should be displayed to the left of Actual results or to the right. With the latest update, you no longer have to worry, just simply drag and drop columns to reorder them.

The reordering is very flexible and intuitive. Your custom column order is preserved even after you resize your table. Some detail, particularly charts, might get converted into numbers to save space and retain readability, however, the information itself is not removed and stays front and center. This feature is great for scaling your stories from beautiful, highly visual full-screen power bi dashboards to extremely compact, mobile-friendly reports and everything in between ... with a simple resize action

Use this feature to add a new level of flexibility to your IBCS-compliant reports and dashboards. Our visuals now support virtually any customization you might need to create powerful and convincing data stories.

Focus on the most important things

To make a point, sometimes you just want to show the most significant items and reduce the visual clutter. To help you do just that, we've added the Top/bottom N feature to our waterfall charts. Once you toggle the option On, you simply select the number of top or bottom values you want to display and the chart adjusts automatically.


How to use this option? Once you have your waterfall chart, click the Format tab in the Visualizations pane. Open the Categories group and toggle the Top/Bottom N switch into the On position. Afterwards, you can set the number of items that need to be shown. Other items are merged into a single group named Other.

This feature helps you focus on the most important data points while reducing visual density to improve chart readability.

Automated data labelling

Everyone hates it when the labels on their charts overlap and ruin an otherwise perfect visual. We've added an advanced algorithm to Zebra BI PowerCharts to prevent this. When you are working with a narrow chart that cannot accommodate all the labels, the visual will hide them automatically. Labels appear again once you resize the chart or switch to the Focus mode. This ensures optimal label density depending on your context and situation.

To turn this feature on, select your chart and click the Format tab in the Visualizations pane. Open the Data labels group and select Auto from the Density dropdown menu.

Small multiples that combine chart types

Small multiples mode now supports charts combining multiple chart types (bar and lollipop, for example). To use this feature, select your chart and click the Format tab in the Visualizations pane. Open the Chart Settings group and select either "Absolute / Relative", "Actual / Absolute" or "Actual / Relative" from the Layout dropdown menu.

At the same time, we've added the Top N + Others option to the small multiple mode. It works similar to the feature described above by showing only the most important charts and combining the others into a single chart named Other.

To use this feature, select your chart and click the Format tab in the Visualizations pane. Open the Small multiples group and select an item from the Top N dropdown menu. Then, set the number of items you want to have shown.

Break the axis in line and area charts

Sometimes you want to track a difference in a KPI that has very small variations, you can now break the axis in line and area charts to emphasize otherwise minor variances. The feature works on individual charts as well as small multiples.

Our advice is to be careful with this feature, because it artificially emphasizes differences where there might not be any. However, you might find this feature useful if you want to focus on the trend of a variance or a similar feature of your data.

You can find the axis break option in the Format tab in the Visualizations pane under the Axis break group. Turn the feature on with the toggle and set the Percent value - lower values might result in a better display of very low data labels.

Make sure you download the free trial!

In addition to out-of-the-box support for IBCS standards, Zebra BI visuals for Power BI feature 1-click data sorting, powerful outlier handling, advanced small multiples, responsive visuals, improved navigation, and full customization. With support for Power BI, best practice reporting is now available on the desktop and mobile and in the cloud.

Try Zebra BI Visuals for Power BI

USAFacts Relies on Zebra BI Visuals to Open US Government Financials to Public

April 17, USAFacts released a 10-k report on US government financial using Zebra BI Visuals for Power BI. The shareable reports will drive interactive storytelling and deeper understanding of complex data.

USAFacts is a fascinating project funded by Steve Ballmer, former CEO of Microsoft. The project's aim is to make vast amounts of US government data more open, accessible, transparent and easier to use. Engineers and researchers collected and organized 30 years’ worth of data from US Treasury Department, the Office of Management and Budget, the US Census Bureau, and the Federal Reserve. They then published it on a well-organized online hub that is neutral and focused on the thing that matters most – impartial data.

On US Tax Day, the initiative launched the 10-K report for the US government – in the US, 10-K is an annual report that gives a comprehensive summary of a company's financial performance. USAFacts did that for government data.

To make their findings better accessible to the public, they turned to Zebra BI visuals on Microsoft’s Power BI platform to create some of their interactive visualizations:

You can also click "Chart view" below to see all of the visualizations interactively:

Zebra BI visuals for Power BI are a perfect fit for a project like this. Not only does it include a host of crystal clear visualizations and features, (chart slider, small multiples and native support for IBCS), its design is completely responsive, so it can be accessed on mobile devices or desktops. It can also be embedded on other websites to drive engagement. All of this makes Zebra BI visuals for Power BI the best choice for data-driven storytelling that uses clear, easy-to-understand and attractive visuals for communicating insights.

You can also find out more in Microsoft’s Power BI team announcement.

We are proud that the USAFacts team, in partnership with the Microsoft Power BI data journalism program management, has chosen our Zebra BI visuals for Power BI to bring the US government financials closer to the public. In many ways, this is for us an essential confirmation that we have have done a good job with our latest product and that our solution is a great choice for presenting business and financial data in a clear and understandable way.

We are an avid supporter of open data. Data transparency and clarity of business communication are at the very core of Zebra BI’s beliefs. It’s what we stand and work for.

USAFacts is a noble initiative and we are looking forward to supporting more projects like this!

Andrej Lapajne,
CEO at Zebra BI

Zebra BI Visuals for Power BI 2.0 Released!

Zebra BI Visuals, the only IBCS®-certified software solution for Power BI, has just been updated to version 2.0.

This release is available for existing subscribers as well as a free trial and delivers improved comparisons and more options for formatting and designing data.

Note: Version 2.0 of Zebra BI visuals for Power BI takes advantage of the latest additions to Power BI core, so it requires August update of Power BI Desktop. Older Power BI versions still work with version 1.3.

The most important new features of version 2.0. include:

Report page tooltips

Now you can make your Power BI reports even more user-friendly. Use report page tooltips to display additional information when a user hovers with his mouse over a specific data category.

Here's an animation of report page tooltips in action:

More info about using report page tooltips with Zebra BI visuals for Power BI is available here: https://zebrabi.com/pbi/use-report-page-tooltips-provide-details/.

Multidimensional small multiples

The small multiples functionality of Zebra BI visuals for Power BI has been improved. Watch the video below to see multidimensional small multiples:

Make sure you download the free trial!

In addition to out-of-the-box support for IBCS standards, Zebra BI visuals for Power BI feature 1-click data sorting, powerful outlier handling, advanced small multiples, responsive visuals, improved navigation, and full customization. With support for Power BI, best practice reporting is now available on the desktop and mobile and in the cloud.

Try Zebra BI Visuals for Power BI

Mastering Variance Reports in Power BI

One of the most important features in any business dashboard is the ability to present variances in a clear and easy-to-understand way that makes an impact. In this article, we explain the different types of variance reports and how to set them up in Microsoft Power BI.

Essentially, variance reports show the difference between the planned or past financial outcomes and the actual financial outcomes. No organization can expect to make informed decisions without having this insight and variance reports are all about providing it.

When you are working in Power BI, the default set-up lacks some of the features you need to create variance reports. However, Zebra BI has worked really hard to create Power BI custom visuals that help you tell the most convincing story. Variance reports are a big part of that and we will look at how to use Zebra BI to create them since many users still have issues creating them. Read more

Microsoft income statement – FY18 Q4 financial results in Power BI

On July 19, 2018, Microsoft Corp. announced the financial results for the quarter ended June 30, 2018, as compared to the corresponding period of last fiscal year.

The results are impressive, for example:

  • Revenue was $30.1 billion and increased 17%
  • Operating income was $10.4 billion and increased 35%
  • Net income was $8.9 billion GAAP and $8.8 billion non-GAAP

But what caught my attention, was the fact the press release included only standard tables with additional downloadable Excel, PowerPoint, and Word documents.

So my immediate thought was, where's the Power BI presentation of these fabulous results???

So I tried to redesign the tables in an attempt to present the financial results in a clearest possible way, by leveraging the amazing Power BI capabilities, together with the advanced visualization capabilities of Zebra BI visuals for Power BI.

Alright, here we go:


View as PDF


What do you think? Please let me know in the comments!


Note: This is not an official presentation of Microsoft Corp. financial results. It's just a quick study of how financial data such as income statements can be visualized in a clear and understandable way in Power BI as an alternative to standard data tables.

Source: Microsoft Corp. Earnings Release FY18 Q4

Zebra BI visuals for Power BI can be downloaded at https://zebrabi.com/pbi


Zebra BI Visuals for Power BI 1.3 Released!

Zebra BI Visuals, the only IBCS®-certified software solution for Power BI, has just been updated to version 1.3 that brings several exciting new features that make it easier to create convincing and clear visual business presentations in Power BI.

This release is available for existing subscribers as well as a free trial and delivers improved comparisons and more options for formatting and designing data.

Let's browse through some of the new features in version 1.3:


Pixel-perfect profit and loss layouts with waterfall charts for improved readability

Support for multiple scenario comparisons that delivers the highly requested actuals vs PY vs plan reports



Ability to display yearly totals next to monthly charts within the same visual

Additional improvements to small multiples

By setting the advanced layout options for small multiples and by controlling the minimal width of data categories in charts, you now have even more control over creating high-density displays of your data. Make sure you check out the 'Auto' and 'Smart row' datavis algorithms in Small multiples section and try out which sort order best suits your needs:


Significantly upgraded displays of hierarchical tables

Hierarchical tables became even more powerful. For example, check out this P&L statement with comparisons to PY and plan at the same time:


Along with many other improvements and new features, Zebra BI Visuals for Power BI v1.3 allow you to build even more powerful business reports and dashboards. Here you can browse through a few more live examples, published directly from Power BI using the Publish to Web function:


Make sure you download the free trial!

In addition to out-of-the-box support for IBCS standards, Zebra BI visuals for Power BI feature 1-click data sorting, powerful outlier handling, advanced small multiples, responsive visuals, improved navigation and full customization. With support for Power BI, best practice reporting is now available on the desktop and mobile and in the cloud.

Try Zebra BI Visuals for Power BI

Income Statement Excel

How to Design Income Statements in Excel

If there is one report that's a business constant, it's the income statement (also known as a profit and loss statement). Its core purpose is to show managers and investors the bottom line of a company for a specific period. It is normally delivered as a structured spreadsheet that shows how the top line (revenues) is transformed into the bottom line after taking out expenses.

There is a much better way, however. An income statement tells a very important story about your company and you can tell it in a much more effective way. Instead of relying on just numbers, you can use a visual language that makes your story not just much more attractive but also much easier to understand.

A Basic Income Statement

First, let's take a look at a relatively simple income statement showing business results for two years.

Income Statement Excel

Tip: There are many things you can do to improve the readability of your report. Some are more subtle than others - for example, in the structure waterfall chart, the numbers that have positive contribution are displayed to the right of the bar, while negative contribution values are marked on the left. This is a good practice that clarifies your story without any added visual noise.

The first and second columns of waterfall charts show the different types of revenue and costs that make up total profit across two different years. The first section of the chart shows revenue sources and growth that contribute to the overall profit, followed by costs, such as purchases, material and other operating expenses that reduce the overall profit. Note the "Operating expenses" which is a subtotal of all other expenses above it. This intermediate calculation improves readability and helps visualize the end result. These subtotals are actually very easy to do in Zebra BI. You only need to tell the software how many rows above a certain cell should be included in the calculation and Zebra BI does the rest.

When you create a report like this, you use the "Structure" waterfall chart. This type of waterfall chart shows the structure of the final profit and contributions of individual items. You could also use the "Variance" chart that puts the focus on actual differences.

The two waterfall charts are complemented by a variance bar chart and lollipop chart on the right:

Income Statement Variance Charts

The bar chart displays absolute values and their change. The lollipop chart represents relative growth or decrease in percentages. The important thing about the variance chart is that it is scaled. The length of the chart now corresponds to the actual difference between 2012 and 2013 values. (You can switch between absolute and relative variance charts with the click of a single button in Zebra BI chart properties.)

Tip: Use a waterfall chart instead of a bar chart to display absolute variance. You will be able to visualize not only the values but also how they contribute to the total difference. Like this:

Income Statement Excel Waterfall

When working with comparisons, you will soon meet the category class column. Instead of using negative or positive numbers, you can use the category class column to define whether an item is a positive or negative addition to the total. You can read more about the category class column here. Using this feature you can easily invert KPIs that have a negative contribution despite being a positive value. For example, an increase in the cost of raw materials is a positive value with a negative impact on your overall profit. Zebra BI will show it in red, despite it being a positive value.

Taking It Up a Notch

In real life, you might want to show data for more than just two years. A typical scenario is a report with the current and past year data and plans for the coming year.

Income Statement Excel 2.5

Notice a different approach to designing the report. With three data sets we are coming up short on space. Since the variance data is much more relevant, we used a table and chart combination. The PY, AC and BU values are displayed in a table, while the more important variance data is visualized with charts.

The bar and a lollipop charts are telling the same story as with the previous template. However, we now have two pairs of them - one pair for showing variance from the past year and the second pair displaying the difference compared to the plan.

The great thing about Zebra BI is that you have complete design freedom. For example, you could change the order of charts or even place data columns between charts.

Tip: When designing a more complex template like this, decide on an order of charts and stick to it. I prefer showing the variance compared to the previous year and then the comparison to the plan. You might want to use a different approach, just make sure to stay consistent. This way, you'll ensure your income statement always tells a clear story at first glance.

When designing a report like this, make sure that all the charts that visualize the same type of data are scaled. You can see here that bar lengths clearly display the difference from the previous year and the plan. Keep in mind that this type of reports is fairly useless if it's not scaled. Luckily, scaling is one of the strong points of Zebra BI. To scale your charts, you just need to select the charts you want to adjust and click on a single "Scale" button.

The more the merrier

These two sample income statements were for a single company. But what about if we are creating an income statement for a group of companies, subsidiaries or profit centers? In this case you might want to show multiple countries or companies on a single page. Take a look at an example:

Income Statement Excel Multiples

Data here is more aggregated - it's five smaller charts with waterfall charts showing key data: net sales, costs, operating profit, EBITDA and net result. On the right are variance charts showing difference compared to the plan. This is a great way to show the picture of your business on a single page.

Again, pay attention to scaling. This report will only work if it is scaled - it must make it clear that Germany is the largest market, followed by France and Great Britain. In this example, the variance bar charts are also scaled to the waterfall charts to show the actual difference.

Tip: When designing visual reports like this, pay attention to labeling and headings. Clear and understandable headings help you tell the story while comments help you draw attention to significant data points.

This is a very complex page - it contains 10 charts in addition to the headings and labels. However, these visualizations comply with the IBCS standards, which ensures that you can easily read this page without losing sight of the overall picture.

Export your work

Zebra BI has predefined page templates. The page templates fit perfectly into a PowerPoint slide, so you just need to enter your data, create your charts and export everything to create a presentation. Page templates include an area for inputting data and an area for designing your reports. In the first area, you prepare your data that will tell your story. Then simply move your charts into the areas predesigned for them. This is one of the easiest ways to create an income statement for your presentation.

You can choose between a number of templates available, including pages that display up to 25 charts. This way, you can choose the page with the data density you require - from a 2x2 grid up to 5x5 grid.

Want to create income statements like these?

If you'd like to create income statements that you see in this article, click below to add Zebra BI to your Excel:

Try Zebra BI

A different way to present cash flow statement

This is a guest post by Saša Butina, a consultant at SASH reporting. See more about SASH reporting at the end of the post.

When one monitors financial activities - inflows and outflows from company’s bank account, he/she usually analyses and presents data in table. It’s just something we are used to and basically there is nothing wrong with it. We should have enough time and carefully read through cash flow statements to clearly see what is happening with our most liquid assets. Let’s be honest - at the end, cash is king and cash is what every business is about :).

But, as life is getting faster and faster we often miss the time to carefully read all reports, especially if they consist mainly of tables with lots of data. In such cases the reader needs to really focus, understand and have good analytical skills to get to some meaningful conclusions based on reports. That is why all BI (business intelligence) solutions put more and more effort into providing good and simple options to visualize our data. The reason for this lies in fact that most people are visual types and are able to see trends and anomalies faster from charts than from tables. Lots of companies already use different BI solutions with such options and present their business results using different charts.

Different type of charts for different type of data

Most commonly used charts in general are:

  • time related charts, showing sales or costs or profit during different time periods, and
  • structure charts showing sales structure by products or markets, costs structure by cost type, inventory structures by product and so on.

There are lots of other data commonly presented in charts but we rarely see cash flow statement data presented in any type of chart. Even if we do, such charts usually are not very user friendly. You can see few examples of such charts below where it is not clear what the message of each chart is. These charts are not easy to read and therefore do not bring added value compared to data presented in tables.

When we present cash flow or any other data in charts we should make sure that:

  • the message we want to deliver is clear and understandable,
  • we use colors that the reader instinctively knows what they mean,
  • we give the reader some recommendation on future actions / decision.

In other words – the reader should get the message with one look at the chart and should quickly know what to do next. Let’s take a look at the chart below to illustrate our point.

Cash flow statement presented with contribution chart

To explain this chart, we should start with grey bars, which present cash position at certain point of time (end of each month in this case). We can see, that at the end of February 2017 cash position was 56k€, at the end of March it was 65k€ and at the end of April it was 47k€.

Note: In the chart above the selected period is a month, but we can also show data weekly or even daily. It depends on data we have available and which we want to look at.

Further, let’s explain the green and red bars in between. They present net inflow (green) or outflow (red, obviously :)) from different types of business activities.

  • Cash flow from operating activities is one generated by company’s core business operations (receivables collection, wages and payables payments).
  • Cash flow from investing activities is the one generated from buying or selling equipment, assets or financial investments (for example shares in other companies).
  • Cash flow from financing is mainly generated from debt, loans or capital changes.

Chart shown above was created at the beginning of May 2017, when the last actual date was end of April 2017. The left part of the chart is presenting past actual data and the right side of chart is reserved for presenting forecasted cash flow for next three months. "Forecast" bars are striped while "actual" bars are colored fully – so you can immediately see what is actual and what forecasted data. On the far right side of the chart you also see red arrow pointing down and two numeric data. These are showing the difference from last actual cash position to last forecasted cash position. The difference is minus 13k€ which means 28%. It is quite a lot and cash position in amount 34k€ is not enough for this company to avoid liquidity issues. Therefore, you can also see red caution alert above the grey bar.

The general rule for services companies is that cash position in every point of time should not be less than the amount of three months costs.

Three questions that come to mind

  1. How do we prepare such a chart, technically?
  2. How do we prepare cash flow data that is used to prepare such chart?
  3. How do we prepare cash flow forecast for next three months?

1. Preparing cash flow contribution chart technically

To prepare such chart you need Excel, plenty of Excel skills and plenty of time. 🙂 Even though Excel 2016 introduced waterfall charts, the default Excel waterfall chart is still too limited for advanced cash flow analysis.

But there's also a quicker and easier way. Instead of doing it manually, you can try using Zebra BI, a great Excel Add-in that allows you to create advanced business charts in a couple of clicks.

The above cash flow statement data is presented in type of analysis called contribution analysis. It is the optimal data visualization in cases where the whole (total) values (visualized as "fixed" columns or rows) are explained by partial (subcategory) values (usually visualized as red/green columns or rows). In our case, the whole value is cash position at certain point of time, while the partial values are net income/outcome from different type of business activities (operating, investing, financing).

You can start your 30-day free trial of Zebra BI here: https://zebrabi.com/free-trial/.

Once you have Zebra BI downloaded, prepare your data as shown on the left, then insert Contribution>Variance Column chart.

Note: You can learn more about creating waterfall charts with Zebra BI in our Support Center.

2. Preparing cash flow statement for past actual data

In general, you have two options / methods to prepare cash flow statement for past periods. Direct and indirect method.

Direct method means that you summarize and classify each inflow and outflow from your company’s bank account. It gives you the best information on your cash activities. However, this method is not preferred and used by most companies since it requires much more effort to prepare.

This is why most companies prefer indirect method of preparing cash flow statement. It means that you take your Profit and loss statement and your Balance sheet and calculate Cash flow statement out of them. It is more convenient to prepare Cash flow statement using indirect method and besides that, this method gives you another point of view.  It clearly shows a connection between reported net income (from Profit and loss statement) and cash provided by operations (as the most important category in Cash flow statement).

3. Preparing cash flow forecast for future months

In our previous chart we also presented cash flow forecast to the right of cash flow for actual data. When it comes to cash flow forecast, we should distinguish between:

  • short term forecast (few weeks) and
  • longer term forecast (few months up to half year).

To prepare short term forecast you first need to have a list of receivables and payables with due dates and should know if and which of your customers tend to delay with their payments. Furthermore you should also take into account all other potential activities that have bigger impact on cash flow (such as investments, loans taken or repaid…). Based on all this information you can prepare inflow and outflow forecast, but for no longer period than a few weeks.

Note: To increase reliability of your business and avoid liquidity issues, you should plan your cash flow for longer than 2-3 weeks. You should know today whether you will need additional financial resources in 4, 5 or 6 months so that you have enough time to arrange them.

Preparing longer term cash flow forecast is not a piece of cake :). The information we talked about earlier (receivables and payables with due dates…) does not give you a good basis to prepare the forecast for next 6 months, because when you're dealing with such long periods, there are also other factors that influence your cash flow (for example season influence, growth…).

SASH reporting team has developed advanced algorithms to calculate future cash flow based on your past actual data. Different types of cash flow projections are suitable for companies in different stages and in different industries and SASH reporting cash flow projections have all of it in mind. This kind of report gives you better insight and ability to make better decisions regarding your planned cash-flow forecast.

We believe that many SMEs lack know-how and resources to prepare and present such cash flow analysis but that doesn’t mean they don’t need it. SMEs need cash flow analysis as much as big companies do. In our opinion even more, because they can run into liquidity problems even faster. For example, SMEs which raised an investment should know their burn rate in order to know if and when they will have to provide additional financial resources. SASH reporting is the option of help for SMEs in need of cash flow or any other type of analysis.

Would you like to see your cash flow in a contribution chart?

Visit our web site www.sash-reporting.com or send your e-mail with your needs to [email protected].

SASH reporting enables you to improve your processes, get better insight in your business and above all achieve greater results.

Trump’s Chances – Redesign of The New York Times’s Infografic

In the recent update of The New York Times's article Who Will Be President? the authors used an interesting and a slightly controversial infografic, consisting of 16 sparklines. The graphic depicts the trends of Donald Trump's winning probabilities in the states, where his chances have increased most over the last two weeks.

Unfortunately, the visualization fails to convey a proper picture of the trends due to inconsistent scaling of charts, proving once more, that the correct scaling is still one of the hardest problems in data visualization. We'll present 3 alternative solutions that allow the readers to properly assess and compare changes across the states.

Read more

Difference highlight: the fastest way to improve your reports and dashboards

The goal of visualizing data with charts instead of plain tables is to bring clarity to the readers and decrease the time it takes them to get the key piece of information from the data.

This is usually achieved by removing all unnecessary items from your visualizations and emphasizing the important values. One of the best tools for this is a difference highlight. Here's how it looks like:

Read more

Sales by salesmen YTD FY - AC BU FC

Top 3 Ways to Report your Variance Analysis in Excel

Most companies create plans and budgets to establish benchmarks for future performance in sales, production, operations, labor, etc. The starting point of these plans and budget are usually estimated cost and revenue figures. The goal is to meet these budgets, but as with all goals - they are not always met. Managers use variance analysis to track the actual performance against these goals. If this analysis is not performed afterwards, then setting budgets is useless.

Read more

Struggling to visualize your data? Zebra BI’s got you covered!

Whether it’s waterfall charts, variance charts, or lollipop charts, Zebra BI has everything you need to visualize your data for all to understand.

Picture this: you’re about to present your company’s quarterly earnings to its top executives. You’ve spent countless hours gathering the needed information and placing it into a chart in Excel. You’ve even tried your hardest to make the chart look as appealing as possible. (Unfortunately, you thought this entailed simply adding different colors to different columns.) Upon beginning your presentation, however, you notice that your report looks similar to the one shown below and might be difficult for people not familiar with the information to properly analyze or understand.

Read more