This project focuses on Manufacturing Operations Risk Analytics, aiming to identify systemic risks within production operations that negatively impact revenue realization, inventory efficiency, and cash flow stability. The analysis leverages PostgreSQL to evaluate production delays, operational bottlenecks, and inventory absorption issues across manufacturing products and categories.
The project is designed to mirror enterprise-level operational analytics, similar to analytical practices used in large multinational manufacturing and technology-driven organizations.
In this project, I acted as an Operations Risk Analyst, responsible for:
- Designing operational risk KPIs aligned with executive decision-making
- Writing structured, reusable PostgreSQL queries using CTEs
- Translating operational metrics into revenue, inventory, and cash flow risk
- Identifying systemic bottlenecks at product and category levels
The primary objectives of this analysis are:
- Identify products that pose the highest revenue risk due to recurring production delays.
- Evaluate whether production delays correlate with inventory buildup and poor inventory absorption.
- Assess whether production delays worsened in 2024 compared to 2023.
- Detect structural operational risks at the product-category level.
- Define an executive-level KPI that best represents overall manufacturing operational health.
- Which products generate the highest revenue exposure due to production delays?
- Are delays associated with excess inventory and inefficient stock absorption?
- Is operational reliability deteriorating year-over-year?
- Which categories represent systemic bottlenecks within manufacturing operations?
- Which KPI should executives monitor to assess operational risk early?
| KPI | Description |
|---|---|
| Delay Rate | Leading indicator of operational failure and revenue risk |
| Average Lead Time | Measures process reliability and bottlenecks |
| Production Efficiency | Ratio of actual output to planned output |
| Inventory Absorption Ratio | Ability of inventory to be absorbed by sales |
| Revenue at Risk | Estimated revenue exposure due to production delays |
The dataset represents a simplified yet realistic manufacturing environment (2023–2024), consisting of:
- Products: Manufacturing items across multiple categories (machined parts, fabrication, assembly, tooling, consumables)
- Production Orders: Planned vs actual production quantities, lead times, and delay status
- Sales Orders: Revenue generated per product and customer
- Inventory Snapshots: End-of-month stock levels to assess buildup and absorption
This analysis was intentionally conducted using PostgreSQL only to reflect:
- Real-world enterprise data environments where SQL is the primary analytical tool
- Scenarios where analysts must work directly on production-like databases
- The need for transparent, auditable, and reproducible business logic
The analysis was conducted entirely in PostgreSQL, utilizing:
- Common Table Expressions (CTEs)
- Aggregate functions
- Time-based trend analysis (Year-over-Year)
- Ratio-based KPIs for operational risk detection
Each query is structured to reflect enterprise-grade SQL practices, prioritizing clarity, reproducibility, and business relevance.
- Delay Rate increased in 2024, indicating declining operational reliability despite stable production volumes.
- Tooling category exhibits the highest structural risk due to long and worsening lead times, creating cascading delays across operations.
- Certain high-revenue products show significant revenue exposure when delays occur, amplifying cash flow risk.
- Inventory buildup is observed in products with weak inventory absorption, suggesting misalignment between production planning and demand realization.
- Delay Rate emerged as the most critical executive KPI, acting as a leading indicator for revenue, inventory, and service-level risk.
Based on the analysis, the following actions are recommended:
- Prioritize risk mitigation on categories with persistently high Delay Rates, particularly tooling, due to its cascading impact on downstream production.
- Review capacity planning and scheduling policies for products with long and worsening lead times.
- Use Delay Rate as an early-warning KPI to detect operational risk before revenue and inventory impacts materialize.
- Align production planning more closely with demand realization to prevent inventory buildup and cash flow strain.
manufacturing-operations-risk-analytics/
│
├── sql/
│ ├── 01_production_delay_risk.sql
│ ├── 02_delay_vs_inventory_buildup.sql
│ ├── 03_yearly_delay_trend.sql
│ ├── 04_category_risk_analysis.sql
│ └── 05_executive_kpi_summary.sql
│
├── data/
│ └── schema_and_sample_data.sql
│
└── README.md
- PostgreSQL
- DBeaver
- SQL (CTE, Aggregation, Time-Series Analysis)
- Manufacturing Operations Analytics
- Risk & Performance Analysis
Sigit Dwiantoro Data Analyst (Operations & Risk Analytics)