Skip navigation

This is a simple demonstration as to how you and your firm can get started turning the data that you already have into the information you desperately need using tools you already own. The task of turning data into information for decision-making is the essence of business intelligence (BI).


So, here we go.


Everybody has data


Everybody has data. Many companies are wallowing in data. What they are lacking is “information.”


Read my posts here and here for more about the differences between data, information and knowledge.


Quick! Take five or ten minutes to peruse the following table of data and write down everything that you see in these data to help make decisions about the firm’s future.





I will give you one hint: the column identified as ‘ARPAC’ is “Average Revenues per Active Customer.”



Okay. Times up.


Hold on to your list.


Turning data into information—simply, easily, cheaply


In order to produce what follows, I used only Microsoft® Excel™ and its native ability to access databases to fetch and refresh data.


Here’s the first graph I produced:





This is nothing more than a simple bar graph of column “SOSales” (Sales Order Sales, as opposed to Invoiced Sales, for example) shown in the data above. I used Microsoft’s native capabilities to add a “trend line.”



By looking at this simple graph, several questions might come to mind that would bear further investigation:


  1. Why have our monthly sales dropped from just over $8 million a month to an average of about $6 million per month over these 29 months?
  2. Why or how were able to produce about $11 million in sales in July of 2008? What did we do differently? How can we build on what we learned in that experience?
  3. Is my drop in sales related to lost customers?



The next graph that I produced looked like this:





This graph answered my question number three above—at least partially. Month-to-month our firm has stayed pretty steady in terms of the number of active customers served. The firm is hovering right in the 250-customers-per-month range.



On the one hand, that is good. It means the firm is steady in this regard, but it does provoke other questions that would need to be answered through further digging:


  1. We are serving about 250 customer per month, but is the same 250 customers, or do I have high turnover rates for customers?
  2. Are we constantly having to spend precious marketing resources to capture new customers, or do we have a high volume of repeat business?




But wait! If we are not loosing customers (at least in numbers), but our sales are falling off (in aggregate), what is that telling us?




The third graph I produced was “Average Sales per Active Customer” (month-to-month). This graph clearly shows that between January 2008 and May 2010, the firm’s average sale per active customer fell from about $32,000 per customer to under $25,000 per customer.



Here again, this graph immediately provides clues worthy of further, more detailed, investigation:


  1. Are these different customers buying less product? Or, are we serving pretty much the same customers, but they are just buying less from us?
  2. Either way, we should figure out why: Are they buying similar quantities, but our prices (and, perhaps, margins) have shrunk over this period? Or, are they buying smaller quantities of merchandise or services from us?
  3. Either way, we should find out why: If they are buying smaller quantities, is some of that business going to our competitors?



Next steps


As you can see, turning the data into information allows our mind to quickly digest it and move toward decision-making. In some cases—perhaps many cases, when you first start—the process will lead to further information gathering.



On the other hand, you will sometimes discover that tribal knowledge already present in your organization will help you take immediate steps to begin making more money tomorrow than you are making today. Frequently, those steps involve no investment at all. Sometimes all it take is understanding better what is happening. Other times, a simple policy change permits significant increases in Throughput and profits.



After all, isn’t that really what you want to do—not spending six-figures on a new business intelligence “solution”?




Read more here about unlocking “tribal knowledge.”



How I did it step-by-step


  1. Identify the data
  2. Build a SQL Server view or query
  3. Connect Microsoft Excel to the data
  4. Build the graphs



Total time: about 2 to 2.5 hours



Richard Cushing is a senior consultant at RKL eSolutions, LLC.

Almost a year ago I wrote an article entitled, “What does ‘demand-driven’ really mean?” in which I outlined a view of a supply chain driven end-to-end by real-time (or near real-time) demand feedback. My recollection of this writing was triggered today by an article that appeared today on the Financial Times website: “Technology: Smarter software helps minimise discounting.”



In the FT (Financial Times) article, Claer Barrett writes:


“As retailers grapple with falling consumer spending and rising costs, the smart use of technology is proving a valuable weapon.

“Creating a point-of-sale linked supply chain is the latest tactic that larger retailers are employing in order to manage inventories and minimise discounting.”


Among other things, Barrett discusses how the entire supply chain—from the retail all the way back to the manufacturer—is being forced to cope with greater and greater uncertainty. At the same time, Barrett correctly points out that today’s “consumer is more empowered than ever before” via online shopping and price-comparison options.



Barrett’s discussion of the matter leads directly to another topic on which I have written here a number of times—namely, market segmentation. [Click here for more.] Retailers everywhere are learning to collect and leverage high volumes of point-of-sale data, mostly through the proliferation of loyalty programs. [Note: I just checked my pockets. I must be a member a more than dozen loyalty programs ranging from pet supply stores to gas stations and more.]


Between a rock and hard place


Even with improved ability to segment the market and identify buying trends and patterns, the whole supply chain is still caught between the “opposing problems of excess inventory and stock shortages,” as Barrett puts it. Barrett, however, is far too gentle, I think. The horns of the dilemma should really be stated as



excess inventory versus stock-outs.



Almost everyone who has had responsibility for managing inventories of any kind knows exactly what I’m talking about. Being short on stock (low inventories) does not on whit of damage. But being out-of-stock means


  1. Lost sales of the out-of-stock goods
  2. Lost sales on other goods that may have been purchased by customers seeking the out-of-stock item(s)  
  3. Potentially, customers lost temporarily or even permanently to competitors  



As I have stated elsewhere, the value of losses resulting from out-of-stock conditions—if calculated at all—is almost always vastly understated.


However, on the other end of the spectrum, even though the supply chain suffered out-of-stocks on (almost always) the most popular items, they are almost never able recoup the profits on those items for which they are overstocked.






In fact, chances are they will have to liquidate their overstocked item at or below the price they paid for them. Hence, Barrett’s reference to finding ways to “minimise discounting.”



The key to creating more profits is a “demand-driven” supply chain



My article on a demand-driven supply chain suggests technology that is within the reach of almost every retailer today—not just the big-box merchants. But it requires management to seek two things that they are presently overlooking in far too great a degree;


  1. The true cost of out-of-stocks to their operations and to the entire supply chain      
  2. The return-on-investment available to them for building a truly connected and collaborative supply chain



If you are a mid-market retailer, distributor, wholesaler or manufacturer, do not delay in pursuing the discovery of ways to create for yourself a sustainable competitive advantage even in a very challenging economy.




Further reading: Dynamic Buffer Management (DBM)




Richard D. Cushing is a senior solution architect at RKL eSolutions in Lancaster, PA.

Filter Blog