The lottery inventory spreadsheet, column by column
Nine columns for every scratch-off book in the bin, the daily count that feeds them, and the reconciliation that tells you whether the lottery cash is all there.
A lottery inventory spreadsheet has one row per scratch-off book, with the game, book number, ticket price, status, yesterday's ticket number, today's ticket number, tickets sold and the dollar sales. Added up, it gives the day's instant lottery sales from the bins, which you then check against the register.
In short
- Track every book from the day it arrives, not just the ones in the bin.
- Count at the same time every day and write the next ticket, not the last one sold.
- Reconcile bins against the register daily, because a gap is easiest to explain the next morning.
What goes on a lottery inventory spreadsheet
Nine columns are enough. Fill the first four when a pack arrives, so books sitting in the back are on the sheet too. Fill the rest at the daily count.
The rows below are examples. Book sizes depend on the game and on your state lottery, and some states number tickets down instead of up, so check one of your own books before you set up the formula.
Lottery inventory sheet, one day
| Bin | Game | Book | Price | Status | Start | End | Sold | Sales |
|---|---|---|---|---|---|---|---|---|
| 1 | 1452 | 0381204 | $1 | Active | 112 | 141 | 29 | $29 |
| 2 | 1467 | 0092217 | $5 | Active | 018 | 031 | 13 | $65 |
| 3 | 1471 | 0150090 | $10 | Active | 004 | 010 | 6 | $60 |
| 1480 | 0207731 | $20 | Received | |||||
| Total | 48 | $154 |
How to do the daily lottery count
Start is yesterday's End, carried forward. End is the number on the next ticket waiting to be sold. Sold is End minus Start, and Sales is Sold times the ticket price.
Do it at the same time every day, usually at the last shift change, and in bin order so nothing is skipped.
The daily count, in order
Copy yesterday's End into today's Start
Never re-read the Start from the book.
Read the next ticket in each bin
Bin 1 first, in the same order every day.
Mark books that sold out or were activated
A new book starts at its first ticket number.
Add up Sold and Sales
That is the day's instant sales from the bins.
Reconciling the sheet against the register
Daily lottery reconciliation means comparing the bin total with the instant sales the register rang for the same day. If the bins say $154 and the register says $144, ten dollars of tickets left without being rung.
Small gaps are usually a timing problem, such as a count taken mid-shift. A gap that repeats on the same shift is the one to look at.
When the bins and the register disagree
- Check the count was taken at the usual time.
- Look for a book activated but not marked on the sheet.
- Check that cash payouts were rung as payouts, not as sales.
- Find which shift the gap falls in before you ask anyone about it.
From spreadsheet to lottery tracking software
The spreadsheet works until a book goes missing between shifts or a formula gets dragged one row too far. Lottery tracking software keeps the same columns but fills most of them for you.
In Neo Office, books go in by Game and Book number as they arrive, activation sets the next sellable ticket, and the daily count only asks for the next ticket in each open book. Sales, payouts, commission and net due add up beside store sales and Till Settlement.
Count the bins once, on your phone
Neo Office keeps every book by Game and Book number and works out sales, payouts and net due from the daily count. Lottery is on Plus at $49.99 per location per month.