How to automate a cash and liquidity forecast

Start with the latest reconciled bank balances, add expected receipts, subtract expected payments, and calculate the resulting cash position by legal entity, currency, and week. Keep actual bank activity, scheduled events, and treasury assumptions separate so every forecast line can be explained and replaced when the actual payment settles.

Decide what the forecast will control

Use a daily cash view for near-term funding decisions and a rolling weekly view for later weeks. Specify the bank-balance cutoff, the account population, the cash definitions, and which receipts or payments count as committed. Distinguish available cash from restricted cash. Set liquidity thresholds by entity because a corporate total can conceal a deficit in an entity that cannot freely draw on another entity's funds.

Oracle documents using payables, receivables, payroll, external transactions, and prior-day bank statements in cash forecasting. Treasury4 also documents cash visibility across accounts, entities, and currencies. Oracle cash forecasting documentation · Treasury4 Cash4

Map the work from statement arrival to treasury review

What to automate

Confirm bank coverage

Ask Treasury Operations to own a calendar of expected entity, account, currency, statement date and arrival deadline. On each morning run, compare files received through the approved bank connection with the calendar and keep the original files and receipt times. A late or truncated statement becomes a named task. Mark the affected entity's view incomplete until the required statement arrives or the treasurer approves a dated exception.

Tie opening cash

Normalize statement amounts to signed decimals in native currency. Start each forecast with the closing available balance at the cutoff and show restricted cash in a separate column. Compare each account with the last accepted position plus intervening bank activity. Treasury Operations supplies the evidence for a reconciling item. Route a balance outside tolerance to that owner and keep the account out of the approved opening position.

Gather expected cash events

Read open AP and AR installments, the approved payroll calendar and any separately authorized funding events on each refresh. Give each item a stable source ID, entity, currency, expected settlement date, amount, confidence and owner. Use the approved date precedence for revised promises or payment-run changes. Put an installment with a missing date into the owning team's queue before the forecast is released.

Remove events already settled

Link settled bank lines to expected receipts and payments using a source reference, currency and documented amount checks. For a partial settlement, keep the open balance and point back to both records. When two candidates qualify for the same movement, send the row to Treasury Operations for a decision. Replay a statement to confirm a duplicate cannot reduce expected cash twice.

Apply scenarios

Store assumption sets by effective date and version. For example, record a specific invoice delay in the downside scenario and keep the original expected date in the base case. Calculate cash by entity, native currency and week, and raise an alert at the approved liquidity floor. Treasury approves assumption changes. Give AI the approved notes and computed differences to draft a short explanation.

Publish and explain variances

Deliver a draft bridge with opening available cash, scheduled activity, closing cash and unresolved items. The treasurer signs off on a specific version and assigns each threshold breach an owner. Save that forecast vintage, then compare it with settled activity at the same horizon. Separate timing, amount and missing-input differences in the variance queue so owners can decide what to change.

Test a shortfall before rolling it out

Take one entity and one week. The opening available cash is $100,000. A $40,000 receipt and a $70,000 payroll run are expected. The base closing cash is $70,000. If the receipt moves into the following week, downside closing cash is $30,000. A $50,000 liquidity floor creates one $20,000 downside alert. This is synthetic data.

Now match $10,000 of the $40,000 receivable to a settled receipt already included in the $100,000 opening balance. Keep opening cash at $100,000 and reduce the forward receipt to $30,000. With the same $70,000 payroll, forecast closing cash is $60,000. Verify that a replay of the same statement leaves the result unchanged, and that an account with a missing statement stays marked incomplete. Run the new and incumbent workflows side by side through a payroll cycle. Measure statement coverage, forecast delivery time, absolute variance by horizon, and how many material exceptions reached an owner before cutoff.

Build the bank-to-forecast reconciliation

Use balance_snapshot(entity, account, currency, as_of, available_minor, restriction, revision) and cash_event(event_id, entity, account, value_date, signed_minor, scenario, source_ref, status). Bridge to the signed opening position with a separate list of pending items. Keep currencies separate until a dated and approved FX conversion is applied.

1. Join bank statements and ledger cash lines by controlled account mapping. Track the feed’s statement date and retrieval date separately. A zero balance can be valid. An absent balance or incomplete account population blocks publish.
2. Resolve the status of pending payments, open receivables and confirmed movements at the snapshot cutoff. If a payment already appears in opening cash, mark its forecast event settled and exclude it from future outflows. Detect reversals and amendments using source references.
3. Schedule from the owner’s calendar and record the forecast horizon and daily close. For each scenario, move only its affected cash legs. Distinguish accessible cash, restricted cash and potential facility availability in the output.
4. Compare forecast against actuals at the same value-date grain. Report opening coverage, miss by event and currency, buffer breaches and aged exceptions. Send a source-linked alert to Treasury with the earliest date and contributing movements.

opening = 1_000_000
events = [
    ("supplier", -120_000, "confirmed"),
    ("payroll", -250_000, "scheduled"),
    ("receipt", 200_000, "scheduled"),
    ("earlier-invoice", -60_000, "already_in_opening"),
]
closing = opening + sum(amount for _, amount, status in events
                        if status in {"confirmed", "scheduled"})
