In our previous post, we announced the beginning of a new series about modern Business intelligence (BI) tools. As the adage goes, “problem first, solution second” – today we’ll start by defining our problem. Let’s consider a fairly common scenario for a large company, one that almost every company, where I happened to work encountered with. Suppose that a top management team holds monthly meetings to review the results of the past month. Their key goal is to maximize the company’s dividends and profits.
Hence the team needs a tool that would display the historical profit trend with some other key indicators for the reporting period. The tool is needed to understand where and how profit is formed, and what are the main drivers for profit growth. We suggest using an analytical dashboard as such a tool.

Problem Statement

Our goal is to design and create a Dashboard using the Superstore Sales data (which is really close to reality) to provide answers to the following questions:

1. What are the performance indicators values for the past month? It’s necessary for stocktaking and comparing it against the same period last year.
2. What key factors do affect profit growth?
3. What categories, subcategories, products and clients generate more profits, and what ones that bring losses?

Reviewing Data

The data contains information about customer purchases (Orders list) and returns (Returns list). The purchasing data includes all available information on orders: record ids, order dates, order-processing priority, number of items, sales and profit margins, discounts, shipping options and prices, customer data, and other useful information. But are only interested in the Orders list.

Snippet of the Orders list

Designing a Layout

We’ll position the header with a brief description on top of the page. Then, goes the time-based filter on par with the header. And the subheading “KPI” on the next line.

First of all, we want to generalize key changes according to the factoids:

• Profit and YoY growth
• Sales and YoY growth
• Orders count and YoY growth
• Avg Discount and YoY growth
• Number of customers and YoY growth
• Sales per Customer and YoY growth

Below will be a graph presented as a tree-like map (or equivalent) with top regions by sales count. It will be comprised of different rectangles, the size will correspond to sales volume while the color to profits made. This brings more clarity and helps understand which regions are most effective. It would be great if the reviewed BI tool would provide expanded information upon clicking on a region so that we could see the difference between regions.

More to the right will be a graph with a historical profit trend, displaying how profits change over time. We will try to dot the reviewed month and the same month last year to trace a trend.

Next is products and customer segments. The horizontal bar chart on the left side will be displayed sales volume and profits arranged by categories and subcategories. And try adding a filter for top product names by profit if the BI tool functionality allows so.

Learn more about how to build an interactive waterfall chart

On the right is a horizontal bar chart with top products sorted by profit
volume.

On the bottom of the page, there will be a horizontal bar chart displaying most lucrative clients. It’s very similar to the previous one, but instead of product names will be shown names of customers grouped by their segment and amount of generated profits.

To sum it up, our dashboard layout will look something like this:

Dashboard draft layout