inventory tracking

Inventory Tracking – Problems and System

How is inventory tracked

Inventory is typically tracked at an aggregated level and "partial counts" are done at different times and locations. Most of the time

  1. Pallets (boxes or any large enough container) are counted at the departure and arrival of trucks when they are moved between locations
  2. (partial) Inventory counts are done on a regular basis on the different locations

Keep in mind a consumer good company can easily have 50 000 SKUs (Stock Keeping Unit) due to the combination of products (Coca-Cola, Fanta, etc), volumes, presentations (glass, PET, BIB, Keg, …), promotional events (world cup bottles, etc) and local particularities (some plants in Mexico use sugar cane instead of HFCS)

A typical supply chain looks like this

In theory products should flow from left to right but in practice there are many mistakes done between the plant and the warehouses.

The tracking information we have are partial sums at a given time

  1. On day D0 a truck moved 30 pallets of SKU 1729 from plant P1 to warehouse W1
  2. On day D1 warehouse W1 had 314 pallets of SKU 1592
  3. On month M1 the supplier S1 was paid for 1234 kg of raw material R1
  4. On month M2 point of sales P1 sold 27182 bottles of SKU 12321

Inventory counts are typically

  1. Partial : you don’t “stop the world” to count inventory
  2. Non homogeneous : suppliers count in kg, plants in boxes, trucks in pallets, warehouse in both pallets and boxes, points of sales in bottles
  3. Wrong : many mistakes are done in the counting process

Example of supply chain with plants in blue, warehouses in red and points of sales in green

Routes for a subset of the SKUs. The size of the point of sales shows the volume they “carry”

Example of supply chain with the plants in green, the warehouses in red, the point of sales are not taken into account because too many (this is called primary distribution). The routes shown are for only one SKU family which is why it looks so “clean”.

When all the routes are displayed, it looks more like this

Typical mistakes in inventory tracking

Inventory information is usually inconsistent because the values reported during the inventory counts are not accurate

  1. Truck companies are usually external companies and they prefer not reporting that pallets were damaged during transportation as that could result in a penalty
  2. Employees prefer avoiding complex paperwork for what they see as small issues (small amount of products damaged, missing or processes that don’t have any simple way of being logged)

Examples of errors we have seen are

  1. Mistake in truck paperwork (origin and destination exchanged by mistake)
  2. Manager from the plant goes to a warehouse and takes with him some products
  3. Pallets damaged in transportation not reported, at the warehouse they are “consolidated” into a smaller number of correct pallets and the difference is never logged
  4. Product transmutation : Coca-cola 12 pack opened and rewrapped as Coca-Cola 6 pack
  5. Managers taking some product in the inventory (allowed) and not logging it precisely because inventory is in pallets and they just need 20 cans for their office. So they guesstimate that overall they have taken half of a pallet out during the last 3 months.
  6. Same sloppiness with products for employees (cafeteria, etc)
  7. Data plain wrong (some trucks carry 50 pallets instead of the standard 48), the software doesn’t allow typing more pallets than the official capacity and to change the software the vendor demands 1M USD. With a magical manual compensation in the warehouse side.
  8. Products misplaced due to warehouse capacity limits (there was no more room in the area for Coca-cola so I put it in the area for Whiskey) or miscommunication
  9. Brutal IDs remapping (Coca-Cola world cup bottles were remapped into normal ones after the event) – most of the time because software is paid by number of active SKUs and because many software / processes don’t handle that case properly.
  10. People robbing (truck drivers, warehouse employees)
  11. People make mistakes counting when they count the inventory
  12. There was a mistake in the delivery and some of the products were sent back to the plant. And given that there is a truck going back, let’s send back some excess inventory we have before it expires. And by the way, isn’t warehouse XXX on your way ? Do you mind dropping a couple of pallets there as well?

Identifying issues with (simple) outlier methods

Traditionally inventory issues are identified by some kind of “outlier method” – usually a combination of simple statistics (regression, deviation) and visualization.

For instance warehouse input / output comparison

  1. Compute the difference between sum of products sent to the warehouse over a period of time and the products exiting the warehouse over the same period of time
  2. Rank by difference %
  3. Discuss with the supply chain manager to see if the values look odd and there is an explanation for them. The manager will often double check with the local responsible (warehouse manager, etc)
  4. Refine based on the feedback for instance the managers could say the difference is due to seasonality (during low demand months the input is larger than the output to accumulate inventory for the high season demand). Then you need to figure out how to factor in the existing inventory information you have to remove the “accumulation” element. And try again.

