Operations Research - Optimizing Inventory Management with Known Demand

Introduction

Efficient inventory management is crucial for businesses to meet customer demand while minimizing holding costs. This article explores the modeling and optimization of inventory management with known demand using Microsoft Excel Solver. We will guide you through the process by presenting an example problem statement, demonstrating the data setup in Excel, and providing step-by-step instructions on leveraging Solver to optimize inventory levels.

Example Problem Statement

Consider a retail scenario where a store needs to determine the optimal order quantity for a specific product with known demand. The objective is to minimize the total cost, which includes ordering costs and holding costs. The parameters for this scenario are as follows:

Demand: 100 units per month

Ordering Cost: $30 per order

Holding Cost: $2 per unit per month

Lead Time: 2 months

Order Cycle: 4 months

Solving the Problem

To solve this inventory management problem, we can use the Economic Order Quantity (EOQ) model. Here's a step-by-step procedure:

Step 1: Determine the Demand:

Identify the demand for the product. In this case, the demand is 100 units per month.

Step 2: Calculate the Annual Demand:

Since the order cycle is 4 months, we need to calculate the annual demand by multiplying the monthly demand by 12 (months in a year):

Annual Demand = 100 units / month × 12 months = 1200 units

Step 3: Calculate the Ordering Cost:

The ordering cost is $30 per order.

Step 4: Calculate the Holding Cost:

The holding cost is $2 per unit per month. Since the lead time is 2 months, we consider the average inventory level during lead time. The average inventory level during lead time is half of the order quantity:

Holding Cost per unit = $2/month

Holding Cost during lead time=2* Order Quantity × Holding Cost per unit

Step 5: Calculate the Total Cost:

The total cost is the sum of ordering cost and holding cost:

Total Cost = Ordering Cost + Holding Cost during lead time

Step 6: Find the Optimal Order Quantity (EOQ):

The EOQ formula can be used to find the optimal order quantity that minimizes the total cost:

EOQ = Sqrt (2×Ordering Cost × Annual Demand / / Holding Cost per unit during lead time)

Step 7: Interpret the Results:

Once you have calculated the EOQ, interpret the result to determine the optimal order quantity for the specific product.

Now, let's apply these steps to the given problem:

Step 1: Determine the Demand:

Demand = 100 units per month

Step 2: Calculate the Annual Demand:

Annual Demand = 100 units/month * 12 months = 1200 units

Step 3: Calculate the Ordering Cost:

Ordering Cost = $30 per order

Step 4: Calculate the Holding Cost:

Holding Cost per unit per month = $2

Holding Cost during lead time = (Order Quantity / 2) * Holding Cost per unit

Step 5: Calculate the Total Cost:

Total Cost = Ordering Cost + Holding Cost during lead time

Step 6: Find the Optimal Order Quantity (EOQ):

EOQ = Sqrt ((2×30×1200) / (2×2))

EOQ = Sqrt (72000 / 4)

EOQ = Sqrt (18000)

EOQ ≈ 134.16

Step 7: Interpret the Results:

The optimal order quantity (EOQ) is approximately 134.16 units. This means that the store should place orders for approximately 134 units at a time to minimize the total cost, which includes ordering costs and holding costs.

Setting Up the Excel Worksheet

Define Decision Variables: Open a new Excel worksheet. In cells B2:D2, label the cells representing the order quantity for each order cycle (Month 1, Month 2, Month 3).

Objective Function: In a cell, let's say E2, label it "Total Cost." Enter the formula =B2*30 + (100/2)*(B2/2 + C2/2 + D2/2)*2 to represent the total cost, considering ordering costs and holding costs.

Constraints: Introduce constraints to ensure that demand is satisfied in each order cycle.

For Month 1: =B2 >= 100

For Month 2: =B2 + C2 >= 100

For Month 3: =B2 + C2 + D2 >= 100

Non-negativity Constraints:

Set non-negativity constraints for each order quantity: =B2, C2, D2 >= 0.

Solver Parameters Dialog Box

Click on "Solver" in the "Data" tab. This opens the Solver Parameters dialog box.

Set Objective Function and Decision Variables: In the Solver Parameters dialog box, set the objective function cell to E2 and the decision variable cells (By Changing Variable Cells) to B2:D2.

Add Constraints: Click on "Add" to enter each constraint. Use the constraints set earlier for demand and non-negativity.

Choose Solving Method: Choose the Simplex LP solving method for linear programming problems.

Solver Options: Optionally, set additional options based on your requirements.

Solve: Click "Solve" in the Solver Parameters dialog box. Solver will analyze the inventory management model and provide the optimal order quantities for each order cycle, minimizing the total cost while satisfying demand.

Interpreting Results

Once Solver completes its analysis, it will display the optimal order quantities for each order cycle in the worksheet. These values represent the most cost-effective approach to manage inventory, considering known demand, ordering costs, and holding costs.

Conclusion

This example demonstrates how Microsoft Excel Solver can be a game-changer in optimizing inventory management with known demand. By effectively setting up the problem, incorporating constraints, and utilizing the Solver function, businesses can streamline their ordering processes, minimize costs, and ensure that customer demand is consistently met. Excel's Solver provides an accessible yet powerful platform for achieving optimal solutions in real-world inventory management scenarios, making it an invaluable tool for businesses of all sizes.