How-to

Getting Started With Python Excel AutomationDeep dive

Run What-If Scenarios on an Excel Model with Python

Keep the Excel model as the single source of truth and let Python drive it: define scenarios as input values, write one copy of the model per scenario with openpyxl, recalculate them all in one headless LibreOffice call, read the outputs back, build a sensitivity table and write a scenario comparison workbook.

Formulas in workbooks Excel functions in pandas

Finance and operations teams keep their models in Excel for good reasons: the logic is visible, reviewers can click a cell and see the formula, and the people who own the assumptions can change them. What Excel is bad at is running the model many times — best case, worst case, the board's case, every input nudged up and down by ten percent. Scenario Manager and Data Tables exist, but they are fiddly, hard to review and do not scale to dozens of runs. Rewriting the model in Python solves the scale problem and creates a new one: two versions of the logic that will drift apart. The approach on this page keeps the workbook as the only copy of the logic and uses Python to feed it inputs, recalculate it and collect the answers. It is part of Working with Excel Formulas in Python.

Prerequisites

Bash

pip install openpyxl pandas
# LibreOffice does the calculating
sudo apt install libreoffice-calc

pricing_model.xlsx is a small product P&L. Five assumptions sit on the Inputs sheet — units sold, price, unit cost, fixed costs and tax rate — and the Model sheet turns them into revenue, contribution, operating and net profit, and a break-even volume. A real model would be larger; nothing below depends on its size.

Step 1 — Name the inputs and outputs

A script that writes to Inputs!B3 breaks the day someone inserts a row. Describe the model's interface once — which cells are inputs, which are outputs — and refer to them by meaning everywhere else:

Python

INPUTS = {"units": "Inputs!B2", "price": "Inputs!B3", "unit_cost": "Inputs!B4",
          "fixed": "Inputs!B5", "tax": "Inputs!B6"}
OUTPUTS = {"revenue": "Model!B2", "operating_profit": "Model!B5",
           "net_profit": "Model!B7", "break_even_units": "Model!B8"}

def split(ref):
    sheet, cell = ref.split("!")
    return sheet, cell

from openpyxl import load_workbook
wb = load_workbook("pricing_model.xlsx")
base = {k: wb[split(v)[0]][split(v)[1]].value for k, v in INPUTS.items()}
print(base)

If the model already has defined names for its inputs — Price, UnitCost — use those instead of addresses, resolving them as in Read a Named Range into pandas. Either way the mapping is the contract between the model's owner and the script, and it is short enough to review.

Step 2 — Define the scenarios and write one copy each

A scenario is a set of overrides on the base inputs. Write a copy of the model for each, changing only the input cells:

Python

from pathlib import Path

SCENARIOS = {
    "base": {},
    "upside": {"units": 12000, "price": 25},
    "downside": {"units": 8500, "unit_cost": 15.5},
    "price_cut": {"price": 21.5, "units": 12500},
}

runs = Path("runs")
runs.mkdir(exist_ok=True)
for name, overrides in SCENARIOS.items():
    wb = load_workbook("pricing_model.xlsx")
    for key, value in {**base, **overrides}.items():
        sheet, cell = split(INPUTS[key])
        wb[sheet][cell] = value
    wb.save(runs / f"{name}.xlsx")
print(sorted(p.name for p in runs.iterdir()))

Unknown keys fail loudly (KeyError on INPUTS[key]), which catches typos such as unit_cots before they produce a silently wrong run. openpyxl keeps the formulas as they are and writes no cached results, so each copy is a model waiting to be calculated.

Step 3 — Recalculate every copy in one call

openpyxl cannot calculate formulas. Headless LibreOffice can, and it accepts many files at once, so one process start covers every scenario:

