Strategy & Operations
Case 02 / 10
Integrated business planning infrastructure
Automated 70% of planning workflows and built a single source of truth across Sales, Finance, Operations, and Supply Chain.
Botrista · 2023–2025 · Architect and owner of the planning infrastructure
- Four functions were planning against four different sets of numbers.
- Sales could promise a deployment before supply chain confirmed it was ready — that gap is now flagged automatically.
- Automated 70% of planning workflows onto one shared dataset.
- Scenario engine for demand shocks, supplier constraints, logistics disruptions.
The numbers
- 70%Planning workflows automated
- 4Functions on a shared dataset
- Google Sheets
- Apps Script
- LLM-assisted development
Open the actual model
Interactive — synthetic dataClick the sheet tabs along the bottom — each one is a real tab from the actual model.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Region▾ | Baseline units▾ | Demand-shock scenario▾ | Δ vs baseline▾ | ||||
| 2 | West | 128000 | 146000 | +14.1% | ||||
| 3 | Central | 94000 | 88000 | −6.4% | ||||
| 4 | East | 111000 | 121000 | +9.0% | ||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 | ||||||||
| 9 | ||||||||
| 10 | ||||||||
| 11 | ||||||||
| 12 | ||||||||
| 13 | ||||||||
| 14 | ||||||||
| 15 | ||||||||
| 16 |
Built on the actual substrate — Google Sheets plus Apps Script, not a purpose-built app. Regions, suppliers, and figures are synthetic; the sheet structure and formulas are representative.
How it fits together
- 1Functional data inputs
- 2Sales commitments
- 3Automated consolidation
- 4Single reconciled dataset
- 5Readiness flagging
- 6Scenario engine
- 7Trade-off outputs
- 8Executive reporting
Read the full case study Hide the case study
The problem
Demand, supply, and financial planning lived in separate spreadsheets owned by separate functions and reconciled by hand. Four functions planned against four different versions of the numbers.
Why it mattered
Integrated business planning only works if functions plan against the same data. Manual reconciliation consumed the planning cycle, and by the time an executive decision was made, the numbers underneath it had already diverged from each other.
What I built
Planning infrastructure creating a single source of truth across demand, supply, and financial data, with automated data flows replacing manual consolidation.
On top of it, a scenario and what-if module for testing demand volatility, supplier constraints, and logistics disruptions — quantifying the revenue, margin, inventory, and service-level trade-offs of each, so executive and Board discussions started from a comparison rather than an assertion.
The function most worth naming is Sales. Sales would commit to a customer’s requested deployment — a menu, a timeline — before checking whether supply chain could actually support it. Folding Sales’ commitments into the same reconciled dataset meant any commitment that outran current supply chain readiness surfaced as a flag automatically, instead of being discovered when a truck failed to show up with the right product. That gave supply chain a real choice: push back on the commitment before the customer heard about it, or start reacting and optimizing procurement immediately instead of finding out at the deployment date.
What I learned
The hard part of a single source of truth is organizational, not technical. Each function had reasons for keeping its own version of the numbers, and adoption required understanding those reasons rather than overriding them.
What I would do next
Move off a spreadsheet substrate onto a proper planning platform. Automate the remaining 30% of manual workflow. Version control on planning assumptions, so a scenario result stays auditable after the assumptions behind it have changed.
Prior employer. Described without supplier, customer, or financial detail. Any figures in a demo are synthetic.