Rolling 12-month forecast: full case study

Context

This is a Belimo-anchored rolling 12-month forecast model, built end-to-end around the question a controller actually faces during the close: can the next-twelve-months base case be defended, what would change it, and can the file be re-run cleanly when next quarter’s data lands.

Belimo Holding AG is the modelling reference. It is not a client project. The reporting is clean enough — public regional revenue, segment-level commentary, group EBIT and balance sheet — to support a defensible portfolio build without proprietary data.

Project layers

The work is structured in seven layers. Each layer’s outputs feed the next.

1. Tooling. Python 3.12, uv for environment management, pyproject.toml with pinned dependencies, uv.lock committed for reproducibility, git from the start. The project is an application, not a library — no install required, just uv sync && jupyter notebook.

2. Driver model. Region-by-region constant-currency growth assumptions, anchored on FY2019–FY2025 normal-year means. Macro signals only where they hold up to scrutiny:

   • EMEA: Eurostat EU27 construction production. OLS sanity check, n=6, R-squared 0.54.
   • Americas: FRED A33HNO (US HVAC equipment new orders). OLS sanity check, n=7, R-squared 0.57.
   • APAC: trend baseline plus scenario tilt. No regression — country mix not disclosed.

   Regression results are documented but the forecast engine is the driver-based assumption with a documented elasticity. With seven annual observations, regression cannot do real forecasting work; it can only sanity-check the elasticity.

3. P&L forecast and FX translation. Constant-currency revenue projected per region, translated to CHF via SNB FX assumptions:

   CHF_revenue(t) = CHF_revenue(t-1) × (1 + cc_growth) × FX(t)/FX(t-1)

   EUR/CHF for EMEA, USD/CHF for Americas, USD/CHF as a proxy for APAC (country mix not disclosed). Group EBIT margin assumption layered on top; SARON-driven net interest at near-zero base; effective tax rate from FY2024–FY2025 actuals.

4. Working capital, capex, free cash flow. NWC and capex projected as % of revenue, anchored on FY2019–FY2025 history. D&A at steady-state 4.5% of revenue. Cash flow:

   OCF = Net income + D&A − ΔNWC
   FCF = OCF − total capex

   Maintenance capex is set as a share of total capex (default 65%); growth capex is the residual.

5. Scenarios. Base / upside / downside, each scenario chained through both forecast years (FY2026 anchored on FY2025 actual; FY2027 chains from each scenario’s own FY2026 result, not a shared anchor).

6. Sensitivity tornado. Every assumption is held at base, then flexed one at a time and the change in FY2026 EBIT measured. Result is filtered to material movers (max_abs_delta ≥ 0.5 CHFm) and presented as a horizontal bar chart, top 6.

7. Excel workbook. Nine sheets generated from the YAML and the model via xlsxwriter. Live SUM formulas where they help, native Excel charts on the Charts sheet, scenario shading on the summary table.

Discipline rules

Five rules carried through the build:

1. One source of truth. Every assumption lives in assumptions.yml. The human-readable assumptions.md, the Excel Assumption_Log sheet, and the case-study methodology all render from that YAML. No hand-edits to derived files.

2. Provisional flags travel. APAC FX is flagged provisional because country mix is not disclosed. The flag appears in YAML, in the rendered markdown, and in the Excel sheet (amber-shaded row). Anywhere a reader sees the assumption, they see the flag.

3. Materiality, not noise. Tornado entries with under 0.5 CHFm of EBIT impact are filtered out. Saves the reader from chasing rows that move the model by less than rounding.

4. Partial-year hygiene. The KOF construction series stops at Q3 in the latest year because Q4 is not yet published. The function that aggregates to annual marks incomplete years explicitly, and downstream regressions filter on the complete flag. A controller has to handle this on every quarterly close; the model does it once, in code.

5. Scenario chaining. Each scenario’s FY2027 path starts from its own FY2026 result, not the base FY2026. This was validated explicitly because shared-anchor chaining can make scenario tables and charts diverge.

Headline results

Base case, both forecast years:

Scenario fan, FY2027 (CHF m):

Findings worth keeping

Three observations the model surfaced that the case study leans on:

• Group EBIT margin dominates EBIT sensitivity. ±1.5 percentage points of margin is ±18 CHFm of EBIT — bigger than the combined effect of all six revenue and FX flexes individually. Margin discipline is where the EBIT story lives.
• NWC dominates FCF sensitivity, asymmetrically. A stretched NWC scenario (30% of revenue) absorbs 37 CHFm of cash; an efficient scenario (25%) releases 24 CHFm. The downside hits harder than the upside helps. Working-capital discipline matters more for the cash story than margin discipline does.
• FY2027 downside FCF sits above base. Lower revenue means smaller WC build and deferred capex. This is cash preservation under demand softness, not operating outperformance. Saying it correctly matters when the result is in front of a CFO.

Reproducing the workbook

The full project lives in a public GitHub repo: Econowiz/belimo-rolling-forecast-2026-2027. The build pipeline is:

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.ipynb

That regenerates examples/belimo_forecast_sample.xlsx, all six diagnostic charts, and the three case-study hero PNGs. Anyone with uv and Python 3.12 can reproduce the full artifact set without Excel installed.

Scope

The model is bounded by what public data supports cleanly. Two natural extensions sit outside that boundary:

• A second US construction signal (PMI alongside HVAC orders) as an Americas driver cross-check.
• An APAC country-mix decomposition that would replace the USD/CHF FX proxy with a trade-weighted basket.

The assumption log already flags where the model is provisional — a reader sees the limitation directly, not a promise to fix it.