ERP-Style Excel Inventory Dashboard
Budget: ₹600 – ₹1,500 INR
ENTERPRISE MASTER EXCEL ARCHITECTURE
File Name
SPARES_ENTERPRISE_SYSTEM.xlsx
DATA MODEL (STAR SCHEMA IN EXCEL)
You will maintain one Fact table + multiple Dimension tables + Analytics layer.
DIM_PART
│
DIM_CUSTOMER ─ FACT_TRANSACTIONS ─ DIM_SUPPLIER
│
DIM_DATE
│
PRICE_HISTORY
│
DASHBOARD / PIVOTS
1. FACT_TRANSACTIONS (CORE ENGINE)
Every row = 1 part line (quote/invoice/PO)
Field Description
Transaction ID Unique (auto)
Date Key Link to DIM_DATE
Part Code Link to DIM_PART
Customer ID Link to DIM_CUSTOMER
Doc Type Quote / PO / Invoice
Doc No QT / INV / PO
Qty Quantity
USD Price Unit price
INR Value Total
Currency USD / INR
Source File QT reference
Remarks Notes
This is your single source of truth
2. DIM_PART (MOST CRITICAL TABLE)
Controls everything about parts
Field Description
Part Code (PK) Internal code
Old Part Number OEM
New Part Number OEM
Description Standardized
Category RSL / RBLT / etc.
Sub-Type Optical / Electrical
HSN Code GST
Last Seen Date
3. DIM_CUSTOMER
Customer ID Customer Name Location Industry Key Account
4. DIM_DATE (POWERFUL FOR ANALYTICS)
Date Key Date Month Quarter Year FY
Enables:
Year-wise growth
Monthly trends
6. PRICE_HISTORY (AUTO-DERIVED)
Part Code Year Avg Price Min Price Max Price % Change
Formula:
% Change = (Current - Previous) / Previous * 100
7. FREQUENCY_ANALYSIS
Part Code Count Total Qty Customers Category
8. PART_CHANGE_LOG
Part Code Old Part New Part Change Date Reason
9. INVENTORY_PLANNING (ADVANCED)
Part Code Avg Monthly Usage Lead Time Safety Stock Reorder Point
10. MARGIN_ANALYSIS (OPTIONAL BUT HIGH VALUE)
Part Code Purchase Price Selling Price Margin %
11. DASHBOARD (EXECUTIVE CONTROL PANEL)
DASHBOARD (ENTERPRISE LEVEL)
KPIs
Total Transactions
Unique Parts
Revenue (INR / USD)
Avg Price Increase %
Top Moving Part
Highest Price Escalation
Visuals
1. Category Revenue Split
→ Pie Chart
2. Price Trend (Multi-Year)
→ Line Chart
3. Top 10 Parts (Frequency)
→ Bar Chart
4. Customer-wise Consumption
→ Column Chart
5. High Risk Parts
→ (High price + high frequency)
SLICERS (MANDATORY)
Year
Category
Customer
Supplier
Part Code
DATA FLOW (ENTERPRISE SOP)
QT / Invoice / PO
↓
DATA ENTRY (FACT_TRANSACTIONS)
↓
Validation (DIM_PART / CUSTOMER / SUPPLIER)
↓
Processing (PRICE_HISTORY / FREQUENCY)
↓
Dashboard (Insights)
GOVERNANCE RULES (VERY IMPORTANT)
1. MASTER DATA FIRST
New part → must be added in DIM_PART
New customer → DIM_CUSTOMER
2. NO DUPLICATION
Part Code is primary key
3. NEVER OVERWRITE HISTORY
Always append new rows
4. STANDARDIZATION
Same description always
ENTERPRISE ADVANTAGES
1. Full Traceability
Every part → every transaction
2. Pricing Intelligence
Track price increase over years
3. Inventory Optimization
Identify fast-moving + critical parts
4. Negotiation Power
Data-backed OEM discussions
5. AMC & Service Strategy
Predict spare consumption
File Name
SPARES_ENTERPRISE_SYSTEM.xlsx
DATA MODEL (STAR SCHEMA IN EXCEL)
You will maintain one Fact table + multiple Dimension tables + Analytics layer.
DIM_PART
│
DIM_CUSTOMER ─ FACT_TRANSACTIONS ─ DIM_SUPPLIER
│
DIM_DATE
│
PRICE_HISTORY
│
DASHBOARD / PIVOTS
1. FACT_TRANSACTIONS (CORE ENGINE)
Every row = 1 part line (quote/invoice/PO)
Field Description
Transaction ID Unique (auto)
Date Key Link to DIM_DATE
Part Code Link to DIM_PART
Customer ID Link to DIM_CUSTOMER
Doc Type Quote / PO / Invoice
Doc No QT / INV / PO
Qty Quantity
USD Price Unit price
INR Value Total
Currency USD / INR
Source File QT reference
Remarks Notes
This is your single source of truth
2. DIM_PART (MOST CRITICAL TABLE)
Controls everything about parts
Field Description
Part Code (PK) Internal code
Old Part Number OEM
New Part Number OEM
Description Standardized
Category RSL / RBLT / etc.
Sub-Type Optical / Electrical
HSN Code GST
Last Seen Date
3. DIM_CUSTOMER
Customer ID Customer Name Location Industry Key Account
4. DIM_DATE (POWERFUL FOR ANALYTICS)
Date Key Date Month Quarter Year FY
Enables:
Year-wise growth
Monthly trends
6. PRICE_HISTORY (AUTO-DERIVED)
Part Code Year Avg Price Min Price Max Price % Change
Formula:
% Change = (Current - Previous) / Previous * 100
7. FREQUENCY_ANALYSIS
Part Code Count Total Qty Customers Category
8. PART_CHANGE_LOG
Part Code Old Part New Part Change Date Reason
9. INVENTORY_PLANNING (ADVANCED)
Part Code Avg Monthly Usage Lead Time Safety Stock Reorder Point
10. MARGIN_ANALYSIS (OPTIONAL BUT HIGH VALUE)
Part Code Purchase Price Selling Price Margin %
11. DASHBOARD (EXECUTIVE CONTROL PANEL)
DASHBOARD (ENTERPRISE LEVEL)
KPIs
Total Transactions
Unique Parts
Revenue (INR / USD)
Avg Price Increase %
Top Moving Part
Highest Price Escalation
Visuals
1. Category Revenue Split
→ Pie Chart
2. Price Trend (Multi-Year)
→ Line Chart
3. Top 10 Parts (Frequency)
→ Bar Chart
4. Customer-wise Consumption
→ Column Chart
5. High Risk Parts
→ (High price + high frequency)
SLICERS (MANDATORY)
Year
Category
Customer
Supplier
Part Code
DATA FLOW (ENTERPRISE SOP)
QT / Invoice / PO
↓
DATA ENTRY (FACT_TRANSACTIONS)
↓
Validation (DIM_PART / CUSTOMER / SUPPLIER)
↓
Processing (PRICE_HISTORY / FREQUENCY)
↓
Dashboard (Insights)
GOVERNANCE RULES (VERY IMPORTANT)
1. MASTER DATA FIRST
New part → must be added in DIM_PART
New customer → DIM_CUSTOMER
2. NO DUPLICATION
Part Code is primary key
3. NEVER OVERWRITE HISTORY
Always append new rows
4. STANDARDIZATION
Same description always
ENTERPRISE ADVANTAGES
1. Full Traceability
Every part → every transaction
2. Pricing Intelligence
Track price increase over years
3. Inventory Optimization
Identify fast-moving + critical parts
4. Negotiation Power
Data-backed OEM discussions
5. AMC & Service Strategy
Predict spare consumption