Track your stock: find shortages before they grow

Learn to record stock coming in and going out, investigate differences and use the free MINT Inventory Tracker for your regular counts.

Before we begin

Your stock may be where your business is quietly losing money. Daily sales do not tell you whether every product is accounted for.

If you run a provisions shop, sell clothes through WhatsApp or keep phone accessories for sale, you need to know what arrived, what left and what is still there.

This lesson teaches you to:

• Calculate expected closing stock.
• Compare it with a real count.
• Record damage, returns and giveaways correctly.
• Investigate differences without jumping to conclusions.
• Use a simple spreadsheet for a regular stock check.

Download the free MINT Inventory Tracker (.xlsx)

The workbook includes Start Here, Overview, Products, Stock, Sales, Expenses, History and a worked Example. It supports 300 products and 500 expense entries per week, with a stock check and an estimated trading result. No payment, account or macros are required. Use a laptop or desktop spreadsheet app for this workbook.

Step 1: give every product a clear name and unit

A useful record begins with a specific product, such as Orange drink, 500 ml, rather than simply “drinks.”

Use one row for each distinct product and unit. Different sizes, colours or pack sizes may need separate rows.

Keep the unit consistent. If you buy a crate of 24 bottles but sell individual bottles, enter 24 bottles as stock in, not one crate.

Choose one shop or storage location for the count. If you include a store room and sales counter together, count both and avoid counting internal transfers as new purchases.

Step 2: understand the four basic numbers

With no other movements:

Opening stock + purchases received - units sold = expected closing stock

These are quantities, not cedis. Enter 30 bottles, not the amount paid for those bottles.

Do not add an order that has not arrived. A supplier invoice and goods received may relate to different dates.

Step 3: work through the bottle example

Ama runs a fictional drinks shop in Tema.

First, calculate what was available: 50 + 30 = 80.

Then subtract the units sold: 80 - 60 = 20.

Ama should be able to account for 20 bottles at the closing date if there were no other movements.

A customer taking goods on credit still reduces stock when the goods leave. Record the units sold now and the money owed in a separate customer record. Receiving payment later does not reduce stock again.

Step 4: count what is actually there

Ama physically counts 17 bottles.

The tracker uses:

Difference = physical count - expected closing stock

So:

17 - 20 = -3 bottles

A match does not prove that every underlying record is perfect. Two errors can sometimes cancel each other out.

A shortage is a reason to investigate, not proof of theft.

Step 5: account for movements other than sales

Products can leave through damage, expiry, free samples, owner use or returns to suppliers. Saleable customer returns can bring products back in.

The workbook therefore uses a slightly fuller calculation:

Expected = Opening + Stock in - Sold - Damage - Gifts - Supplier returns

This weekly template is designed for straightforward trading. Customer returns and owner withdrawals require separate records and appropriate cost treatment; do not enter them as new purchases or business gifts just to force the figures to match.

Use a consistent scope: this tracker records stock available for sale. Keep unusable goods separately identified. Do not include a damaged customer return as saleable stock.

What if the missing three bottles were damaged?

Suppose Ama finds a dated record confirming that three broken bottles were removed, but this movement was omitted from the totals.

She records 3 in Damage:

50 + 30 - 60 - 3 = 17 expected

The physical count is still 17, so the variance becomes zero.

The damage has now been explained. The business has still lost three bottles. A zero unexplained variance does not mean there was no loss.

Do not invent damage to force a match. Do not enter the same units under both Sold and Damage.

Step 6: investigate in a sensible order

1. Recount. Look in the store room, display area and any agreed storage locations.
2. Check the product and unit. Could a crate, packet or similar-looking product have been confused?
3. Check the dates. Did a delivery or sale happen before or after the count cut-off?
4. Check incoming goods. Compare recorded quantities with delivery records.
5. Check sales. Look for missing entries or transactions recorded twice.
6. Check other movements. Review damage, expiry, samples, returns and owner use.
7. Document the conclusion. Record the correction and supporting reference, or leave the difference flagged for follow-up.

If customers continue buying while you count, log movements during the count so the records and physical quantity refer to the same point in time.

Preserve the original count and evidence. Never overwrite the expected result or pretend the physical count was something else just to remove a warning.

