📦 Inventory Management Decision Model

MAIN TOOL

Excel Workbook

Secondary tool

R Programming Language

INDUSTRY

Business

📚 About the Project

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 costsordering costs, and economic order quantity (EOQ). The analysis is conducted using Excel and R, providing a comprehensive solution to inventory optimization challenges.


🔍 Key Objectives

 

  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.

🚀 Tools & Techniques

 

  • 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

📊 Key Results

 

Economic Order Quantity (EOQ)

 

  • 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

Insights

 

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

📂 Project Structure

 

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


📜 Full Report

 

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


🤝 Connect with Me

 

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


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

Excel Workbook and the Report of the Analysis