What I built
I built a reproducible Python and SQLite FP&A modeling workbench that decomposes multi-channel Budget vs. Actual (BvA) gross-margin variance into four exact, additive drivers—Volume, Portfolio Mix, Price Realization, and Unit Cost—and connects those historical drivers directly to a rolling four-quarter scenario forecast.
In monthly financial reviews, naive variance formulas often leave an unexplained cross-term “plug” or conflate pure volume growth with unfavorable channel mix shifts. When unit volume grows by 9% primarily in a lower-margin wholesale channel while unit costs inflate, headline revenue rises even as gross margin dollars and basis points compress. This workbench isolates each lever mathematically so commercial and finance leaders can act on the real driver.
Explore the PVM & forecast workbench
The browser workbench lets you inspect a three-channel portfolio ($8.71M Budget GM vs. $8.60M Actual GM, -317 bps margin compression), drill into channel-level Price-Volume-Mix bridges, and toggle between three FY2027 rolling forecast scenarios (Baseline, Commercial Recovery, and Downside Cost Headwind) to see how pricing and mix interventions restore gross margin.
All channels, unit counts, prices, and cost structures are synthetic. The browser controls switch between summaries generated from the pinned Python model rather than executing it in the browser; the same generated data supplies the initial page and interactive views.
Architecture and decisions
- Enforce an exact additive variance identity. Portfolio Volume Effect is evaluated at the budget portfolio average unit margin, and Portfolio Mix captures channel-share shifts weighted by each channel’s margin differential relative to the portfolio average. The four components (
Volume + Mix + Price + Cost) sum to 100% ofActual GM - Budget GMwith zero unexplained plug. - Carry a synthetic baseline into illustrative scenarios. The forecast applies configured growth, price, cost, and channel-weight assumptions across four quarters with fixed seasonal factors; the mix control is a heuristic that can also change total units.
- Persist structured marts for BI and audit. Both the channel-level PVM bridge (
pvm_channel_bridge) and the multi-scenario quarterly projections (rolling_scenario_forecast) load cleanly into SQLite for downstream SQL or Power BI reporting.
My contribution
I designed the additive PVM decomposition math, channel data contracts, rolling scenario parameterization, and verification suite—drawing on financial modeling at Michael Foods and commission reconciliation and process analysis at Best Buy—using the AI-assisted workflow described on the About page.
Scope and verification
Local validation passed 4 unit tests covering cent-level additive closure (Volume + Mix + Price + Cost == Total GM Variance), negative mix detection in the supplied scenario, scenario ordering, and SQLite persistence. This compact modeling prototype uses synthetic data and simplified forecast assumptions; those tests do not establish comprehensive input validation or predictive accuracy. The pinned source revision is available locally and has not been published to GitHub.
Source availability
The reviewed implementation is not publicly available yet. Source publication will follow a separate review; use the synthetic browser walkthrough above in the meantime.
Source snapshot 2690cc8 · Reviewed 2026-10-25. This case study describes that revision; development may continue separately.