Repository overview. The full implementation, including raw extract notes, the executed notebook, and the generated workbook, is on GitHub: Econowiz/belimo-rolling-forecast-2026-2027.
Repository structure
belimo-rolling-forecast-2026-2027/
├── README.md
├── sources.md # every input source with date pulled
├── assumptions.yml # single source of truth for assumptions
├── assumptions.md # generated from YAML (do not hand-edit)
├── pyproject.toml # uv-managed deps; package = false
├── uv.lock # committed for reproducibility
├── notebooks/
│ └── forecast.ipynb # 33 cells: setup → drivers → P&L → cash → scenarios → Excel → heroes
├── src/
│ ├── __init__.py
│ ├── forecast.py # model: drivers, P&L, FX, working capital, cash flow, scenarios, sensitivity
│ ├── render.py # assumptions.yml → assumptions.md
│ ├── excel_writer.py # assumptions.yml + model → 9-sheet workbook
│ └── case_study_charts.py # three public-facing hero PNGs
├── data/
│ ├── raw/ # untouched downloads, dated
│ └── processed/ # cleaned CSVs, the model reads from here
├── examples/
│ ├── belimo_forecast_sample.xlsx # committed sample workbook
│ ├── case_study_revenue_forecast.png # hero 1
│ ├── case_study_sensitivity.png # hero 2
│ ├── case_study_cash_resilience.png # hero 3
│ ├── emea_driver.png # diagnostic chart
│ ├── americas_driver.png
│ ├── apac_driver.png
│ ├── pnl_forecast.png
│ ├── cash_flow.png
│ └── scenarios_and_sensitivity.png
└── output/ # generated outputs (gitignored)Key modules
src/forecast.py
The model. Driver-based forecast with regression demoted to sanity check.
@dataclass
class PnLForecast:
year: int
scenario: str
revenue_by_region_chf_m: dict[str, float]
revenue_total_chf_m: float
ebit_margin_pct: float
ebit_chf_m: float
net_income_chf_m: float
# ...
def assemble_pnl(
*,
prior_revenue_by_region_chf_m: dict[str, float],
cc_growth_by_region_pct: dict[str, float],
fx_factor_by_region: dict[str, float],
ebit_margin_pct: float,
net_interest_chf_m: float,
effective_tax_rate_pct: float,
year: int,
scenario: str,
) -> PnLForecast:
"""Per-region: CHF_revenue(t) = CHF_revenue(t-1) * (1 + cc_growth) * fx_factor.
fx_factor = FX(t) / FX(t-1), CHF per unit foreign currency.
"""
# ...
def assemble_cash_flow(
*,
pnl: PnLForecast,
prior_nwc_chf_m: float,
nwc_to_revenue_pct: float,
capex_to_revenue_pct: float,
da_to_revenue_pct: float,
maintenance_share_pct: float,
) -> CashFlowForecast:
"""OCF = NI + D&A - delta NWC; FCF = OCF - total capex."""
# ...
def sensitivity_tornado(
*,
assumptions: list[dict],
rev_anchor: dict[str, float],
fx_anchor: dict[str, float],
nwc_anchor: float,
target_year: int = 2026,
target_metric: str = "ebit_chf_m",
) -> pd.DataFrame:
"""Hold every assumption at base; flex one at a time; return ranked deltas.
Filtered downstream to max_abs_delta >= 0.5 CHFm for materiality.
"""
# ...src/excel_writer.py
Generates the 9-sheet workbook from assumptions.yml and the model. Engine: xlsxwriter (chosen over openpyxl for cleaner native charts and a write-only build pattern that fits the generate-from-YAML flow).
def write_workbook(
*,
output_path: Path | str,
assumptions: list[dict],
rev_history_df: pd.DataFrame,
group_metrics_df: pd.DataFrame,
macro_annual_df: pd.DataFrame,
macro_monthly_df: pd.DataFrame,
pnl_df: pd.DataFrame,
cf_df: pd.DataFrame,
tornado_df: pd.DataFrame,
as_of_date: str,
project_name: str = "belimo-rolling-forecast",
) -> Path:
"""One private helper per sheet; single _make_formats() table reused
across sheets for consistent number formats, headers, and scenario shading.
"""
# ...src/render.py
Renders assumptions.yml into the human-readable assumptions.md. The Excel Assumption_Log sheet uses the same YAML source via excel_writer.
def render_assumptions_md(
yml_path: Path | str = "assumptions.yml",
md_path: Path | str = "assumptions.md",
) -> None:
"""assumptions.yml is the single source of truth.
assumptions.md is a generated artifact — do not hand-edit.
"""
# ...src/case_study_charts.py
Three single-panel hero PNGs at 200 DPI for the case-study page. Higher DPI and larger fonts than the notebook diagnostic charts, single-panel narrative composition, friendly assumption labels in the tornado.
def hero_revenue_forecast(*, group_metrics_df, pnl_df, output_path) -> Path: ...
def hero_sensitivity_tornado(*, tornado_df, output_path, top_n=6, materiality_floor=0.5) -> Path: ...
def hero_cash_resilience(*, group_metrics_df, cf_df, da_to_revenue_pct, output_path) -> Path: ...Reproducing the workbook
git clone https://github.com/Econowiz/belimo-rolling-forecast-2026-2027.git
cd belimo-rolling-forecast-2026-2027
uv sync
uv run jupyter nbconvert --to notebook --execute --inplace notebooks/forecast.ipynbThat regenerates:
examples/belimo_forecast_sample.xlsxexamples/case_study_*.png(three hero charts)examples/{emea,americas,apac}_driver.png(diagnostic charts)examples/pnl_forecast.png,cash_flow.png,scenarios_and_sensitivity.pngassumptions.md(regenerated from YAML)
Open the sample workbook in Excel like any standard .xlsx. Generated from Python via xlsxwriter.
Dependencies
[project]
requires-python = ">=3.12"
dependencies = [
"pandas>=2.2",
"numpy>=1.26",
"scikit-learn>=1.4",
"statsmodels>=0.14",
"matplotlib>=3.8",
"openpyxl>=3.1",
"pyyaml>=6.0",
"jupyter>=1.0",
"ipykernel>=6.29",
"requests>=2.31",
"xlsxwriter>=3.2.9",
]
[tool.uv]
package = falseopenpyxl stays in deps as a passive dependency for read-side sanity checks (verifying the generated workbook). xlsxwriter is the build engine.
The complete repository, including the raw data extracts, executed notebook, and generated workbook, is on GitHub: Econowiz/belimo-rolling-forecast-2026-2027.