Bash

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to xlsx --outdir calculated runs/*.xlsx > /dev/null 2>&1
ls calculated

LibreOffice opens each file, calculates the formulas (the copies have no cached values, so it has to), and saves a new .xlsx that contains both the formulas and their results. Starting LibreOffice is the slow part — around a second — so batching all scenarios into one call matters more than anything else for speed. The private -env:UserInstallation profile keeps this from clashing with any other LibreOffice process on the machine. Recalculate Excel Formulas Without Excel discusses where LibreOffice's results can differ from Excel's.

Step 4 — Read the outputs into a table

Open each calculated copy with data_only=True, which returns the stored results instead of the formulas, and collect the outputs:

Python

import pandas as pd

def read_outputs(path):
    wb = load_workbook(path, data_only=True)
    return {k: wb[split(v)[0]][split(v)[1]].value for k, v in OUTPUTS.items()}

results = pd.DataFrame({name: read_outputs(Path("calculated") / f"{name}.xlsx") for name in SCENARIOS}).T
print(results.round(1))

The price cut sells 2,500 more units than the base case and still earns less: at €21.50 the contribution per unit falls from €9.50 to €7, and the extra volume does not make up for it. Its break-even volume rises from about 6,300 units to 8,600. That is the kind of result a scenario table exists to make obvious.

Step 5 — Sensitivity: nudge every input

A sensitivity run changes one input at a time by the same percentage and records how far the result moves. It shows which assumptions deserve the most scrutiny. Generate the runs, recalculate them together, and compare:

Python

sens = {}
for key in INPUTS:
    for sign, factor in (("-", 0.9), ("+", 1.1)):
        sens[f"{key}{sign}"] = {key: base[key] * factor}

for name, overrides in sens.items():
    wb = load_workbook("pricing_model.xlsx")
    for k, v in overrides.items():
        sheet, cell = split(INPUTS[k])
        wb[sheet][cell] = v
    wb.save(runs / f"sens_{name}.xlsx")

Bash

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to xlsx --outdir calculated runs/sens_*.xlsx > /dev/null 2>&1
ls calculated | wc -l

Python

base_net = results.loc["base", "net_profit"]
rows = []
for key in INPUTS:
    low = read_outputs(Path("calculated") / f"sens_{key}-.xlsx")["net_profit"] - base_net
    high = read_outputs(Path("calculated") / f"sens_{key}+.xlsx")["net_profit"] - base_net
    rows.append({"input": key, "-10%": round(low), "+10%": round(high), "swing": round(abs(high - low))})
tornado = pd.DataFrame(rows).sort_values("swing", ascending=False)
print(tornado.to_string(index=False))

Sorted by swing, this is the data behind a tornado chart. Price dominates: a 10% move in price shifts net profit by €18,000 either way, more than any other assumption, followed by unit cost and volume. The tax rate barely matters at this level of profit. If the pricing assumption is the least certain one, it is also the one most worth getting right.

Step 6 — Write the comparison workbook

Put the scenario table and the sensitivity table in a workbook for the people who own the model:

Python

with pd.ExcelWriter("scenario_comparison.xlsx", engine="xlsxwriter") as xw:
    table = pd.DataFrame({n: {**base, **o} for n, o in SCENARIOS.items()}).T.join(results)
    table.to_excel(xw, sheet_name="Scenarios")
    tornado.to_excel(xw, sheet_name="Sensitivity", index=False)
    money = xw.book.add_format({"num_format": "#,##0"})
    xw.sheets["Scenarios"].set_column("B:K", 14, money)
    xw.sheets["Sensitivity"].set_column("A:D", 12, money)
print(table[["units", "price", "net_profit"]].round(2))

Showing the inputs next to the outputs matters: a scenario table without its assumptions invites arguments about what "upside" meant.

On Windows with Excel: let Excel calculate

When the model uses functions LibreOffice lacks, or the results must match Excel to the last decimal for an audit, drive Excel itself. xlwings opens the model once, and each scenario is a matter of writing the inputs and reading the outputs — Excel recalculates in between, with no files written at all:

Python

import xlwings as xw

with xw.App(visible=False) as app:
    book = app.books.open(r"C:\models\pricing_model.xlsx")
    rows = {}
    for name, overrides in SCENARIOS.items():
        for key, value in {**base, **overrides}.items():
            sheet, cell = split(INPUTS[key])
            book.sheets[sheet].range(cell).value = value
        rows[name] = {k: book.sheets[split(v)[0]].range(split(v)[1]).value for k, v in OUTPUTS.items()}
    book.close()

This is faster per scenario than writing files, because Excel stays open, but it needs Windows or macOS with Excel and a desktop session, as described in Read and Write a Live Excel Workbook with xlwings.

Common pitfalls

Reading formulas instead of results. Use data_only=True on the calculated copies; on the originals it returns None, because openpyxl never stored results.
One LibreOffice start per scenario. Batch the files into one call; process start-up dominates the run time.
Hard-coded addresses scattered through the script. Keep one mapping of inputs and outputs.
Functions LibreOffice does not implement. Most models use standard functions that calculate identically; check any model that uses newer Excel functions by comparing the base case with Excel's own result once.
Volatile functions. RAND, TODAY and NOW recalculate differently on every run; fix them to inputs for reproducible scenarios.

Conclusion

Keep the logic in the workbook and put a thin Python layer around it: a mapping of input and output cells, scenarios as overrides on the base inputs, one copy of the model per run written with openpyxl, a single headless LibreOffice call to calculate them all, and data_only=True to read the answers back. The same loop produces sensitivity tables, and the results go back to the model's owners as a workbook of inputs and outputs side by side.

Frequently asked questions

Can Python run Excel what-if analysis without Excel installed? Yes. openpyxl writes the input values into copies of the model, and headless LibreOffice recalculates the formulas; openpyxl then reads the results with data_only=True. On Windows with Excel installed, xlwings can drive Excel itself instead.

Why do I get None when reading formula results with openpyxl? openpyxl does not calculate formulas, and files it saves contain no cached results. Read results only from files that Excel or LibreOffice has calculated and saved.

How do I run a sensitivity analysis on an Excel model in Python? Change one input at a time by the same percentage up and down, recalculate each copy, and record the change in the output. Sorting the inputs by the size of the swing gives the data for a tornado chart.

Should I rewrite the model in pandas instead? Only if Python will own the logic from now on. Keeping the formulas in Excel lets the people who own the assumptions review and change them, and Python only drives the runs.

Up one level: Working with Excel Formulas in Python — writing, reading and recalculating formulas.
Goal Seek in Python Instead of Excel — solving for the input that hits a target.
Map Formula Dependencies in a Workbook with Python — finding which inputs a result depends on.
Read Formula Results with openpyxl data_only — why results need a calculating application first.

More on formulas in workbooks