Distribution Inventory Management

So you have a business that buys and sells products, and you want to keep track of that process and keep track of your inventory. Simple, right? Well… maybe.

Fact is, there’s a lot that goes into that process. For the person actually performing the work, it’s reasonable to have various “rules of thumb” about what “usually” happens (see “The Bizarro World of the Developer”) and then just do a quick workaround when things are different.

Don’t worry, we’re bean counters too!

Unless you’re a serious bean counter (as we are), there are probably a number of scenarios and exceptions that you aren’t yet thinking about. (More accurately, you think about them so often that you don’t realize that you are, and that your conclusions are not obvious to everyone else and to your new software.)

It may well turn out that your process is “simple”, but it’s still important to consider all of these factors to be sure your software solution is going to be able to track your processes and inventory well.

Over the years, I’ve personally designed several software solutions that track buying, selling, internal processing and inventory. Each solution had its own quirks and subtleties, as dictated by the client’s business. However, one of the ways that Extensitech is able to effectively create and deploy solutions for so many diverse clients in so many different industries is by recognizing patterns. Due to our experience creating software solutions, some experience of mine in “past lives” with EDI, and also some research, we’ve noticed a number of patterns to watch for, which make architecting such solutions a lot easier. We’re able to ask the right questions, uncover more opportunities, and avoid common pitfalls.

(Yeah, I read about this stuff for fun. I’m a geek. For a good general primer on inventory management, check out Gordon Graham’s Distribution Inventory Management. The technology he talks about is seriously dated, but the principals are super-solid.)

In this post, I won’t be writing much, if anything, about the specifics of developing solutions in FileMaker, our platform of choice. This is more about underlying theory.

Also, keep in mind that while this pattern applies most thoroughly to distributors, some or all of the principals are applicable in other businesses, as well. Our clients include a meat distributor and a steel distributor, among others, for whom these underlying principals were instrumental to how we designed and built their solutions. For other clients, though, who sell services or manufacture the products they sell, many of these principals still come into play.

Here are the basic elements (tables) in an inventory management system:

Items

The most obvious element is, of course, Items. Items are a list of things that you sell. The list offers, first of all, a “menu” of items, which is used when buying or selling as a list from which to select. Secondly, it gives us a spot to calculate and show current inventory values, as well as a history of purchases and sales. And of course, this is also where we can track attributes of the items, such as size, shape, weight, color, categories, etc.

• Available plus on order

For inventory purposes, we’ll want all (or at least, part) of the following breakdown:

• On Hand: How much is actually on the shelves
• Allocated: How much of that is reserved, or promised to someone
• Available: On hand minus Allocated
• On Order:  Promised to us by someone else
• Projected: Minimum Quantity: How much should we have on hand, to meet current and projected orders?
• Maximum Quantity: What’s the most we should keep on hand?
• Recommended Purchase: If Projected is less than Minimum, then Maximum minus Projected

Note that in an (over)simplified world, we could update each of these values manually, but that’s not our goal. All of these figures can and should be derived by looking at our Documents (described later). This includes Minimum and Maximum, which ideally will take into account historical sales, lead times from primary vendors, and cyclical sales patterns.

Customers and Vendors

Customers and Vendors are the “parties” with whom you’ll be exchanging goods and services. They have many similarities.

It’s important to remember that the same company (or person) could be both a customer and a vendor. We link each of these “parties” to a Contact record (a Contact being either a person or a company) so that those common attributes, things like addresses, phone numbers, company name, etc., can be stored with our record of them as a “contact”. This is how we recognize that although we may deal with ABC Company today as a customer and tomorrow as a vendor, they’re still the same company.

The Customers and Vendors tables are also where we store preferences, such as preferred billing/remittance addresses, preferred shipping carriers, payment terms, and other requirements.

Prices

A Price record is a record of how much money will be exchanged with a particular Party for a particular Item. For instance, how much will Vendor A charge us for Product X? How much will we charge Customer B for Product Y? It’s tempting (and common) to see prices as fields in the Items table. However, by keeping Prices in a separate table, we can recognize that we may have a special price for a given customer, or that we may order the same Item from more than one Vendor, and that they may have different costs.

(Short side note: For this blog, I refer to prices as “prices” whether they are the Vendors’ prices or the Customers’ prices. When discussing things in more detail, it’s useful to call vendors’ prices “costs” just to avoid driving oneself crazy. For instance, when determining profit, comparing “price to cost” is much easier to talk about than comparing “price to price”.)

