# Current tooling analysis — the PlusROI books + PM stack What rian actually runs the PlusROI side of the business with today, extracted from the live artifacts (2026-07-07): the "PlusROI + Bowden Works Bookkeeping / Revenue / Expenses / Invoices" Google Sheet (analyzed in full, incl. formulas) and the Airtable PM base (analysis pending token access — section stubbed below). These are the systems the rebuilt app absorbs in R2–R4; this doc is their spec-by-archaeology. Business context (rian, 2026-07-07): PlusROI = revenue-share partnership with Rob Cooper, worker pool incl. Heather, Summer, Yulia; Adi works for rian but often on PlusROI work — his hours are marked up and charged through as a shared expense. Bowden Works = no partner, mainly Adi + Heather. Summer's agency (which hires rian) is a future tenant; clients and commission partners are future portal-view users, each seeing the system from their own perspective. --- ## 1. The bookkeeping sheet — structure Nineteen sheets, but the real system is five ledger tables + one settlement report + one margin report: | sheet | role | |---|---| | **Clients** (255 rows) | client registry: short/official name, **currency (CAD 137 / USD 56)**, credit limit, default commission + %, invoice timing, terms (net-15/30), contacts, archive flag (⛔) | | **Projects** (~400 real rows) | `{Client} : {Title}` registry: type (Monthly / Fixed Project / Yearly / Hourly / Quarterly / Bi-Monthly), invoice timing, billing frequency (incl. milestone patterns like "50% Deposit Prepaid / 50% Final", "1/3 + 1/3 + 1/3"), **amount + currency**, terms, start date, commission + %, status (Active 225 / Closed 132 / Paused 44), expected-next-invoice tracking | | **Invoices** (2,711 rows) | the receivables ledger — see §2 | | **Expenses** (4,600 rows) | the payables ledger — see §3 | | **Vendors** (100 rows) | payee registry with default expense category + default paid-by. **People and companies are the same list**: Heather (1,160 expense rows!), Brennan Doyle, Yulia, Summer, Taylor Harris, Alim Rawji, Alden Loutit, Kayla Cooper, Emma Slone… alongside Google, Facebook, Moz, Zoom — and **"Bowden Works Team" (806 rows)**, which is Adi's marked-up time charged through (see §3) | | **Monthly Split** | THE settlement report — the BW↔PlusROI monthly split formula (§4) | | **Margins** | per-project P&L for any date window (§5) | | **Std_Rev / Std_Exp / Std_Tra** | a normalized transaction ledger (union journal): Project ID, Client, Project, Transaction Date, Revenue, Transaction Fee, Commission, Net Revenue, Expense, Type, Relevant Period, Ad Spend — rian already normalized his books into journal form; this is the R4 ledger's direct ancestor | | **Process** | written SOP for the billing runs (§6) | | **Sheet22** | rian's own **current-model vs new-model** unit-economics redesign (§7) — effectively his R3/R4 requirements doc | ## 2. Invoices ledger — semantics Columns: Company (`{Client} : {Project}`), Invoice Amount, Status, Invoice ID, Invoice Date, Fee, Relevant Period, Invoice Timing, Type, Description of Work, **Currency**, Terms, **Com.% / Commission**, After Fees, Due Date, Project Amount, Notes, **BW Invoice**. - **Lifecycle:** Pending Approval → Ready to Invoice → Invoiced → Paid (+ Cancelled, Delayed Payment). 2,182 of 2,711 are Paid. - **Currency is real and structural:** 1,346 CAD / 851 USD. The invoice-ID scheme encodes it — numeric ids = CAD (QuickBooks-style seq), `US`-prefixed ids = USD. **Books are kept in CAD**: `Invoice Amount` is the CAD-converted value; `Project Amount` holds the original-currency face; observed implied FX 1.10–1.26 varying per invoice (realized rates, not a fixed rate). - **Fee** = payment-processor transaction fee per invoice (~1.6–3% where present); `After Fees = Amount − Fee` is what the settlement uses. Stripe/online-payment links live in "DC Links". - **Commission is a per-invoice fact** (435 of 2,711 rows carry one), defaulted from client/project Com.% — referral commissions are computed at invoice level, not just annually. - **BW Invoice** (a month date) tags every invoice into the monthly BW↔PlusROI settlement period. This is the grouping key for §4. - **Type** captures the billing pattern per invoice (Monthly 1,725; One-Time; Yearly ±Prepaid; deposit/milestone splits; Quarterly). ## 3. Expenses ledger — semantics Columns: Project, Vendor, Amount, Expense Date, Expense Invoice ID, **Paid By**, Expense Category, Notes, **BW Invoice**, ⛔, Relevant Period, Type. - **Paid By ∈ {PlusROI 3,228, Bowden Works 1,307}** — either partner can front an expense; the settlement nets it out because all expenses reduce the shared pot before the split. - **Categories:** Labour (3,474 — the dominant cost), Client Advertising Spend (437 — pass-through), Overhead, Marketing, General Licenses, Admin, Project Costs, Client Expense, Commission. - **The Adi pass-through confirmed:** vendor "Bowden Works Team" (806 rows, category Labour) is rian invoicing Adi's marked-up hours into PlusROI as a shared expense — exactly the two-hop engagement chain in 02-domain-model §2. The current work app's invoice paste-block is what generates these rows' source data. - Overhead expenses attach to internal projects (`PlusROI : Admin`, `PlusROI : Overhead`) — internal projects are first-class, same as the work app's `PlusROI : Admin` time-tracking convention. ## 4. The monthly settlement (Monthly Split) — the formula Reverse-engineered from the live formulas, labeled by its own row headers. For settlement month M (grouping by the `BW Invoice` tag): ``` Revenue = Σ Invoices.AfterFees where BW Invoice = M (CAD) Total Expenses = Σ Expenses.Amount where BW Invoice = M Bookkeeping = Revenue × 3% ← Danielle's cut Net = Revenue − Total Expenses − Bookkeeping Split = Net / 2 ← the Bowden Works invoice ``` - The 3% is hardcoded in-formula (`=B5*0.03`) — the "gross-percentage" comp of the domain model, applied to after-fee revenue, monthly, before the 50/50. - The report also compares each month against prior month, chosen month, YTD, prior year, and since-inception (per-month averages) — the partner-facing analytics view. - The margin block decomposes: Revenue − Transaction Fees − Commissions − Client Advertising Spend (pass-throughs) − Labour − Other Expenses − Bookkeeping = Profit, with four margin ratios (gross−ad-spend, gross−COGS, gross−COGS&bookkeeping, net). **R4 note:** this settlement is exactly computable from the 02-domain-model foundations — invoices (with fees + commissions), expenses (with paid-by), a gross-percentage term (Danielle), a partnership term (50/50), grouped by settlement month. The "paid by either partner" wrinkle means the settlement must credit the fronting party: BW-paid expenses effectively transfer through the split. ## 5. Margins report Per-project P&L for any date window over the Std_* journal: Project ID → Revenue, Transaction Fee, Commission, Ad Spend, Gross Revenue, Gross Margin, Project Expense, Expense, Net Margin. This is the R2-era "project profitability" view generalized beyond the work app's current income-vs-cost margin. ## 6. The billing process (Process sheet, verbatim SOP) Twice-monthly billing runs (Mid-Month + Month-End), with named roles: - **Closer** (sales): enters client + project, sets billing frequency (deposit/final for one-time), pre-enters the invoice schedule with "Ready to Invoice" for the initial and "Pending" for subsequent. - **Bookkeeper**: copies recurring invoices forward each run, notifies PM. - **PM**: reviews invoices against Podio + internal assignments, removes previous month's one-off exceptions, notifies Sales lead. - **Sales lead**: verifies their accounts are covered. - **Bookkeeper or proxy (RC = Rob)**: diff-checks current run against the same period last month ("nothing slipped through the cracks"), then sends. **R4 implications:** invoice *generation* is schedule-driven (recurring templates + pre-entered milestone schedules), the runs are batch events with a review workflow (draft → reviewed → sent), and the last-month-diff check is a first-class safety feature to build, not spreadsheet folklore. Podio appears here as the task system of record on the PlusROI side (alongside Airtable for planning). ## 7. Sheet22 — rian's own redesign (the R3/R4 requirements doc) A worked "standard client" example, current vs new: **Current model (unit-based):** client billed $1,500/mo → units = billed ÷ $125 (unit base is "often $100") → 12 units. Commission 20% off top (most clients: none); ad-spend pass-through; Net = $1,000. Allocations in units: Admin 10% (notional — "we don't actually pay admin"), Lead 15%, Labour = remainder rounded to nearest 0.5 unit. Workers are PAID $50/unit. Profit = billed − commission − ad − lead − labour = $475 → Split $237.50 each. **New model (his design):** allocations become **straight percentages of Net**: Client Owner 5%, Project Lead 7.5%, Labour 40% — adjustable per project "with consent from partners". Same profit/split outcome on the standard case; far cleaner generalization. **Roles he has already designed (quoting his own notes):** - **Client Owner**: owns the relationship; sets up projects ("in airtable"), picks the Project Lead (partners can veto), upsells, handles retention; "a source of passive income" if all flows; reassignable with rationale; may also be Project Lead if suitable for the work. - **Project Lead**: allocates budget + assigns tasks ("before end of the first week of every month" for recurring), first right of refusal on tasks, strategic support, reviews reports; may take reasonable extra admin tasks. - **Standard tasks**: in the new system "Client Owner Duties" and "Project Lead Duties" become *tasks like any other* with standard descriptions — so invoicing rolls up from tasks uniformly (today workers must combine Podio tasks + lead allocations manually). - **Projects may split by activity type** (same client: PPC project + SEO project; same Client Owner, different Project Leads). **Deviations from standard** (his list): unit base often $100, admin often 0, lead % varies, commission varies (mostly none), ad pass-through is the exception. ## 8. Airtable PM base ("Production") — analyzed 2026-07-07 Eight tables. **This base IS Sheet22's "new model," implemented and running** — the R3 design isn't a proposal to build, it's a live system to port. Two sibling bases exist ("Alessio - Rian", "Project Estimator") — not analyzed, assumed out of scope. ### Structure - **Contacts** (people: Team Member / Client Contact / Partner Contact) and **Companies** (Client / Partner / Vendor / Other, **currency USD|CAD**, default commission earner + %, default client owner + %, default assignment %) — parties, exactly as 02-domain-model models them, with a **three-level defaults cascade: Company → Project → Set** (same specificity pattern as the work app's rate overrides). - **Projects**: **Paying Client ≠ End Client** (two links — the subcontract/prime chain is modeled structurally); default charged amount, client expense (ad pass-through), commission earner + %, client owner + % + duties, project lead + % + duties, assignment (labour) %; derived unit budgets/costs; target vs actual profit + margins; **recurring-set machinery**: Start Day of Month (1/8/15/22), Months per Cycle (1–12), Autocreate Next (N days ahead), "New Set Needed" formula + automation views; Invoice Timing (Month End / Mid Month / Pre-Entered / Year End — matches the bookkeeping sheet's runs). - **Sets** (69 fields — the operational heart): named `YY.MM {name}`, per-set overrides of EVERY percentage (observed: lead 20% on one set, 9% on another, vs 7–8% defaults; per-set commission earner + %), charged/services/net amounts, unit + cost budgets, units assigned/remaining, profit + three margin levels, **reverse computation** (Required Charged Amount from a desired net — pricing helper), planning/resourcing state machine rendered as icon strings (🌱 needs assignments / 🌲 fully / 🌳 perfectly / 🪵 over- assigned; per-set completeness, period, archived), **auto-created Client Owner / Project Lead duty assignments** (checkbox + Airtable automations). - **Assignments** (tasks): set-linked, owner (contact), 11-state status workflow (📝 Pending → 📬 Open → 👌 Accepted → ⚙️ In Progress → 🗹 Complete, plus Standby / Waiting / Blocked / Abandoned / Reference / Cancelled, each with a sort weight in Tags), **units**, amount = units × unit value, due dates, priorities, Recurring? flag, Client-Owner-Task / Project-Lead-Task flags, **Invoice link**. - **Invoices** (worker payables!): a team member's invoice = Pending/Sent/Paid + linked assignments; amount rolls up from assignment amounts (verified: "24.06 Yulia Ekeltchik" = $5,460). Workers bill by attaching their completed assignments — "tasks like any other" invoicing, live. Unattached completed work shows as "Not Assigned to Invoice" (the open-payables queue). - **Tags**: the status/priority/resourcing/period vocabularies with sort values. **Variables**: global config — Default Currency CAD, Default Project Lead 8%, Default Assignment 40%, **Default Client Owner 0%** (the 5% in Sheet22 was illustrative; passive default is zero), default duties checklists, default start day. ### The live unit economics (decoded from records) **The unit is $5** (assignment Amount = Units × 5 verified across records; unit ≈ 6 minutes at $50/hr). Percentages of Net drive unit budgets: e.g. net $1,000 → lead 8% = $80 = 16 units; labour 40% = $400 = 80 units; client owner 3% = $30 = 6 units → set budget 102 units / $510 cost → target profit $490 / 49% margin. So Sheet22's "percentages of net" and "units" are not competing models — **percentages define budgets; units are the working grain** workers are assigned and paid in. Per-set commission live too (observed: 20% to a partner company off a $750 set). ### Podio note **Podio is retired** (rian, 2026-07-07: "podio is what we used to use, we converted to airtable a few years ago"). The Process SOP's "review against Podio" step is stale text — Airtable Assignments is the system of record. R3's migration has exactly two sources: the bookkeeping sheet (money) and the Airtable base (work); nothing imports from Podio. ## 9. What this changes in the rebuild plan Folded into the core docs (see also judgment calls #26–#29): 1. **The R4 money engine now has a real spec** — §4's settlement formula, §2's invoice semantics (fees, per-invoice commission, realized FX), §3's paid-by-either-partner netting, and §7's pct-of-net allocation model are the requirements. The engagement/terms foundations in 02 remain the right substrate: Danielle = gross_percentage(3%, monthly, after-fees); Rob↔rian = partnership 50/50; Client Owner / Project Lead = per-project engagement roles with percentage compensation terms; workers = per_unit ($50 global unit value) transitioning to pct-of-net labour budgets. 2. **Books-currency confirmed:** PlusROI's books are CAD with realized-rate conversion of USD receipts at invoice level, and the original face value retained. The rebuild's per-row currency + R4 FX design matches observed practice; migration stamping gets its answer for this dataset (invoice rows: CAD after conversion + original amount preserved — both survive). 3. **Invoice model (R4 target) gains:** transaction fee + after-fees amount, per-invoice commission (% + amount), lifecycle states (Pending Approval / Ready to Invoice / Invoiced / Paid / Cancelled / Delayed Payment), recurring generation from project billing frequency + milestone schedules, settlement-month tagging, and the twice-monthly batch run with the last-month diff check. 4. **Client/vendor registries** map onto parties exactly (defaults: currency, terms, commission, category, paid-by — all engagement/party attributes in 02). 5. **R3 task model requirement:** Client Owner Duties / Project Lead Duties as standard tasks; project-splitting by activity type; monthly allocation deadline; first-right-of-refusal assignment. 6. **Rollout intent (rian, 2026-07-07):** current work app first → fold in project management + bookkeeping for PlusROI and Bowden Works → then external tenants (Summer's agency) → client + commission-partner portal views ("everyone sees it from their own perspective" — the visibility levels of 02 §2).