Quick Facts
-
Bronson designed and built a 25-year capital expenditure forecasting tool for the Department of National Defence (DND), covering hundreds of capital projects across multiple business areas within a $50 billion portfolio.
- An automated Alteryx workflow replaced a manual, multi-hour spreadsheet process, cutting each planning run from four hours to ten minutes, a 96 percent reduction in cycle time.
- 15 quality assurance checkpoints are embedded directly in the workflow, catching data issues at the earliest correctable stage rather than surfacing them at the end of the planning cycle.
- The tool produces both Cash and Accrual outputs from a single underlying dataset, closing the reconciliation gap between DND’s internal planning views and external reporting requirements.
- Project-level forecasts, portfolio roll-ups, and exception reports are structured for direct use in DND’s annual capital planning cycle.
- DND capital planning analysts can run scenarios, read QA outputs, and adjust assumptions without requiring data science support.
Project Description
The Department of National Defence manages one of the largest capital portfolios in Canada, with $50 billion in capital commitments spanning hundreds of projects across multiple programme areas. Long-horizon planning is central to DND’s capital function: commitments are made years in advance and revisited annually through a structured planning cycle that must produce both internal budget views and outputs that meet external reporting requirements.
The forecasting process DND relied on was largely manual. Analysts pulled project-level data from several source systems, applied inflation and escalation factors, mapped each project against the long-horizon plan, and assembled separate Cash and Accrual outputs for different reporting audiences. The process consumed significant analyst time each cycle, was difficult to audit, and left data quality issues largely unchecked until late-stage review, when corrections were most disruptive.
DND engaged Bronson to automate the forecasting workflow from end to end, embed quality assurance throughout rather than deferring it to post-run review, and produce planning-ready outputs in both required formats. The tool had to span the full 25-year horizon, handle the complete project inventory, and support both the annual planning cycle and ad hoc scenario requests from planning leadership.
Business Challenge
Building a 25-year capital expenditure forecasting tool for a $50 billion defence portfolio is not a routine modelling task. It required structured automation across heterogeneous source systems, enforceable data quality controls at scale, and outputs that met DND’s internal planning standards and external reporting obligations at the same time.
The specific challenges Bronson addressed:
- Decade-scale forecasting complexity. Covering 25 years of capital activity required handling escalation curves, inflation assumptions, multi-year project timing adjustments, and portfolio rebalancing well beyond the near-term budget horizon, where inputs are less certain and modelling choices accumulate over time.
- Heterogeneous source systems. DND’s project data was distributed across several source systems with differing structures, field naming conventions, and update cadences. Pulling those sources into a consistent forecasting input without altering source-of-truth records required deliberate integration design.
- Manual process throughput constraints. At approximately four hours per planning run, the existing process limited how many scenarios DND analysts could realistically test in a single cycle and made late corrections expensive in both time and capacity.
- Absence of inline data checks. Without quality controls built into the process, issues such as missing values, inconsistent project codes, and escalation factor mismatches typically went undetected until the review stage, where they were most costly to unwind.
- Dual output requirement. Cash and Accrual views serve different DND audiences and different reporting obligations. Producing them from separate processes created reconciliation risk. The tool had to generate both from one shared dataset.
- Audit traceability. Capital decisions at DND’s scale require outputs traceable back to source records and the logic applied at each workflow step, reviewable by finance and audit functions without reconstructing the model.
- Analyst-led operation. The tool had to be run by DND’s existing capital planning team without data science skills, and had to integrate into the team’s established planning process rather than displace it.
DND required a forecasting capability that was faster, more reliable, and more transparent than the manual process, while maintaining the rigour that a portfolio of this scale demands.
Our Solution
Bronson structured the engagement as a focused Alteryx tool development program, organized across seven workstreams:
1. Process Mapping and Requirements Definition
Bronson mapped DND’s existing manual forecasting process with the capital planning team, documented the contributing source systems, identified the inputs required at each workflow stage, and defined the Cash and Accrual output specifications for both internal planning and external reporting.
2. Alteryx Workflow Architecture
Bronson designed the core Alteryx workflow to span the full 25-year horizon across DND’s complete project inventory. The architecture handles escalation and inflation assumptions, project timing adjustment logic, portfolio-level aggregation, and exception routing.
3. Source System Integration
Bronson connected the workflow to DND’s multiple source systems, reconciling field structures and naming conventions across systems and ensuring the tool pulls current data on each run without writing back to or overwriting source records.
4. 15 Embedded Quality Assurance Checkpoints
Bronson built 15 quality assurance checkpoints into the workflow at the stages where specific data quality issues are most likely to occur: missing fields, out-of-range values, inconsistent project identifiers, and escalation factor mismatches are each intercepted before they advance further into the plan.
5. Cash and Accrual Output Generation
Bronson built the output layer producing both Cash and Accrual views from the same underlying dataset, formatted for direct use in DND’s annual planning cycle and structured to satisfy external reporting requirements without a separate reconciliation process.
6. Project, Portfolio, and Exception Reporting
Bronson built project-level forecasts, portfolio-level roll-up reports, and exception reports that surface projects requiring planner attention. The reporting suite supports DND’s standard annual cycle and ad hoc scenario requests from planning leadership.
7. Knowledge Transfer and Adoption Support
Bronson provided documentation and structured walkthroughs enabling DND’s capital planning team to operate the tool independently: running the workflow, interpreting QA checkpoint outputs, and adjusting assumptions for scenario analysis without specialist support.
Key Deliverables
- 25-Year Capital Expenditure Forecasting Tool – The completed Alteryx workflow automating long-horizon capital forecasting across DND’s full $50B project portfolio over a 25-year horizon.
- Source System Integration Layer – Connections to DND’s multiple project data source systems, reconciling structures across systems and preserving source-of-truth records on every run.
- 15 Embedded QA Checkpoints – Data quality controls built into the workflow at the stages where each issue category is most likely to occur and least costly to address.
- Cash and Accrual Output Generation – Dual-format output capability producing both planning views from one shared dataset, eliminating post-run reconciliation between DND’s internal and external reporting.
- Project, Portfolio, and Exception Reports – Project-level forecasts, portfolio aggregations, and exception reports supporting both DND’s standard planning cycle and scenario analysis requests.
The Impact
Bronson’s engagement replaced a constrained, manually intensive process with an automated, auditable forecasting capability built to the speed and scale DND’s capital portfolio requires. The outcomes delivered:
- 96 percent reduction in planning run time, from four hours to ten minutes per cycle, freeing DND analyst capacity for scenario modelling and capital decision support rather than data preparation.
- 15 inline quality assurance checkpoints that intercept data issues during the workflow, substantially reducing the frequency and cost of corrections that previously surfaced only at late-stage review.
- Cash and Accrual outputs from a single source of truth, removing the reconciliation burden between DND’s internal planning views and external reporting and reducing the risk of discrepancies between the two.
- Full 25-year, portfolio-wide coverage across hundreds of projects, supporting DND’s annual planning cycle and ad hoc scenario requests without additional manual effort.
- Audit-grade output traceability linking each forecast figure to its source record and the workflow logic applied, accessible to DND finance and audit reviewers without model reconstruction.
- Analyst-operated tooling that requires no data science skills and integrates into DND’s established planning cycle rather than displacing it.
With the forecasting tool in place, DND can evaluate more scenarios per planning cycle, catch data quality issues before they reach the plan, and produce the long-horizon capital forecast its investment decisions depend on in a fraction of the time previously required. The tool gives DND’s capital planning function the speed, traceability, and dual-format output capability that a $50 billion defence portfolio demands.
Background Clearance Fee Structure Redesign and Cost Recovery Modelling for the Ottawa Police Services
Financial Management and People Management Data Strategy for Environment Canada
Ballast Water Reporting Forms Data Extraction and Transformation for Transport Canada
Survivors Circle for Reproductive Justice Registry Database Development
