Fix Weak Scenario Planning in Finance with Gemini-Powered Models

Connect Gemini to a Clean, Structured Scenario Sheet

Before involving AI, consolidate your key planning assumptions into a structured Google Sheet. Separate input drivers (volumes, prices, FX, headcount) from calculated outputs (revenue, margin, cash flow). Use consistent names for driver cells or ranges (e.g. "volume_base_case", "price_sensitivity_range") so Gemini can reference them clearly.

Once the sheet is ready, use Gemini in Sheets to describe what each driver means and how scenarios should be built. For example, add a note in a separate tab called "Scenario_Instructions" and let Gemini read it as context for further actions.

Example Gemini prompt in Sheets:
You are assisting with financial scenario planning.

The current sheet contains:
- Input drivers in the tab 'Drivers'
- The base P&L model in 'Base_Model'

Tasks:
1) Create a new tab 'Scenario_Assumptions' listing scenario names in rows
   and driver names in columns.
2) Propose 6 coherent scenarios covering:
   - Demand: -20%, -10%, base, +10%, +20%
   - Price changes by product group
   - FX movements for EUR/USD and EUR/GBP
3) Fill the table with suggested percentage deltas vs base for each driver.

Make sure the scenarios are internally consistent and business plausible.

This approach lets Gemini do the heavy lifting of structuring scenarios while finance retains full control over the underlying formulas and logic.

Use Gemini to Generate Sensitivity Tables and Tornado Charts

Manual sensitivity analysis usually stops at 1–2 variables. With Gemini in Sheets, you can automatically generate multi-variable sensitivity tables and prepare the data for visualisations such as tornado charts in your BI tool.

Prepare a dedicated tab (e.g. "Sensitivity_Setup") where you list key drivers and their test ranges. Then instruct Gemini to build an output table that calculates the effect on EBIT, cash flow or another KPI.

Example Gemini prompt in Sheets:
Create a sensitivity analysis in a new tab called 'Sensitivity_Output'.

Use the following drivers and ranges from 'Sensitivity_Setup':
- Unit volume delta: -20% to +20% in 5% steps
- Average selling price: -5% to +5% in 1% steps
- FX EUR/USD: -10% to +10% in 2% steps

For each combination, calculate:
- Revenue
- Gross margin
- EBIT

Link all calculations back to 'Base_Model' formulas. Do not hard-code
numbers. Prepare the output so it can be easily used as the data source
for a tornado chart (one row per scenario, one column per KPI).

Once Gemini builds this table, connect it to Looker Studio, Power BI or your preferred BI tool to visualise which drivers matter most.

Automate Narrative Scenario Summaries for Management

Senior leaders often struggle to digest raw tables. Use Gemini in Docs to automatically convert scenario outputs into short, comparable narratives that highlight impacts on revenue, margin and cash. This not only saves time but also ensures consistent messaging across cycles.

Export or link key scenario outputs from Sheets into a summary tab, then copy them into a Doc Gemini can read. Ask Gemini to produce management-ready explanations.

Example Gemini prompt in Docs:
You are a finance business partner preparing a board briefing.

Below is a table summarising 5 scenarios (Base, Demand Shock,
Price Increase, Supply Disruption, FX Shock) with the following
metrics per scenario: Revenue, Gross Margin %, EBIT, Operating Cash Flow.

Write a concise narrative (max 150 words per scenario) that:
- Explains the main driver differences vs base case
- Highlights the impact on EBIT and cash
- Flags operational implications (capacity, headcount, working capital)

Use clear, non-technical language and avoid overconfidence.
Mention where assumptions are particularly uncertain.

This turns Gemini into a narrative engine that keeps finance focused on validating content, not drafting from scratch.

Run What-If Simulations via Natural-Language Q&A

Instead of building every what-if scenario manually, use Gemini as a conversational interface on top of your model. In Sheets, you can ask Gemini to temporarily apply new assumptions, calculate the impact, and then either store or discard that scenario. This is especially useful in live meetings with business stakeholders.

Keep one dedicated "sandbox" tab where Gemini can safely change assumptions without touching the canonical model. Use prompts that clearly describe both the change and the desired outputs.

Example Gemini prompt in Sheets:
Assume we are in a meeting with Sales discussing a potential
10% list price increase for Product Line A starting in Q3.

Tasks:
1) In the 'Sandbox' tab, copy the current base assumptions.
2) Apply a +10% price increase for Product Line A in Q3 and Q4 only.
3) Recalculate revenue, gross margin and EBIT for FY.
4) Summarise the incremental impact vs base case in a small table
   (Revenue delta, Gross margin delta in %, EBIT delta).

Do not change any other drivers.

This setup gives finance the agility to answer "what happens if…" questions in minutes without breaking core models.

Integrate External Data for More Realistic Scenarios

Weak scenarios often ignore market reality. Use Gemini with external data sources (CSV exports, APIs feeding into Sheets, or data warehouse connections powering BI) to incorporate FX rates, commodity prices, interest curves or macro indicators into your planning. Gemini can then build scenarios that explicitly reference these external drivers.

For example, you can load historical FX data into a sheet and let Gemini propose plausible FX paths and their impact on revenue and cost.

Example Gemini prompt in Sheets:
We have 5 years of monthly EUR/USD FX data in the tab 'FX_History'.

1) Analyse volatility and identify typical annual ranges.
2) Propose three 12-month FX scenarios (Stable, Moderate Swing,
   High Volatility) with monthly rates in a new tab 'FX_Scenarios'.
3) Link these scenarios into the revenue and cost calculations
   in 'Base_Model' and calculate the impact on EBIT for each.

Document your logic in a short explanation note in 'FX_Scenarios'.

By embedding external factors this way, your Gemini-generated scenarios become more robust and easier to defend in front of stakeholders.

Set Up KPIs and Logs to Track Scenario Quality and Usage

To make AI-driven scenario planning sustainable, treat it as a product, not a one-off project. Track metrics like number of scenarios generated per planning cycle, turnaround time from request to delivery, and how often scenario insights are used in actual decisions (e.g. referenced in steering committee minutes).

Maintain a simple log (in Sheets or a lightweight database) where each scenario set is tagged with its purpose, key assumptions, Gemini’s involvement (e.g. "assumption generation", "sensitivity build", "narrative drafting") and final outcome. Over time, this gives you evidence about where Gemini adds most value and where you need additional controls.

Expected outcomes when these best practices are implemented are realistic and measurable: 30–50% reduction in manual time spent on scenario construction, 2–3x increase in the number of scenarios considered per major decision, and significantly faster turnaround for what-if requests from the business. More importantly, finance gains a repeatable, explainable AI-enabled process instead of ad-hoc spreadsheet heroics.