ROVENTAQProducts, sales, and receipts in one place Create free account

How to Track Stock on Hand: Formula, Example and Spreadsheet

If a customer asks whether you have five bottles of shampoo, your stock record should give you a useful answer. But a number in a spreadsheet is not proof that five sellable bottles are actually available. Some may be damaged, reserved for another customer, or missing because a sale was never entered.

Tracking stock on hand means keeping a consistent record of what came in, what went out, and what is still physically present. Tracking available-to-sell stock takes one more step: excluding units that cannot be sold or are already committed.

Stock on hand, sellable stock, and available stock

These terms are related but not identical:

  • Physical stock on hand is the quantity physically present at a specified site, including units that may be damaged or reserved.
  • Sellable stock on hand is the physically present quantity in acceptable condition for sale.
  • Available-to-sell stock is sellable stock minus units committed or reserved for existing customers, if your business tracks reservations.
  • Inventory may be used more broadly for merchandise and other stock owned or accounted for by a business. Goods in transit, consigned goods, and customer-owned items require separate ownership and availability treatment. An item that is physically on a supplier's shelf does not automatically belong in your inventory.

Always label the number you report. “12 on hand” might mean 12 physically present while only eight are free to sell.

The stock-on-hand formula

For a product, calculate a sellable book quantity over a period as:

Ending sellable stock = opening sellable stock + units received − units sold + customer returns restored to saleable stock − units damaged or written off ± other signed adjustments.

A purchase should increase stock when goods are received and recorded according to the system's workflow. A customer payment does not change product quantities. A return increases sellable stock only if the returned units are resalable and explicitly restocked.

Worked example: shampoo bottles

Assume you begin the week with 40 sellable bottles. The following events occur:

EventChange to sellable stockRunning sellable quantity
Opening count+4040
Receive 25 bottles+2565
Sell 30 bottles−3035
Accept 2 returned bottles as resalable+237
Mark 3 bottles as damaged, not for sale−334

Your sellable stock on hand is 34. If five of those bottles are already reserved for other customers, your available-to-sell quantity is 29 (34 − 5).

The three damaged bottles might still be physically in your back room. If so, they remain part of a physical count until disposed of or moved elsewhere, but should not be offered for sale. That is why a physical count, a sellable quantity, and an available quantity can differ.

Build a basic inventory spreadsheet

Start with one row per product/SKU that you track. A good minimum design separates the quantities that increase stock from those that decrease it.

ColumnFieldExample
ASKUSHAMP-500
BProduct name500 mL Shampoo
CUnitBottle
DOpening sellable quantity40
EReceived quantity25
FSold quantity30
GResalable returns2
HDamaged or written off3
IOther signed adjustments0
JEnding sellable quantity34
KReserved sellable quantity5
LAvailable-to-sell quantity29
MReorder point36

With the columns above, the formula for J2 is:

=D2+E2-F2+G2-H2+I2

The formula for L2 is:

=J2-K2

Here I stores a signed adjustment: a negative value reduces stock, and a positive value increases it. Do not subtract a negative adjustment again, or you will add missing units back into inventory by mistake. If L2 is negative, investigate the overcommitment; do not hide it by automatically replacing the result with zero.

Your summary sheet should not be the only record. Keep a separate movement log with date, SKU, event type, quantity, reason, person, and transaction reference. A weekly total is much easier to trust when each change can be traced back to an event.

Count opening stock instead of copying an old estimate

At the beginning of a new system, count the shelf and back room. Separate sellable, damaged, reserved, and customer-owned or consignment units where applicable. Do not trust last month's notebook quantity if the purpose of moving is to correct discrepancies.

For ongoing control, log sales and receipts promptly and perform periodic physical checks. Busy shops may count fast-moving items more often than slow-moving ones. Choose a cycle-count schedule that fits your risk and staffing; a monthly full count may be useful for some shops, but it is not a universal rule.

Record adjustments with a reason

An adjustment is a stock change that does not come from a normal sale or receipt: damage, spoilage, loss, found stock, or a correction after a physical count.

Suppose your system says 50 units but the physical sellable count is 47. Record an adjustment of −3 with the reason COUNT_CORRECTION and investigate the cause. If you later find two sellable units stored in the wrong drawer, use a new adjustment of +2 with a note. Do not quietly overwrite the old number and erase the explanation.

Reason labels such as DAMAGE, LOSS, SPOILAGE, and COUNT_CORRECTION are recommended spreadsheet examples, not a claim that every ROVENTAQ screen requires those exact codes.

Calculate a reorder point that matches your supplier

A reorder point is not necessarily “two weeks of sales.” It should reflect how quickly you sell and how long replenishment takes:

Reorder point = average daily demand × expected replenishment lead time in days + safety stock.

For example, if you normally sell four bottles per day, delivery takes seven days, and you want eight bottles as a safety buffer:

4 × 7 + 8 = 36 bottles.

If your available-to-sell count is 29, you are below the 36-bottle reorder point. Review incoming orders, supplier reliability, seasonal changes, and the size of the next order before buying. Reorder points are planning aids, not guaranteed protection from stockouts.

What a useful stock report shows

A practical weekly report includes SKU, product, sellable stock, committed quantity, available stock, recent units sold, reorder point, expected deliveries, and unresolved differences. Sort by products below their reorder point so staff can act on them. Do not add sales values in different currencies without an explicit and appropriate conversion rule.

Physical and recorded quantities will sometimes differ. A good process makes those differences visible and correctable rather than silently treating the spreadsheet as always right.

When should you move beyond a spreadsheet?

Spreadsheets can work for a small operation that maintains them carefully. They become harder to coordinate when several people edit the same product count, a sale should change stock immediately, customer balances are tracked in another file, or you need reliable roles and transaction history.

ROVENTAQ's inventory and sales features include product quantities, reorder levels, damaged and written-off stock, sales, purchases, and saved receipts. According to its published workflow, saving a purchase increases stock and saving a tracked sale reduces available product quantity. Barcode lookup can fill an open form but does not automatically save the transaction. You still need accurate entry and regular physical counts.

Its Free business plan includes inventory, sales, and purchases for up to 200 products, three signed-in users, and one location. More products or more users may justify Starter; expenses, reports, or stock quantities separated across two locations point toward Professional. Confirm what you actually need before paying.

ROVENTAQ operational reports should not be mistaken for complete tax or accounting records. For US businesses, IRS Publication 334 discusses beginning and ending inventory and cost of goods sold; specific treatment depends on the business and applicable rules.

Start with one product, then scale

Count one SKU, record a sale, receive a supplier delivery, test an adjustment, and reconcile the result with the shelf. Once that simple sequence works, expand the catalog. If you need a wider decision framework, read How to Choose Inventory and Sales Software.

You can create a free ROVENTAQ business account to test the inventory workflow with a small product list.

Create free account

Paid plans start after sign-in. Stripe hosts the card. Refunds follow the published policy. Refunds.