by Tim Cose, Hal Leonard Corporation

I recently had the chance to play with some high-power simulation software called Oracle Crystal Ball. It allows the user to easily iterate independent variables subject to probability distributions in an Excel framework with the goal of optimizing targeted decision variables. That sounds fancy, but at its core, Crystal Ball just lets you apply mathematics to complicated decision making. It is an efficient way of capturing the variability in our businesses to arrive confidently at strategies that will maximize our goals without violating the boundaries imposed by doing business in the real world.

My goal was to develop inventory heuristics to apply to our industry with regards to ordering habits and maximizing profitability at the end of the supply chain (your store). The results surprised me, and they may surprise you too, but taken in the context of print music and brick and mortar retail, there are good explanations. In the broadest sense, if you want to maximize your Gross Margin Return on Investment (GMROI), your customer service level (CSL) is going to stink. I also found the corollary: if the only metric important to you is never missing a sale (maximized CSL), your GMROI will stink, too. Before I provide results, I will explain the model. Apologies in advance for the mathiness.

## The simulation model

Crystal Ball allows you to assign a degree of certainty about a variable in the form of a probability distribution. For this model, the variables included retail price, wholesale discount, supplier lead time, and average shipping cost (as a percent of product cost). I assigned a generous range of values I know represent our industry, and I based their variation on a uniform distribution. Over the thousands of simulations the model runs, these variables change every time (based on probability) so that the output of the model can be considered confident within the boundaries set as typical for the industry.

As the simulation runs and the variables change, its goal is to maximize GMROI, CSL, or a combination of the two. In most cases, I ran the model to maximize GMROI while maintaining a minimum CSL as I believe this is the most accurate portrayal of the trade-off a real business needs to make. The model maximizes these targets by varying two significant decision variables: order quantity and reorder point. These represent how many of an item is ordered and when this number is ordered, relative to the quantity on hand (like a “min” in the min/max system).

Inventory and demand interaction is handled by a Poisson distribution and random number generator. The Poisson distribution is the most accurate probabilistic model for purchases in a retail store. It forms the foundation for demand in inventory forecast models used by retail giants and ERP systems all over the world. If it’s good enough for Walmart, it should be good enough for us, too. This randomly determined demand affects a per week inventory in a simple beginning inventory minus demand equals ending inventory way. A ‘weeks on reorder’ counter works in conjunction with the lead time variable to deliver new inventory orders to our virtual store.

To summarize the model: as simulated inventory (48 week period) is depleted, the model orders as it hits a reorder point and works to find the right quantity and order time to maximize GMROI and CSL when the other variables (retail price, discount, lead time, and ship cost) are not known with 100% precision. The assumption it makes about the nature of demand is the same assumption made by virtually all forecast systems. This all sounds very complicated, but if you were to experience the mathematical juggling act of variables, targets, and constraints in real life, it would feel just like business as usual.

## The lessons

Going into this experiment to optimize the order quantity and reorder point, I expected to see a result close to the way that I believe many retailers use mins and maxes. The min (the QOH that triggers a reorder) would be fairly small and the order quantity would be relatively large. This reflects the ordering habits I frequently see in the industry. But when the model was done optimizing our decision variables, I found exactly the opposite. If we pay any respect at all to Customer Service Level, GMROI is maximized with small order quantities and a relatively high reorder point. Initially, this came as a surprise to me, but after some reflection on how big retailers handle inventory and what running lean REALLY means, it makes perfect sense.

In choosing an order quantity and reorder point, the model is searching for the right amount of inventory to have in the pipeline (on the way from suppliers) so that A) sales are not missed and B) the average inventory on hand does not become large. Looking at the actual inventory behavior in the model, rather than just the output, it is clear that inventory is continuously on order because of the high reorder point. In this virtual store, inventory is always moving in and out with supply and demand. The high reorder point serves as a buffer for unexpected surges in demand, while the small order quantities in the pipeline trickle in and, on average, keep inventory at an optimal level to maximize GMROI.

Customer Service Level (CSL) is treated as a straight percent of sales opportunities realized. What if we ignore this constraint and just maximize GMROI? We end up with a model that will only reorder a small quantity when the inventory on hand is zero. The model tolerates zero risk in inventory and, as a result, has a high GMROI but a low net profit and many missed opportunities. In reality, missed opportunities are unsatisfied customers. This is how a generation relying on internet retail where everything is always “in stock” is created.

What if, on the other hand, we require that the model meets all demand without regard to its GMROI and the associated opportunity cost? Then we end up with customers who always find what they need, but not enough incoming revenue to justify the huge cost of inventory. The model sets reorder points that will always meet demand, even if that demand has a 0.0001% chance of occurring in the real (or simulated) world. Obviously, that is bad business.

**Next month, Tim continues **with: Actionables**, **Sensetivity Analysis** and a Conclusion.**

Whew…LOTS to consider…thank you, Tim! This may be a dumb question, but does the model account for in-bound shipping costs of a large number of small orders?…from numerous publishers?

This is one more piece of the ongoing “balance” that we struggle with daily…just curious!

Hi Don,

No dumb questions at all! I know I’m throwing a lot in there that reads as very technical; I debated about how much to include on the finer details of the model and decided it was worth it at least as a demonstration of what ERP and forecast systems do behind the scenes. All the software work is there to try to approximate exactly the daily balance you’re talking about.

The model accounts for inbound shipping as a percent of the cost of the product. It does not account for volume discounts in shipping or wholesale costs (eg ‘stock orders’). Shipping costs and discounts are both treated as an array of values from X% to Y% to account for the variation from publisher to publisher. This doesn’t result in an equation for buy Z copies if shipping is X% and wholesale discount is Y%, rather it results in a guideline for the industry where we know costs are between X% and Y% but not specifically what the costs will be.

My goals for the project were to a) see what kind of ordering approach a software optimized model would recommend and b) see if I can develop general rules of thumb that can be used in the real world. In Part 2 you’ll see the rules of thumb that hold true given the X% to Y% spread for our industry. They are given as probabilities to reflect variations from vendor to vendor and product to product.

Probabilities are the nature of the beast even with the very best of software and tools. The cutting edge of supply chain right now is all about reducing variability, which makes for more accurate predictions, which can be used to increase revenue and/or reduce costs. Statistics is at the heart of the field, so there are no guaranteed silver bullets. Results always come with a “given these assumptions and provided nothing crazy happens” kind of caveat.

Thanks for your comment. I hope this generates some discussion about the process and relationships between vendors and retailers. We’re working together in this supply chain to serve end-users and only by working together as efficiently as possible will we all see the best results.

Tim

Thank you for the clarification, Tim! One more thought (variable)…back catalog versus new product. Obviously the turns on new are much higher than (most) back catalog titles. The consequence of this not only affects the variable for product re-order, but also the supposed quantity (indicated as “smaller” amounts to maximize GMROI).

Of course…there IS a chance I just don’t understand this model well enough! 🙂

Don

The second part will show how I decided to tackle this. The results are organized based on average weekly demand, so long tail products would naturally fall at the lower end of average demand while front of catalog hot sellers would be in higher demand. Recommended reorder points and order quantities are given based on the average demand.

Knowing the exact demand is no trivial matter though and it isn’t something that someone else can really provide. That has to happen at the retail level using sales history, familiarity with customers, and intuition. This is one of the many reasons that engaged local business is so vitally important to our industry. Preaching to the choir, I know 🙂