Step 7: use the free tracker

Begin with the Example sheet

The Example shows 50 opening bottles, 30 received, 60 sold and three confirmed damaged, leaving 17. At GHS 10 cost and GHS 15 selling price, sales are GHS 900, cost of sales is GHS 600 and gross profit is GHS 300. Subtract GHS 30 damage and GHS 70 expenses to get a GHS 200 estimated trading result.

Set up, record, then count

1. Save a copy with your business name and week in the filename. Read Start Here.
2. Set the week on Overview. Add each product on Products, including a unique ID, unit, selling price, opening quantity and unit cost. Enter zero opening quantity if none exists.
3. On Stock, enter units received and their total purchase cost, not the price of one unit. For 10 units at GHS 8 and 20 at GHS 11, enter 30 units and GHS 300. The workbook blends this with opening stock cost.
4. Record weekly quantity sold on Sales. For mixed selling prices, use the total revenue earned in Actual net sales. Include credit sales and track money owed separately.
5. Record damage, gifts and supplier returns on Stock. Record dated running costs on Expenses; do not repeat stock purchases as expenses.
6. Leave Counted blank until you count. Enter zero only when you physically find none.
7. Read the variance and add evidence and follow-up in Notes. Review input warnings, stock health and the estimated result on Overview.

White cells on the entry sheets are editable; green cells calculate. The Overview uses cream input cells. Formulas are protected without a password. Sorting and row insertion are disabled because the sheets share product rows. Add products only in the next unused Products row.

Supporting Stock calculations are grouped and hidden to reduce clutter. Expand them to inspect costing or copy the next opening balances. Keep dated delivery and sales records: this is a weekly totals workbook, not a transaction database.

Step 8: keep a small daily movement record

A notebook can be enough to support the tracker:

These rows illustrate the recording format; they are not the complete weekly bottle example.

Recording money received alone is not enough. A GHS 100 MoMo receipt does not establish how many units were sold unless you can link it to the products and quantities.

Assign responsibility for entering movements and for checking the count. In a one-person business, count at a consistent time and retain the supporting records.

Step 9: start the next period without hiding a problem

Finish all physical counts and fix input warnings. Save the complete dated workbook before clearing anything. It preserves each product's quantities, costs, differences and investigation notes. The optional History summary alone is not a replacement for this archive.

Use Save As to create the next week's file. In that new file, expand Stock's grouped columns P:Y and copy V4:W303 as values into Products G4:H303. This carries the next opening quantity and average unit cost. Follow the precise clearing ranges in Start Here, then set the new week. Carry unresolved investigations into the new Notes while retaining the old file.

After recounting and recording any confirmed corrections, use the verified physical closing quantity as the next opening quantity. If a shortage remains unexplained, preserve that variance and follow-up in the old file rather than erasing it.

For example, if 17 bottles are verified on hand, the next period begins with 17. Carrying 20 forward would keep the next period wrong from the start.

The workbook has 300 prepared product rows. Use another copy for an additional clearly labelled group if needed. Do not assume formulas extend beyond the prepared area.

Practice: can you find the difference?

A fictional accessories seller in Kumasi starts with 40 phone cases, receives 25, sells 50 and gives away two as recorded samples. The physical count is 12.

Calculate expected stock and variance before opening the answer.

Understand the result's limits

The workbook estimates stock values and trading profit using a weekly average cost. It does not calculate tax liability or cash flow, and it does not replace customer-debt, supplier-credit or accounting records. Unresolved shortages are shown separately rather than automatically charged as expenses. The trading result can therefore overstate the final result while losses remain unresolved.

A three-bottle shortage is not automatically a loss equal to three times the selling price. Establish the cause and appropriate cost treatment before deciding its money impact.

Continue with sales versus profit to understand why stock purchases, stock sold and profit are different figures.

Your first stock check

Choose a manageable group of products, establish the counting period and use your records to enter the movements. Count independently, investigate differences and retain the completed file.

Get the free MINT Inventory Tracker

Start with records you can maintain consistently. Their value comes from entering real movements and following up on differences.

Figures as of 12 October 2026; fictional quantity examples.

Share on WhatsApp

Try this