No visibility. No system. Constant shortages.
The café was managing inventory the way most small businesses do — in someone's head. No standardized way to track what was in stock, what was being used, or when items needed reordering.
The result was a recurring cycle of shortages, over-purchasing, and decisions made on instinct rather than data. Over-ordering perishables led to waste. Under-ordering disrupted service.
A structured inventory system with full SKU-level visibility.
I built a complete inventory tracking system in Excel that gave the business full visibility into every product in its supply chain. Every item was assigned a SKU, categorized, and tracked against usage and reorder thresholds.
Designed to be practical — simple enough for the team to use daily without a learning curve.
Note: The downloadable file uses anonymized sample data to protect business confidentiality. The system was built and used with real operational data at the café.
What's inside the system.
- SKU assignment for every inventory item with category tagging and unit-of-measure standardization
- Current stock level tracking with automatic comparison against defined reorder points
- Usage monitoring over rolling time periods to identify consumption patterns
- Low stock flagging with visual alerts for items below threshold
- Stock flow log tracking incoming purchases and outgoing usage by date
- Supplier and cost data embedded at SKU level for cost-of-goods analysis
- Summary dashboard showing overall inventory health at a glance
Open the actual system.
This is the real working spreadsheet built for Lotus Café — SKU master sheet, stock flow log, usage monitoring, and automated reorder flags. All tabs included.
From daily emergency runs to a scheduled system.
Before this system, the café was doing 5 resupply runs a week — each one taking roughly 4 hours of the owner's time. That's 20 hours a week spent reacting to shortages that a proper system would have prevented. After building tiered reorder thresholds from actual daily usage data, runs dropped to 2 per week and were handed off to a dedicated supply runner. The owner got 12 hours a week back.
The system used statistical safety stock calculations — assigning 99% service levels to critical items like oat milk, croissants, and eggs, and 90% levels to low-variance items. The café stopped running out of things, stopped over-buying, and made purchasing decisions based on data instead of instinct.