Bookkeeping – Retail Accounting with Item Tracking (Lesson 76)
Prior lessons focused on the construction or project driven industries. Other industries require different methods of accounting based on their particular function. One of these business sectors sells multiple products to a wide array of consumers – the retail sector. The retail business is interested in what sells well, not how well something performs against an estimate (project accounting). The retail business uses a different type of accounting to analyze financial performance.
Why is retail accounting so important? How is retail accounting conducted? What exactly should an accountant prepare as reports for a retail establishment to help owners or management understand their business? This lesson covers these questions and explains the fundamentals of retail accounting and the technology used for retail accounting. I’ll provide helpful insights and some industry specific formats to help management understand the financial performance of the retail business.
[do_widget id=black-studio-tinymce-7]
Retail Accounting Fundamentals
One of the adages of business is to sell the customer what he wants. There is nothing more embarrassing than not having in stock the item the customer wants. It is one thing if your store is a boutique and the customer wants tires but it is really downright erroneous not to have red roses in a floral shoppe. To resolve this, management uses retail accounting and its associated item tracking to ensure proper items are available when the customer buys the product. The first two fundamentals of retail accounting are:
1) Available Product at
2) The Proper Time.
As an example, there isn’t much demand for pool cleaning products in Michigan during January and February. However, it should be available in December as there is a good probability of sales associated with Christmas gift giving.
The next fundamental of retail accounting is directly tied to the economic concepts of supply and demand associated with price elasticity. For those of you that didn’t take economics, this simply means at what sales price will the demand for product begin to decrease. Management is trying to achieve the best price for maximum sales. For example, suppose your store sells doughnuts. At what price will sales peak? Look at the following chart:
Price Per Doughnut $.49 $.59 $.69 $.79
Units Sold 3,050 2,900 2,600 1,800
Total Dollar Sales $1,495 $1,711 $1,794 $1,422
In this case, at 69 cents the revenue is maximized for doughnuts. I know, it would be great to sell 3,050 doughnuts at this price but the customers are voting with their pocketbooks. To them, 69 cents each is a fair price and still maintains demand. Any higher and sales no longer peak but decline quickly.
Now there are three retail accounting fundamentals:
1) Available Product at
2) The Proper Time for
3) Optimum Price.
There is actually a fourth fundamental – the right location.
In retail, location is often at the forefront of the business retail fundamentals. This is derived from the old business saying of “It’s all about location, location, location”. This is not only true with store locations but also with the location of the actual product in the store. In a feed and seed store, where is the best place to locate equestrian supplies. Another example, 7-11 stores purposely locate their best sellers along the interior walls forcing the customer to pass other products to get to the item and then pass by them again to get to the register. The arrangement creates greater instant sales bumping up revenue and overall profit.
Location also plays a role in customer selection. Eye level items tend to have greater sales volume than other spots.
Thus, there are four retail fundamentals to track:
1) Available Product at
2) The Proper Time for
3) Optimum Price in
4) The Right Location.
How does an enterprise track this information?; answer: good retail accounting and item tracking.
Retail Accounting Software
General accounting software lacks the capacity to serve retail establishments. Retail involves high volume multiple product transactions per day. Traditional small business level software will receive upwards of several hundred line entries per day. Retail will have several hundred line entries per hour covering upwards of a thousand different items. Typical grocery stores carry 8,000 to 10,000 different items and sizes. In lieu of accounting software, high volume activity stores use retail software. It is very similar except retail software is limited in focus to tracking the following information:
A) Sales of Products by UPC (Universal Product Codes) and the Corresponding:
– Date and Time of Sale
– Price
– Location
B) Method and Amounts Paid
C) Cost of the Item Sold
D) Inventory Balance of the Item Sold
E) Discounts, Returns and Allowances
F) In Some Cases, Customer Balances
G) More Sophisticated Retail Software also Provides Inventory Related Information:
– Purchase Orders
– Receiving
– Shelf and Warehousing Information
– Accounts Payable
– Custody Status
Notice the first piece of information retail software tracks directly corresponds with the four fundamentals of retail accounting. What product was sold at what time for how much and its location. The other pieces of information correspond to inventory and cash control.
In a typical small retail environment, the retail software produces a single complex journal entry to post to the accounting software. The accounting software does not track the retail level details. Here is an example of a daily entry from a retail software for a tobacco store.
Sales Journal
Date ID Ledger Dept. Description DR CR
09/30/16 16189 Sales Cigarettes Total Sales for the Day $3,196.47
16189 Sales Tobacco Cigars, Pipe, Chew 763.59
16189 Sales Accessories Humidor, Lighters, Etc. 357.91
16189 COS/Produ Cigarettes Cigarettes $2,080.90
16189 COS/Produ Tobacco By Pound 308.15
16189 COS/Produ Accessories 16189 Detail 103.50
16189 Sales Tax 280.66
16189 Discounts Cigarettes Coupons 107.00
16189 Discounts Tobacco Advertisement 18.00
16189 Returns Accessories Humidor 97.40
16189 COS/Produ Accessories Humidor Returned Item 43.29
16189 Sales Tax Returned Item 6.23
16189 Inventory Total Costs 2,449.26
16189 Checking Deposit to Checking 327.15
16189 Credit/Debit Delay Cash Account Delay 4,042.85
$7,091.18 $7,091.18
The retail software provides details related to item tracking. In the above case, retail software identifies which brand or length of cigarettes are the best sellers. Over time, the software will provide information such as price elasticity for particular products and what spot in the store is the real value center for sales. This is known as item tracking.
Item Tracking
Item tracking takes retail accounting to the next level. It is literally putting the individual items sold under the microscopic eye of management. Management will always want to know the top 10 items sold in the store. This is in regards to volume. In addition, they will desire information about which product sold creates the highest margin per unit sold and which item overall produces the greatest amount of gross profit. The following three subsections explain this in more detail.
Item Tracking – Volume
Every owner would desire to have a single product that simply outperforms other products in sheer volume and produces great margins. The reality is more in line with few items with lots of volume generating a significant portion of sales. Knowing this volume relationship is essential to not only making sure there is adequate inventory available but it is often what keeps the customer coming back again and again.
My son works at a Tractor-Supply retail center. At dinner one night I asked him what is the number one volume driven product sold at the store. His response startled me, dog food. It finally made sense to me; many of the customers have small farms or large estates and dogs serve both as security and for hunting purposes. He also mentioned that other forms of feed did well too. A manager of this kind of store, needs to know how much volume of a particular product is sold. Retail accounting software can provide this type of a report to assist him.
Feed Group – Top 10 Selling Items by Volume October 2016
Item Type Units Sold Price/Unit Total Sales
A Chicken 311 $12.99 $4,039.89
B Pig 74 15.84 1,172.16
C Chicken 107 14.99 1,603.93
D Dog 402 9.99 4,015.98
E Horse 57 13.49 768.93
F Dog 517 8.79 4,544.43
G Horse 84 12.79 1,074.36
H Chicken 212 14.69 3,114.28
I Goat 87 17.94 1,560.78
J Dog 184 13.29 2,445.36
. $24,340.10
In the above illustration the manager of a feed store offers over 50 different animal feeds. The manager wants a report of the top 10 sellers during a single month. From the above, it is obvious that the manager never wants to run out of item F. Honestly, all 10 items should never have low inventory as these products sell daily. If rearranged based on volume, here is the report.
Feed Group – Top 10 Sellers by Volume in Order, October 2016
Item Type Units Sold Price/Unit Total Sales
F Dog 517 $8.79 $4,544.43
D Dog 402 9.99 4,015.98
A Chicken 311 12.99 4,039.89
H Chicken 212 14.69 3,114.28
J Dog 184 13.29 2,445.36
C Chicken 107 14.99 1,603.93
I Goat 87 17.94 1,560.78
G Horse 84 12.79 1,074.36
B Pig 74 15.84 1,172.16
E Horse 57 13.49 768.93
. $24,340.10
Notice with this report the items are arranged by volume and not total (sales) generated. Look at item rows ‘D’ and ‘A’. ‘A’s dollar volume is more than D’s yet A’s unit volume is 231 units lower. This report isn’t about the money, it is strictly about the top 10 selling items by volume.
It isn’t always about volume though. One of the principles of business is the rule about generating profit; in retail, it’s about gross profit.
Item Tracking – Gross Profit
As explained in prior lessons, the gross profit reflects the net sales price of the item less the cost of the product. Remember though in retail costs also include labor, storage, shipping and more. But with item costing the management team is strictly limiting the gross profit to the sales price and the cost of the item sold. The difference is important in understanding which item produces the greatest gross profit margin. With the above 10 items, let’s find out.
Item Type Price Cost Margin Margin Percentage
A Chicken $12.99 $9.07 $3.92 30.2
B Pig 15.84 11.58 4.26 26.9
C Chicken 14.99 10.72 4.27 28.5
D Dog 9.99 7.26 2.73 27.3
E Horse 13.49 8.66 4.83 35.8
F Dog 8.79 7.02 1.77 20.1
G Horse 12.79 8.23 4.56 35.7
H Chicken 14.69 8.99 5.70 38.8
I Goat 17.94 11.16 6.78 37.8
J Dog 13.29 9.97 3.32 25.0
With the above, item ‘I’ goat feed generates the best absolute dollars of gross profit whereas item ‘H’ chicken feed has the highest margin percentage. Notice also that both item ‘A’ and ‘H’ are chicken feed. ‘A’ costs more and sells for a lower price. Why? Most likely ‘A’ was on sale during October. Ideally, an owner would want the goat feed to also be the number one item sold. The store’s profit would soar. Look at this math.
Goat Feed – Item ‘I’
Sales @ 517 Units @ 17.94 = $9,274.98
Costs @11.16/Unit for 517 = 5,769.72
Gross Profit =$3,505.26
Dog Food – Item ‘F’
Sales @ 517 Units @ 8.79/ea = $4,544.43
Costs @ 7.02/Unit = 3,629.34
Gross Profit = $915.09
This is a whopping $2,590 difference in gross profit between the two items for one month.
Why is it important to know which product has greater gross profit because obviously most people don’t have goats as pets. The answer is simple, try to get customers to buy the product with more gross profit. Let’s stick to the dog food and compare the top three dog food items.
Dog Food – Top Three Comparison – Total Gross Profit
Item Sales Price Cost $ Margin Units Total Gross Profit
D $9.99 $7.26 2.73 402 $1,097.46
F 8.79 7.02 1.77 517 915.09
J 13.29 9.97 3.32 184 610.88
It is apparent that the customer is price conscious when it comes to dog food. But what if we could get 100 more customers to buy item ‘D’ over item ‘F’. How much more gross profit is earned? Simply take the gross profit difference and multiple by 100. In this case 96 cents equals $96 of more gross profit. Since all other costs remain the same, overall profit will increase $96. How does a store increase the sales of item ‘D’ over item ‘F’? There are many tools, one – use a center aisle display; two – advocate ‘D’s value over ‘F’s; three – make the product more prominent via display signs, eye level location or lighting. There are many retail tricks to this; the point here is that it is beneficial to management to have a hierarchy of items based on gross profit.
Even with this, a good store manager will want to give priority to the one or two items that generate the highest absolute dollars of margin for the store.
Item Tracking – Absolute Dollars
Absolute dollars is what pays the bills and ultimately net profit. To determine the actual gross profit per line item, the report must take the total sales per item and subtract the associated costs to derive gross profit. Here is that report.
Gross Profit by Item – Feed Group Top 10, October 2016
Item Type Units Sales Cost Gross Profit
A Chicken 311 $4,039.89 $2,820.77 $1,219.12
B Pig 74 1,172.16 856.92 315.24
C Chicken 107 1,603.93 1,147.04 456.89
D Dog 402 4,015.98 2,918.52 1,097.46
E Horse 57 768.93 493.62 275.31
F Dog 517 4,544.43 3,629.34 915.09
G Horse 84 1,074.36 691.32 383.04
H Chicken 212 3,114.28 1,905.88 1,208.40
I Goat 87 1,560.78 970.92 589.86
J Dog 184 2,445.36 1,834.48 610.88
. $24,340.10 $17,268.48 $7,071.29
Notice the top two gross profit dollar generators are chicken feed. The third is dog food and its absolute dollars are driven by the most volume at 517 bags sold. Without a doubt, both chicken feed and dog food generate the real value for the store’s feed department. Here they are in order of gross profit.
Feed Group – Gross Profit by Item from Highest to Lowest
Item Type Gross Profit
A Chicken $1,219.12
H Chicken 1,208.40
D Dog 1,097.46
F Dog 915.09
J Dog 610.88
I Goat 589.86
C Chicken 456.89
G Horse 383.04
B Pig 315.24
E Horse 275.31
. $7,071.29
To help the reader better understand all this, some insights are appropriate.
Insights
The above illustration was for one department in this store. This same process is repeated for the other sections of the store:
A) Pet Supplies
B) Tools
C) Farm Equipment Accessories
D) Stockyard (Fencing, Cattle Items, Tractor Attachments)
E) Truck Equipment
F) Lawn & Garden
G) Clothing
H) Seasonal
The manager then knows what sells well and what products really generate value for the store. This concept is used in all sorts of retail environments:
1) Liquor Stores
2) Arts and Crafts
3) Auto Parts
4) Equipment
5) Lawn & Garden
6) Hardware
7) Paint
8) Clothing
9) Furniture
10) Home Decor
The list can go on and on.
The real value comes with acting on this information. Every store manager should know the top three items in every department and for the store overall. Retail accounting goes beyond just knowing the top products, retail accounting will also identify the top time periods for sales. This is important to maximize customer service and reduce labor costs.
As each register transaction is conducted, the retail software puts a time stamp on the receipt. After several months a pattern develops pertaining to customer shopping habits including the time periods for shopping.
My brother owned a liquor store and used his retail accounting software to understand the pattern of activity. His store’s activity followed this schedule:
Average Volume of Transaction Activity 52- Weeks
10 AM – 1 PM 1 PM – 4 PM 4 PM – 7 PM Totals
Monday 7 19 39 65
Tuesday 9 24 41 74
Wednesday 9 27 43 79
Thursday 10 32 47 89
Friday 13 40 59 112
Saturday 22 46 70 138
This pattern confirmed his gut feeling that it behoove him to have at least two employees in the store serving the customer from 4 PM to 7 PM on Fridays and Saturdays. Mondays and Tuesdays are excellent days to conduct training for new employees. Remember from the fundamentals section above, retail accounting is about available product at the proper time for optimum price in the right location. Now is an appropriate time to explain a little bit about optimum price.
Retail accounting allows management to track individual products, product groups and the entire store at various prices. At the individual level, a price variance up or down can be evaluated over time against the unit sales at the various prices. Below is an example of a pet supply store’s bedding unit volume sales (for Guinea Pigs) against the corresponding price over three months.
Guinea Pig Bedding -1/2 Cubic Yard Package, Volume and Sales Price
Sales Price $10.99 $11.59 $11.99 $12.59
Unit Sales (3 Months) 508 506 498 462
There is a 9.1% spread in count between the two extremes, yet only a .4% count spread between $10.99 and $11.99 pricing levels. It would appear that bedding is inelastic in price (most likely because it is a necessity) up to $11.99. The store should sell this bedding for $11.99 and make an additional $490 (approximately) as profit per quarter. Notice in this case, one product’s optimum price analysis generates $1,992 of additional profit per year.
The fourth fundamental of retail accounting is directly related to location. This includes macro analysis such as where to place stores down to micro analysis, where on the shelf to place certain items. Naturally, eye level location on a shelf is more lucrative than up high or on the bottom. Vendors often advocate for their products to be placed at eye level. Good software will have location fields identifying shelf location to assist management in maximizing value. Often the best combination is to have the product with the highest absolute dollar contribution at eye level to maximize profit.
Most importantly, management uses this software to make sure adequate inventory is on hand especially for volume, high gross profit and absolute dollar items. These three elements of the product fundamentals are what drives a retail store’s bottom line. The inventory is an essential component of good retail software.
Now for some recommendations for retail software.
Recommendations
Many industries especially retail have customized software. As an example, auto parts stores have software that pulls up the car’s model by year and the various car packages. Then the software will divide the car into the respective systems and components. It continues by drilling down to the component’s specifications with pictures to assist the salesman and customer. This type of software is leased for several thousand dollars per month including annual upgrades (new model year).
Larger multiple store location networks use either customized retail software or their franchisor software.
For those small independent shops like a Mom and Pop boutique or a flower shop, I encourage them to use Intuit’s retail software that ties into QuickBooks Pro. This software/hardware package costs around $3,500 and is very cost effective.
If your store only sells 50 or less items, then use the accounting software and not retail software to act as the retail software. It will save you a lot of money. It is slightly more cumbersome with each transaction but the cost savings is worth the additional time.
None of this is helpful unless the accountant can generate good reports.
Retail Accounting – Reports
The following is a breakdown of the various reports a retail accounting software should generate for management:
Sales – Daily sales including total sales, discounts applied, adjusted gross sales, along with corresponding returns and allowances resulting in net sales. In addition, it will include forms of payment. Sales reports can also be generated over an extended period of time such as weekly, monthly, quarterly and on an annual basis.
Product
– Top 10 items based on volume over the accounting period (sorted highest to lowest)
– Top 10 items based on gross profit margin over the accounting period (sorted)
– Top 10 items related to absolute dollars (contribution margin) over the accounting period (sorted highest to lowest).
Time
– Sales volume and transaction activity during time periods (hourly, daily etc.)
– Seasonal item volume in incremental periods (weekly)
Price
– Price variances per item for volume over a set period of time
– Price changes per item
– Item costs and changes
Location
– Location of items on shelves and in warehouse
– Sales by store and by aisle
– Sales by groups and group store locations
– Place in sales section
Inventory
– Total value by cost, group value by cost, item value by cost
– Item balances
– Reorder points
– Units received per period
– Inventory worksheets
There are lots more but they deal more with accounts payable and are addressed in other lessons.
Summary – Item Tracking
With retail, management is interested in providing the customer the right product at the right time at optimum price in the correct location. To do this and maximize profit, the company must incorporate the fundamentals of retail accounting. This includes item tracking for sales volume, gross profit margins and absolute dollar earnings per item sold. Good retail accounting software has a database designed to do this and more. With this information, management can maximize sales, improve margins and reduce labor costs. Act on Knowledge.
[do_widget id=black-studio-tinymce-2]