assert closing == 830_000
assert closing - 200_000 == 630_000  # receipt slips beyond the horizon

Exercise the stress case with an invoice already settled and an unrecognized bank account. The first must avoid double counting and the second must prevent a signed forecast.

Implement the work step by step

The treasurer decides whether the controller can publish a reconciled cash bridge before the funding cutoff and identify when a legal entity may fall below its floor. The workflow begins when prior-day statements are due. Ends with the approved base and downside forecast and open items with owners. Payment initiation, lender terms and approved transfers are outside this operation.

Bank coverage and opening position

• Verify expected account arrivals. Poll adapters, persist statements by account and cutoff, alert on late arrivals. Read TMS, bank files, account manifest. Record Received and missing account list to analyst. Owner Treasury ops. TMS owner resolves expired access. Check Block complete badge if mandatory account missing.
• Reconcile and classify opening balance. Parse signed cents and account date, compare close to open, use approved cash restrictions. Read Bank statements, TMS, restriction register. Record Reconciled cash in native currency to forecast. Owner Treasury analyst. Approve new restrictions and investigate a broken tie. Check Currency and cutoff must match, retain unknown balance separately.

Expected events

• Prepare expected collections. Read unpaid installments and approved timing profile, maintain stable IDs and estimates. AI can suggest reason codes for redacted collection notes. Read ERP open invoices, receipt history. Record Invoice-level events to treasury. Owner AR owner. Confirm unusual promised receipt dates. Check Reconcile total unpaid installments with aged AR.
• Prepare payments and payroll. Compute remaining payable by installment, add payroll at approved net cash amount. Read ERP open payables, payment run, payroll calendar. Record Payment and payroll schedule to analyst. Owner AP owner. Approve material changes outside scheduled runs. Check Hold unapproved release amounts out of committed scenario.
• Replace settled or partially settled events. Join settlement reference and currency, subtract matched cents, preserve relation. AI can suggest candidates for ambiguous descriptions only. Read Bank transactions, open event schedule. Record Outstanding forecast events and evidence. Owner Treasury analyst. Resolve conflicting, shared, and high-value references. Check Forecast remainder cannot be negative. Ambiguous match never consumes an event.

Forecast and release

• Review missing, late, or partial-source break. Create deduplicated ticket and hold affected entity by source manifest. AI can draft internal issue summary without secrets. Read Exception queue, bank portal, issue register. Record Resolved input or explicitly incomplete forecast to owner. Owner Treasury ops. Contact owner and approve delayed cut. Check Record time waiting outside staff minutes.
• Compute scenarios and liquidity alerts. Calculate native-currency close and separately translated totals. Compare with entity floor. AI can draft driver explanation from computed variances. Read Reconciled balance, expected events, FX, assumptions. Record Entity and week bridge to treasurer. Owner Treasury analyst. Approve material assumption overrides. Check Assert internal transfer treatment and no stale rates marked final.
• Sign off, publish, and log forecast vintage. Stage immutable revision, ACL-check recipients, and capture delivery. AI can summarize prepared aggregate exceptions. Read Forecast review, approved distribution. Record Accepted report to Treasury and Finance. Owner Treasurer. Own funding decision and release approval. Check No externally sent forecast outside approved list.

Parallel run. Shadow the incumbent over payroll, month end and at least 15 business days. Force a missing statement, partial settlement and late receipt. No unowned material breaks or duplicate forecast events.

Build it yourself with AI

Start with synthetic files for one entity and currency. Create bank.csv, events.csv and assumptions.json, and put the matching policy and cash floor in playbook.md. Give the coding assistant only those fixtures. Ask it to build a local Python command that writes cash_bridge.csv and exceptions.csv to a dated output folder.

1. Define the data and rules

   Read the fixture files and playbook. Propose an input contract and a signed-decimal cash bridge. Show required IDs, cutoff rules, expected event statuses and the exception owner for each missing input. Mark unresolved decisions for Treasury approval.

2. Build and prove the calculation

   Write a local Python command using Decimal. Replace expected cash only for settled amounts already reflected in opening cash. Save stable event IDs and source references. Test opening cash of 100000, a receipt of 40000, and payroll of 70000. Expect 70000 in the base case. Move the receipt one week to get 30000 and a 20000 shortfall against a 50000 floor. With 10000 of the receipt already in opening cash, expect 60000 after payroll. Duplicate the bank row and check the result again.

3. Draft the review from validated outputs

   Given cash_bridge.csv and exceptions.csv, list the entity, currency, week, scenario and shortfall for each breach. Cite the source IDs. Label a possible timing explanation as a hypothesis and ask Treasury for the missing evidence.

Run the same inputs twice and compare the resulting balances and alert IDs. Remove a statement to prove that the incomplete status reaches the output. When the treasurer approves the fixture results, schedule the command with your existing job runner in shadow mode and compare it with the current forecast through a payroll cycle. Keep money movement and approval in the existing Treasury process.

Need help automating this?

Explore the related finance and accounting workflows for bank-to-ledger checks, recurring cash reports and exception routing. Explore Mage's finance and accounting workflows.