Owner's dashboard · PostgreSQL
Twelve months of sales, stock and GST for a mobile-accessories and kitchenware trader in Bhiwandi. Every number on this page comes from a SQL view over the shop's purchase bills and sales invoices. Data to 30 Sep 2026.
Simulated data. The business, its customers, suppliers and GSTINs are made up. A seeded simulation generated the year of bills, modelling festivals, growing and fading products, and an owner who reorders by feel. The SQL runs unchanged on real purchase and sales registers.
They're 17% of sales but 5% of gross profit. The supplier put prices up 8% in February and the shelf price stayed at ₹999, so the margin fell from 10.6% to 6.9% (3.4% online, before courier costs).
Raising the 10,000 mAh to ₹1,099 would take its counter margin from 8% to 16.5%.
Micro-USB chargers and cables, AUX cables and wired earphones: 8% of all stock, unsold for 4 to 8 months. ₹1.08 lakh of them was bought this year while sales were falling. The last wired-earphone order (280 units, 13 May) came in when they were selling 60 a month, down from 149 in January.
Clear them to repair shops at cost and take them off the reorder list.
On top of everyday reorders, 13 items need festival stock before the rush starts on 18 October. Brass and copper gift sets come from Moradabad, 9 days away, so they must be ordered by 9 October; two are already sold out.
The everyday reorder levels won't catch this. They're set from last year's sales, which is also why the fast-growing 33W charger was out of stock 21 of the last 180 days (about ₹1 lakh of missed sales).
Monthly sales before GST, by channel. Hover or tab through the months for the numbers.
Each category's share of sales and of gross profit, last 12 months
Power banks against the whole shop. Cost is the latest supplier bill's rate.
Pace is what sold in the last 28 days, divided by the days the item was actually in stock. Order when stock won't last the supplier's lead time plus a week. Quantities cover the lead time plus 30 days, rounded up to the supplier's pack size.
Missed sales = days out of stock × the item's pace on in-stock days. Treat it as the upper end: some customers wait.
Dead: no sale in 90 days. Slow: more than six months of stock at the current pace. Seasonal: a festival item with stock left over, so hold it for the next festival rather than discounting it.
Laid out like GSTR-3B: tax collected on sales, input tax credit from purchase bills, and the cash left to pay. Unused credit carries forward. CGST, SGST and IGST credit are pooled here, so treat it as an estimate for your CA.
October 2025 looks high because the Diwali stock was bought, and its credit claimed, in September, before this data starts.
A items make the first 80% of sales and should never run out. C items make the last 5%. Where the profit rank sits far below the sales rank, the item is busy but not earning.
Stock is never typed in. It's opening stock plus purchases minus sales, so it can't drift from the bills.
01_schema.sql), loaded with \copy.The build stops if any of these fail. A dashboard is only as good as the bills under it.