Gathering Data from Sales
In business the best source of new business is the existing customer. Discovering the customer’s habits and characteristics allows the sales department to expand into new geographical territories with similar customer characteristics and/or modify the existing product lines. The key to success is gathering the proper information at the point of sale.
The best example of point of sale data gathering is Lowe’s and Home Depot. At the register, the first step the cashier takes is recording your zip code. Why? This allows the company to value territory based on sales revenue associated with the zip code. In small business, management should do this and more.
To fully grasp and appreciate gathering data from sales and its impact on business, especially small business, the reader must first understand the basic formula for permutations. It is a relatively simple formula to understand and use. With this knowledge of permutations the concept is applied in business especially at the point of sales. In addition several examples are illustrated using relational comparisons. To finish up with the importance of gathering data, results are used to reduce costs and increase sales.
[do_widget id=black-studio-tinymce-7]
Simple Permutations Formula
One of the basic math formulas used in science and business addresses permutations, i.e. the number of possible outputs or relationships from a given set. The formula is N times (N-1) + 1. As an example, if three people have a friendship then there are six possible outcomes; 3 * (3-1)+1=7. Look at the following table:
Permutations of Membership
#1 #2 #3 #4 #5 #6 #7
Tom Tom Tom Tom
Susan Susan Susan Susan
Heidi Heidi Heidi Heidi
What is really interesting about this formula is the growth rate by adding one more unit to the formula and the number of permutations expands at an accelerating rate. Look at the following table:
Number of Fields of Data Formula Possible Outcomes
4 4*(4-1)+1 13
5 5*(5-1)+1 21
6 6*(6-1)+1 31
7 7*(7-1)+1 43
8 8*(8-1)+1 57
9 9*(9-1)+1 73
This is the basic principle used in databases to is the ability to create relationships between values. The goal is to understand if a particular relationship exists that will benefit the business.
Here is a simple relationship of product to consumer gender.
Assume there are three products sold – laundry soap, window cleaner and motor oil. Intuitively the results would be a preponderance of cleaning products purchased by women and motor oil by men; but the results could surprise anyone. Window cleaner is a common cleaning agent used by men for auto maintenance and household maintenance. The only tool available to provide accurate results is an exit pay system that can identify these outcomes.
This is why the number of data fields used in the retail software become important. The product item field is naturally one field. Let’s take a look at others and the possibilities.
Sales System Data Gathering
In general there are three types of sales information and payment collection systems. They are:
1) Exit Pay – The traditional scan the product and enter the form of payment with retail.
2) Accounts Receivable – Customarily used in customized product or wholesale whereby the customer is a separate database interacting with the generation of invoices and payment collection (accounts receivable).
3) Combination – Both the exit pay and invoicing are used depending on the customer.
With sales there are three critical groups of information desired. The first is the customer group including name, address and contact information (e-mail and phone number). The second group includes products sold along with quantity and price. The third group is the form of payment. All three groups are essential in developing better marketing and advertising venues. Remember, in business the goal is to sell the customer what he wants, when he wants it and where he wants to buy.
A better understanding of these three groupings of information is warranted, let’s explore them individually.
Payment Group
The payment group of information fields is designed to resolve several issues:
1) How the customer pays for his purchase,
2) When the customer buys product, AND
3) How much labor is needed to process the customer.
Information gathered from the payment group of data fields reduces costs, improves the customer’s experience and assists the company in managing cash flow. It also helps management establish internal controls to reduce and eliminate defalcations (theft, embezzlement and miscalculations). Internal controls is explained in other articles in the accounting section.
To assist in cash flow, management would need to understand the relationship of the total purchase ticket against the different forms of payment.
Let’s look at a very basic relationship of receipt value to form of payment. With this illustration, there are three price points and five forms of payment as shown in this table:
Price Groups Forms of Payment
<$50 Cash
>$50 and <$200 Check
>$200 Debit Card
– Credit Card
– On Account
The database over time could reveal a multitude of possible relationships between the two separate categories (Price Groups and Forms of Payment) of information. For this example, suppose the outcomes are as follows:
% of Payment
Price Group Cash Check Debit Card Credit Card On Account
<$50 70% 20% 10% – –
>$50 <$200 20% 40% 25% 15% –
>$200 – – 40% 20% 40%
This simple outcome table illustrates some important relationships. A quick gleaming of information identifies that as the dollar value of sales increases there is a greater likelihood of payment with credit (credit card or on account using accounts receivable). For the novice entrepreneur this would appear meaningless. But for more sophisticated business owners it is a wealth of information. First off, credit forms of payment cost money. Credit card payments cost the usual small business 1.3% to as much as 2.7% of the sale via processing charges. Items sold on account have higher historical default rates and costs to collect (labor to communicate with customers about account status). For typical small businesses these costs approach 7 – 9% of sales on account.
The use of in-house accounts for customers consumes working capital that may be of better use for other purposes. Also the use of accounts receivable extends the working capital cycle possibly impacting cash flow from operations.
These business issues are considered when devising product sales. Most businesses want to sell larger ticket items because of the absolute dollars they can generate from contribution margin. If however there is inadequate working capital to finance absorbing the sales on account then this form of a campaign may have to be deferred. It may be more appropriate to have advertising focus on products in the $50 to $200 range reducing the volume of accounts receivable form of transactions. This also increases cash flow because more customers pay using forms of payment that exclude accounts receivable.
In small business the above seems a bit overwhelming because first it is difficult to decide on what should be tracked with sales and secondly; how to actually track the information.
In reality it is much simpler than management realizes. The key is to primarily focus on the big picture. Sell the customer what he wants in the most efficient manner. Efficiency refers to the least amount of costs to deliver the product. This is often labor costs. To efficiently deliver the product with the least amount of labor the business must know its demand timing. Here is an actual example:
Crane Island Spirits
Joe owns a liquor store on Crane Island. The liquor has a direct margin average of 27%. His second biggest cost is labor. The store is open 6 days a week from 10 AM to 7 PM. The state regulates the hours and the requirement to close the doors by 7 PM. Intuitively Joe knows Friday and Saturday afternoons are the busiest time periods. Joe has at least two employees to expedite customer service during this time frame. He wants to lower his labor costs. He decides to track demand for the product.
Tracking demand requires Joe adding a new information field to his retail exit pay software assigning a time stamp to each sales receipt. Joe has figured out that an average employee can easily manage $600 in sales volume in any given hour. More experienced clerks can handle upwards of $900 in sales in an hour. After four months of gathering data, Joe averages the results. Her is Joe’s hourly groups and daily average of sales based on time stamps.
Hour Groups
Day 10 AM – 1 PM 1 PM – 4 PM 4 PM-7 PM Totals
Monday $140 $300 $1,100 $1,540
Tuesday 140 400 1,500 2,040
Wednesday 160 500 1,600 2,260
Thursday 160 600 1,800 2,560
Friday 180 900 2,200 3,280
Saturday 200 1,100 2,500 3,800
Average/Hour $55/Hr $211/Hr $594/Hr $287/Hr
Based on the schedule above, Joe only needs an experienced clerk from 4 to 7 PM on Friday and Saturdays; it would also be wise to include Thursdays. The balance of the week and times can easily be managed and controlled by a less experienced clerk. Mondays and Tuesdays are excellent days to train new clerks.
Based on the above, Joe can reduce the payroll to just one clerk at all times contingent on experience.
This same principle is used by major retailers to provide adequate clerks/cashiers at peak times throughout the year. The concept of additional information takes it one step further than just intuitive thinking. Not only do grocery stores know that Saturdays are going to be the busiest day of the week; now with accurate information, they know what time it will start and end.
Notice with a time stamp and form of payment the business easily answers the price group questions:
1) How the customer pays for his purchase,
2) When the customer buys product, AND
3) How much labor is needed to process the customer.
In addition, based on this information a good accountant can develop internal controls over the payments to provide a high level of confidence with collection and accounting for sales.
Product Group
The product group of fields answers the big question of what the customer wants. If done properly it should answer a lot of questions for every business. The wealth of information is instrumental in success as business is about three primary goals:
1) Make a profit
2) Provide long-term security, especially to employees
3) Achieve community service by providing the product or service the customer wants
The product group of fields is designed to provide information related to goals one and three. When setting up the data fields, it is important to include the following fields:
* Item Code – Bar Code
* Item Description
* Item Size
* Sales Price
To gain true effectiveness, the system should have a cost field for the product to calculate gross margin. Usually a decent purchase order system tied into the inventory will provide this information. How valuable is this information? Let’s go back to Crane Island Spirits and see how Joe addressed this issue.
Crane Island Spirits
Based on national information and Joe’s intuition, Joe knows Vodka is the number one selling spirit. Joe has 7 different brands of vodka. Some are low quality (only distilled once or twice) others are high quality and expensive. Joe is interested in figuring out which particular brand is his best seller and which brand contributes the greatest amount of absolute dollars (actual dollars to the margin and not gross margin as a percentage).
Joe ties his purchase order system to his retail system to retrieve reports on total sales, margin in dollars and margin percentage for each of his seven brands. Here are the results for a single month.
VODKA PERFORMANCE REPORT (FIFTHS)
Brand Sales Cost Units Margin % Margin $
‘A’ $2,069 $1,417 145 31.51% $652
‘B’ 637 498 42 21.82 139
‘C’ 3,929 2,677 207 31.87 1,252
‘D’ 2,243 1,561 53 30.41 682
‘E’ 1,490 825 31 44.60 665
‘F’ 5,140 3,816 227 25.76 1,324
‘G’ 1,379 1,060 29 23.13 319
Totals $16,887 $11,854 734 29.8% $5,033
This report identifies brand ‘F’ as the best contributor of absolute dollars to the margin ($1,324) yet brand ‘E’ has the highest margin percentage contribution at 44.60%. Interestingly brand ‘E’ only contributes $665 towards the total absolute dollars.
Based on the above information Joe now knows the following:
A) Brand ‘F’ is his best seller with 227 units.
B) Brand ‘F’ also contributes the greatest amount of absolute dollars towards operating costs.
C) Brand ‘E’ has the highest margin percentage at 44.60%.
D) Brand ‘G’ has the least number of unit sales.
Joe also knows that he is only going to sell so many bottles of vodka per month. If he sells a bottle of one brand over another brand, he will only earn the marginal difference in contribution of absolute dollars. To determine this, Joe recalculates a schedule of the marginal contribution provided by each brand. Here is the schedule:
CONTRIBUTION MARGIN OF VODKA (FIFTHS)
Brand Contribution $ Bottles Sold Contribution/Bottle
‘A’ $652 145 $4.50
‘B’ 139 42 3.31
‘C’ 1,252 207 6.05
‘D’ 682 53 12.86
‘E’ 665 31 21.45
‘F’ 3,816 227 5.83
‘G’ 1,060 29 11.00
With the above information, Joe would love to sell more bottles of brand ‘E’ as each bottle generates $21.45 of value. This is $8.50 more than brand ‘D’ the next marginal contribution brand. Ideally, he would love to sell brand ‘E’ bottles over brand ‘B’ as this would contribute $18.14 more per bottle.
To gain the maximum value from the above information Joe could run several sales tests. First he could provide the premium shelf space to brand ‘E’ with more facings to entice the customer to purchase this brand. Secondly, he could eliminate brand ‘B’ from his inventory guaranteeing at least $1.19 more contribution margin (brand ‘A’s contribution over brand ‘B’s) per bottle for 42 bottles for $50 of additional contribution margin per month. This assumes those buying brand ‘B’ purchase only brand ‘A’ as a substitute. Most likely some will buy brand ‘F’ or ‘C’ in lieu of ‘A’ which will add even more contribution margin. But worse case is $50 more per month.
Other options exist too. He could raise the price of brand ‘F’ the most popular vodka and force the customer to pay more or switch brands which should increase contribution margin. This would be especially true if he eliminates brand ‘B’ which means worse case is forcing brand ‘F’ customers to brand ‘C’. Brand ‘C’ only provides a marginal contribution margin of 22 cents ($6.05 – $5.83) per bottle.
The key to product information gathering from sales is that it provides opportunities to fine tune sales to maximize absolute dollars (gross profit) and contribution margin. This form of data gathering and analysis is very beneficial to niche retailers such as sporting goods stores, athletic footwear, hobby shops, restaurants, auto parts, furniture dealers and lots more.
Customer Group of Information
In business your existing customer is your best source of new business. In the service industry clients often require service on a regular basis, think of hair salons, law firms, accounting practices, medical offices, dentists, auto repair and so on. The existing customer can provide a wealth of information. Therefore the data fields must go beyond simple name and address and should include:
* Phone Number * E-Mail Address
* Memberships * Employer
* Hobbies * Birthday
* Family Member Names
Why is there so much more information needed? It provides a resource of information to gain access to potential referrals. This is instrumental in the service sector. Most dentists send a quirky or cute birthday card just as simple reminder that he is there. Look at the following example:
Cardinal Bikes
JD owns a sport bike shop specializing in racing and touring bikes. His customers buy high end customized bikes for racing and long bike runs (100 milers called ‘Centuries’). JD uses the customer’s e-mail address to send out a monthly newsletter identifying key issues in this sport and local bike club information. Each customer has his bike club membership identified for the purpose of contact and sales volume. Once a year he pulls a report identifying sales volume for the top 7 clubs. Look at the following list and sales volume:
Club Name Unit Sold Sales Volume
Speed Racers 7 $9,283
Mean Machine 7 8,742
Need for Speed 6 8,110
Long Haul Demons 4 5,118
Geriatric Touring 4 4,089
Helmet Buddies 10 4,013
Montgomery High 3 2,714
To increase sales JD decides to focus attention on both Geriatric Touring and Long Haul Demons for additional sales in the new year. By sending thank you’s to the 4 customers from each club and a ‘refer a friend discount card’. JD is marketing in a personal fashion to other members of that club. Without this information, JD would have to use a mass marketing or advertising appeal to attract new customers. In addition JD may provide sponsorships to those clubs with minimum sales volume per year.
Finally JD uses the family member field of information to send out friendly reminders to buy family members auxiliary bicycling gear throughout the year, especially just prior to Christmas.
The key to business marketing and advertising is to discover the customer’s spending habits or desires. Then with this information let the customer know that the business has the product and is an expert about the product. Take a look at how one RV dealership focused on just the zip code of the customer.
RV Dealership
Nathan owns a reputable RV dealership serving a population area consisting of 1.8 million people. There are over 80 zip codes in this geographical area.
Over a course of five years each deal recorded had the zip code logged to a spreadsheet for sales volume. The zip codes are summarized to a master spreadsheet and the data is sorted on sales volume. The top 12 zip codes receive direct advertising material for future sales. Mass advertising such as radio and TV are geared towards the population characteristics of those zip code populations.
Over time Nathan developed better financing products and retail products (camping gear, recreational gear and family outdoor items) for this population group.
Summary – Sales Data
Gathering data from sales is an essential requirement for any business. The information used can reduce costs and expand sales.
To fully appreciate the value information provides good business entrepreneurs take advantage of the permutations formula N * (N-1)+1 which expands the relational outputs at an accelerating rate.
Sales databases have three primary groups. They are the customer, product and payment groups. Each group has fields of information and with a sound reporting system the business can relate one field in one group against any field in the other two groups. Using intuition and actual outcomes management can reduce operational costs, increase gross margins and expand sales. ACT ON KNOWLEDGE.