Our inventory system has undergone huge evolution over the years. From a clipboard and pencil in my garage 4 years ago, I've evolved the system to a robust database with normalized tables, automated processes called by cron jobs, and the potential of very accurate data. All of this is fully and seamlessly integrated with the accounting, product and store system. I mention "potentially accurate" because inventory in a database is a very difficult thing to sync with the stuff that sits on shelves. Still working on a bar-code system which will hopefully reduce inventory/shipping errors.
The latest step in the evolution of my system involved a total re-write of the re-ordering system to greatly reduce the time involved in re-ordering. Some phases of re-ordering will become fully automated.
I hope this information is useful to someone. And I'm curious how others are handling the supply side. I'm always looking for better ways to do it.
Here's what I've done in my latest and biggest revision:
THE BASIC COMPONENTS
1) An algorithm that measures past sales and current in-stock amounts, then predicts how much quantity to re-order. (not nearly as simple as it sounds)
2) A purchase order system that handles all phases and exceptions of placing a new order with a supplier, including a web page for the supplier to view, edit, negotiate, and record notes. No more clumsy emails.
3) A screen for the warehouse guys to view incoming purchase orders, and mark some quantity as fully received, partially received, etc.
4) A screen to write-off products that we ordered, but are never coming in.
============
1) RE-ORDER QTY ALGORITHM
We don't want to have items out of stock, but we also want a quick turnover. We want to optimize the use of capital and warehouse space. Sales numbers are always changing, but we do the best we can with historic sales data to estimate the number of days left for a product before we need to re-order. And of course calculate how much to re-order.
We can fine-tune different parameters at the suppler level. For example, min_days, max_days, leadtime, margin_threshold, order_max, order_min, pct_limit, sum_limit, count_limit, qty_limit, avg_ratio_limit, order_alert_threshold, dom_factor, rounding_one, rounding_two, min_instock_pct, sales_string_months, repo_additional_days, low_order_count, outage_loss_factor (all explained below).
And we can manage whether to allow automatic creation of new purchase orders. For example, with some suppliers we just want the system to automatically figure out what to order, then automatically place the order. And if they approve it, send the wire transfer. (all while we sleep or sip tropical beverages on a beach!)
For suppliers where we prefer to re-order manually, there's a report that shows the estimated dollar amount to re-order for each supplier. Click a supplier name, and there's ample data/statistics to assist in verifying suggested re-order quantities and prices. Once satisfied with the new order, click another button and the supplier is emailed a secure link to view the proposed order, then either accept it or negotiate on price, qty or some other aspect of the order. The negotiation page even includes any product diagrams or photos I may have included to describe production details.
We use loads of data. Here are the definitions of the parameters used in our re-order algorithm:
Average Per Month = Average monthly sales qty per product. We give more weight to sales from more recent periods.
Average Ratio Limit = One of five factors used in finding and excluding outliers from the repo (re-purchase order) calculations. This is the largest order qty for a product during our 52 week period, divided by the average order qty for that product. If the average ratio for a product does not exceed the average_ratio_limi, then that product is not eligible for further outlier testing. The concept here is that any outlier should exceed the average order size by a significant percentage. We usually set this limit to 5, which means any outlier must be more than 5 times larger than the average order size.
Calc Qty = This is the inventory value used in the repo calculation. The formula for calc_qty is: full_qty + potential_inv (from kits/components) - unsent_inv (from backorders and unshipped orders). In turn, full_qty = on_shelf_inv + pending_inv.
Count Limit = One of five factors used in finding and excluding outliers from the repo calculations. We count the number of orders for each product during the 52 week (or maximum) period. If that number is below our count_limit, then this product is excluded from further consideration of outliers. Usually set to 20. The concept of this factor is that a very low qty product is not statistically significant for consideration of outliers. For example, a product might have total sales of 20, consisting of one order of 15 and 5 orders of 1. The large order is clearly an outlier, yet the total quantity is so low that it should not be excluded.
Days On Market = The number of days since first bringing the product to market. Doesn't account for any out-of-stock days, nor periods where it may have been temporarily discontinued. We have products that have been on-market for over 1000 days.
Days Left = This value represents the estimated days remaining before current inventory is depleted. The formula is: days_left = calc_qty / avg_per_day
DOM Factor = The days_on_market factor serves to prevent repo calculations when a product has been on the market for only a very short time. It works by adding the DOM factor to the actual number of days the product has been on the market. This value is called dom_value. If the number of days in the sales period is more than the dom_value, the the repo is not run for that period. An example: blue widgets first appeared in the store 2 weeks ago, so they've been on the market for 14 days. The supplier of blue widgets has a DOM factor of 20. That gives blue widgets a dom_value of 34 (14 + 20). When we run the report, the 4 week period, which is 28 days, is less than our dom_value of 34, so the function gives us a repo value for the 4-week period. For the 8-week period, which is 56 days, the dom_value is less than the period length, so no repo is calculated. The same rule will prevent repo calculation for the longer periods as well. Note that the DOM calculation is different from out-of-stock calculations. It is really just to cover new products and make sure there is enough data in the period before making a calculation.
First Exclusion Flag, Second Exclusion Flag, etc. = This is the qty of the largest (and second largest) order for that product for the 52 week (or max) sales period. These orders are usually excluded from repo calculations.
Lead Time = The number of days that we expect to occur between placing a purchase order, and receiving the first shipment. This is one of four elements that has an important effect on repo values. Over-estimating lead time will result in purchase orders being recommended sooner - thus larger purchase amounts at any given time.
Margin Threshold = The algorithm calculates the average profit margin for each product during the given sales period. The margin threshold allows us to screen out products with very low margins, or at least display them in a useful way.
Max Days = The target number of days that we want each purchase of this product to last. For example, if the system assumes we sell one unit per day, and we set max days to 200, then it will recommend a repo qty of 200, then adjust for current inventory and lead time.
Min In-Stock Percent = The more a product is out of stock, the less reliable the sales data. On the other hand, if a product is out of stock for a day or two, the effect is probably negligible. To account for the out-of-stock effect, we first count the number of days for each sales period that a product was not on the shelves. We then multiply that number of days by an outage loss factor. This factor, initially set to 20%, is designed to recognize the fact that out-of-stock items still sell, thought typically at a lower rate. We then divided adjusted out-of-stock days by the total number of days in the period, to get the percentage of days that the item was out of stock for this period. If that percentage is more than our min_instock_pct, then we don't run calculations for this product for this period.
Order Alert = The dollar value parameter stored for each supplier, that tells the system the minimum dollar value of a suggested order for which we should be alerted. For example if the order_alert is set to 50 and the algorithm says the total re-order value for a supplier is $49, it will not alert us. At $50 or above it would.
Order Max = The upper limit in dollars that we allow the system to generate an automated order.
Order Min = Similar to order max. The lower limit in dollars that we allow the system to generate an automated order.
Percent Limit = This is one of five factors used in finding and excluding outliers from the repo calculations. An outlier is any order that is significantly out of "normal" range. The pct limit is simply the minimum percentage of total sales that any single order must exceed to be eligible for further outlier testing. The full sales period (typically 52 weeks) is always used. For example, if sales volume for a product for a year is 500 units and the pct_limit is set to 20, then only orders of 101 units or more would pass this outlier test. An order of 100 units, regardless of which other outlier tests it passed, would not qualify as an outlier.
Pending Qty = The quantity of a product that has been ordered from a supplier, but not yet received. It does NOT include orders under negotiation.
Period String = The algorithm estimates future sales by looking at past sales. In doing so, it views up to 7 overlapping periods set by us. Measured in weeks, the initial sales periods are set to 4, 8, 18, 30, & 52 weeks. The system calculates sales for the past 4 weeks, then the past 8 weeks, then 18, and so on. So each calculation will contain results from the past 4 weeks, thereby naturally biasing the calculations to more recent periods. The final recommended repo qty will be the average of the results from each of the sales string periods. We use weeks instead of months because we find that weeks are a more natural shopping cycle. Months vary in length and number of weekends and therefore have more variation than weekly measurements.
Potential Inventory = The inventory calculations include the potential inventory from kits and components. This can lead to inevitable double-counting of kit-related products. For example, a kit = iMac. A component = keyboard, mouse, computer. Sometimes, we need to break open kits to sell a component if we don't have the component in stock. Other times, we merge components together to create a kit. We can apply exclusion or inclusion exceptions to any product to avoid cases that would tend to yield unreliable purchase suggestions.
Qty Limit = One of five factors used in finding and excluding outliers from repo calculations. This value sets the order qty that any order must exceed to be considered for further outlier testing.
Rounding Factors = There are two rounding factors, called rounding_one and rounding_two. These determine the level at which the algorithm will round repo values to more acceptable numbers. Rounding_one rounds set the level at which repo quantities above rounding_one will be rounded to the nearest 10. If the value is above rounding_two, then it will be rounded to the nearest 100. It cleans up the qty to reasonable amounts for the suppliers. For example, it'd be strange to order 89 widgets. We'd just order 100.
Unsent Qty = This inventory value is the total qty from received customer orders, whether on backorder or not, that have not been shipped. Using this number as part of calc_qty for the repo calculation ensures that the repo calculation is as up-to-the-minute as possible.
*Screen shot! [
i44.tinypic.com...] (forgive the messy design, and fake names of course)
2) PURCHASE ORDER SYSTEM
Ajax-enabled, for an easy to use screen. Change a price/qty, and the totals instantly update. Prices can be entered with up to 4 decimals. As soon as a new purchase order is created (either automatically, or manually), the supplier gets an email from the system. Its got a link asking them to view the order, and either approve it as-is, or enter a revision/counter offer.
They enter a counter offer (new pricing, change in qty, or notes). We get alerted, and then either approve it, or enter our own counter offer. We can go back and forth an unlimited number of times until finally one party approves the order.
This is done all online, with our fully integrated system (PHP, MYSQL). No messy emails back and forth with pro-forma invoices in xls or pdf format. No re-typing of data. We get the supplier to put the data directly into our system.
When the order is approved, a cURL process automatically logs into our bank account and sends a wire transfer. The wire transfer screenshot is automatically added to the purchase order page, and the supplier is alerted.
There's a payment tracking system that is tied in with the accounting system. So we know which orders each payment is for. And we know how much we owe suppliers (in the case of sending 50% up front, and 50% upon shipping - usually for larger orders).
The suppliers manage their bank transfer details. Sometimes their swift/account numbers change from time to time. They can update it directly in our system. So the new info gets fed into our automatic wire system.
The suppliers must enter an expected ship date. And we assign a number of transit days based on the ship method. If ocean, usually 35 days. If air, usually 7 days. We use the combination of these dates to display expected dates on the store for out of stock products. For example, "ships within 2 weeks".
The supplier is automatically emailed every week and asked to click a link, and update the expected ship date. We keep this information as accurate as possible.
When expected dates change, customers get a notification email if they have an order with those items on backorder. For example, "Last week we said it would be 2 more weeks, but now it will be another 2 weeks. We currently expect to ship on April 3. But of course this is still an estimate, etc, etc." This proactive alert system prevents a bunch of extra calls and emails.
Suppliers can manage their part numbers as they relate to our products. Suppliers can assign their own internal reference number to relate to our PO number.
We've got a nice ajax-enabled note/comment system, so everyone involved can communicate about the purchase order. We can add notes at the product level, and at the general purchase order level. We can also upload files, and tie them to a certain product. For example, if we have a diagram explaining changes to the green widget, we just upload the file. And there's a facebook-style notification system, to notify people when a comment/update has been made.
3) WAREHOUSE RECEIVING SCREEN
Pretty simple. When a new order arrives at the warehouse, we pull up the screen and find the order. We count everything to make sure its all there. Options for each product line item: fully received, partially received (enter qty received), or none. It of course updates the inventory records. It also makes an entry in the accounting system, journaling the dollar value of the received amount from "inventory receivable" to "in-stock inventory". If the warehouse guy makes a mistake, he can undo the entire process by clicking the "un-receive button".
4) WRITE OFF SCREEN
Pretty rare, but important nonetheless. For example, if we order 500 widgets, but the warehouse guys only count 490 in the shipment, we may either go back and ask the supplier what happened, or just write the items off. A lot of things could have happened. The 10 could have fallen out of the box in transit. The warehouse guys could have counted wrong. Or it may not be worth dealing with, so we just write it off. This process modifies the inventory records for these 10 missing items and makes the appropriate accounting transaction.
WHATS NEXT?
Next I'm going to build a price history system. Not just for my store pricing, and not just for my supplier price history, but for loads of prices that will be useful for us in negotiations.
We will gather monthly data points on a handful of items:
- Competitor pricing
- Other suppliers pricing (ones we deal with, AND ones we don't deal with)
- Raw component and commodity pricing (copper, steel, oil, silicone, plastic, flexible circuit boards, RMB/USD exchange rates, CPI)
- Service pricing (average cost of factory worker in China, average cost of 1CBM via air, 1CBM via ocean)
We will use this data to help us AUTOMATE the negotiations of pricing. We will automatically propose new pricing when placing a PO. And we will have lots of data to back it up. Later on, we may use the competitor pricing data to help us automatically react to market price changes.
I've noticed some suppliers take advantage of us on pricing. I may have a gut feeling that the price is too high, but don't have the time to research and negotiate, so I let it slide. This will help us automatically negotiate the pricing. Should be powerful data if we can do it right.
