Business

Stock bought versus stock sold: a simple monthly check for a shop

A simple monthly check of stock bought versus stock sold: opening stock plus purchases minus closing stock, with a grocery shop example.

Editorial illustration for Stock bought versus stock sold: a simple monthly check for a shop

To check whether your shop is overbuying, work out the cost of goods you actually sold in the month: opening stock plus purchase bills minus closing stock. Compare it with the purchases and with sales. If purchases are well above the cost of goods sold, stock is piling up on the shelf. A made-up grocery shop below shows how to do it in about an hour at month end.

  • Cost of goods sold = opening stock + purchases for the month − closing stock
  • Change in stock = closing stock − opening stock
  • Gross profit = sales − cost of goods sold
  • Gross margin percent = gross profit ÷ sales × 100

Opening stock is the value of goods on the shelf at the start of the month, which is last month's closing figure. Purchases are the bills you received for goods in the month. Closing stock is what a count tells you at month end. Value everything at what you paid, not at the selling price.

How does it work for a made-up grocery shop?

Say the shop's opening stock at cost was ₹2,40,000, purchase bills for the month came to ₹3,10,000, and a month-end count gave closing stock of ₹2,70,000. Sales for the month were ₹3,50,000.

LineCalculationAmount
Cost of goods sold2,40,000 + 3,10,000 − 2,70,000₹2,80,000
Change in stock2,70,000 − 2,40,000+₹30,000
Gross profit3,50,000 − 2,80,000₹70,000
Gross margin70,000 ÷ 3,50,00020 percent
Purchases as a share of goods sold3,10,000 ÷ 2,80,000about 111 percent

The shop ordered ₹30,000 more than it sold, so ₹30,000 of cash moved from the account onto the shelf. One month of this is normal, for instance before a festival. If it repeats for three months with flat sales, that is ₹90,000 of cash sitting in goods.

Is the margin what I expected?

The same sums reveal leaks. Suppose you expect a margin of 22 percent from your price list. Expected cost of goods sold is 78 percent of sales: ₹3,50,000 × 0.78 = ₹2,73,000. The shop worked out ₹2,80,000, which is ₹7,000 higher. That is not proof of a problem, but it is a reason to look. Possible causes are listed below.

  • Sales not billed or not recorded, such as small cash sales
  • Goods given away, used at home or sampled
  • Expired, damaged or spoiled goods thrown out
  • Supplier price rises not yet passed on in your selling prices
  • A count error in the opening or closing stock

How do I do the monthly check in a sheet?

Use a column for the figures. In B2 type opening stock, in B3 purchases, in B4 closing stock. Then:

  • B5 (cost of goods sold): =B2+B3-B4
  • B6: type the month's sales
  • B7 (gross profit): =B6-B5
  • B8 (margin percent): =B7/B6*100
  • B9 (change in stock): =B4-B2

Keep one row of results for every month so that you can see the trend across the year. This works in Excel and Google Sheets.

How do I count closing stock in a small shop?

  1. Count the top 30 items by value in full, shelf by shelf. These usually hold a large share of the money.
  2. For the long tail of small items, count a sample and estimate, or count a different group each month.
  3. Multiply each quantity by the last purchase cost, not the selling price.
  4. Use the same method every month, so changes mean something.

Be consistent about GST too. If your purchase bills include GST, decide with your CA whether stock is valued with or without it and use the same basis each month. Rules differ by registration, so confirm with your CA.

What should I do with the answer?

  • Purchases far above goods sold, flat sales: cut or delay next month's orders for the items that grew most.
  • Margin lower than expected: check prices and the leaks listed above.
  • Stock falling month after month: check that you are not running down the shelves and losing sales.

Where a tracker helps

Supplier payments are among your largest bank debits. If you import the statement into SpendixAI and keep a custom category for stock purchases, you can read the month's total quickly, and attach the supplier bill to the transaction as a receipt. Cash purchases are added by hand. The month-end stock count is still yours to do.

Month-end checklist

  • Gather every purchase bill dated in the month.
  • Count closing stock and value it at cost.
  • Work out cost of goods sold and gross margin.
  • Compare purchases with goods sold and note the difference.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports