Bookkeeping for Online Sellers: Payout Reconciliation, Fees, Refunds, Chargebacks, Reserves & Clearing Accounts

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Turn an order report into the expected payout of every order, total it per payout id with SUMIFS, separate the tax that sits inside each payout and see why a payout is never the same as the sales.
• Look up what the bank actually received for each payout with XLOOKUP, calculate the difference, give every payout a status (Matched, Check or Missing) and measure the days the money was in transit.
• Split a fee log into a month × fee-type grid with two-criteria SUMIFS, total the fees per month, turn them into a fee rate on gross sales and work out the effective fee rate of a single small order.
• Work out the fee that comes back on each full or partial refund under a fictional rule, the net cost of every refund, flag the refunds that land in a later payout than the sale and total the refund cost per payout.
• Total the lost disputes, calculate the win rate of the decided ones, measure what chargebacks cost as a share of sales including every dispute fee, and post a lost chargeback through the clearing account.
• Calculate what a fictional rolling reserve holds back from each sales day, when each amount is released, the reserve balance on any date with SUMIFS on dates, and how much is released in a given month with EOMONTH.
• Run a balance through the payout clearing account line by line, check that each payout brings it back to zero, flag the payout with an unexplained remainder using ABS and a tolerance, and post a complete payout as journal entries.
• Build the month-end bridge from the opening clearing balance through sales, tax, fees, refunds, chargebacks and reserve movements to the closing balance, compare it with the books, give each month a status and chart sales against payouts.