The Prices table is also a good place to store external item numbers. Your vendors and customers probably have item numbers of their own. As you enter, for instance, how much Vendor A will charge you for your Item “1234”, you can also indicate that they call it “ABCD”.

Documents

Now that we know what our goods or services are, and with whom we’re exchanging them, we can get down to actual documents. In its broadest terms, a Document is a record of sending or receiving goods or services, to or from a “Party” (a Customer or Vendor).

Parties and Contacts

A Document’s line items detail what actual goods or services are being exchanged, but even before we add any line items, a Document carries a wealth of important data:

• Party: what Customer or Vendor are we working with? This can pre-set a number of values, such as payment terms, ship-to, bill-to, carrier, etc.
• Bill-To: what Contact will be paying for the goods? For purchasing, this will be us, whereas for selling it will be someone else. Note that the bill-to company is usually the Customer or us, but it could be a third party, such as the Customer’s parent company, or our own.
• Remit-To: What Contact will be paid? Again, this is usually either the Vendor or us, but could be a third party, such as a factoring company.
• Ship-To: Where are the goods to arrive?
• Ship-From: Where are the goods coming from?
• Carrier: What company will be transporting the goods?
• Shipping Payer: Who will pay for the shipping?

For each of the entities above, depending on your business and also on the particular type of document, you may need additional information, such as an address, phone number, fax number, contact person, etc. All of these should have defaults (values that fill in just based on the Party), but ideally you’d be able to alter any of these just for one document, as needed. For example, my Customer might want me to use a different contact person, or ship to a different address, just for this one Sales Order, but not change their preferences for other future documents.

Directions and Stages

A document can be inbound or outbound (other options are available, such as internal, but we’ll keep it simple at least for now). Whether the document is inbound or outbound tells us how the line items should adjust the inventory. It also tells us more about the default values for all those Contacts above. For example, if a document is outbound, such as a Shipment, the ship-to and bill-to are based on the Customer and the ship-from and remit-to are ours. However, if the customer is returning goods to us (“inbound”) then those parties should be reversed.

Next, a document should determine what “stage” of the buying or selling process we’re in. Both processes (as well as internal processing of goods) go through some distinct and fairly standard stages:

It’s important to note at this point that while all of these stages take place in buying and selling, not every solution requires all of these documents. Sometimes, for instance, prices are established at the outset of the relationship, so stage 1 is assumed done and we have prices on file for our customers and/or vendors. Sometimes a stage takes place outside the system, such as stage 4. Other times, two or more stages are combined: it may be practical, for instance, to skip straight to the invoice, and treat the invoice as stages 2 through 4 occurring all at once.

Often, though, what should be included and/or what should be combined is not abundantly obvious. For example, we’ve encountered clients who start out thinking that all they need on the sales side is an invoice (for stages 1-4), but then start talking about backordering, or creating “dummy” invoices to act as quotations. By laying out the various stages, we can determine whether some of these other documents make more sense for them.

It’s important to consider the relationships between these documents. For some businesses, one Quote becomes one Sales Order which becomes one Shipment which becomes one Invoice. If that’s true, combining these stages into fewer documents is a viable option. For other businesses, one Quote becomes one or more Sales Orders, which become one or more Shipments which become one or more Invoices. And for others, some or all of the relationships may be many-to-many. For example, one Shipment could include some, but not all, items from multiple Sales Orders. Having documents for each stage becomes more valuable for businesses where the document flow is less linear. In theory, all of these documents could be many-to-many as they go from stage to stage. Often, though, there are real-world policies or other rules that should prevent combination. The multiple document approach works for all those cases.

Line Items

Each Document will have one or more line items. Each line item is linked to the Document and also to an Item. The description and so forth can look up from the Items table, and the prices can look up from the Prices table. Some or all of these item characteristics (description, color, price) may be editable for this specific instance. How the line item affects inventory is based on the direction and stage of the document.

Aside from that, a line item indicates how many (or how much) of the Item is involved, and also needs to include how many are still not processed further. For example, a Sales Order may call for 100 widgets. If 75 have been shipped already, then 25 remain Allocated, and still need to be shipped. This second quantity allows us to accurately get the totals we need on the Item. It also eliminates the need for additional documents in cases of backorder. No new document needs to be created; this same Sales Order remains open until all 100 are shipped, or the decision is made not to ship the rest.

Other Considerations

The outline above should give you a basic framework for reviewing your business process, or developing a solution that tracks the buying and selling goods or services. There are many other considerations, a few of which I’ll list here, but a full exploration of everything would likely be too long for a reasonable blog post, and also starts to get into areas where variations are more plentiful. Below are very brief descriptions of other factors that commonly come into play when designing a solution for managing these processes.

