How to Build a 60-Minute Cash-Flow & Staffing Forecast with AI

What you’ll have at the end

• 13-week rolling cash forecast (collections, payables, payroll, taxes, net cash).
• Staffing forecast by week and by role (demand vs. scheduled hours vs. hiring gap).
• Auto-alerts for red flags (cash < target; overtime spike; coverage gap).
• A one-page owner dashboard you can skim in 60 seconds, refreshed daily.

Prereqs (quick)

• Banking/accounting: QuickBooks Online or Xero with bank feeds ON.
• Timesheets/scheduling: Gusto, ADP, Homebase, When I Work, Deputy, or similar.
• Sheet/database: Google Sheets or Airtable.
• Automation glue (optional but recommended): Zapier or Make.
• AI: ChatGPT (connected to your sheet via paste/upload—no PII).

Time-saving tip: if you can’t set up automations today, export CSVs from your accounting and time tool and paste into the sheet. You can automate later.

The 60-Minute Build (sprint)

1) Create your sheet (10 minutes)

In Google Sheets, make four tabs:

1. Inputs_Bank – last 90 days transactions (date, payee, memo, amount).
2. Inputs_Timesheets – last 8 weeks (date, employee, role/site, hours, pay rate, OT flag).
3. Assumptions – payment terms, payroll cadence, tax % estimates, target cash floor, hiring lead times.
4. Forecast – 13 weeks (Week Start, AR In, AP Out, Payroll, Taxes, Net, Ending Cash; plus Demand Hrs, Scheduled Hrs, Gap Hrs by role).

Starter weekly columns (rows = weeks 1–13):
Week, Beg Cash, AR In, AP Out, Payroll, Taxes/Fees, Other, Net, End Cash, Demand Hrs (Role A/B/…), Scheduled Hrs, Gap Hrs, OT Risk %.

2) Let AI classify your transactions (10 minutes)

Paste 90 days of Inputs_Bank into ChatGPT and use this prompt:

Prompt:
“Classify each transaction into one of {AR receipt, AP vendor, Payroll, Tax/Fees, Transfer, Owner Draw/Contrib, Other}. Return a CSV with columns: date, payee, memo, amount, category, probable counterparty (client/vendor), and if AR/AP: expected week of cash movement based on memo patterns and typical terms (assume Net 14 unless otherwise implied). Flag subscriptions and variable utilities separately.”

Paste the result back into a new tab Bank_Classified.

3) Build AR & AP projection (10 minutes)

Still in ChatGPT, paste your top 20 open invoices/bills (or export open AR/AP). Use:

Prompt:
“Using open invoices/bills + past payment behavior from Bank_Classified, project weekly collections and payments for the next 13 weeks. Assume: {your terms from Assumptions}. Distribute doubtful/late invoices by probability. Output a 13-row table: Week Start, AR In, AP Out, notes.”

Add the output to Forecast columns AR In and AP Out.

4) Project payroll & taxes from timesheets (10 minutes)

From Inputs_Timesheets, calculate average hours per role per week, OT %, and blended wage.

Example helper formulas in a Calc tab:

/* Last 8 weeks avg hours per role */
=QUERY(Inputs_Timesheets!A:E,
"select C, sum(D)/8 group by C label sum(D)/8 'Avg Hrs/Wk'", 1)

/* Blended wage per role */
=QUERY(Inputs_Timesheets!A:E,
"select C, sum(D*E)/sum(D) group by C label sum(D*E)/sum(D) 'Blended $/Hr'", 1)

Payroll per week in Forecast:

/* Payroll = Scheduled Hrs * Blended Wage * (1 + Burden%) */
=ROUND(ScheduledHrs_ThisWeek * BlendedWage * (1 + Assumptions!BurdenPct), 0)

For taxes/fees, use a single % burden (e.g., 12–18%) until you automate.

5) Create the staffing forecast (10 minutes)

Add (or paste) your Demand Plan by role (contracted hours, events, seasonality). If you don’t have one, draft it:

