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:
| Avg Unit Cost | Sales (kRs) |
Jan | Feb | Mar | Apr | May | Jan | 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.
