29 December 2011

What’s wrong with EOQ?

Economic Order Quantity (EOQ) EOQ is essentially an accounting formula that determines the point at which the combination of replenishment costs and inventory carrying costs are the least. The goal being to minimize both the ongoing costs of carrying inventory and the expenses involved with replenishing inventory.

The basic EOQ formula looks like this:EOQ_basic_formula

As you can see, this formula attempts to balance (simultaneously) the following factors related to the business expense linked to holding and replenishing inventory:

  1. Usage rates – how many are sold or consumed over a period of time (one year in the basic formula)
  2. Cost of replenishment – how much it costs the firm to replenish a single inventory item (SKU) from the point of recognizing the need for replenishment through putting the quantities back on the shelf
  3. Carrying costs – all of the costs and expenses related to storing and handling of the inventory quantities held

Let us take a look at how these factors interact in a practical example:


In our example, we have an item that has a cost of $25 per unit, and the average daily demand is five (5) units. For this firm, the cost of replenishment is slightly above average—sitting at $30 per PO line processed for inventoried goods.

Observe what happens to the EOQ on this item as the cost of carrying inventory moves through the range from five percent (5%) to 40 percent.

When inventory carrying costs are very low compared to the cost of replenishment (five percent and $30, respectively), EOQ recommends big orders. In this case, each order would support more than 75 days of average demand.

On the other end of the spectrum, when carrying costs are quite high (40 percent) relative to the cost of replenishment, EOQ suggests smaller inventories (as the result of smaller orders) and the order cycle is slashed to almost one-third its former value (now, just over 26 days).

Underlying assumptions

The assumption being made in the construction of the EOQ formula is that the cost of carrying inventory is linear. That, at a five percent rate, a one dollar decrease in inventory on-hand will lead to a five cent reduction in carrying costs to the firm. Similarly, at a 40 percent carrying cost rate, a one dollar decrease in inventory on-hand will lead to a 40 cent decline in carrying costs.

Unfortunately, the linear relationship assumed by the EOQ formula simply does not exist.

When calculating the cost of carrying inventory, a large number of factors are generally considered:

  1. Warehouse space rental (or equivalent)
  2. Utilities expense
  3. Property tax expense
  4. Maintenance expenses on the warehouse and warehouse equipment
  5. Inventory write-offs/write-downs
  6. Other inventory shrinkage
  7. Financing expenses for the warehouse, the equipment, and the inventory itself
  8. Insurance expenses on the warehouse and the inventory
  9. Labor expenses related to warehouse operations

When inventory is reduced $1,000 in a warehouse with a calculated 25 percent carrying cost, what are the likely real impacts on expenses for carrying inventory?

  1. Warehouse space rental (or equivalent) – no change
  2. Utilities expense – no change
  3. Property tax expense – no change
  4. Maintenance expenses -  no change
  5. Inventory write-offs/write-downs – possibly some change, but not necessarily at the same “average” rate
  6. Other inventory shrinkage – same as above
  7. Financing expenses for the warehouse, et al -  no change
    Financing expense on the value of the inventory – some change possible
  8. Insurance expenses on the warehouse, et al – no change
    Insurance expenses on inventory – some change
  9. Labor expenses – no change

In short, only three of the nine items involved in calculating the cost of carrying inventory would likely change based on $1,000 reduction in inventory. That’s because increases or decreases in the volume and dollar amount of inventory held in a warehouse operations produce relatively large but non-linear changes operating expenses.

IM CostOfCarry_stepIncreases

As inventory grows, changes like adding a second shift in the warehouse, acquiring additional warehouse space, or adding manpower to handle increased volumes happen incrementally. The EOQ formula has no way to account for these non-linear changes to operating expenses. Therefore, your EOQ decision-making my be entirely off the mark for success and increased profits.

What’s the answer?

To manage your inventory quantities, I would highly recommend the application of Dynamic Buffer Management. [Click on the link and read the article there.]

To deal with non-linear changes in your enterprise—decisions that may lead to major changes in inventories (increases or decreases)—you need a broader formula that considers your system (your enterprise) as a whole. That would be this one:



This formula would cover changes like adding a second shift (change in Operating Expenses) or building a new warehouse (change in Investment).

Think about. Contact me if you need further clarifications.

Getting started in Business Intelligence (BI) on a budget

