Here is an example of an Inventory Optimization model using excel Solver. See below for all the details.
In today’s era of Big Data, wading through piles of information and knowing what to focus on is often half the battle — literally leading to “analysis paralysis”.
This is one more area where Sam will be invaluable for you.
Largely employing the law of 80/20 — where 80% of your results come from just 20% of your actions — Sam will help you choose the most important pieces of information so that your organization has the focus to thrive.
And this is the purpose of a well-crafted decision model which gets right to the critical core. Sam will help you transform piles of “ugly data” into a hyper-clear valuable tool known as a decision model.
The Excel based Decision Model
If you haven’t heard of “decision models”, they are used in business to make better choices when faced with uncertain conditions. Businessdictionary.com defines a decision model as:
A computer based system that predicts the outcomes of decisions. The relationships between elements of the decision and the forecasted results are mapped in order to understand or control problems.
The decision model may also predict what will happen if a certain action is taken.
The National Institute of Health (NIH) uses models to solve problems and they report, “A decision model provides a way to visualize the sequences of events that can occur following alternative decisions.”
Decision models are used to solve a variety of problems, such as:
- Inventory control
- Inventory optimization
- Advertising optimization
- Marketing and return on advertising investment (ROAI) solutions
- Net present value solutions
- Capital management
- Ticket sales
- Product sales
- Portfolio optimization
- Accounting problems
- Human resources issues such as scheduling
- Manufacturing production time optimization
- Raw materials calculations
- Real estate project optimization
- Restaurant seating and square-footage optimization
Sam takes Decision Models to the next level with Optimization
Many organizations create beautiful reports with colored lines and bolded fonts – but many times reflecting outcomes that are rarely achieved. They look very pretty. But most of the time they don’t reflect the realities of an unknown business climate.
Sam creates beautiful reports, too, but based on the problem to be solved he integrates unexpected factors such as randomness in the market, sales fluctuations, and a wide array of probabilities and variables into his decision models. This can more accurately reflect the unknowns of the market. He also integrates the option to optimize decisions — making the options statistically relevant — as a result of running hundreds of thousands and sometimes millions of calculations.
Additionally, Sam can help you discover your absolute best case scenario given virtually endless roadblocks (called constraints) so that your team can see what the optimal situation actually looks like. And he can tell you what the probability of the optimal solution actually is under real world scenarios.
This insight will help you make better decisions.
Here’s How to Decrease Variable and Fixed Costs to Make More Net Income!
— Using a Decision Model Sam Helped to Optimize a Restaurant with Multiple Locations
For example, Sam was asked to optimize the profit of a restaurant business model, so that their main location can have benchmarks for improvement. In order to be competitive, this business model needed to help the manager achieve a net profit margin of 18% and relate it to the project investment payback period.
Optimizing the net profit of this restaurant became the goal of the model.
This company needed to know what decisions to make in order to keep control over costs (variable) to maintain an industry standard for gross profits. The model also needed to keep control on fixed costs in order to maintain a net income goal.
Before Sam, all these moving parts were very difficult to manage. Finding the optimal solution – reflecting the real business environment — was near impossible.
Once Sam built the foundation of the model, based on real world historical data, Sam integrated the sales revenue information in order to optimize the model. These became the ‘decision variables’.
His model also integrated break even amounts and investment payback periods, as well as other key performance indicators.
Here is an example of what this model can look like:
Sam then built in multiple formulas instructing the model to find optimal solutions. Using statistics, algebraic formulas and a variety of excel software add-ins as core tools, Sam runs literally thousands of calculations to find the most optimal decision(s).
How to Be a Better Inventory Manager
— So your resources make the highest return possible!
As another example, Sam was asked by a manufacturer in the construction industry to help them optimize their inventory investment. His task was to locate the ideal amount of inventory to buy so they had ample product to serve their customers, but not so much inventory that it was wasting valuable resources that could be used in other places earning a better return.
How do you know the optimal amount of inventory to buy?
The optimal amount of inventory to purchase is called the ‘economic order quantity’ (EOQ).
EOQ is built on three main factors:
- The holding cost to keep inventory on the shelves,
- The cost to order the inventory, and
- The quantity demanded by the customers.
Sam built the model to create the optimal amount that the company should order at one time, the EOQ. After updating the annual unit demand (top yellow cell) and the purchase price per unit (lower yellow cell), and tied the values to an algebraic formula, Sam then ran thousands of calculations to find the optimal value. Here is an example of the type of model:
Sam will Help You Make Better Decisions
The decision to be made was calculated as the optimal value and tells the company to order 1,127 units. Again, this optimized number is based on the purchase price of the product, the amount ordered by customers, the cost of holding it in the warehouse and the cost to order. With the EOQ, this construction manufacturer now knows what their optimal inventory purchase is – leading to a better purchase decision and better allocation of valuable resources .
For more information about Sam, and how he can help you, please call his 24-hour FREE recorded message at: (602) 892-0777.
Source: Kuntz, Karen. “Overview of Decision Models Used in Research.” Decision and Simulation Modeling in Systematic Reviews [Internet]. U.S. National Library of Medicine, n.d. Web. 01 Feb. 2017.