Prompt:
“Based on last 8 weeks by role and the upcoming calendar (holidays, events), create a conservative weekly demand plan for 13 weeks. Output: Week, Role, Demand_Hrs. Highlight any weeks with >10% swing.”

Now compute gaps:

/* Gap Hrs = Demand - Scheduled (not below 0) */
=MAX(0, DemandHrs - ScheduledHrs)

/* OT Risk % = IF ScheduledHrs per FTE > 40, show excess share */
=MAX(0, (ScheduledHrs / (Headcount * 40)) - 1)

If Gap Hrs > 0 for three consecutive weeks, that’s a hiring trigger. Put “Hire 1 x Role” in a Triggers column.

6) Wire simple alerts (5 minutes)

In a small Alerts block on the Dashboard tab:

• Cash Floor Breach: =IF(Forecast!EndCash < Assumptions!TargetCash, "ALERT: Cash below floor", "")
• OT Spike: =IF(Forecast!OTRiskPct > 0.15, "ALERT: OT > 15%", "")
• Staffing Gap: =IF(Forecast!GapHrs > 40, "ALERT: Hire/Temp Coverage", "")

Use Conditional Formatting to turn cells red/yellow.

7) Owner dashboard (5 minutes)

One page with:

• Sparkline: Ending Cash (13 weeks).
• Cards: Next Payroll Due, Largest AP Week, Expected Collections This Week.
• Table: By-role: Demand, Scheduled, Gap, Hiring Trigger, OT Risk.
• Notes: AI-generated bullet risks for the next 4 weeks.

Prompt for risk notes:
“Summarize top 5 risks in the next 4 weeks based on Forecast and Alerts. Keep bullets under 15 words each. Include plain-English actions.”

Optional automations (30–45 minutes once you’re ready)

• Bank → Sheet (daily): Zapier/Make pulls new transactions → append to Inputs_Bank → ChatGPT step classifies → updates Bank_Classified.
• Timesheets → Sheet (daily): Export yesterday’s hours per role/site → append to Inputs_Timesheets.
• Email/Slack alerts: When EndCash < Target or GapHrs > threshold, send a message with the week, amount, and suggested action.
• Hiring tickets: If GapHrs sustained 3 weeks, auto-create a recruiting task with the exact role, site, hours, and deadline.

Governance & accuracy (what keeps this honest)

• Lock your Assumptions behind permissions; log changes with a date & user.
• Reconcile weekly: True-up actual collections/payroll vs. forecast; let AI explain variance.
• Versioned prompts: Paste your exact prompts in a Prompts tab so anyone can repeat the steps.
• Privacy: Strip PII on exports (no SSNs, full account numbers).

What “good” looks like

• You can say, “We’ll bottom at $112k on the week of Dec 8 unless we pull $45k from two late payers.”
• You know which role and which week needs hiring or temp coverage—before overtime becomes culture.
• Your Monday stand-up takes 10 minutes: last week actuals, this week forecast, three actions.

Common pitfalls (and easy fixes)

• All AR modeled at full value. Instead, apply probability by customer based on history.
• Ignoring payroll taxes/benefits. Add a blended burden and refine later.
• Static demand. Re-estimate demand monthly; seasonality is real.
• One giant “Other” bucket. Break out subscriptions, insurance, SaaS, fuel—patterns drive better forecasts.

Copy-paste checklist (fast start)

• Create 4 tabs: Inputs_Bank, Inputs_Timesheets, Assumptions, Forecast
• Classify 90 days of transactions with the AI prompt
• Import open AR/AP and project 13 weeks
• Calculate blended wages and payroll burden from timesheets
• Draft 13-week demand plan; compute gaps & OT risk
• Build dashboard + three alerts
• Schedule weekly 15-minute review & reconcile

Want the ready-made template?

I can give you a Google Sheets template pre-wired with formulas, prompts, and alert rules that mirrors this guide.

Contact Us

Questions or want this for your business? Contact us today!