This is a simple demonstration as to how you and your firm can get started turning the data that you already have into the information you desperately need using tools you already own. The task of turning data into information for decision-making is the essence of business intelligence (BI).

So, here we go.

Everybody has data

Everybody has data. Many companies are wallowing in data. What they are lacking is “information.”

Read my posts here and here for more about the differences between data, information and knowledge.

Quick! Take five or ten minutes to peruse the following table of data and write down everything that you see in these data to help make decisions about the firm’s future.


I will give you one hint: the column identified as ‘ARPAC’ is “Average Revenues per Active Customer.”

Okay. Times up.

Hold on to your list.

Turning data into information—simply, easily, cheaply

In order to produce what follows, I used only Microsoft® Excel™ and its native ability to access databases to fetch and refresh data.

Here’s the first graph I produced:


This is nothing more than a simple bar graph of column “SOSales” (Sales Order Sales, as opposed to Invoiced Sales, for example) shown in the data above. I used Microsoft’s native capabilities to add a “trend line.”

By looking at this simple graph, several questions might come to mind that would bear further investigation:

  1. Why have our monthly sales dropped from just over $8 million a month to an average of about $6 million per month over these 29 months?
  2. Why or how were able to produce about $11 million in sales in July of 2008? What did we do differently? How can we build on what we learned in that experience?
  3. Is my drop in sales related to lost customers?

The next graph that I produced looked like this:


This graph answered my question number three above—at least partially. Month-to-month our firm has stayed pretty steady in terms of the number of active customers served. The firm is hovering right in the 250-customers-per-month range.

On the one hand, that is good. It means the firm is steady in this regard, but it does provoke other questions that would need to be answered through further digging:

  1. We are serving about 250 customer per month, but is the same 250 customers, or do I have high turnover rates for customers?
  2. Are we constantly having to spend precious marketing resources to capture new customers, or do we have a high volume of repeat business?

But wait! If we are not loosing customers (at least in numbers), but our sales are falling off (in aggregate), what is that telling us?


The third graph I produced was “Average Sales per Active Customer” (month-to-month). This graph clearly shows that between January 2008 and May 2010, the firm’s average sale per active customer fell from about $32,000 per customer to under $25,000 per customer.

Here again, this graph immediately provides clues worthy of further, more detailed, investigation:

  1. Are these different customers buying less product? Or, are we serving pretty much the same customers, but they are just buying less from us?
  2. Either way, we should figure out why: Are they buying similar quantities, but our prices (and, perhaps, margins) have shrunk over this period? Or, are they buying smaller quantities of merchandise or services from us?
  3. Either way, we should find out why: If they are buying smaller quantities, is some of that business going to our competitors?

Next steps

As you can see, turning the data into information allows our mind to quickly digest it and move toward decision-making. In some cases—perhaps many cases, when you first start—the process will lead to further information gathering.

On the other hand, you will sometimes discover that tribal knowledge already present in your organization will help you take immediate steps to begin making more money tomorrow than you are making today. Frequently, those steps involve no investment at all. Sometimes all it take is understanding better what is happening. Other times, a simple policy change permits significant increases in Throughput and profits.

After all, isn’t that really what you want to do—not spending six-figures on a new business intelligence “solution”?

Read more here about unlocking “tribal knowledge.”

How I did it step-by-step

  1. Identify the data
  2. Build a SQL Server view or query
  3. Connect Microsoft Excel to the data
  4. Build the graphs

Total time: about 2 to 2.5 hours

28 December 2011

Business Intelligence for the coming year

Recently I was asked by a business writer for my recommendations for “BI New Year’s Resolutions.” I doubt my response was what the writer had hoped for, since many business blogs and publications garner support from advertisers. And, when you are doing that for a living, you really want to write things that are supportive of the kinds of products your advertisers supply. These days, since business intelligence (BI) is all the rage, there are a lot of dollars being proffered for advertising of upscale business intelligence solutions.

For better or worse, I don’t have to worry about that. (Of course, my income is smaller as a result.) But, here’s what I wrote—along with some other advice to round it out.

BI New Year’s Resolution

RESOVED – I will never, ever, ever again undertake a BI project just because someone in my organization thinks “it might pay-off.” Instead, I will faithfully resolve to calculate—in advance—the expected ROI (return on investment) for the project.

