Inventory Automation & Alert System
Budget: ₹1,500 – ₹12,500 INR
Project Goal: Inventory Automation via Power Query
• Source Data: Convert handwritten/printed daily inventory logs (JPEG/PNG photos) into a structured digital format.
• OCR Integration: Implement a workflow to extract data from images accurately (using Excel’s "Data from Picture" or a specialized OCR tool).
• Data Cleaning (Power Query): * Automate the "Fill Down" logic for category headers (e.g., Veg Puffs, Samosas).
• Handle inconsistent row heights and merged cells from the original scan.
• Automated Calculations:
• Stock Reconciliation: Calculate Opening + Production - Returns = Actual Sales.
• Financial Tracking: Automatically multiply Sales * MRP to get Total Revenue.
• Theft/Variance Detection: Flag discrepancies between "Actual Sale" and "Physical Stock" automatically.
• Daily Refresh: The system must allow me to simply "add a new photo" and click Refresh to update the master dashboard.
• Output: A clean, tabular Excel table ready for monthly reporting.Updated Requirements for SMS Alerts
• Trigger-Based Alerts: System must detect if the "Theft Value" or "Daily Variance" is Positive (+) or Negative (-).
• Automated SMS Notification: * If the balance is Negative (Shortage/Loss), send an SMS: "Alert: Inventory Shortage of [Amount] detected for [Date]."
• If the balance is Positive (Surplus), send an SMS: "Daily Summary: Surplus of [Amount] recorded."
• Integration Method: Prefer using Power Automate (Microsoft Flow) or a simple VBA script connected to an SMS Gateway API (like Twilio, TextLocal, or Msg91).
• Real-time Processing: The SMS should trigger immediately after the user clicks "Refresh" in Power Query.
• Source Data: Convert handwritten/printed daily inventory logs (JPEG/PNG photos) into a structured digital format.
• OCR Integration: Implement a workflow to extract data from images accurately (using Excel’s "Data from Picture" or a specialized OCR tool).
• Data Cleaning (Power Query): * Automate the "Fill Down" logic for category headers (e.g., Veg Puffs, Samosas).
• Handle inconsistent row heights and merged cells from the original scan.
• Automated Calculations:
• Stock Reconciliation: Calculate Opening + Production - Returns = Actual Sales.
• Financial Tracking: Automatically multiply Sales * MRP to get Total Revenue.
• Theft/Variance Detection: Flag discrepancies between "Actual Sale" and "Physical Stock" automatically.
• Daily Refresh: The system must allow me to simply "add a new photo" and click Refresh to update the master dashboard.
• Output: A clean, tabular Excel table ready for monthly reporting.Updated Requirements for SMS Alerts
• Trigger-Based Alerts: System must detect if the "Theft Value" or "Daily Variance" is Positive (+) or Negative (-).
• Automated SMS Notification: * If the balance is Negative (Shortage/Loss), send an SMS: "Alert: Inventory Shortage of [Amount] detected for [Date]."
• If the balance is Positive (Surplus), send an SMS: "Daily Summary: Surplus of [Amount] recorded."
• Integration Method: Prefer using Power Automate (Microsoft Flow) or a simple VBA script connected to an SMS Gateway API (like Twilio, TextLocal, or Msg91).
• Real-time Processing: The SMS should trigger immediately after the user clicks "Refresh" in Power Query.