By Ray Whitley
Finding the right balance between inventory investment and customer service is arguably one of the most
important objectives for any retailer. Buy too much inventory and you tie up cash (balance sheet impact)
and risk bottlenecks in the supply chain. Buy too little inventory and you risk stocking out in the store and
losing a potential sale (income statement impact) thereby annoying the customer. The cost of a lost sale
due to a stockout varies due to individual product economics but generally the cost is high for specialty
retailers due to their high initial markups, and of course, the cost of losing a customer's ongoing share of
wallet is unacceptably high.
There are numerous tactics a retailer can employ within its supply chain to reduce stockouts. This paper
will address some of the typical methods used by retailers. However, the focus and bulk of this paper will be
on detailing innovative approaches to reducing stockouts by extracting very large amounts of data from
corporate databases, reconstituting it in unusual ways, and then leveraging it.
A SHORT LIST OF TYPICAL APPROACHES
- Buy more: Many retailers confronted with the problem of stocking out in the stores will simply instruct
their merchants to increase the size of their purchases. While this approach will reduce the probability of
a store stockout for each affected SKU, serious side effects may occur in the form of incremental
inventory processing and holding costs, an adverse balance sheet impact to cash, increased markdown
activity, a risk of bottlenecks in the supply chain, etc.
- Optimize initial inventory commitments: For example, rather than committing 70% of a purchase to the
stores and keeping 30% in the DC as a reserve, a retailer may adjust the ratio downward with the intent
of capturing more regular priced sales and staying in stock longer generally throughout the store fleet.
- Change system parameters: Retailers using systems to support their DC to store allocation process can
make adjustments to the auto-replenishment and/or allocation logic in their systems to decrease the
probability of stocking out (e.g., increase display inventory, increase replenishment amounts, etc).
Another approach is to address the front end (if there is one) of the replenishment and allocation
processes by improving the demand forecasting model to generate more accurate forecasts.
- Reduce cycle-time: Any effort to take time out of the store-sale-to-storeshelf-replenishment process will
have some positive impact on store stockouts. Typical areas to focus on would be increasing allocation
and store delivery frequency (e.g., instead of twice a week allocate and deliver three times per week),
streamlining DC processing and outbound transportation service, and optimizing the entire DC physical
network to enable faster cycle times.
Two points I'd like to make about this list. First, this is by no means a comprehensive list of tactics to reduce
stockouts in the stores. Second, every one of the listed tactics can positively impact stockouts in the stores
and therefore should be considered and perhaps attempted. As a caveat, the "Buy more" tactic should be
deployed judiciously for reasons mentioned earlier.
APPROACHES LEVERAGING LARGE DATA SETS
It is axiomatic to say that retailers are awash in data. Much of it generated at the tail end of the supply chain
through simple point of sale (POS) transactions occurring every day in every store, and through the need to
maintain inventory position for every SKU/store. Large SKU counts and large store fleets increase
exponentially the size of the databases that are managed by the IT organization. Yes retailers are awash in
data and much of it lies inert: buried in giant databases. Accessing this data, extracting key data elements,
and combining disparate data can provide new insight into the state of the business, or for this paper's
purpose, enable real progress in the ongoing effort to prevent stockouts in the stores.
What follows is a number of data-intensive approaches that address stockout visibility, potential stockouts
due to imperfect merchandise allocations, potential stockouts due to inadequate merchandise purchases,
and current stockouts due to poor execution in the stores.
ADDRESSING DATA INTEGRITY
“In the land of the blind, the one-eyed man is king.” Desiderius Erasmus
Data integrity should be confronted immediately as really poor data integrity will render any reporting
useless, generally. In particular, poor data integrity in the store inventory positions will effectively blind the
allocation team (the team within the supply chain tasked with balancing store inventories to cover demand
and prevent store stockouts). Efforts to improve data integrity should begin immediately if the SKU/store
inventory numbers are suspect.
One way to test data integrity in the store inventory counts is to randomly select SKUs in a sample of stores
and have physical counts taken and then compared to systems counts.
A data-intensive approach would be better in that the results would be comprehensive as one can check
every SKU in every store, and conclusive as the results would be based on hard facts rather than
extrapolation from a small sample set.
The first step in this approach is to obtain the complete inventory counts by store from the company's last
full physical count and compare them to the IT inventory system counts captured at the time of the last
physical inventory count. Essentially, this would be a system count to physical count comparison of every
SKU/store in the company. This comparison at the SKU/store level should have been conducted by the
finance, internal auditing, or loss prevention team at the time of the company's last full physical inventory
count. Unfortunately, the prevailing practice is to net all of the physical count to system count variances in
order to arrive at a total company shrink number for financial reporting purposes. Usually, netting all of the
variances produces shrink numbers in the low single digits (e.g., 2%).
This is not a measure of data integrity. However, using the same large data set and taking the absolute
variance of the physical count to the system count will reveal the size of the company's data integrity
problem with respect to SKU/store inventory positions in the stores.
If the absolute variance is unacceptably large, take full physical counts more frequently. Taking a full
physical count once a year is not enough. If the cost of incremental full physical counts is prohibitive, then
layer in cycle counts. Slicing and dicing of the absolute variance data will reveal where the bulk of the
inventory integrity exists. Employing a pareto (80/20 rule) approach will probably reveal that 80% of the
absolute variance is due to 20% of the stores: or 20% of the SKUs account for 80% of the variance.
Note: calculating the absolute variance between the physical and system counts of inventory in the
distribution centers will also reveal whether or not the DC teams have to contend with a data integrity
problem: yet another cause of data integrity in the store onhand inventories.
STOCKOUTS ARE INVISIBLE…..MOSTLY
Once the store inventory onhand data integrity issue is addressed the next area to tackle is the actual
measure of stockouts in the stores. Mayn retailers archive a weekly snapshop of the inventory levels or
"onhands". This view is available after a company runs its large weekly batch jobs to process sales and all
of the other transactions that update inventory levels for its stock ledger. From the perspective of an analyst
studying and reporting SKU performance over time, a SKU can be out of stock everyday in a given week
except for one day – the day the snapshot is taken – and give the appearance of being in stock that week. In
other words, stockouts occurring in 6 of 7 days of the week are essentially invisible to the company. A much
more complete picture can be provided by developing a stockout measure of the SKU/store computed for each day. Computing daily SKU/store stockouts will involve manipulating an enormous amount of data,
particularly if the SKU/store count is high. There are several viable ways to develop this metric. One
method is outlined below.
Calculating stockouts at the day/SKU/store level:
- Take a weekly snapshot of inventory unit counts for every SKU in every store over some period of time.
- Obtain all transactions that affect inventory levels (e.g., product returns, store receipts, store sales,
exchanges, interstore transfers, etc.) at the day/SKU/store level for the time period to be measured.
- For day 1 take these day/SKU/store transactions occurring on that day (the day after the inventory
snapshot) and increment or decrement the store/SKU inventory levels appropriately (e.g., subtract
sales, add store receipts).
- The ending inventory level for day 1 will be the beginning inventory level for day 2.
- To get the day 2 ending inventory level, take the day 1 ending inventory level and adjust it based on
SKU/store transactions occurring day 2.
- Roll forward the same process to get the ending inventory levels by day for every SKU in each store
for the rest of the days in the week.
- Any day/SKU/store ending inventory level with an inventory level of "0" is a stockout.
Armed with a truer measure of stockouts a company can confidently size their stockout problem by
calculating “lost sales” and then take the next step by calculating lost gross margin and adding incremental
expenses (historically) to fill in stockouts in order to calculate bottom-line impact to the company's income
statement. Knowing the actual bottom-line costs associated with the company's true stockout rate will
inform the senior management as they prioritize initiatives to reduce stockouts against other corporate
initiatives competing for corporate attention and resources.
On a more immediate and tactical level, visibility to daily stockouts at the SKU/store level can enable
pinpoint supply chain actions like increasing store receipt frequency or changing the delivery days of store
receipts by store to better match supply with demand.
Another benefit of developing an accurate stockout measure compiled at the day/SKU/store level is that
the senior management team will likely trust the veracity of this metric given the thought and rigor
employed in developing it. This will be helpful initially in galvanizing the senior management team to
support discrete efforts to reduce stockouts in the stores. After these efforts are launched to reduce store
stockouts, reporting the day/SKU/store stockout measure will reflect exactly where real progress is or isn't
occurring.
A QUERY TO PREVENT FUTURE STOCKOUTS THROUGH ALLOCATION
Every week, several times per week, allocators review the company's sales performance (usually with
system support) and then conduct the process of allocation – invoking the systems commands that release
specific DC held SKU quantities into the DC work flow to be processed and ultimately transported to the
store. Depending on the amount of system support on the front end, this process can be laborious as each
allocator is responsible for allocating a sizeable number of SKUs multiplied by the number of stores in the
fleet.
One way to assist (or follow up) on the allocation team is to develop and run a data query addressing each
and every SKU in every store to determine whether or not further (after the formal allocation process has
been completed) action should be taken to prevent a stockout in the near future. To create this query onewill need to extract current and historical store onhand inventory data and sales history at the
SKU/Store/week level, and DC onhand and intransit data for every SKU from various corporate data bases.
The data will be voluminous. For example, pulling data on 20,000 SKUs for a company with 1000 stores will
create 20 million SKU/store combinations. Too much data to manipulate in a spreadsheet or even in
Microsoft Access as each SKU/store combination will in effect become 20 million rows of data in a very large
data set. My recommendation is to leverage the company IT team to extract and manipulate the data in a
more robust database.
At a high-level the objective is to incorporate all of this data into a program that will first forecast sales for the
next one to three weeks (depending on the store replenishment cycle time) for every SKU/store
combination in the company. The sales forecast should factor in trend and seasonality. If then the
forecasted sales exceed the current on hand inventory (plus in transit inventory) for any SKU/store
combination, the query should then check the distribution center to see if that particular SKU is available in
the DC to allocate back to that particular store. If DC quantities are available, the program should surface
the SKU/store combination, organize it with other SKU/stores that need to be addressed, and send it to the
responsible allocator who can then move the products out of the DC and into the specific stores identified.
In effect, what is created is an exception list of SKU/store combinations requiring inventory sitting in the DC.
Developing the query may seem straightforward for a programmer or database expert with access to all of
the required data. There are, however, some potential pitfalls. At a more detailed level, one must control for
data integrity and the forecasting issues associated with building a demand forecast on top of sparse data.
Factoring seasonality into the SKU/store demand forecasts can also be a tricky maneuver. My
recommendation is to involve somebody with expertise in demand forecasting to avoid these pitfalls.
A QUERY TO PREVENT FUTURE STOCKOUTS BY RE-BUYING
A similar program can be created to identify SKU/store combinations that will stock out over a longer time
frame where no DC inventory exists in the supply chain to fulfill the projected demand. These potential
stockouts are not actionable by the allocation team because the DC itself is out of stock. Only the
merchants can take action on this information and only if they are able to buy more of the same SKU to be
delivered to the DC in short order. This will leave out SKUs with medium to long production lead times. To
the extent a SKU can be ordered and received within two months, this query can be useful to prevent future
stockouts in the stores.
In addition to all of the data elements required for the allocation query (listed above), one will need access to
all of the incoming purchase order data.
The objective is to identify all SKU/store combinations in the company that will run out of stock in the
forecasted time frame (based on the re-buy cycle time) where there are inadequate or no inventory
quantities in the DC and where no purchase orders have been written for merchandise to arrive in the near
term. The forecast will be at the SKU/store level and should factor in trend and seasonality. The forecasted
time period will be based on how quickly the merchants can get the products back into the supply chain
network. SKU/store combinations matching the criteria of the query can be organized into an exception list
and delivered to the merchants every week. The merchants can review the list of SKUs and stores that will
stock out in the near future and then write new purchase orders where it makes sense.
EMPLOY THE STANDARD DEVIATION FORMULA
“The future is here, it's only unevenly distributed”. William Gibson
To the extent the ratios of sales to allocated inventory for every store for any particular SKU are uneven
across the fleet of stores, the allocation was imperfect. An imperfect allocation could result in some stores
stocking out while other stores are overstocked; lost sales in some stores and unnecessary markdowns in
others. It is not an easy task to identify all of the SKUs with poor allocations. One has to sift through every
SKU/store combination and study the demand/supply relationships for each. This could easily result in
analyzing tens of millions of combinations depending on the SKU count and store count of the company.
One approach to handle this is to begin by calculating the supply and demand (to date) ratio for every
SKU/store combination. I recommend using the sell through metric. Simply take the receipt units of a
particular SKU in a particular store and divide it by accumulated sales. For example, if a store received 10
units and sold 4 units to date, the sell through is 40%. In a retail chain with 1000 stores selling that particular
SKU, there would be 1000 distinct sell through data points for that one SKU. The question to answer is
whether that particular SKU's allocation was “good” or “poor”. That would depend on the dispersion of sell
through ratios among the stores. For example, if every store had a sell through of 50%, the allocation looks
good (at that point in time) because the stores are selling out evenly across the fleet. If the sell through
measures are widely dispersed, then the company is probably losing sales or soon will be. Calculating a
sell through for each SKU/store combination and then analyzing the sell through dispersion for each SKU
against that of every other SKU in the company is a herculean task. Fortunately, there is a method to easily
measure the dispersion of sell through data amongst the store fleet for each and every SKU.
Generally, leveraging the standard deviation formula is a good way to measure dispersion in any
population of data points: the more disperse the data, the larger the standard deviation. Taking the
standard deviation of sell through ratios for each SKU will provide a measure to compare and rank the
allocation of each SKU against every other SKU in the population. This can be done by pulling sales and
inventory data for every SKU/store, calculating the sell through, and then applying the standard deviation
methodology to the population of sell through metrics for each SKU. Those SKUs with a higher standard
deviations are the SKUs where the ratio of sales to onhand inventories is more uneven across the stores.In
other words their inventory allocation was suboptimal when compared to the allocations associated with
the bulk of the SKUs in the population.
This visibility into SKUs with widely dispersed outcomes provides an opportunity to rebalance the inventory
in the stores through additional allocations if there is DC inventory available or invoke interstore transfers if
it is cost-effective.
A side benefit of this exercise is that sorting the SKUs by dispersion rates and by allocator responsibility will
reveal over time which allocators are generally better or worse at matching inventory to sales across the
store fleet.
ADDRESSING THE LAST YARD
One insidious type of stockout difficult to measure occurs when the product has travelled the entire length
of the supply chain from manufacturing facility to the store receiving dock and is now physically in the store
but not available on the sales floor. In other words, the product is probably sitting in the store stock room
literally yards away from the sales floor and waiting to be processed. Unfortunately, the company
management information system will reflect that the store is actually in stock although the product is not
available for purchase by the customer. Retailers address this issue differently. Some address it manually
at the store level by slowly improving discipline and execution in the stores organization. Some retailers
engineer the problem away by ridding their stores of backrooms thereby forcing the inventory to the sales
floor. Some retailers take a data-driven approach similar to the one outlined below.
This approach to addressing “the last yard” issue is to create a program to monitor and compare sales
movement at the SKU/store/day level against actual SKU/store/day level inventory receipts to identify
SKU/store combinations where the store received the product and yet no sales occurred for a selected
time period. This may or may not point to poor execution in those stores. There is always the chance that
that particular SKU may be unappealing to the customer. To control for this, the program can have
additional logic requiring the retrieved SKU/store combination to be selling in at least some large
percentage of the stores in the company. Once the list of SKU/store combinations matching the criteria is
pulled, the resulting data needs to be organized by store hierarchy and within each store by merchandise
hierarchy so that the output of the program is an organized list of SKUs for each store general manager to
use to verify that the SKU isn't actually on the sales floor and to locate the product in the back room and
process it to the sales floor.
CONCLUSION
One final point on leveraging existing company data to tackle the store stockout problem. The careful
reader may have noticed that I haven't covered the not insignificant financial investment normally
associated with new information wielding capabilities or improvement to information systems. The reason
for this is that in most cases new hardware and software is unnecessary to develop the programs I've
outlined in this paper. What is necessary is access to existing corporate data, senior management support,
and an analyst/programmer knowledgeable in supply chain and skilled at creating and manipulating large
data sets.
|