Units of Measure

It’s likely that some or all of your Items can be bought and/or sold in various units of measure. Paper cups, for instance, can be in Cases, sleeves and perhaps even individual cups. Unit of Measure is a factor on each of the Line Items and probably on each of the Prices. Beware the impulse to set up separate Items for each Unit of Measure. Create a table of UOM that indicates that in a Case, there are 4 sleeves and a total of 200 cups, so that when you price, buy or sell the item you can “translate” the amount into a common unit of measure for inventory purposes.

Transferring from Document to Document

Assuming you’ll end up with multiple documents in each process, you’ll want a common routine for “moving” line items from one document to the next smoothly. For example, a routine to ship items on a Sales Order should allow the user to seamlessly “move” all line items on the Sales Order that have an open quantity to a new Shipments document, and have the line items on the new Shipment linked to their source line items on the Sales Order.

We find that that routine is much easier to build and maintain if we use common tables, as described next.

Common Tables

We have found it useful to create a common table for Documents (“TXH__Transaction_Headers”), such that each Document record has a corresponding record in TXH. This allows us to put common attributes (bill-to, ship-to, terms, direction, stage, etc.) in one table, which eliminates a lot of field proliferation. This also allows us to view a list of history for, say, a particular Customer, all in one list from the same table.

We’ve also found it useful to create a common table for Line Items (“TXL__Transaction_Line_Items”), and have all the Line Items in the system reside in one table. Again, this allows us to consolidate the common fields (there are more common fields than unique ones usually) and allows us to consolidate history in one table. For example, by doing this you’ll be able to see the entire history of an Item, where it has appeared on any Document.

Internal Processing

The outline above focuses on the purchasing and selling process. A third process is common, as well, which is when you internally manipulate your inventory. For example, our client who is a meat distributor has an on-staff butcher. Another who distributes steel also cuts metal to order. Potentially, this process can go through the same stages: Stage 1 (proposal) is a Requisition, where someone indicates that they need some Item B made. Stage 2 (order) is when a Cut Schedule (or other name) is created, where we’re planning to cut some Item A to make Item B. Stage 3 (fulfillment) is when some of Item A is actually removed from inventory, transformed, and put back into inventory as Item B. Stage 4 (request for payment) is unlikely in this process.

In addition to planned changes to inventory, there are inevitably unplanned changes. Initial inventory needs to be added. Items are lost or found due to waste, shrinkage, or giving the warehouse a good cleaning. There needs to be an Inventory Adjustment Document where a user (probably an administrator or warehouse manager) can add or remove inventory without resorting to dummy documents.

Returns

In both the Sales and Purchasing processes, there are potentially circumstances where goods need to be returned. It helps to think of returns as the reverse of the original process, either as an outbound process to a Vendor or an inbound process from a Customer. The return follows the same basic stages. The return is Proposed (RMA, or Return Materials Authorization). The goods are shipped or received, which constitutes, in this case, an “Order” since the goods haven’t officially been added or removed from our possession yet. When the return is actually approved, this constitutes Fulfillment, and the goods are actually added to inventory or no longer ours. Finally, there’s the Request for Payment, in the form of a credit, which acts in the opposite direction, with us requesting money/credit from a Vendor, or our Customer requesting same from us.

Payments

You may have noticed that the payments themselves are not included in the outline above. This is because Payments normally do not include individual line items, but rather whole documents. A Payment, for instance, might be applied to Invoice 123, not to Item A and/or Item B that appear on that Invoice.

It’s worth noting that we find it useful to have Payments have a TXH record, like other Documents. This is because a payment could be an overpayment or a prepayment. In those cases, when I create a new Payment from Customer A, I should be able to apply that payment to Invoices, Credits (for returns) and also other Payments that have already been made and not applied to anything. By having Invoices, Credits and Payments all recorded in the TXH table, I can show the list of available documents in a list from one table.

I should also note, because it may not be obvious to some, that Payments have line items, each of which links a Payment to a Document to which we’re applying an amount. While one Payment may often apply to one Invoice, it’s quite common that a Payment pays part of an Invoice, or multiple Invoices. Payment Line Items allow us to account for that very common scenario.

Conclusion

As promised, this post does not get into a lot of details about how we implement this model in FileMaker. I hope, though, that you’ve found it thought-provoking, whether you are a developer yourself (FileMaker or otherwise) or are a business owner/manager trying to map out your own processes.

Other posts by Chris Cain+