← All demos

Marketplace settlement reconciliation

Three systems, 438 orders, one month-end close: the bot pulls orders and payouts from two portals, reconciles them against SAP to the cent, and hands finance the commission entry to post.

ExcelSeller portalPayment gateway portalSAP export
Who
E-commerce finance executive at a brand that sells on a marketplace
When
Every month, first week, before the books close
Why
The marketplace pays net of its commission and fees, weeks after delivery. Without this recon nobody sees the overcharged commission, the orders SAP never billed, or the payouts stuck for 60 days.
By hand
Three downloads, a tracker carried forward month to month, and hundreds of lookups, all by hand.
3systems: marketplace, payment gateway, SAP
438orders tracked across 4 months
120payouts matched to orders
225SAP open items tied out
22exceptions flagged for follow-up
0.00unexplained difference

What it does

  1. Seller centre: logs in, exports every order with activity in September (191 orders)
  2. Payment gateway: logs in, downloads September's payouts (120 lines in 5 weekly batches)
  3. Makes this month's file from last month's closed reconciliation
  4. Carries last month forward: each order's status and payments, and August's accrual
  5. Cleans history: filters closed June orders and deletes them (106 rows)
  6. Loads the month: pastes orders, payouts and the SAP open items (225)
  7. Adds the 160 new orders to the order book; 17 formula columns fill themselves
  8. Reconciles every order into one of 13 categories: settled, short paid, overdue, not billed in SAP...
  9. Copies the 22 exceptions to a follow-up sheet, sorts and formats them
  10. Refreshes the pivot and checks: SAP ties to the cent, every payout matched, postings balance
  11. Lands on the answer: commission expense, the accrual, and three SAP entries ready to post

What the bot hands you

  • Commission expense for SeptemberSGD 1,767.90
  • Accrue at month-end (commission + fees, reverses day 1)SGD 1,373.06
  • Claim back: 3 watch orders charged 15% instead of 8%SGD 38.29
  • Bill in SAP: shipped, never billed3 orders, SGD 826.00
  • Chase: payouts more than 30 days late12 orders, 3 over 60 days
  • SAP balance tied to the order bookSGD 30,718.00, 0.00 unexplained

Screens

Recon Summary after the run: every check OK
Recon Summary after the run: every check OK
The order book: 438 orders, 25 columns, carried forward month to month
The order book: 438 orders, 25 columns, carried forward month to month
The 22 exceptions the bot pulled out for follow-up
The 22 exceptions the bot pulled out for follow-up
Three SAP entries, balanced and ready to post
Three SAP entries, balanced and ready to post
Order value by recon category and month
Order value by recon category and month

Try the demo portals

Pasarly seller centre (demo)TembusuPay merchant portal (demo)

Inside the workbook

  • Order Book Every order for 4 months, carried forward, 25 columns, 13 recon categories
  • Recon Summary Payout check, SAP tie-out, ageing, commission for the month, checks
  • Postings Cash application, credit memos, commission and fee accrual
  • Exceptions The follow-up list: short paid, overdue, not billed, price differences
  • Status Pivot Order value by recon category and month

Sample files

Get the bot

Coming soon.

How to run it

  1. Install Operative and the Guerrilla Bots extension
  2. Operative > Import the .gbw
  3. Download the three sample files into the bot's working folder
  4. The two demo portals accept any email and password
  5. Run, then open Recon Summary: every check should say OK