A box of ten matching items is one purchase, but it can become several sales over several weeks. Recording the whole purchase against every sale overstates your costs. Calling all incoming cash profit understates them. A useful lot record separates the purchase, each sale and what remains.
This guide uses homogeneous units of similar value, US dollars and a simple equal-cost planning model. It is not a mixed-value allocation method, FIFO system or tax guide. All examples below are hypothetical.
Start with one purchase record
Give the purchase a stable ID, such as LOT-001. Record the description, purchase date, units bought, location and all-in cost for the whole lot. Include purchase tax and acquisition transport allocated to this lot once. Do not enter those amounts again as selling costs.
For our example, you bought 10 matching units for $100. Your purchase record contains 10 units and $100, not ten separate $100 purchases. Keep its ID unchanged as sales arrive.
The free reseller lot tracker separates a Purchases sheet, Sales sheet and Summary, plus instructions. Download the blank XLSX for your own records or open the filled workbook beside it to see the fictional examples. No account or upload is needed. These are planning files, not direct-import files for Upcyclist.
Link each sale to exactly one lot
Give every sale its own unique ID and copy the corresponding lot ID into its reference field. A purchase can have many sale rows; a sale row references one purchase only. If an order contains units from several lots, this template cannot represent that bundle as one transaction; use a record system designed to allocate its receipts and selling costs.
In our example:
| Sale | Lot reference | Units sold | Item receipts | Credited shipping | Fees | Actual postage | Other |
|---|---|---|---|---|---|---|---|
| SALE-001 | LOT-001 | 2 | $40 | $0 | $4 | $6 | $0 |
| SALE-003 | LOT-001 | 2 | $40 | $0 | $4 | $6 | $0 |
| Combined | LOT-001 | 4 | $80 | $0 | $8 | $12 | $0 |
Receipts are before seller fees, not the payout after fees. Buyer sales tax is not your revenue. Record shipping only when it is actually credited to you. If you receive shipping money, record that receipt and your actual seller-paid postage once each.
If a buyer-paid label is a pass-through never credited to you, enter shipping receipts 0 and seller-paid postage 0 for that label. Do not deduct it a second time. Enter actual marketplace fees and other direct sale costs, such as packaging. There is no assumed marketplace fee preset in the tracker.
Allocate only the units that sold
For the $100/10-unit lot, every unit has $10 of purchase cost. The first two-unit sale receives $20; the next two-unit sale receives another $20. Together, the four sold units receive $40 of sold cost, leaving $60 attached to six unsold units.
The workbook’s model is equal-lot-usd-v1, the same explicit planning model used by the lot profit and cash recovery calculator. It is not a claim that every Upcyclist in-app lot or recorded sale uses this model.
When equal division leaves a fraction of a cent, allocate the remainder deterministically:
- Convert the whole purchase to integer cents.
- Divide by bought units:
q = floor(purchase cents / units bought). - Let
rbe the remaining cents. - Give the first
runit positionsq + 1cents; all other positions receiveqcents. - A later sale consumes positions after the units already sold, not the first positions again.
For $10 divided across three units, the positions cost $3.34, $3.33 and $3.33. Three successive one-unit sales allocate those amounts in that order. They total exactly $10. Multiplying a rounded $3.33 average by three would lose a cent; repeatedly taking the first $3.34 would overallocate by two cents.
The cumulative cost of k sold positions is q × k + min(k, r). A sale of s units after p previous units receives cumulative(p + s) − cumulative(p).
Sales sheet row order determines position order, not the date or sale ID. Do not sort Sales. Append subsequent sales lower down. Blank prepared rows can be left between entries. You can review the “previous units sold” column to confirm that a later sale starts at the correct position.
Calculate realized profit, not just receipts
Use actual receipts and actual selling costs:
Seller receipts = item receipts + shipping actually credited to you
Selling costs = actual marketplace fees + actual seller postage + other direct sale costs
Net cash recovered = seller receipts − selling costs
Realized profit = net cash recovered − allocated sold purchase cost
For the four sold units:
| Measure | Calculation | Result |
|---|---|---|
| Seller receipts | $80 item receipts + $0 credited shipping | $80 |
| Selling costs | $8 fees + $12 postage + $0 other | $20 |
| Net cash recovered | $80 − $20 | $60 |
| Allocated sold cost | Four positions × $10 | $40 |
| Realized profit before income tax | $60 − $40 | $20 |
| Remaining inventory cost | $100 − $40 | $60 |
The $60 of unsold cost is not deducted from the $20 realized profit. It is still held in inventory. It is also not a promise that the remaining stock will sell for $60, or any other amount.
Keep whole-lot cash recovery separate
Realized profit answers what the sold units earned after their allocated purchase cost. Cash recovery answers how much of the whole original purchase outlay has returned after selling costs.
Our lot has $20 realized profit but only $60 recovered against its $100 purchase. It still has $40 unrecovered outlay:
Unrecovered outlay = max(0, whole purchase cost − net cash recovered)
Cash above outlay = max(0, net cash recovered − whole purchase cost)
This is why “profitable so far” does not mean “the entire lot paid back.” Conversely, cash above the whole outlay is not another name for realized profit.
Recovery is signed. If a sale brings $3 but selling costs are $4, net cash recovered is −$1. A $12 purchase would then have $13 unrecovered, not $12. The tracker retains losses; it does not turn them into zero.
Across several lots, Summary adds each lot’s unrecovered amount and excess separately. A profitable lot’s excess does not silently cancel another lot’s deficit.
Use the workbook checks before trusting totals
Enter only the yellow input columns: Purchases A:F and Sales A:I. Calculated columns are protected with no-password sheet protection; keep that protection enabled. It protects formulas from accidental edits, not the confidentiality of your records.
- Work within rows 2–101, with up to 100 purchases and 100 sales. Do not extend ranges.
- Lot IDs and sale IDs each need to be unique, case-insensitive. Start with a letter, then use only letters, digits, hyphens or underscores, up to 64 characters.
- Dates are real YYYY-MM-DD text, years 1900–9999; a sale cannot predate its purchase.
- Quantities are numeric whole units from 1 to 1,000,000. The combined sales cannot exceed units bought.
- Money is numeric USD from 0 to $1,000,000 with at most two decimal places. Enter explicit 0 when a required amount is genuinely zero. A blank is missing information.
- Do not enter formulas in input cells. Pasting may bypass entry prompts, but the prepared-row checks still flag invalid records.
Review Purchases Q for purchase checks, Purchases R for status, Sales P for sale checks and Summary for overall readiness. Duplicate IDs, missing purchase references, invalid inputs or an oversold lot cannot produce a complete financial summary. Fix an earlier invalid sale before later sales from that lot calculate. Any linked invalid sale suppresses that lot’s financial fields; any issue suppresses all Summary financial totals.
A wholly unsold lot retains its full purchase cost, has zero sold cost and has no completed realized-profit result. Explicit zero acquisition is valid, and a loss remains negative.
The tracker detects duplicate IDs, not whether two different valid IDs represent the same real transaction. Check your source records for accidental duplicate entry. Duplicate checks do not cross workbook files.
Reconcile the filled example
The filled file contains five fictional lots and seven sales:
- LOT-001: the shared 10/$100 example above: $40 sold cost, $20 profit, $60 remaining, $60 recovered and $40 unrecovered.
- LOT-002: $10/3 units, all three sold separately. Costs allocate $3.34 + $3.33 + $3.33; $15 receipts with no selling costs leaves $5 profit.
- LOT-003: two gifted units with zero acquisition cost; one sold. $10 credited receipts minus $3 selling costs leaves $7 profit.
- LOT-004: two units for $12; one sold. $3 receipts minus $4 selling costs minus $6 sold cost leaves −$7 profit, −$1 recovered and $13 unrecovered.
- LOT-005: four units for $8, none sold. All $8 remains in inventory; realized profit is unavailable.
Together, $130 purchase cost = $56 allocated sold cost + $74 remaining cost. $108 seller receipts − $27 selling costs − $56 sold cost = $25 realized profit. Net recovered is $81. Per-lot unrecovered totals $61 and per-lot excess totals $12; their difference, $49, agrees with $130 outlay minus $81 recovered.
Know the file and accounting limits
The XLSX files contain formulas, input checks and protected calculation columns. Recalculate and review checks in your chosen application.
The purchase and sales CSV companions are separate UTF-8 values-only files, not workbook tabs. Blank CSVs contain headers only. Example calculated fields are snapshots: editing a CSV does not update sold cost, profit or recovery. There are no formulas, dropdowns, protection or automatic checks; recalculate and update outputs manually. Empty values mean missing or unavailable, not zero.
The files contain no macros or external connections. Text is literal in XLSX; formula-triggering CSV text is apostrophe-prefixed for safety. No financial inputs are uploaded or collected. Existing consent-aware page-view measurement is separate; downloads and file inputs are not action-tracked.
These files do not support unlike-value lots, mixed-lot bundles, FIFO, returns/refunds, negative quantities or tax valuation advice. Do not represent a return by entering negative units or rewriting sale history. Use a record system and professional guidance appropriate to those cases.
When you need ongoing records instead
For a broader foundation, read how to track reseller inventory and profit. If you track only separate single items, the inventory spreadsheet is a different planning resource, not a substitute for multi-unit allocation.
Upcyclist’s existing app lets you record purchases and sales and review inventory and reports. You can start with the normal signup flow. The tracker does not automatically import into the app, transfer values at signup or sync files to your account. Review the app’s own allocation and recorded-sale rules rather than assuming they are identical to this worksheet.