I have learned my lesson: BI is not like an engine oil additive: departments can’t just “pour it in and expect the company to run smoother, faster, longer and get higher mileage” through some mystical power brought to them by the BI fairy.

In calculating the ROI, I will also remember that “approximately right” is fart better than “precisely wrong,” so will not waste my firm’s precious resources trying to hone a number to perfection before taking action—especially in this tough economy.


The second question to which this writer asked me to reply regard “top BI trends” for 2012. Once again, I’m pretty sure I let her down. Here’s what I wrote:

BI Trends for 2012

In 2012, an increasing number of small-to-mid-sized firms will discover that, to get started in BI, they do not need to make six-figure investment. In fact, they may not even need to make a five-figure investment.

If they can unlock “tribal knowledge” and begin to understand what to measure in order to make a real difference in the Throughput of their system (i.e., the whole firm), chances are they can make use of tools they already have like Microsoft® Excel™ to capture data from their ERP system directly via ODBC (open database connectivity) or OLEDB (object linking and embedding for databases). This may lead to insights, and those insights may lead them to market segmentation or other innovative profit-improvers. They do not need expensive software to build a simple, yet valuable, dashboard so they can start making more money sooner—rather than later.

In the next post, I will provide a concrete example of how simple BI can be done using tools your firm probably already owns.

19 December 2011

Technology Wars 2: The Search for More Profits

Almost a year ago I wrote an article entitled, “What does ‘demand-driven’ really mean?” in which I outlined a view of a supply chain driven end-to-end by real-time (or near real-time) demand feedback. My recollection of this writing was triggered today by an article that appeared today on the Financial Times website: “Technology: Smarter software helps minimise discounting.”

In the FT (Financial Times) article, Claer Barrett writes:

“As retailers grapple with falling consumer spending and rising costs, the smart use of technology is proving a valuable weapon.

“Creating a point-of-sale linked supply chain is the latest tactic that larger retailers are employing in order to manage inventories and minimise discounting.”

Among other things, Barrett discusses how the entire supply chain—from the retail all the way back to the manufacturer—is being forced to cope with greater and greater uncertainty. At the same time, Barrett correctly points out that today’s “consumer is more empowered than ever before” via online shopping and price-comparison options.

Barrett’s discussion of the matter leads directly to another topic on which I have written here a number of times—namely, market segmentation. [Click here for more.] Retailers everywhere are learning to collect and leverage high volumes of point-of-sale data, mostly through the proliferation of loyalty programs. [Note: I just checked my pockets. I must be a member a more than dozen loyalty programs ranging from pet supply stores to gas stations and more.]

Between a rock and hard place

Even with improved ability to segment the market and identify buying trends and patterns, the whole supply chain is still caught between the “opposing problems of excess inventory and stock shortages,” as Barrett puts it. Barrett, however, is far too gentle, I think. The horns of the dilemma should really be stated as

excess inventory versus stock-outs.

Almost everyone who has had responsibility for managing inventories of any kind knows exactly what I’m talking about. Being short on stock (low inventories) does not on whit of damage. But being out-of-stock means

  1. Lost sales of the out-of-stock goods
  2. Lost sales on other goods that may have been purchased by customers seeking the out-of-stock item(s)
  3. Potentially, customers lost temporarily or even permanently to competitors

As I have stated elsewhere, the value of losses resulting from out-of-stock conditions—if calculated at all—is almost always vastly understated.

However, on the other end of the spectrum, even though the supply chain suffered out-of-stocks on (almost always) the most popular items, they are almost never able recoup the profits on those items for which they are overstocked.


In fact, chances are they will have to liquidate their overstocked item at or below the price they paid for them. Hence, Barrett’s reference to finding ways to “minimise discounting.”

The key to creating more profits is a “demand-driven” supply chain

My article on a demand-driven supply chain suggests technology that is within the reach of almost every retailer today—not just the big-box merchants. But it requires management to seek two things that they are presently overlooking in far too great a degree;

  1. The true cost of out-of-stocks to their operations and to the entire supply chain
  2. The return-on-investment available to them for building a truly connected and collaborative supply chain

If you are a mid-market retailer, distributor, wholesaler or manufacturer, do not delay in pursuing the discovery of ways to create for yourself a sustainable competitive advantage even in a very challenging economy.

Further reading: Dynamic Buffer Management (DBM)

Richard D. Cushing is a senior solution architect at RKL eSolutions in Lancaster, PA.