Spreadsheets

Order tracker for boutiques and tailors: advance and balance

A boutique and tailor order tracker for Excel: advance, balance due, delivery date and an overdue flag, plus a month view of pending balances.

Editorial illustration for Order tracker for boutiques and tailors: advance and balance

A tailor or boutique tracker needs one row per order, with the quoted price, the advance taken, the balance due, the delivery date and a flag that turns on when the order is late. Formulas handle the balance and the overdue flag, and a small month view shows how much money is still to collect. It works the same in Excel and Google Sheets.

What columns does an order row need?

ColumnHeadingNotes
AOrder no.A running number, such as B-0231
BCustomerFirst name; keep phone numbers in a separate private tab if needed
CItemBlouse, lehenga, alteration, shirt
DOrder dateReal date
EDelivery datePromised date
FQuoted priceTotal agreed
GAdvanceTaken at the time of order
HBalance receivedCollected later
IBalance due=F2-G2-H2
JStatusCutting, Stitching, Ready, Delivered

How does the overdue flag work?

In column K put:

=IF(AND(J2<>"Delivered", E2<TODAY()), "Overdue", "")

It shows the word Overdue whenever the promised date has passed and the order is not yet delivered. For a gentler warning, make a second flag for orders due in the next three days: =IF(AND(J2<>"Delivered", E2>=TODAY(), E2<=TODAY()+3), "Due soon", ""). Add conditional formatting so the cells turn red or amber and you can scan the sheet in seconds.

What does an order look like in numbers?

Say a blouse stitching is quoted at ₹1,800 and the customer pays ₹600 in advance. The balance due is 1,800 - 600 - 0 = ₹1,200. When she collects it and pays, enter ₹1,200 under Balance received and the due amount goes to zero. These are made-up figures.

How do you see pending balances by month?

On a Summary tab, list the first of each month in column A. For balance due on orders promised in that month and not yet cleared:

=SUMIFS(Orders!$I$2:$I$1000, Orders!$E$2:$E$1000, ">="&A2, Orders!$E$2:$E$1000, "<"&EDATE(A2,1))

Next to it, count the orders still open with =COUNTIFS(Orders!$E$2:$E$1000, ">="&A2, Orders!$E$2:$E$1000, "<"&EDATE(A2,1), Orders!$J$2:$J$1000, "<>Delivered").

MonthOrders promisedBalance due (sample)
October64₹38,400
November52₹27,600
December41₹19,200

How do festival and wedding seasons change the way you use it?

The weeks before Diwali, Eid and wedding season are when promises slip. Sort by delivery date, filter to anything not Delivered, and read the list each morning. Many shops also add a Capacity column that counts orders due on each date, using =COUNTIF($E$2:$E$1000, E2), so they stop accepting new work for a date that is already full.

A routine that works

  • At order time, fill every column up to the advance before the cloth leaves the counter.
  • At the end of each day, update the Status and Balance received columns.
  • On the first of each month, read the pending balance view and decide whom to call.

How do you handle alterations and returns?

Give an alteration its own order number and a price of zero if it is free, so that the work still shows in the status column and the Capacity count. If a customer cancels, change the status to Cancelled and write the refund, if any, in a note. Do not delete the row. A cancelled order that kept its advance, or returned it, is part of your real month and the numbers should say so.

Review cancelled and unclaimed orders every quarter, and decide how long you keep finished garments before you call the customer again.

If you also pay fabric suppliers and rent from your bank account, SpendixAI can import those statements and sort the costs into categories, so you see the shop's spending next to what the tracker says you are still owed.

SPENDIXAI JOURNAL

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

Explore your reports