Excel Inventory Template vs Inventory Software: Which Should You Use?
A free Excel inventory template for Nigerian shops, formulas in plain words, its honest limits, and when to move to software so stock stops going missing.
By Ajinex Team · · 9 min read
In this article
- A simple inventory template you can build today
- The formulas in plain words
- Where the template works well
- The honest limits of a spreadsheet
- Signs you have outgrown your inventory template
- Spreadsheet vs inventory software: side by side
- How to migrate from a spreadsheet to inventory software
- Frequently asked questions
- Next step
Almost every Nigerian shop that tracks stock at all starts with an Excel inventory template. One sheet, a row for each product, and the owner updates it on Sunday evening. It is free, it is familiar, and for a small provision store or a one-person boutique it genuinely works.
The problem is that nobody tells you where it stops working, so you only find out when ₦400,000 worth of stock cannot be explained and the sheet says everything is fine.
This article gives you a usable inventory template for Excel or Google Sheets, explains the formulas in plain words, and then tells you honestly where it breaks and how to move on when it does.
A simple inventory template you can build today
You need three sheets in one workbook. Most people try to do everything on one sheet, which is why it turns into a mess by the third month.
- Stock sheet: one row per product, showing what you should have right now.
- Purchases log: one row every time goods come in from a supplier or the market.
- Sales log: one row every time something is sold.
Stock sheet columns
| Column | What goes in it | Example |
|---|---|---|
| Product code | A short unique code you make up | MILK-PEAK-400 |
| Product name | Full name as you call it in the shop | Peak Milk 400g tin |
| Unit | How you sell it | Tin, carton, pack, piece |
| Opening stock | Count at the start of the period | 48 |
| Purchases | Total received this period (from purchases log) | 96 |
| Sales | Total sold this period (from sales log) | 110 |
| Closing stock | Calculated | 34 |
| Cost price | What you paid per unit | ₦1,250 |
| Selling price | What you charge per unit | ₦1,500 |
| Stock value | Calculated | ₦42,500 |
| Reorder level | The quantity where you should restock | 24 |
| Status | Calculated: "Reorder" or "OK" | Reorder |
Sample stock sheet
| Code | Product | Unit | Opening | Purchases | Sales | Closing | Cost | Selling | Stock value | Reorder at | Status |
|---|---|---|---|---|---|---|---|---|---|---|---|
| MILK-PEAK-400 | Peak Milk 400g | Tin | 48 | 96 | 110 | 34 | ₦1,250 | ₦1,500 | ₦42,500 | 24 | OK |
| IND-CHK-70 | Indomie Chicken 70g | Carton | 10 | 20 | 26 | 4 | ₦8,900 | ₦9,800 | ₦35,600 | 6 | Reorder |
| SUG-DAN-1KG | Dangote Sugar 1kg | Pack | 30 | 0 | 22 | 8 | ₦1,400 | ₦1,650 | ₦11,200 | 15 | Reorder |
| OIL-KINGS-1L | Kings Oil 1L | Bottle | 24 | 24 | 19 | 29 | ₦2,100 | ₦2,400 | ₦60,900 | 12 | OK |
Purchases log columns
Date, supplier or market (for example "Mile 12" or "Alaba"), product code, quantity received, cost per unit, total paid, paid by (cash, transfer, POS), and a notes column for things like "5 tins dented".
Sales log columns
Date, product code, quantity sold, selling price, total, payment method (cash, transfer, POS terminal), and who sold it. That last column matters more than you think.
The formulas in plain words
There are only five calculations and all of them are simple arithmetic.
- Closing stock = Opening stock + Purchases - Sales. This is the heart of the sheet. If your physical count does not match this number, something went missing or was never recorded.
- Purchases for a product = sum of all quantities in the purchases log with that product code. In Excel or Sheets this is a SUMIF: add up the quantity column where the code matches.
- Sales for a product = sum of all quantities in the sales log with that product code. Same SUMIF idea, pointed at the sales log.
- Stock value = Closing stock × Cost price. Use cost, not selling price. Stock value at selling price tells you what you hope to earn, not what you have tied up.
- Status = if Closing stock is less than or equal to Reorder level, show "Reorder", otherwise show "OK". A simple IF formula. Set a reorder level for every product; the method is explained in our guide to reorder levels and low-stock alerts.
At the end of each month, copy closing stock into the opening stock column, clear the purchases and sales totals, and start a fresh period. Keep the old logs.
Two habits keep the sheet honest. First, count physically at least once a month and write the count next to closing stock; the difference is your shrinkage (our guide on how to do stock taking in a shop covers the count itself). Second, never type directly into the closing stock column. The moment someone overwrites it "to correct it", the sheet stops being evidence of anything.
Where the template works well
Be fair to the spreadsheet. It is a good fit when one person, usually the owner, does all the selling and all the recording; when you have fewer than roughly 150 products; when you make perhaps 10 to 40 sales a day so writing each one down is realistic; and when you have one location. A small boutique, a phone accessories kiosk or a home-based bakery can run comfortably on this for a year or two.
The honest limits of a spreadsheet
Only one person can really use it
Excel on one laptop is one user. Google Sheets allows several people to edit, but it has no concept of who is allowed to do what. Your cashier can see your cost prices, change a formula by accident, or delete a row. There is no cashier role, just "everyone or nobody".
It is not a POS
A spreadsheet does not sell anything. It records that you sold something, after the fact, if someone remembers. At the counter, with a customer waiting and NEPA just taking light, nobody scrolls to the right row. They sell, and promise to write it down later. "Later" is where stock disappears.
A proper POS system reverses this: the sale is recorded because that is how the receipt is produced. The stock update is a side effect of selling, not an extra chore.
No receipts
Customers increasingly want a receipt, especially for electronics, pharmacy items and anything over ₦20,000. A spreadsheet cannot print one or send one on WhatsApp. You end up with a receipt booklet on the side, and now you have two records that never quite agree.
Errors creep in silently
A typo turns 12 into 120. A row gets sorted out of alignment. A formula gets dragged over the wrong range. Nobody notices until the monthly count.
It cannot catch theft
This is the big one. A spreadsheet only knows what someone chooses to type into it. If a staff member sells five tins and records three, the sheet shows three. There is no activity log, no timestamp per sale, no record of who was logged in. When stock goes missing you have a number, not a story. Our article on how to prevent staff theft in your shop explains why per-staff records matter so much.
No credit tracking, no accounting
Customers who buy on credit end up in a separate notebook. VAT, expenses and profit all live somewhere else. You spend hours every month stitching together numbers that software would simply have.
Signs you have outgrown your inventory template
If three or more of these sound familiar, the template is costing you more than software would:
- Your physical count and your sheet disagree every single month, and the gap is growing.
- You have hired a second salesperson and cannot say who sold what.
- You have opened a second shop and are emailing spreadsheets back and forth.
- Customers ask for receipts and you are writing them by hand.
- You cannot answer "what did I make last week" without an evening of work.
- Customer debts are tracked in a notebook or in your head.
- You have accidentally deleted or overwritten the sheet at least once.
Spreadsheet vs inventory software: side by side
| Excel or Google Sheets | Inventory software such as Ajinex | |
|---|---|---|
| Cost | Free or near-free | From ₦3,000/month |
| Records a sale | Manually, after the fact | Automatically when the receipt is issued |
| Receipts | None | Thermal print and digital receipts |
| Multiple staff | Shared file, no roles | Owner, manager, cashier with permissions |
| Who sold what | Only if typed in | Activity log per staff member |
| Multiple branches | Separate files | Stock per branch, transfers, one dashboard |
| Works without internet | Yes | Desktop app works offline and syncs later |
| Low-stock alerts | A formula you maintain | Built in |
| Barcode scanning | No | Yes |
| Credit sales and debtors | Separate notebook | Built in with limits and part payments |
| Accounting and VAT | Separate work | Posted automatically |
| Risk of silent errors | High | Low; calculations cannot be overwritten |
We go deeper on this in our Ajinex vs Excel comparison.
How to migrate from a spreadsheet to inventory software
Moving is less painful than people fear, provided you do it in this order.
- Clean the product list first. Remove duplicates ("Peak Milk" and "peak milk 400g" as two rows), fix units, and make sure every product has a cost and a selling price. This is the most valuable hour of the whole migration.
- Do a fresh physical count. Do not carry over your spreadsheet's closing stock. Enter what is actually on the shelf.
- Load the cleaned product list into the software with product name, code, unit, cost, selling price and opening quantity.
- Pick a clean start date, a Monday or the first of the month. Everything before stays in the spreadsheet as history. Everything after goes through the software.
- Run both for one week only, compare, fix any product mapping issues, then stop the spreadsheet. Running both for months is how people never finish migrating.
- Train staff on the till, not the dashboard. Cashiers need to know how to sell, take a part payment and print a receipt. That is a 20-minute lesson.
If you want to try before committing, the Free inventory software in Nigeria: what you get with Ajinex gives you 30 days with every feature and no card required, which is enough time to set up, count and run a full month.
Frequently asked questions
Is there a free Excel inventory template for Nigerian shops?
You do not need to download one. The layout above, three sheets with the columns listed, is the whole template. Build it yourself in 30 minutes and you will understand every formula in it, which matters far more than a pretty download with formulas you cannot explain.
Can Google Sheets work as inventory software?
For a single-location shop with one person recording everything, yes, and it has the advantage of being accessible from your phone. It still has no POS, no receipts, no staff permissions and no activity log, so the limits described above apply just as much as they do to Excel.
How many products can a spreadsheet handle?
Technically tens of thousands of rows. Practically, once you pass roughly 150 to 200 products, finding the right row at the counter takes longer than the sale, and the chance of typing into the wrong row goes up.
Will I lose my spreadsheet data if I move to software?
No. Your spreadsheet stays as your history. You enter the product list and a fresh count into the new system and keep the old file for reference. Nothing is destroyed.
Can inventory software work when there is no light or internet?
Ajinex's desktop app is offline-first: it keeps recording sales, stock movements and receipts on a laptop running on battery or generator, then syncs when the connection comes back.
Is inventory software worth ₦3,000 a month for a small shop?
Work it out from your own numbers. If your monthly count shows even ₦10,000 of unexplained missing stock, or you spend three evenings a month reconciling sheets, the software has already paid for itself. If your count matches every month and you are the only person selling, stay on the spreadsheet.
Next step
A spreadsheet is the right first tool. When you start recognising the signs above, Ajinex is built as the next one: inventory management, POS and accounting in one system, priced in Naira, working offline when NEPA takes light. Start your 30-day free trial, add your products, and run your first clean month with no formulas to maintain.
Ajinex Team
The people who build Ajinex, writing from what we learn supporting supermarkets, pharmacies, restaurants and wholesalers across Nigeria. Questions? Talk to us.