This project focuses on creating a prescriptive decision model to assist a manufacturing company in optimizing inventory management for a key engine component. The objective is to minimize total inventory costs by balancing holding costs, ordering costs, and economic order quantity (EOQ). The analysis is conducted using Excel and R, providing a comprehensive solution to inventory optimization challenges.

1. Develop Mathematical Models:

   • Compute annual holding and ordering costs.
   • Formulate a total inventory cost model.

2. Determine Economic Order Quantity (EOQ):

   • Calculate the optimal order quantity and frequency to minimize total costs.

3. Implement Sensitivity Analysis:

   • Explore the impact of varying parameters like demand, order costs, and holding costs.

4. Verify Results with Solver:

   • Use Excel Solver to validate the mathematical model and optimize the total cost.

• Primary Tool: Microsoft Excel
• Secondary Tool: R
• Key Features:
  
  • EOQ Model Development
  • Sensitivity Analysis using Two-Way Data Tables
  • Visualizations of Total Cost vs. Order Quantity
  • Solver Optimization for Validation

• Annual Demand: 15,000 units
• Order Cost: $220 per order
• Holding Cost: $14.40 per unit/year
• EOQ: 677 units
• Annual Total Cost: $9,748.85

• Optimal Order Frequency: 22.16 orders per year.
• Balanced Costs: Holding and ordering costs are minimized at $4,874.43 each.
• Validation: Results verified using Excel Solver and sensitivity analysis.

. ├── Data/ │ ├── Inventory_Modelling.xlsx ├── Analysis/ │ ├── EOQ_Model.xlsx │ ├── Sensitivity_Analysis.xlsx ├── Visualizations/ │ ├── EOQ_Plot.png ├── Reports/ │ ├── Inventory_Modelling_Report.pdf ├── README.md

For a detailed analysis, including methods, formulas, and results, refer to the full report:
📄 Inventory Modelling Report

Feel free to reach out for feedback, questions, or collaboration opportunities:
LinkedIn: Dr. Syed Faizan

Author: Syed Faizan
Master’s Student in Data Analytics and Machine Learning