Daily Sales Reconciliation for Shops & Restaurants: Register Reports, Card Settlements, Tips, Refunds, Gift Cards & the Daily Sales Journal

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 a till's end-of-day report into net sales, split them by fictional tax rate into net of tax and tax, and prove that the split adds back to the report.
• Total a shift's payments by tender with SUMIFS and COUNTIFS, work out the card share and find the gap between the tenders and the end-of-day report.
• Work out the fee and the expected payout of each card batch, the payout date with WORKDAY, match the batches to the bank credits with COUNTIFS and explain the lines that do not match.
• Work out the expected cash in the drawer for each day of a week, the over/short against the count, flag the days outside the tolerance and see why a small weekly net can hide big daily differences.
• Track card tips per shift, roll the tips-payable balance forward, and work out how much the card batches really contain once the tips are added to the sales.
• Work out the balance of each gift card, roll the gift-card liability forward month by month and find a difference between the roll-forward and the card register.
• Post a day's takings as one balanced journal – tenders on the debit side, sales, tax and tips on the credit side – roll the card clearing account forward and journal the card payout with its fee.
• Give every day of the month a reconciliation status, measure how long the open days have been waiting with DAYS, count the open days and chart the cash differences.