Example with damaged products in trucks comparison

  1. Compute the average damage per truck line per product per truck
  2. Rank based on value
  3. Discuss with the supply chain manager to see if the values look odd and there is an explanation for them. The manager will often double check with the local responsible (transportation manager, etc)
  4. Refine based on the feedback for instance the manager could say the difference is due to road conditions. Then you have to factor in the road condition by some type of classification (bad | average | good) and redo the analysis inside of each class.

Problem with (simple) outlier methods

The problem is the accumulative nature of inventory and the way inventory counts are done can make the errors appear in a different place and time than the place and time they occurred. This makes outlier detection very difficult.

Let’s consider the following scenario

Because the road between A and B is full of holes, when pallets are transported in the oldest truck in the fleet, they break more often. The truck driver is afraid that if this gets known to his management they are going to dump the truck and fire him. He befriended the pallets receivers in the warehouse and in particular there is one that doesn’t like to fill the paperwork for the broken product. The pallet receiver thinks is too much work and the problem will get “corrected” anyway when the inventory is counted once a week. Because the receivers alternate, broken pallets are sometimes properly logged, other times they aren’t. There is no trace in the paperwork of who was the receiver of the pallets, nor the driver of the truck.

In this scenario, the mistake was very localized and could have been easily spotted if the inventory count during transportation was accurate. Instead, it’s now mixed with

  1. Other deliveries to the warehouse for the same products (other trucks)
  2. Product movement from the warehouse to the point of sales or back to the plant
  3. Multiple other errors

The only thing that is visible in the numbers is an apparent inconsistency between the aggregated products reported by the plant, the warehouse and the point of sales over a 3 month duration.

What we want

We want a tool that allows identifying the most likely cause(s) of an inventory inconsistency and that supports an iterative refinement process

  1. After discussion with the plant manager, he started double-checking the inventory for that SKU himself (that should increase the trust we have in the inventory counts and should reduce the probability of miscount)
  2. Then the manager found an excuse to move all people packing the product from the warehouse to the point of sales to another position (if there is no change, that should reduce the probability that one of them is stealing during the Warehouse -> PoS process)
  3. Then the manager gives us the information of the pallet receivers planned schedules for us to cross it with the existing inventory information (planned can be different from actual)
  4. Then manager talks with the plant and ask that all shipments to the warehouse be properly recorded because he knows that sometimes there are unlogged product returns to the plant.
  5. Etc.

Also, we need a tool that supports a variety of supply chain configurations

  1. Some transportation means take more than a day and in that sense can “hold inventory” for more than one inventory counting cycle on the origin or destination
  2. Some companies have hubs where trucks start, deliver multiple points and return all the products they have left (including the ones sent back) in the hub
  3. Some companies have routinely “transversal” product movements (between plants, between warehouses, between points of sales)
  4. Some companies have processes to send back inventory to the warehouse
  5. Some companies do “stop the world“ inventory counts once a month but no partial inventory count
  6. Some companies do lots of local inventory counts (every time you take a product out, you need to count the remaining)
  7. Some companies are very sloppy with their recipes (1 bottle of coca-cola ID 1729 requires x amount of water, y amount of HFCS, etc)
  8. Some companies track things at very different time horizons, like raw materials consumption is tracked at an annual level
  9. In some companies the picking of products (warehouse -> PoS) is mechanized

What is our role in all that ?

Basically we compute what should happen in an ideal world

  1. Raw material orders to suppliers (time and size)
  2. Plant production per day, per machine
  3. Amount of products that should be transported
  4. Amount of trucks needed, their routes and schedules
  5. Ideal inventory levels per product, per location, per time
  6. Employee count and schedules for all the operation

In order to do a computation of ideal operations, we typically

  1. Model completely the supply chain
    1. Products and recipes
    2. Plants (machines, production capacities, constraints)
    3. Warehouses (capacity, reception periodicity)
    4. Point of Sales (forecast of products sold)
    5. Transportation
  2. Build a baseline : fix some parts of the supply chain using historical values
    1. Fix production and sales, compute ideal transportation
    2. Fix inventories at all points and see what happens
  3. Compute the ideal situation
    1. leave only the forecast of sales and compute everything else
  4. Compare ideal and existing
    1. Identify mistakes and their (financial) impact
    2. Recommend progressive changes to get closer to the ideal situation

A problem we face all the time is historical data is inconsistent. That’s a serious problem when you want to compare ideal and historical results, or even fix part of the data and compute the other part.

When you complain to the customer, you discover they are aware of the inconsistencies but don’t know how to extract from it information that allows them to act (launch an audit, review processes, fire people, etc).

As a result we ended doing a lot of (simple) outlier analysis to help fix the data until it was usable by our optimisation algorithms.

Real examples: Inventory counting system

The purpose of this project is to do an inventory counting system for a small company that manages snacks and drink machines.

