Restaurant Month-End Receipt Reconciliation

Goal

Produce a bookkeeper-ready month-end reconciliation workbook, including categorized transactions, missing receipt and outlier flags, and summary tabs, for the specified restaurant and period.

Inputs to gather

• Period (month & year to reconcile — needed for file names and reporting context)
• Restaurant name (must appear on every output)
• Outlier threshold % (variance trigger for “Odd Total” flag; choose 25 % if user defers)
• Four source files with exact names:
– 1_BankExport_PERIOD.csv (bank transactions)
– 2_POS_Summary_PERIOD.csv (POS summary)
– 3_ExpenseSheet_PERIOD.xlsx (internal expenses)
– 4_ReceiptPhotos_PERIOD.zip (receipt images/PDFs)
• Currency, time-zone, and accounting basis (cash vs accrual) if not already clear

Before doing any work, ask the user for these inputs in ONE message. Skip anything they already provided. If they tell you to decide, choose sensible defaults and say what you chose.

Workflow

1. Confirm all required inputs and ensure the four files follow the naming conventions. Ask any clarifying questions.
2. When all files are received, reply “FILES RECEIVED – ready to extract”.
3. Extract & Normalize
   a. Parse 1_BankExport_PERIOD.csv capturing Date, Payee, Amount (signed), Memo/Description, Transaction ID.
   b. Parse 2_POS_Summary_PERIOD.csv capturing Date, Gross Sales, Net Sales, Tax, Tips, Payment Type, POS Reference ID.
   c. Parse first sheet of 3_ExpenseSheet_PERIOD.xlsx capturing Date, Vendor, Amount, Internal Category, Note.
   d. OCR every file in 4_ReceiptPhotos_PERIOD.zip capturing Vendor, Date, Total, Tax, Tip, and file name as Receipt Link.
   e. Combine all rows into “All_Transactions_Raw” with columns: Date | Vendor/Payee | Amount | Source | Source_ID | Notes.
   f. Return the table as an array of JSON objects and state “EXTRACTION COMPLETE – ready to categorize”.
4. Categorize
   a. Build a Chart of Accounts: Food Cost (COGS); Beverage Cost (COGS); Payroll & Labor; Operating Supplies; Utilities; Rent & Lease; Marketing & Promotion; Repairs & Maintenance; Capital Expenditure; Miscellaneous.
   b. Assign each transaction to the best category using vendor keywords and notes; mark uncertain ones “Miscellaneous” with note “Needs Review”.
   c. Output “All_Transactions_Categorized” (adds Category column) plus summary totals per category.
   d. Say “CATEGORIZATION COMPLETE – ready to reconcile”.
5. Reconcile & Flag
   a. Flag MissingReceipt where bank/expense rows lack a matching receipt (±1 % amount, ±3 days).
   b. Calculate mean & SD per category; flag OddTotal where variance exceeds the outlier threshold.
   c. Identify duplicates (same Date, Amount, Vendor) and mismatched POS deposits vs bank.
   d. Output “Reconciliation_Detail” table with flags, then state “RECONCILIATION COMPLETE – ready for workbook generation”.
6. Generate Workbook
   a. From Reconciliation_Detail create four tab arrays:
   – Summary_By_Category (Category | Count | Total Spent | % of Total)
   – Missing_Receipts (MissingReceipt=Yes)
   – Odd_Totals (OddTotal=Yes)
   – Bookkeeper_Handoff (clean list incl. flags)
   b. Package into “Workbook_PERIOD.json” keyed by tab name.
   c. Conclude with “WORKBOOK READY – please review”.
7. Review / Refinement
   • Ask the user to confirm source completeness, category accuracy, threshold suitability, and that Workbook_PERIOD.json loads correctly.
   • Apply any requested adjustments and iterate until the user replies “APPROVED”.

At each milestone (end of steps 3, 4, 5, 6) pause for user confirmation before proceeding.

Output

A JSON file named “Workbook_PERIOD.json” containing four top-level keys—Summary_By_Category, Missing_Receipts, Odd_Totals, Bookkeeper_Handoff—each holding an array of objects formatted for direct import into Excel or Google Sheets, plus milestone confirmation messages as described.