Spreadsheets

Small shop monthly profit sheet in Excel: sales, stock, expenses

A small shop monthly profit sheet for Excel: sales, opening and closing stock, purchases, wages, rent and other costs, giving gross margin and a net figure.

Editorial illustration for Small shop monthly profit sheet in Excel: sales, stock, expenses

A shop's monthly profit sheet needs sales, opening stock, purchases and closing stock to get the cost of goods sold, then wages, rent and other expenses to reach a net figure. The one number people skip is closing stock, which needs a quick count at month end. Without it, a month of heavy buying looks like a loss.

What are the lines of the sheet?

Use column A for labels and column B for the month's figures. Put the month's first date in B1.

RowLabelHow to fill it
2Sales (cash, UPI, credit)Total from the day book; keep the three types on separate lines underneath
3Opening stockClosing stock from last month, at cost price
4PurchasesAll goods bought for resale this month
5Closing stockCount at month end, at cost price
6Cost of goods sold=B3+B4-B5
7Gross profit=B2-B6
8Gross margin=IF(B2=0,0,B7/B2), formatted as a percentage
9 to 13Wages, rent, electricity, transport, otherOne line each
14Total expenses=SUM(B9:B13)
15Net profit=B7-B14

Why does stock sit in the middle of the calculation?

What you sold in a month is not what you bought in that month. If you bought extra for a festival and it is still on the shelf, that stock is an asset, not a cost. The formula opening stock plus purchases minus closing stock isolates the cost of what actually left the shop. Leave out the stock count and your profit swings with your buying pattern, not with your sales.

What does a worked month look like?

These numbers are invented for a small general store.

LineAmount
Sales₹4,20,000
Opening stock₹1,80,000
Purchases₹3,00,000
Closing stock₹1,90,000
Cost of goods sold₹2,90,000
Gross profit₹1,30,000
Wages (two helpers)₹45,000
Rent₹25,000
Electricity₹6,500
Transport₹4,000
Other₹7,500
Total expenses₹88,000
Net profit₹42,000

The cost of goods sold is 1,80,000 + 3,00,000 - 1,90,000 = ₹2,90,000. Gross profit is 4,20,000 - 2,90,000 = ₹1,30,000, a gross margin of 1,30,000 / 4,20,000, roughly 31.0%. Expenses add up to 45,000 + 25,000 + 6,500 + 4,000 + 7,500 = ₹88,000, so the net is 1,30,000 - 88,000 = ₹42,000. Your own owner's drawings are not an expense; keep them on a separate line below the net figure.

How do I do a stock count without closing the shop?

  • Count the high-value or fast-moving categories every month, such as oil, rice, dal, biscuits and soaps, and the rest every quarter.
  • Count in units and multiply by the cost price, not the selling price.
  • Use a clipboard or a simple count sheet by shelf, and have a second person do the totals.
  • Note damaged or expired stock separately, since it is a loss and not an asset.

A rough count done every month beats a perfect one done once a year. If you cannot count at all, use last month's closing stock and say clearly that the margin is approximate.

Which numbers should I watch month to month?

  1. Gross margin: a drop may mean cost prices rose and you have not passed them on, or that discounts and wastage grew.

Rent and wages as a share of sales, which shows how heavily the shop carries fixed costs. Credit sales still unpaid, which you can pick up from your udhaar register. Cash in hand versus UPI, to check that deposits match the sheet.

Where can an app help?

If shop spending goes through a bank account or card, SpendixAI can import the statement, categorise rent, electricity and transport, and let you download a report as CSV to copy figures into this sheet. It is not a billing or stock tool, so sales and stock stay in your day book and sheet.

Month-end routine

  • Close the day book and total sales by type
  • Count the stock and enter closing stock
  • Enter wages, rent and other expenses
  • Read gross margin and net, and compare with last month
SPENDIXAI JOURNAL

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

Explore your reports