The company has

  1. 30 machines
  2. 3 mini vans, 3 drivers
  3. A 30m2 warehouse
  4. An accountant (in the same room as the warehouse)

Their problems are

  1. All the counting process is done manually
  2. They are losing money probably because of robbery (either employees or customers)

Current inventory process

Today when drivers arrive at a machine they

  1. Count the remaining inventory of products and write it down in a paper
  2. Read the money amount reported by the machine
  3. Put the coins from the machine in a bag (machines always keep a minimum amount, only the extra money can be collected)
  4. Add products to the machine and write it down in a paper


The global numbers are manually written into a spreadsheet (Mac Numbers) and added.

Current issues

The company is losing money (paying more in expenses than they get back in sales).

There are many things that could be happening

  1. Deliveries are inaccurate (external employee)
  2. Inventory count in the machines is inaccurate (driver)
  3. Excess inventory is not returned to the warehouse properly (driver)
  4. Inventory disappears from the warehouse at random moments (any employee)
  5. Money is taken out of the money bags (any employee)
  6. Customers are robbing the machines (found a to trick the machine)

The spreadsheets done by the manager look like this

Current SKU table

The inventory is counted in dollars instead of products. This introduces uncertainties, hides promotions and limits the possibility to double check the quantities.

Current inventory tables, first column is the target inventory

Current sales tables

Inventory computation

Table of money movements

This table shows all the financial balance

  1. Date
  2. Cash in safe
  3. Expenses
  4. Purchases
  5. Deposit (?)
  6. Comments
  7. Cash collected driver 1
  8. Cash collected diver 2
  9. Expenses driver 1
  10. Expenses driver 2

The comments column says

  1. Diesel 10 passes 26.65 pieces 30 loan coffee
  2. Payment fortnight
  3. 30 diesel, 35 repairs, 13.50 socket block
  4. Pascual biscuits
  5. 15 passes 20 diesel, payment debts november
  6. Ivan deposit
  7. 10 diesel 0.85 complaint 10.50 med vir, 10 passes
  8. 62.60 ink, 4 copies 59.90, commission EMI

Inventory tool app

The purpose of this project is to create a (simple) tool app that helps them manage their inventory.

Owner

The owner must be able to

  1. Manage SKUs
    1. about 50
    2. Ability to add and discontinue products
    3. Products have different presentations (you buy cookies per box containing 100 individual packs and sell them in the machines by individual packs)
    4. Product have a bulk price and a resales price (in different units)
    5. Bulk price may vary in time (inflation, price adjustments, promotions)
    6. Sales price may vary in time and space (different prices at different machines)
    7. The sales may even vary in presentation (ex. Promo giving 3 cookies for the price of two)
  2. Manage expenses
    1. Input money in / out
    2. Link it to inventory in (bought product)
  3. See reports
    1. Details to be defined with the owner

Driver

The driver must be able to

  1. Input inventory present in a machine
  2. Input money reported by the machine
  3. Input money counted if any
  4. Input inventory in the machine after refill

Concepts

We are going to borrow some concepts from supply chain management tools

  1. Raw materials We are going to represent products bought in bulk as raw materials (separate IDs)
  2. Recipes We are going to represent the transformation from bulk into individual packs as a recipe. There are going to be as many recipes as presentations of bulk products
    1. Bulk 100 pack @ 95 => 100 individual packs (recipe 1)
    2. Bulk 1000 pack @ 921 => 1000 individual packs (recipe 2)
    3. …
  3. Prices We are going to allow prices to change over time in a price table [{id, date, price}, {id, date, price}] …
  4. Inventory locations Trucks are going to be represented as inventory locations and all movements are going to be represented as a transportation arcs. In other words when the truck leaves we are going to move inventory from the warehouse to the truck and for each stop, move inventory between the truck and the machine, at the end of the day, the truck inventory is moved back to the warehouse.
  5. Transportation tracking All movement between inventory locations is a transportation arc. We should any time be able to add a “transportation count” that says that at time T person X counted at the origin and destination and found that Y items were moved
  6. Inventory tracking We should any time be able to add an “inventory count” that says that at time T person X counted the inventory in location Y and found Z items

Work to be done [to be improved]

We provide a tool basis that includes

  1. Conventions to generate tables
  2. Utility tables and tools
  3. A simple GUI frame

To be done

  1. Tables for SKUs, prices, locations, transportation arcs
  2. Screens to capture the data (skus, prices, routes, machines, etc)
  3. Screen to capture inventory counts in desktop
  4. Screen to capture inventory counts in mobile

The mobile part should be a separate application using a shared MongoDB or not. The drivers will use their personal smartphone to access the tool. Probably a cheap Android phone.