Fix Manual Working-Capital Assumptions with Gemini-Powered Forecasting
Finance teams still drive cash forecasts with static DSO, DPO and inventory assumptions that barely reflect reality. This page shows how to use Gemini with Google Sheets and BigQuery to infer payment patterns, automate working‑capital drivers and build rolling cash forecasts. You will learn the strategic decisions to make and the concrete workflows to implement.
Inhalt
The Challenge: Manual Working-Capital Assumptions
Most finance teams still anchor their cash forecasts on a handful of manually set assumptions: Days Sales Outstanding (DSO), Days Payables Outstanding (DPO) and inventory turns. These numbers usually live in a single tab of a spreadsheet, are updated once per quarter at best, and then drive millions in funding, investment and liquidity decisions. The problem: real payment behaviour, supplier terms and inventory dynamics change weekly, not annually.
Traditional approaches were built for stability, not for the volatility and complexity of today’s markets. Spreadsheets with hard‑coded working-capital drivers can’t keep up with shifting customer cohorts, promotional campaigns, supply chain disruptions or evolving discount policies. Even when finance adds more detail, the result is an explosion of manual maintenance, VLOOKUPs and error‑prone overrides. ERP reports help a bit, but they are backward‑looking snapshots, not forward‑looking models of actual cash movements.
The impact is substantial. Static DSO/DPO/inventory assumptions systematically over‑ or underestimate future cash, causing weak liquidity planning, unnecessary credit lines, late reactions to cash crunches and missed investment opportunities. Treasury runs conservative buffers “just in case”, operations get mixed signals on working‑capital targets, and the CFO spends too much time explaining forecast deviations instead of steering the business. Competitors that react faster to cash signals win on pricing flexibility, M&A readiness and resilience in downturns.
This challenge is very real, but it is also highly solvable. With today’s AI, especially models like Gemini integrated into Sheets and BigQuery, finance teams can infer up‑to‑date payment patterns directly from transaction data, automate working‑capital assumptions and refresh cash forecasts continuously. At Reruption, we’ve seen how embedding AI into core financial workflows transforms decision speed and quality. In the sections below, you’ll find practical guidance on how to get there – step by step, with a clear path from your current spreadsheets to AI‑enhanced cash forecasting.
Need a sparring partner for this challenge?
Let's have a no-obligation chat and brainstorm together.
Innovators at these companies trust us:
Our Assessment
A strategic assessment of the challenge and high-level tips how to tackle it.
From Reruption’s perspective, the real opportunity is not a shiny new forecasting tool, but using Gemini on top of your existing finance data stack to continuously learn and update working‑capital drivers. Because Gemini integrates natively with Google Sheets and BigQuery, it can sit where your finance team already works, infer dynamic DSO, DPO and inventory patterns from real transactions, and feed them straight back into your cash forecasting model. Our hands-on experience building AI solutions shows that this combination of embedded AI and finance ownership is what turns static assumptions into a living, data-driven system.
Anchor on Business Questions, Not on the Algorithm
Before you think about models and prompts, define the core decisions your cash forecasting must support. Is the priority to reduce reliance on short-term credit, to plan investments with more confidence, or to avoid breaching covenants? Each goal implies different granularity: a bank covenant view might focus on weekly net cash positions, while investment planning needs scenario simulations over quarters. Clarifying this up front prevents you from building an AI that is technically impressive but operationally irrelevant.
With Gemini, it’s tempting to ask for "better forecasts" in general. Instead, structure questions such as, “Given our last 18 months of AR data, what is the probability distribution of collections for invoices older than 45 days by key customer segment?” This mindset ensures that AI‑derived working-capital drivers directly answer material business questions and not just produce more numbers.
Treat AI-Assisted DSO/DPO as a Living Policy, Not a One-Off Project
Static DSO/DPO assumptions are attractive because they feel final. An AI‑assisted approach with Gemini is different: you are effectively creating a living policy that adapts as customer and supplier behaviour changes. Strategically, this means finance leadership must accept that working-capital assumptions will move more frequently, and that this is a strength, not a loss of control.
Design governance for this from day one. Decide who approves major shifts in model‑suggested DSO/DPO, how often to review them, and how to communicate changes to business units. When AI updates are treated as managed policy adjustments, you avoid the perception of a “black box” and instead position Gemini as an intelligent assistant inside your existing control framework.
Prepare Data Ownership and Finance–IT Collaboration
Gemini can only infer high‑quality payment patterns if your transaction histories, customer master data and vendor terms are reasonably complete and accessible. Strategically, this requires clear ownership between finance, IT and data teams: who curates AR/AP data in BigQuery, who controls access, and who is accountable for data quality issues that impact forecasts.
Use the introduction of Gemini as a catalyst to define this ownership. For example, finance might own the logic of segmentation (by region, industry, payment term), while IT owns the pipelines into BigQuery. This division allows Gemini to be deployed quickly while minimising security and compliance risk – themes we prioritise in every Reruption engagement.
Start with Narrow, High-Impact Use Cases
Instead of trying to overhaul your entire cash forecasting process in one go, identify narrow use cases where Gemini can prove value quickly. Typical candidates include predicting collections for late‑paying customer segments, forecasting disbursements for a specific supplier group, or dynamically adjusting DPO for critical vendors with early-payment discounts.
This strategic focus reduces resistance and allows your team to build trust in AI outputs. Once the organisation sees that Gemini can, for instance, cut forecast variance for a specific AR portfolio by a meaningful margin, it becomes far easier to extend the approach to full working-capital coverage.
Build Risk Controls and Stress Scenarios Around the AI
Finance leaders rightly worry about relying blindly on models. Strategically, Gemini should be introduced with explicit risk mitigation measures: compare its suggested DSO/DPO to legacy assumptions, enforce guardrails (e.g. maximum allowed change per month), and build stress scenarios that deliberately challenge the AI’s baseline.
For example, you can ask Gemini to simulate cash impacts if a top customer cohort suddenly extends payment behaviour by 15 days, or if a key supplier tightens terms. This positions AI not as a single source of truth, but as a powerful engine for scenario thinking – something auditors and boards are more comfortable with, and something that strengthens, rather than weakens, your control environment.
Used thoughtfully, Gemini can turn DSO, DPO and inventory from static spreadsheet inputs into dynamic, data-driven levers that materially improve cash forecasting accuracy. The real value comes when finance owns the questions and governance, while AI quietly maintains the assumptions in the background. Reruption’s combination of AI engineering depth and a Co‑Preneur mindset allows us to embed these capabilities directly in your Sheets and BigQuery environment, so they become part of how your team works every day. If you want to explore how Gemini could cleanly replace manual working-capital assumptions in your context, we’re ready to co-design and validate a concrete approach with you.
Need help implementing these ideas?
Feel free to reach out to us with no obligation.
Real-World Case Studies
From Food Manufacturing to Transportation: Learn how companies successfully use Gemini.
Best Practices
Successful implementations follow proven patterns. Have a look at our tactical advice to get started.
Connect BigQuery AR/AP Tables and Build a Clean History
Effective automation of working-capital assumptions starts with a reliable transaction history. Use your data team or a lightweight ETL to load AR and AP ledger data into BigQuery: invoice IDs, issue dates, due dates, payment dates, amounts, customer/vendor IDs, currencies and payment terms. Standardise field names and ensure timestamp formats are consistent. This provides Gemini with the raw material to infer real DSO/DPO rather than relying on static term tables.
Once the base tables are in BigQuery, create curated views: for example, a view that calculates days-to-pay for each invoice and tags it with customer segment, business unit and region. Gemini can then query these views to detect patterns such as “customers in segment A with 30-day terms actually pay in 42 days on average”. The better you structure these views, the simpler and more repeatable your AI prompts and workflows become.
Use Gemini in Sheets to Generate Dynamic DSO/DPO by Segment
With your history in place, set up a Google Sheet that pulls summary data from BigQuery (e.g. via Connected Sheets). Organise rows by customer/vendor segment and columns for metrics like historical average days-to-pay, weighted by invoice value, over different lookback windows (3, 6, 12 months). Then use Gemini in Sheets to translate that history into dynamic DSO/DPO assumptions for your forecast model.
You can prompt Gemini directly in a cell or via an Apps Script call. For example:
Example prompt for Gemini (cell note or script context):
You are assisting a finance team with working-capital forecasting.
We provide historical days-to-pay data by customer segment.
Task:
- Calculate a recommended DSO by segment
- Use a 70/30 weighting between the last 3 months and the prior 9 months
- Flag any segment where behaviour has shifted by >10 days vs the last forecast.
Return a table with columns:
- Segment
- Recommended_DSO
- Change_vs_Prior_Forecast
- Comment_on_Driver (e.g. seasonality, outlier invoices, new terms)Link the "Recommended_DSO" column directly into your cash-flow model. This removes manual updates while keeping the logic transparent for controllers and auditors.
Forecast Collections and Disbursements on a Daily/Weekly Basis
Once DSO/DPO are dynamic, extend Gemini’s role to forecasting actual collections and disbursements. Use BigQuery to generate expected cash events: each open invoice with an expected payment date based on the AI‑enhanced days-to-pay pattern. Then re-aggregate these into daily or weekly buckets. Gemini can help refine this projection by adjusting for seasonality, known campaigns or upcoming price changes.
A practical pattern is to export the projected cash buckets into Sheets and ask Gemini to refine and annotate them:
Example prompt:
You are helping to refine a short-term cash collection forecast.
Sheet range A2:E100 contains:
- Bucket_Start_Date
- Bucket_End_Date
- Expected_Collections_Base (EUR)
- Segment
- Historic_Collection_Volatility (standard deviation %)
Tasks:
1) Adjust Expected_Collections_Base for known seasonality based on the last 3 years.
2) Add a column Adjusted_Collections.
3) Add a Comment column explaining major adjustments (>5%).
Assume:
- Strong seasonality for B2C segments around November-December.
- Slight slowdown for B2B in August.
Return the updated table.Feed the "Adjusted_Collections" back into your cash forecast to get a more realistic short-term liquidity view.
Automate Inventory-Related Cash Assumptions
Inventory is often left as a crude assumption in forecasting models. Use Gemini to refine inventory-driven cash flows by combining sales, purchase orders and stock-level data from BigQuery. Start with a table that shows, by product category and region, historical days of inventory on hand and gross margin. Then prompt Gemini to recommend safety stock and reorder patterns that balance service levels and cash tied up in inventory.
For instance:
Example prompt:
You are supporting inventory-related cash forecasting.
We provide a table with columns:
Category, Region, Avg_Days_Inventory, Stockout_Events, Gross_Margin.
Tasks:
1) Propose a target Days_Inventory for each Category/Region that:
- Reduces current Avg_Days_Inventory by 10-20% where Stockout_Events are rare.
- Keeps or increases inventory where Stockout_Events are frequent and Gross_Margin is high.
2) Estimate the cash impact (in EUR) of moving from current to target Days_Inventory,
assuming cost of goods as provided in column COGS_per_Day.
3) Return a table with Target_Days_Inventory and Estimated_Cash_Release.Translate the "Estimated_Cash_Release" into phased monthly impacts in your cash-flow forecast, and align with operations on realistic implementation timelines.
Embed Alerts and Scenario Buttons for Finance Users
To move beyond one-off analysis, embed Gemini into your daily cash monitoring. In Sheets, create simple "Scenario" buttons (using Apps Script) that call Gemini with different assumptions: for example, “Economic Slowdown”, “Aggressive Collections”, or “Supplier Tightening”. Each button triggers a recalculation of DSO, DPO and inventory impacts under that scenario and writes results into dedicated columns or tabs.
Additionally, have Gemini generate alert narratives when leading indicators change. A daily or weekly job can query BigQuery for shifts in days-to-pay or overdue buckets and ask Gemini to summarise the risk:
Example prompt for an automated alert summary:
You receive aggregated AR/AP metrics for the last week vs the prior 4-week average.
Tasks:
1) Identify material changes in customer payment behaviour (e.g. +5 days in a major segment).
2) Identify suppliers where average payment timing has shifted.
3) Summarise cash risk or opportunity over the next 8 weeks.
4) Provide 3 concrete actions for the finance team.
Output a concise email-style summary.Send this summary to treasury and the CFO, so AI is continuously highlighting where working-capital assumptions are drifting away from reality.
Track KPIs to Validate and Tune the AI-Assisted Forecast
To make Gemini a trusted part of your liquidity planning, define and monitor a small set of KPIs. At minimum, track: forecast error for net cash by week, forecast error for collections by major segment, and the variance between AI‑recommended and actual realised DSO/DPO. Use BigQuery to calculate these KPIs automatically and store time series for trend analysis.
Periodically involve Gemini to analyse these KPI series and suggest model adjustments, such as changing lookback windows, segregating additional customer cohorts or excluding known one-off events. Over time, you should see forecast variance decrease and the need for manual overrides shrink. Many organisations can realistically aim for 20–40% improvement in short-term cash forecast accuracy and a significant reduction in manual assumption maintenance effort once these practices are in place.
Expected outcome: a more responsive cash forecasting process, fewer emergency liquidity measures, and a finance team that spends less time massaging assumptions and more time acting on early cash signals.
Need implementation expertise now?
Let's talk about your ideas!
Frequently Asked Questions
Gemini improves DSO and DPO assumptions by learning directly from your transaction history instead of relying on static averages. Using BigQuery data, it analyses invoice issue dates, due dates, payment dates and customer or supplier segments to infer real payment patterns over different time windows. It can then recommend segment-specific DSO/DPO values, highlight where behaviour has recently shifted, and push those recommendations back into Google Sheets where your cash-flow model lives.
In practice, this means your assumptions continuously reflect current behaviour — for example, if a key customer cohort has started paying 7 days later, or if you’re systematically stretching payments to a non-critical supplier. The finance team remains in control, but the manual spreadsheet work and guesswork are largely removed.
You don’t need a large data science team to get value from Gemini in finance, but a few capabilities are important. First, you need access to AR/AP, sales and vendor term data in a structured form – ideally in BigQuery or a similar warehouse. Second, you need someone comfortable with Google Sheets (controllers, FP&A, or treasury analysts typically fit) who can work with Connected Sheets and basic formulas.
From there, Gemini’s natural language interface does a lot of the heavy lifting. For more advanced automation and scheduled runs, light support from IT or a data engineer helps – for setting up data pipelines, scripts and access controls. Reruption usually works directly with finance and one technical counterpart to stand up a working solution quickly, then upskills the finance team to own the process.
Timelines depend on your data readiness, but many organisations can see meaningful improvements in cash forecast accuracy within 6–10 weeks. The first 2–3 weeks typically focus on connecting AR/AP data to BigQuery, cleaning obvious quality issues, and defining the segmentation that matters for your business (e.g. regions, customer types, key supplier groups).
Weeks 3–6 are usually enough to deploy Gemini in Sheets, generate AI‑assisted DSO/DPO assumptions and wire them into your existing forecast model. From there, you can start tracking variance and tuning the approach. More advanced use cases – like automated scenario simulations or inventory-related cash modelling – can be layered on over the following months without disrupting the core process.
From a tooling perspective, Gemini’s cost is relatively modest compared to the financial impact of better working-capital management. The main investments are time to connect your data and redesign some of the forecasting workflow. ROI typically comes from three areas: fewer surprises in short-term liquidity (reducing emergency credit usage), lower cash buffers because forecasts are more reliable, and productivity gains from eliminating manual assumption maintenance.
While exact figures depend on your size and sector, it’s common to see reduced forecast variance by 20–40% and measurable reductions in working capital tied up, even in the first year. We recommend treating the first implementation as a focused pilot with clear KPIs so that you can quantify ROI before rolling out more broadly.
Reruption supports you end-to-end, from scoping the use case to shipping a working solution. With our AI PoC offering (9,900€), we first validate that Gemini can reliably infer your DSO, DPO and key working-capital drivers from existing data – including model selection, architecture and performance metrics. You get a functioning prototype running on your Google Sheets/BigQuery stack, not just a slide deck.
Beyond the PoC, our Co-Preneur approach means we embed with your finance and data teams like co-founders: designing governance, automating workflows, setting up risk controls and training your people to own the AI-enabled forecasting process. We focus on fast engineering and real outcomes, so that Gemini becomes a durable capability in your cash forecasting, not a one-off experiment.
Contact Us!
Contact Directly
Philipp M. W. Hoffmann
Founder & Partner
Address
Reruption GmbH
Falkertstraße 2
70176 Stuttgart
Contact
Phone