How Much? – BI Intro (3)

Previous article: How Many? – BI General Question (Part 2)

Next article: How Often? – BI General Question (Part 4)

This is the third part of a general introduction to BI using a case study of a mobile phone store.

How Much? – this relates to the money handled by the enterprise and measures are done over fixed periods of time (e.g. monthly, yearly or even since the start of the enterprise).
Again many enterprises report this data as tabular or bar chart forms, but proper analysis can often reveal much more than what the data seems to represent. Back to our high street phone shop example, here are the sales figure for phones sold on a monthly basis:

Table 2: Phone sales figures for the year
Avg Unit Cost Sales
(kRs)
Jan Feb Mar April May Jun Jul Aug Sep Oct Nov Dec
2500 Bar 1122.5 1117.5 1002.5 1280 1087.5 987.5 950 975 987.5 1100 1025 967.5
3300 Flip 828.3 663.3 478.5 653.4 613.8 580.8 514.8 478.5 448.8 617.1 524.7 422.4
5500 Touch 1903 1716 1353 1903 1787.5 1771 1666.5 1699.5 1639 1677.5 1644.5 1446.5
9800 Smart 2401 2303 1969.8 2616.6 2273.6 2077.6 1979.6 1940.4 1842.4 1950.2 1724.8 1499.4
8100 Android 2065.5 2162.7 1879.2 2810.7 2324.7 2349 2511 2551.5 2592 3134.7 2826.9 2883.6
Total 8320.3 7962.5 6683 9263.7 8087.1 7765.9 7621.9 7644.9 7509.7 8479.5 7745.9 7219.4

Again, tabular form report does not tell the story, one needs to use visual representations spot the trends.

Figure 4 shows the line chart of the sales for each phone category in a month by month basis. The trend that is again apparent that we saw in figure 2 is the two high months in April and October. We can also conclude that Android and Smart phones are the biggest contributors to the turnover of the shop. However, the data is hiding more information. Let’s keep probing.

In figure 5, the total sales (turnover) is plotted as a line chart on the left axis, while the total number of phones is plotted on the right axis. The line chart reflects what we already expect, namely that the turnover is correlated to the number of phones sold. However, there is one rather curious artifact, the blue line (total sales) is below the number of phones (green line) in the first quarter, but rises and stays above it from the 3rd quarter. This means that there is intrinsic evolution of the unit price of phones which could tell the executive management a very important story. The next plot is an attempt to clarify this trend.

As we can see in figure 6, the average unit cost (total sales divided by total phones in a given month) is rising while the actual phone numbers sold is dropping. This would be the start of a deeper analysis to answer further questions that the executive management would invariably want to find. Which phone category is contributing most strongly to the rise in unit cost? Which phone category is most affecting the drop in turnover? The answers to these questions would allow the executive management to reevaluate the local marketing strategy for this shop as well as determine which phone categories the shop should promote. This kind of analysis is used to fine tune the sales and better perform in a competitive market.