1. Problem Statement

Manufacturing firms struggle with efficiently allocating raw materials (RM) to finished goods (FG) while adhering to FIFO principles while preparing for ITC 04 return. The primary challenges include:

1. Manual allocation leading to errors and inefficiencies.
2. High execution time in VBA for large datasets.
3. Difficulty in debugging and ensuring FIFO compliance in Excel-based workflows.

2.Objective

To leverage AI (ChatGPT) to:

1. Debug and optimize the existing VBA code for FIFO-based RM allocation.
2. Reduce execution time while maintaining accuracy.
3. Ensure correct FIFO-based batch-wise RM consumption.
4. Provide automated solutionsfor identifying raw material shortages.

3.AI Solution Overview

ChatGPT, an AI-driven assistant, was utilized to:

1. Analyze the provided Excel dataset and identify inconsistencies.
2. Debug VBA scriptsfor efficiency and accuracy.
3. Optimize the FIFO allocation logicusing structured programming techniques.
4. Suggest alternatives (e.g.,Python) to improveexecution speed.
5. Workflow

Step 1: Input

1. Excel sheets containing:
2. Bill of Material (BOM) – Defines RM needed for each FG.
3. Outward Sheet – Logs RM dispatches with batch numbers.
4. Inward Sheet – Tracks FG receipts.

Step 2: Processing (AI Assistance in VBA Debugging& Optimization)

1. Debugging: Identified missing FIFO sortinglogic.
2. Optimization: Suggestedin-memory calculations to speed up processing.
3. Validation: Ensured correctRM allocation to FG basedon FIFO principles.

Step 3: Output

Optimized VBA Code generating:

1. Output Sheet → Tracks batch-wise RM usage.
2. Insufficient RM Sheet → Logs FG production shortages.
3. Tools and Data Mention Tools Used:
4. AI Models: ChatGPT (for VBA debugging and optimization)
5. Excel & VBA: For automation and data processing
6. Python (Explored but Not Implemented): For alternative fasterexecution Data Requirements:
7. Structured Excel data with BOM, Outward,and Inward records.
8. Date-wise batch detailsfor FIFO allocation.
9. Example: AI’s Impact on Process Improvement Challenges: Before AI (Manual VBA Debugging Approach)
10. Long execution time.
11. Difficult to trace errors in nested loops.
12. Unoptimized FIFO logic causedincorrect batch-wise allocations. Improvements: After AI (ChatGPT-Assisted Optimization)
13. Execution Speed: VBA optimizedto handle large datasets efficiently.
14. Debugging Efficiency: AI pinpointed incorrect logic, reducingmanual effort.
15. Correct FIFO Implementation: Ensured batch-wise allocation as per FIFO.
16. Automation: AI   provided   structured   debugging insights, reducing   human intervention.
17. Key Metrics for Success
18. Reduction in Execution Time: 30-50% improvement.
19. Error Reduction: Ensured correctFIFO-based batch-wise allocation.
20. Manual Debugging Time Saved:At least 70% reduction.
21. Accuracy in ShortageReports: Identified insufficient RM cases correctly.
22. Challenges and Risks
23. Excel VBA Limitations: Processing large data still slow comparedto Python.

Mitigation: Suggested using Pythonfor future scalability.

1. Data Formatting Issues: AI identified columnmismatches and inconsistencies.

Mitigation: Standardized input data validation beforeexecution.

1. Resistance to Change: Users hesitant to shift from VBA to Python.

Mitigation: VBA code optimizedfor current workflow with Python as a future option.

Relevance to Chartered Accountants (CA) and BusinessProfessionals

1. Data Accuracy:Ensures financial and inventory recordsreflect true material consumption.
2. Audit &Compliance: Provides structured FIFO-based batch trackingfor compliance audits.
3. Efficiency &Cost Savings: Reducesmanual intervention, ensuringfaster and error- free reporting.
4. Process Standardization: Helps standardize RM allocation acrossmanufacturing units.
5. Conclusion

AI-powered tools like ChatGPT play a significant role in debugging, optimizing, and ensuring the accuracy of automation processes. By integrating AI assistance into VBA workflows, businesses can enhance efficiency, reduce errors, and ensure structured FIFO- based raw material allocation.