Most small retailers still run the shop on three different things at once: a bill book at the counter, a spreadsheet for stock, and a notebook for expenses. Nothing reconciles, stock is a guess, and nobody knows the month’s real profit until the accountant says so.
The Retail Store Management System V1.0 in Excel VBA puts all of it in one macro-enabled Excel file. You bill from a proper invoice form, record purchases, keep products, customers and suppliers in real masters, log expenses, and read five dashboards plus a Profit & Loss statement that are built from the same data you just entered.
Because it runs inside Microsoft Excel, there is nothing to install, no user licence to renew and no internet connection required. Enable macros, open the MAIN screen and start working.
🎥 Watch the Demo Video
The 12-minute walkthrough shows the system exactly as you will use it: the MAIN screen with its live KPIs, a real multi-line sale invoice built with the type-to-search product picker, saved and printed in one go, then the Sales Ledger, Customer Master, purchase bills, Product Master and the expense forms. It goes on to the five report pages — Sales, Purchase, Stock & Inventory and Expense dashboards and the Profit & Loss statement — and finishes with the setup you do once: the Category Master and dropdown lists, the Print Centre previewing a tax invoice on A4 and a receipt on a 3 inch roll, the save-as-PDF option, and the Settings sheet where you enter your company details and switch the currency and tax rate.
🚀 Key Features of the Retail Store Management System V1.0
✅ Menu-driven MAIN screen – Sales, Purchase, Products, Parties, Expenses, Dashboards, Accounts and Print & Setup, each with its own colour-coded card.
✅ Live KPIs on the home page – net sales, number of invoices, gross profit, expenses and the count of low / out-of-stock products.
✅ Multi-line sale invoice form – pick a customer, store branch and cashier, then add as many products as the bill needs; totals, discount and tax are worked out for you.
✅ Multi-line purchase bill form – the same basket flow for supplier bills, with cost price and quantity feeding stock automatically.
✅ Type-to-search dropdowns – start typing a product, customer or supplier name and the list filters as you type (substring match, not just the first letters).
✅ Themed calendar date picker – every date box is locked to the calendar button, so a date can never be typed in the wrong format.
✅ Add, Update, Delete and Reset on every form – with confirmation before anything is removed.
✅ Print Centre – Tax Invoice, Payment Receipt and Purchase Order, printed, previewed or saved as PDF.
✅ Three paper sizes – A4, 4 inch (104 mm) and 3 inch (80 mm) thermal, switched from the Settings sheet.
✅ Five analysis pages – Sales, Purchase, Stock & Inventory and Expense dashboards, all driven by pivot charts and slicers.
✅ Formula-driven Profit & Loss – 12 months plus a total column, with a Financial Year dropdown that runs to 2050.
✅ Your currency, your tax – set the currency code, symbol and VAT / tax rate on the Settings sheet and every sheet recalculates.
✅ Manage Dropdown Lists – add or remove a branch, category, brand, unit, cashier, city or expense head and every form follows.
✅ Document numbering – your own invoice and receipt prefixes.
📊 Sales Dashboard
The Sales Dashboard answers the questions a shop owner actually asks: what did we sell, what did we keep, and how much of it has been collected. In the sample data it reports $93,594 net sales, 18,329 units sold, $16,638 profit, $5,512 discount given and $73,208 collected.
Below the KPI row you get net sales by month, share of net sales by product category, and net sales against gross profit for every store branch. Slicers on the right filter the whole page by Store Branch, Product Category and Payment Status.
🛒 Purchase Dashboard
Track what the shop is buying and from whom: purchase value by month, share of purchases by supplier, and the top products by units purchased, with paid and pending values on the KPI row.
📦 Stock & Inventory Dashboard
Closing stock value, units in hand, products listed, units sold and units purchased on one row, then stock value by category, share of stock value by brand, and stock in hand plotted against the reorder level for every product, so a reorder decision takes seconds.
💸 Expense Dashboard
Total expenses, expense net of tax, tax on expenses, voucher count and average voucher, plus expenses by month, by category and by store branch.
📈 Profit & Loss Statement
A proper P&L, not a pivot table: revenue, discounts allowed, cost of sales, gross profit and margin, every operating expense line, net profit and net margin, plus a tax summary — for all twelve months and the year total. Pick the financial year from the dropdown and press Refresh All Reports.
🧾 Sales Ledger and Purchase Register
Every invoice line is kept in the Sales Ledger with the customer, branch, cashier, product, quantity, rate, discount, tax, payment status and month. Select a row and use Edit Invoice, Delete Selected or Print Invoice straight from the action bar. The Purchase Register does the same job for supplier bills.
🗂️ Masters: products, customers and suppliers
The Product Master holds the SKU code, category, brand, unit, supplier, cost price, selling price, opening stock, stock in hand, reorder level and stock status. The Customer Register keeps customer type, contact, email, city, orders, total business, outstanding and loyalty points. The Supplier Register keeps contact person, payment terms, products supplied and purchase value.
🧮 Expense Register
Log every voucher with date, category, description, store branch, amount, tax, payment mode and status — the Expense Dashboard and the P&L both read from it.
⚙️ Category Master and Settings
The Category Master is the single place where store branches, product categories, brands, cashiers and cities live. Manage Dropdown Lists adds or removes a value and every form picks it up immediately.
On the Settings sheet you enter your company details, trade licence and tax registration number, choose the currency code and symbol and the VAT / tax rate, set your invoice and receipt prefixes, switch permissions on or off, and choose the paper size and what the Print button does — Preview, Print or Save as PDF.
📦 What You Get
| File | Retail Store Management System V1.0 in Excel (.xlsm, macro-enabled) |
| Manual | 13-page illustrated PDF user manual, including the printing chapter |
| Dashboards | Sales, Purchase, Stock & Inventory, Expense, Profit & Loss |
| VBA forms | Sale invoice, purchase bill, product, customer, supplier, expense, print |
| Registers | Sales Ledger, Purchase Register, Product Master, Customer Register, Supplier Register, Expense Register |
| Print documents | Tax Invoice, Payment Receipt, Purchase Order — A4, 4 inch, 3 inch |
| Sample data | 900 sales lines, 120 purchase bills, 16 products, 24 customers, 6 suppliers, 180 expense vouchers |
| Currency | USD out of the box, changeable to any currency and tax rate |
| Works with | Microsoft Excel 2016, 2019, 2021 and Microsoft 365 for Windows (macros enabled) |
👥 Who This Is For
- Grocery, mini-mart, stationery, electronics and personal-care shops
- Single-branch retailers and small multi-branch chains
- Store managers who need a real invoice and a real P&L, not another tracker
- Accountants who want the shop’s data in a file they can open anywhere
- Anyone replacing a paid POS subscription with a one-time Excel tool
🧭 How to Use It
- Open the .xlsm file and click Enable Content so the macros can run.
- Go to Print & Setup → Settings & Paper Size and enter your company details, currency, tax rate, prefixes and paper size.
- Use Accounts → Category Master and Manage Dropdown Lists to set your branches, categories, brands, units, cashiers and cities.
- Add your items in Products → Product Master, then your customers and suppliers under Parties.
- Bill from Sales → New Sale Invoice and record supplier bills from Purchase → New Purchase Bill.
- Click Accounts → Refresh All Reports whenever you want the dashboards and the P&L brought up to date.
❓ Frequently Asked Questions
Do I need to know Excel or VBA to use this?
No. Everything runs from buttons and forms on the MAIN screen. You only need basic Excel to open the file and enable macros.
Can I change the currency from USD?
Yes. Set the currency code, symbol and tax rate on the Settings sheet, click Apply Currency & Tax, and every sheet, form and printed document updates.
Will it print on my thermal printer?
Yes. Choose 3 inch (80 mm) or 4 inch (104 mm) on the Settings sheet; the print areas are already sized for those widths, one page per document. A4 is there for a full-page tax invoice.
Can I use it for more than one store branch?
Yes. Add your branches in the Category Master, pick the branch on each invoice, and the dashboards compare branches automatically.
Does it work on Mac or on Excel Online?
It is built for Microsoft Excel on Windows. VBA user forms are not supported in Excel Online and behave differently on Mac, so Windows is recommended.
Can I remove the sample data and start with my own?
Yes. Delete the demo rows from the Data, Purchases, Products, Customers, Suppliers and Expenses sheets, refresh the reports, and the system is yours.
Is it a one-time purchase?
Yes. You download the file once and use it forever. There is no subscription and no activation.
✍️ About the Creator
Built by NextGenTemplates — ready-to-use Excel, Google Sheets and Power BI templates for real businesses. Every system is compile-checked, tested form by form and shipped with an illustrated manual.
Reviews
There are no reviews yet.