Synthetic Dataset & Generator for Waste Hauling Company

Category tags: Intuit QuickBooks · Accounting · Python · API Integration · Financial Analysis · Data Management · SQL Budget: Fixed price, milestone-based. Bid the full scope. Timeline: 3–4 weeks from kickoff. What I need A reproducible synthetic dataset for a fictitious solid-waste hauling company, covering 24 consecutive months of operations. This is for internal staff training, reporting/BI sandbox testing, and month-end close practice. No real company, customer, vendor, or employee data will be used or accepted. The scope has two linked layers: Financial layer — a populated QuickBooks company file (GL, AR, AP, payroll, fixed assets). Operational layer — the underlying service, tonnage, container, and route data that the financials are built from. The two must reconcile to each other on shared keys. This is not optional or a nice-to-have — see "Operational layer" below. A GL-only dataset will not be accepted. The other critical requirement: I want a generator, not just a filled-in file. I need to be able to re-run it, extend the period, or change parameters without re-hiring. A hand-keyed file is not an acceptable deliverable. The fictitious company Single US operating entity, one state, accrual basis, calendar fiscal year. Annual revenue target: ~$8M in year 1, growing ~11% in year 2. Service lines and revenue mix (approximate, you can propose adjustments): Commercial front-load — recurring monthly subscription billing Roll-off / temporary — per-haul charge plus per-ton disposal Residential subscription — quarterly prepaid (creates deferred revenue) Ancillary: fuel surcharge, environmental/regulatory fee, container delivery, extra pickups, contamination/overweight charges, late fees Customer base of roughly 150–250 accounts with a realistic concentration curve (top 10 accounts = meaningful share of revenue), plus new-customer adds and churn across the 24 months. Chart of accounts and structure Full COA separating Revenue, COGS/Direct Cost, Opex, Assets, Liabilities, Equity. Direct costs must include: disposal / tipping fees, transfer station charges, fuel, driver wages and overtime, subcontracted hauling, truck maintenance and parts, tires, container repair. Fixed assets: front-load trucks, roll-off trucks, rear-loaders, containers, compactors — each with acquisition date, cost, useful life, and monthly depreciation posted. Liabilities: equipment finance notes with amortizing principal/interest splits, accrued payroll, accrued disposal, deferred revenue. Classes or locations for at least two operating branches/routes, so segment reporting is testable. Realism requirements (this is where most bids fall short) Seasonality — roll-off and construction-driven volume peaks in warm months; Q1 trough. Should be visible in monthly revenue and disposal cost. Cost correlation — disposal cost must move with tonnage/volume, not float independently. Fuel cost should track a drifting synthetic price series. Price actions — annual rate increases applied at customer anniversary dates, not all on 1/1. AR behavior — an aging distribution with a genuine >90-day tail, partial payments, credit memos, customer disputes, unapplied cash, and a small number of bad-debt write-offs. AP behavior — vendor bills, partial payments, a handful of vendor credits. Payroll — drivers, helpers, dispatch, mechanics, sales, admin. Overtime concentrated in peak months. Controlled defects with an answer key. I want a defined set of intentional errors seeded into the data: misclassified expenses, a duplicate vendor bill, a transaction posted to the wrong period, a mis-keyed customer, an unreconciled bank item. These must be documented in a separate defect ledger so I can grade trainees against it. Tell me in your bid how many defects you'd seed and how you'd document them. Operational layer (must reconcile to the GL) QuickBooks alone cannot represent how this industry actually works. Revenue and disposal cost in a hauling business are driven by service events, tonnage, and container logistics — and a large part of my training goal is teaching people that bridge (tons → disposal cost → gross margin per route). So the GL must be generated from an operational dataset, not invented alongside one. Deliver the following as flat files (CSV, or SQLite if you prefer), keyed so they join cleanly to each other and to the QuickBooks records: Table Grain Key fields Route master One row per route route_id, branch, service days, assigned truck Truck / asset register One row per vehicle truck_id, type, route_id, in-service date, ties to GL fixed assets Container register One row per container container_id, size, type, status (on-site / yard / repair), assigned customer Container moves One row per delivery, swap, or pull container_id, customer_id, date, move type Service events One row per lift or pull event_id, date, customer_id, service_location, route_id, truck_id, service type, container size, billable flag Disposal tickets One row per landfill/transfer scale ticket ticket_id, date, truck_id, facility, gross/tare/net tons, rate per ton Fuel log Truck by week truck_id, week, gallons, price per gallon Labor hours Employee by week employee_id, week, regular hours, OT hours, route_id Required reconciliations. Each of these must hold for every month, and you should demonstrate them in the tie-out pack. Tolerance is 1% of the monthly GL balance for the account in question — these do not need to tie to the penny, and I'd rather you not add artificial plug entries to force an exact match. The key mappings in the last bullet must be exact. Billable service events × contracted rate = recurring + per-haul revenue in the GL. Disposal tickets: net tons × facility rate = disposal/tipping expense in the GL. Fuel log: gallons × price = fuel expense in the GL. Labor hours × pay rate (incl. OT premium) = driver and helper wages in the GL. Active container assignments are consistent with active customer count and service events. customer_id in the operationa