QlikView Your Business. Troyansky Oleg

on a course of action of what to do about losing volume. This in turn led to looking at spending reductions that could ultimately harm their long-term goals.

      The real issue was not volume. If the company had been able to look at sales at the detailed level of volume and average selling price (ASP) by customer and product, they would have found some completely different drivers. Volume had actually grown 10 % and average price had actually decreased 4 %!

      There were in fact a number of important drivers that the company could not see:

      ● Volume was up significantly due to new customers and some new products.

      ● Some of the invisible price decrease was due to the product mix. The new customers were buying products in categories that were lower priced on average.

      ● The price increase to existing customers, announced at the beginning of the year, was delayed due to customer timetables to accept the increase.

      There was additional critical information that the company could not see. While some new products were contributing to the volume increase, some other new products were not meeting expectations. In the independent channel of distribution of their products, they were losing customers faster than they could add them (as measured by the number of store doors).

      Defining Measures of Drivers for Sales Analysis

      We recognize that companies may understand their unique drivers of sales. Here are some examples of drivers (leading indicators) of sales that we will use in the QlikView example.

      Pricing drivers:

      ● Average sales price (ASP)– Calculated by dividing the sales dollars by units in total. ASP should also be calculated at relevant levels for the business, such as product categories and customer channels. Each channel and category may have its own pricing dynamics that affect ASP.

      ● Price mix– With the calculation of ASP comes the ability to understand the price mix of your products. The mix impact is revealed by calculating the percentage of volume that each category of average price is to the total of all sales. This “weight” is then compared against the same categories of another period to analyze the effect of the change in mix.

      Customer drivers:

      ● New customer sales– Calculating the percent of new customers based on ship dates gives you a picture of whether you’re meeting organic growth goals or not.

      ● New and lost customers– Measuring the number of new store doors added based on ship to data (your own or that of a distributor) will give you market penetration achievement. Conversely, measuring the number of store doors lost will give you your attrition rate for a given channel. The net doors added/lost is a key measurement of the future of a given channel of distribution.

      ● Channel mix dynamics– Like price mix, this measurement will allow you to understand what is happening to your business as the channels that customers use to buy your product change. For example, in many categories the channels where customers buy products have changed from independent local merchants to big box retailers, which are more centrally located. We also see a shift from “brick and mortar” (physical) stores to “virtual” (online) stores. You can calculate channel mix by taking the sales in each channel and dividing it by the total sales across all channels. You can then compare this calculation to other periods to determine trends.

      Product drivers:

      ● Unit volume– It is important to be able to analyze unit volume by product categories. These categories are groupings of like products (for example, same sizes, same use, and so on).

      ● New products– Percent of new product sales to total sales will help reveal any issues with acceptance or penetration.

      ● The rate of sales– You can calculate the rate of sales for any product or group of products based on unit movement information. This calculation frequently takes the form of units per week per selling location. The selling location is usually the store where your products are sold. If that information is not available, shipment information out of your warehouse or your distributor’s warehouse can be used.

      Service-level drivers:

      ● Service level– Defined as percent of orders on time and complete. The calculation here is based on actual units shipped versus the original order and the actual ship date versus the original order requirement. If you have three orders and two of them are complete in units and on time while the third order is on time but not complete in units, your service level is 66 %. You get zero credit for any orders that are not on time and complete.

      ● Service level by reason– Calculating service level is one thing, but being able to understand service level failures requires being able to describe those failures by some relevant reason code. Losing sales because the product was not available is an example that is important to understand at a more detailed level. More detailed levels of understanding for lost sales could reveal that a supplier did not deliver to your business on time, or that your own production issues delayed delivery to your warehouse, or that your inventory system is inaccurate, and so on.

      Sales person drivers:

      ● Sales per person– This is a calculation of sales productivity. It is calculated just as it sounds; you divide sales by the number of sales people. The number can be calculated in total for the business as well as for each salesperson for comparison purposes.

      ● New accounts closed– The purpose of this driver is the same as when looking at sales in total. At the individual level, it can be a leading indicator of expected sales growth.

      ● Placement of new products– Like new accounts closed, this metric can be a leading indicator of expected sales growth.

      ● Attainment of quota– This is a measure of actual sales achieved by a salesperson compared to the quota, or goal, set in advance for that person. It should be noted that quota is not always the same as the goal in the published financial plan. This particular driver will not be illustrated in our example analysis in the following chapters.

      In order to make this analysis of these and other drivers a reality, you need business intelligence tools.

      What Data Is Needed

      To lead an organization that can answer the questions about what happened, why it happened, and what it means to the business, you need measures of results and measures of drivers of results. QlikView allows you to get at the data to do this. The data elements required to analyze your sales in ways that we have discussed are:

      ● Invoice information at the line level, including units and amounts

      ● List price information by product and by customer

      ● Invoice or line discounts (discounts used to get from list price to invoice) by customer or channel

      ● Customer master information

      ● Customer grouping designation (usually by channel or class of trade)

      ● Product master information

      ● Product classification (brands, categories, styles, and so on)

      ● Sales person information and sales hierarchy

      ● Date dimension, if available, including fiscal calendar data

      These data elements can involve data from invoice tables, as well as product and customer master tables. Some information may need to be integrated from spreadsheets.

      If your company is managing sales on multiple systems (typically a result of a recent merger or acquisition), you might need to merge data from multiple systems and map the master data from one system to another.

      Advanced Sales Analysis Makes the Data Visible and Available

      With the