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.
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.