loops-a-lot/fifo-inventory-analysis

FIFO Inventory Analysis Tool

A web-based application for performing First-In-First-Out (FIFO) inventory analysis on kitchen goods and other products. Built with Python and Streamlit, this tool helps businesses accurately calculate cost of goods sold (COGS) and current inventory valuations.

🚀 Features

• FIFO Calculation Engine: Accurate First-In-First-Out cost calculations
• File Upload Support: Import data from CSV and Excel files
• Manual Data Entry: Add purchases and sales directly in the web interface
• Real-time Analysis: Instant COGS and inventory valuation calculations
• Detailed Reporting: Transaction history, inventory batches, and cost breakdowns
• Multi-product Support: Track multiple products simultaneously
• Web-based Interface: Easy-to-use Streamlit dashboard

📋 Requirements

• Python 3.7+
• Required packages (see requirements.txt)

🛠️ Installation

1. Clone or download this repository
2. Install required packages:

   pip install -r requirements.txt
   

🚀 Usage

1. Start the application:

   streamlit run app.py
   

2. Upload your data:

   • Go to the "Data Upload" page
   • Upload CSV/Excel files with your purchase and sales data
   • Or manually enter transactions using the forms

3. Analyze inventory:

   • Navigate to "FIFO Analysis" page
   • View calculated COGS, inventory valuations, and detailed reports

📁 Data Format

Purchase Data

Required columns:

• date: Purchase date (YYYY-MM-DD format)
• product: Product name
• quantity: Number of items purchased
• unit_cost: Cost per unit

Example:

date,product,quantity,unit_cost
2024-01-01,Dish Brush A,50,1.50
2024-01-15,Sponge Pack,30,3.00

Sales Data

Required columns:

• date: Sale date (YYYY-MM-DD format)
• product: Product name
• quantity: Number of items sold

Example:

date,product,quantity
2024-01-10,Dish Brush A,20
2024-01-20,Sponge Pack,12

🧪 Testing

Run the test suite:

python -m pytest test_fifo_calculator.py -v

Test with sample data:

python test_sample_data.py

📊 Example Output

The tool provides detailed analysis including:

• Total Inventory Value: Current value of all remaining inventory
• Total COGS: Cost of goods sold for all sales transactions
• COGS by Product: Breakdown of costs by individual products
• Inventory Valuation: Current stock quantities and values
• Batch Details: FIFO inventory layers with purchase dates and costs
• Transaction History: Complete record of all purchases and sales

Sample Results

Total Inventory Value: $444.25
Total COGS: $361.75
Items in Stock: 164
Items Sold: 146

COGS by Product:
Dish Brush A         | Qty:  60 | COGS: $   92.50
Sponge Pack          | Qty:  20 | COGS: $   60.00
Kitchen Towel Pack   | Qty:  18 | COGS: $   81.00

Current Inventory:
Dish Brush A         | Qty:  55 | Avg: $ 1.64 | Value: $   90.25
Sponge Pack          | Qty:  30 | Avg: $ 3.17 | Value: $   95.00

🔍 FIFO Methodology

The tool uses the First-In-First-Out method:

1. Purchases are recorded with date, quantity, and unit cost
2. Sales consume inventory from the oldest purchases first
3. COGS is calculated using the cost of the oldest inventory
4. Remaining inventory is valued using the most recent purchase costs

📝 Project Structure

├── app.py                    # Main Streamlit application
├── fifo_calculator.py        # Core FIFO calculation engine
├── mcp_fifo_server.py        # MCP server for AI assistant integration
├── test_fifo_calculator.py   # Comprehensive test suite
├── test_sample_data.py       # Sample data testing script
├── test_mcp_server.py        # MCP server test suite
├── requirements.txt          # Python dependencies (includes MCP)
├── mcp_config.json          # MCP server configuration
├── sample_purchases.csv      # Sample purchase data
├── sample_sales.csv          # Sample sales data
├── README.md                # Main documentation
└── MCP_README.md            # MCP server documentation

🎯 Use Cases

Perfect for:

• Small to medium businesses tracking inventory costs
• Kitchen goods retailers (dishbrushes, sponges, cleaning supplies)
• Any business requiring FIFO cost accounting
• Educational purposes to understand FIFO methodology
• Preparing data for tax reporting and financial statements

🔧 Technical Details

• Frontend: Streamlit web framework
• Backend: Pure Python with pandas for data processing
• FIFO Engine: Custom queue-based algorithm using collections.deque
• Precision: Decimal arithmetic for accurate cost calculations
• File Support: CSV and Excel file formats via pandas
• Deployment: Easy deployment via Streamlit Cloud or local hosting

🤖 MCP Server Integration

This project now includes a Model Context Protocol (MCP) server that enables AI assistants to perform FIFO inventory analysis through natural language conversation!

MCP Features

• 7 MCP Tools for inventory management
• Multi-table Support for different inventory contexts
• SQLite Persistence with automatic data storage
• CSV Import for bulk data processing
• Real-time FIFO Analysis through conversational interface

Quick MCP Setup

1. Install: pip install mcp>=1.0.0
2. Run server: python mcp_fifo_server.py
3. Configure in your MCP client using mcp_config.json

See for complete MCP server documentation.

📈 Future Enhancements

Potential additions:

• Data visualization charts and graphs
• Export functionality (PDF reports, Excel summaries)
• Multi-location inventory support
• Integration with accounting systems
• Automated report scheduling
• Advanced filtering and search capabilities
• Additional MCP tools for forecasting and alerts

🤝 Contributing

This tool is designed to be easily extensible. Key areas for contribution:

• Additional inventory methods (LIFO, Weighted Average)
• Enhanced reporting features
• Data visualization improvements
• Performance optimizations for large datasets

Built with ❤️ for accurate inventory accounting