INVENTORY AND SUPPLY CHAIN PLANNING models are typically part of ERP systems in mid to large companies, but those systems are expensive and many small companies find that they have to resort to Excel to manage their inventory and materials purchasing.  This is daunting in that there are usually significant complexities associated with shared materials and large numbers of materials.  Companies that are primarily buy/sell, where they buy finished goods and sell those goods to their customers, usually find the management of inventory and the supply chain easier to manage. The same is true for companies with simple bills of material (BOMS).

CategoryMaster has developed an Excel based model that can be adapted to manage simple to moderately complex supply chain needs.  It requires a forecast and inventory status to be input monthly, but otherwise uses parameters such as safety stock levels and minimum/economic order quantities (MOQ/EOQ’s) to provide a proposed purchase plan.  On a monthly basis this plan is reviewed and decisions made as to whether to use the plan or modify it.  The manager then hard codes the decision into the model and moves forward accordingly.

The model starts with a beginning inventory and then makes adjustments to the inventory based on requirements, inbound receipts and safety stock to then calculate a net requirements for the month. When that “Net Requirements” value is greater than zero, the model suggests an order based on the MOQ.

In this example, the manager has worked through the first quarter and made decisions not to buy these two components. The model had originally suggested an order of the second item in March of 140,000 (the MOQ), but the manager overrode the suggestion and did not place the order.  The result was an increase in the suggested order to be placed in April. 

So, as the manager reviews the recommendations and makes a decision, he/she hard codes the decision in to the Suggested Order line and use a format color  to indicate the item has been reviewed in that month. The model will then incorporate that decision into future order suggestions.