The example below gives every input and formula needed to check two variants across a main warehouse and a shop. It tracks operating quantities and a simple constant-cost valuation. It is not a replacement for your inventory subledger or an accounting policy for changing purchase costs.
1. Set up the apparel inventory tracking spreadsheet
Create five tabs with these exact names: SKU Master, Stock Movements, Inventory Summary, Assumptions, and Dashboard. The formulas use commas as argument separators. If your Sheets locale uses semicolons, adapt the separators.
In Assumptions, enter these labels and formulas:
| Cell | Label or value |
|---|---|
| A2 | Reporting start |
| B2 | =DATE(2026,10,1) |
| A3 | Reporting end |
| B3 | =DATE(2026,10,7) |
In SKU Master!A1:D3, enter:
| A: SKU | B: Description | C: Reference unit cost | D: Reorder point |
|---|---|---|---|
| TEE-RED-M | Red tee, medium | 12 | 10 |
| TEE-RED-L | Red tee, large | 12 | 10 |
Enter costs as numbers, not text containing a currency symbol. Both variants have a $12 reference cost in this illustrative example. Do not add an opening-units field here. SKU Master defines the variant; Stock Movements records its quantity.
Use SKU codes consistently in every tab. A red medium tee and a red large tee are different SKUs even if their descriptions are similar. If the same SKU appears twice in SKU Master, resolve the duplicate before using lookup formulas.
2. Record opening counts and movements once
Put these headers in Stock Movements!A1:G1: Date, SKU, Location, Movement type, Units in, Units out, and Net units.
Enter the following rows. Dates must be actual Sheets dates, such as =DATE(2026,10,1), rather than text strings. Use zero for an unused units field.
| Row | A: Date | B: SKU | C: Location | D: Movement type | E: Units in | F: Units out |
|---|---|---|---|---|---|---|
| 2 | 2026-10-01 | TEE-RED-M | Main | Opening | 10 | 0 |
| 3 | 2026-10-01 | TEE-RED-L | Main | Opening | 20 | 0 |
| 4 | 2026-10-02 | TEE-RED-M | Main | Receipt | 5 | 0 |
| 5 | 2026-10-03 | TEE-RED-M | Main | Sale | 0 | 4 |
| 6 | 2026-10-04 | TEE-RED-M | Main | Write-off | 0 | 1 |
| 7 | 2026-10-05 | TEE-RED-M | Main | Customer return | 1 | 0 |
| 8 | 2026-10-06 | TEE-RED-M | Main | Transfer out | 0 | 2 |
| 9 | 2026-10-06 | TEE-RED-M | Shop | Transfer in | 2 | 0 |
In Stock Movements!G2, enter =E2-F2 and fill down to G9. Net units for rows 2 through 9 must be 10, 20, 5, -4, -1, 1, -2, 2.
A transfer has two rows: an outgoing movement at one location and an equal incoming movement at the other. It changes location stock without changing company stock. A customer return adds stock only if the item is physically returned and resalable. Record damaged returns separately under your operating policy.
Use Google Sheets data validation to restrict movement types and locations to your approved labels. Append movements instead of overwriting calculated stock. Correct a posted mistake with a dated reversing or adjustment entry that your team can trace.
3. Calculate stock by SKU and location
In Inventory Summary!A1:F1, enter SKU, Location, Stock on hand, Reference unit cost, Reference stock value, and Reorder flag. Enter these pairs in A2:B4:
| Row | A: SKU | B: Location |
|---|---|---|
| 2 | TEE-RED-M | Main |
| 3 | TEE-RED-M | Shop |
| 4 | TEE-RED-L | Main |
In C2, calculate cumulative movements through the reporting end:
=SUMIFS('Stock Movements'!$G$2:$G$1000,'Stock Movements'!$B$2:$B$1000,A2,'Stock Movements'!$C$2:$C$1000,B2,'Stock Movements'!$A$2:$A$1000,"<="&Assumptions!$B$3)
The lower date bound is deliberately absent. Stock is a balance, so the formula includes the opening count and all movements through the end date, including history before a later reporting start. In a new real workbook, enter verified opening counts as dated Opening rows before adding subsequent movements.
In D2, look up the reference cost:
=VLOOKUP(A2,'SKU Master'!$A$2:$D$3,3,FALSE)
In E2, enter =C2*D2. In F2, compare stock with the variant's reorder point:
=IF(C2<0,"CHECK NEGATIVE STOCK",IF(C2<=VLOOKUP(A2,'SKU Master'!$A$2:$D$3,4,FALSE),"REORDER","OK"))
Fill C2:F2 down through row 4. Expected results are:
| SKU and location | Stock on hand | Reference value | Flag |
|---|---|---|---|
| TEE-RED-M, Main | 9 | $108 | REORDER |
| TEE-RED-M, Shop | 2 | $24 | REORDER |
| TEE-RED-L, Main | 20 | $240 | OK |
The reorder flag is a review signal, not a purchase order. For the red medium tee, combine location stock before deciding whether to buy: a shop transfer may solve a location shortage without purchasing more units.
Google's SUMIFS documentation defines the first range as the values to sum and the following range/criterion pairs as filters. Keep every range the same size and check that the date criterion points to the date column.
4. Measure sales without counting write-offs as sell-through
On Dashboard, enter TEE-RED-M in B2. The example uses company-wide gross unit sell-through during the reporting period: units sold divided by opening stock plus receipts. Customer returns are tracked separately; this gross measure does not subtract them from sales. This definition assumes no other external stock additions during the period.
Put Gross sales units in A3 and this formula in B3:
=SUMIFS('Stock Movements'!$F$2:$F$1000,'Stock Movements'!$B$2:$B$1000,B2,'Stock Movements'!$D$2:$D$1000,"Sale",'Stock Movements'!$A$2:$A$1000,">="&Assumptions!$B$2,'Stock Movements'!$A$2:$A$1000,"<="&Assumptions!$B$3)
B3 must return 4. Summing all units out would return 7 and incorrectly count the write-off and warehouse transfer as sales.
Put Opening stock before period movements in A4 and this formula in B4:
=SUMIFS('Stock Movements'!$G$2:$G$1000,'Stock Movements'!$B$2:$B$1000,B2,'Stock Movements'!$A$2:$A$1000,"<"&Assumptions!$B$2)+SUMIFS('Stock Movements'!$E$2:$E$1000,'Stock Movements'!$B$2:$B$1000,B2,'Stock Movements'!$D$2:$D$1000,"Opening",'Stock Movements'!$A$2:$A$1000,Assumptions!$B$2)
B4 returns 10. The first term carries prior history forward. The second includes the start-of-day opening count when the initial workbook begins on the reporting start. Do not post another opening count at each reporting period or you will duplicate stock.
Put Period receipts in A5 and this formula in B5:
=SUMIFS('Stock Movements'!$E$2:$E$1000,'Stock Movements'!$B$2:$B$1000,B2,'Stock Movements'!$D$2:$D$1000,"Receipt",'Stock Movements'!$A$2:$A$1000,">="&Assumptions!$B$2,'Stock Movements'!$A$2:$A$1000,"<="&Assumptions!$B$3)
B5 returns 5. Put Gross unit sell-through in A6 and =IFERROR(B3/(B4+B5),0) in B6. Format B6 as a percentage. The result is 26.67%, or 4 divided by 15. If no stock was available, the formula displays zero; investigate that situation before interpreting it as performance.
5. Check quantities and the valuation boundary
For TEE-RED-M across both locations, the quantity bridge is:
10 opening + 5 receipts + 1 resalable return - 4 sales - 1 write-off = 11 closing units.
The paired transfer nets to zero across the company. =SUM('Inventory Summary'!C2:C3) must therefore return 11, while =SUM('Inventory Summary'!C2:C4) must return 31 for both variants. Compare these balances with a physical count and investigate differences before using them in a forecast.
This sample assumes every TEE-RED-M unit, including the opening stock, receipt, resalable return, sale, and write-off, has the same $12 cost. Under that explicit assumption, the value bridge is:
$120 opening + $60 receipts + $12 returned cost - $48 cost of sales - $12 write-off = $132 closing value.
The independent summary calculation is 11 units multiplied by $12, also $132. Transfers carry the same cost between locations and do not change the total. These are illustrative checks, not claimed results from a customer's workbook.
If purchase costs change, a current SKU reference cost cannot establish accounting inventory value or COGS. Keep receipt-level costs and use your approved valuation method in an inventory subledger. Reconcile its verified closing balance to the general ledger before treating the stock estimate as a finance reporting balance. Do not apply a report-end average cost to historical opening movements.
Keep the workbook reviewable
Protect formula columns and use a weekly physical-count check. Review negative balances, duplicate SKU-location summary rows, unpaired transfers, unknown SKUs, and future-dated entries beyond the reporting end. Historical ledger rows before the reporting start remain valid and are needed for stock balances. Apply the start-date filter only to period metrics such as receipts and gross sales. Increase the 1,000-row formula bounds consistently before the ledger grows beyond them. Keep the SKU Master lookup range synchronized when adding variants.
For related spreadsheet work, see the Google Sheets formula guide and chart-source setup.
ModelMonkey can help read and search the ledger, analyze its tables, and organize or format the spreadsheet. Select the movement cells in Google Sheets, open the ModelMonkey sidebar, and ask it to identify duplicate SKUs, missing locations, or negative balances. When the workflow presents a preview, review proposed changes before proceeding. Check the formula outputs and control totals yourself after edits.