Bronson designed and built the tool as a suite of modular Alteryx workflows that separated data loading from scenario parameter application. The work was organized into the following streams:
1. Source Data Compilation Workflow
Bronson built an Alteryx workflow to compile capital project data from across DND’s contributing subject matter experts. The compilation workflow ingests, structures, and prepares hundreds of project submissions for downstream forecasting.
2. Fifteen Automated Data Quality Checkpoints
Bronson configured 15 sequential data quality assurance checkpoints within the workflow. Each checkpoint verifies that required data points are both present and valid for each project before it can be loaded into the master Capital Forecasting dataset. Detailed reporting is automatically generated for any project failing any checkpoint, with specific descriptions of irregularities to facilitate rapid troubleshooting. Only projects that pass all 15 quality checkpoints are admitted to the master dataset.
3. Financial Modelling Layer
The tool was built to handle multiple layers of financial complexity, including inflation forecasts, foreign exchange rates, amortization schedule assumptions, and contingency factors for various project components. Outputs are produced on both Cash and Accrual accounting bases.
4. Scenario Architecture
Bronson separated source data loading from scenario parameter application in the workflow architecture. This is the key design decision that enables real-time scenario analysis. Analysts can change scenario inputs without re-running data compilation.
5. Real-Time Scenario Analysis
Analysts can accelerate or delay implementation timing for specific projects, modify contingency factors on a project-by-project basis, or temporarily exclude and re-introduce selected projects, all in real time. A modified scenario can be generated in under five minutes.
6. Performance Validation
The completed tool runs a full project data load in under 10 minutes, a 96% reduction from the previous four-hour process. DND confirmed that the automated QA reporting was highly effective at identifying and resolving data quality issues, a task that had been significantly more onerous under prior forecasting exercises.
Key Deliverables
-
Alteryx Capital Expenditure Forecasting Tool – A suite of Alteryx workflows that compiles project data and produces 25-year capital forecasts on both Cash and Accrual bases for DND’s Strong, Secure, Engaged investment plan.
- Source Data Compilation Workflow – An automated Alteryx workflow that ingests, structures, and prepares capital project data submitted by dozens of subject matter experts across DND.
- 15-Checkpoint Data Quality Assurance Framework – A configured set of 15 sequential data quality checkpoints embedded in the workflow, validating presence and accuracy of required data points before any project enters the master Capital Forecasting dataset.
- Automated QA Exception Reporting – Automatically generated reporting for any project failing any of the 15 checkpoints, with specific, actionable descriptions of each irregularity to support rapid remediation.
- Financial Modelling Layer – Embedded financial logic handling inflation forecasts, foreign exchange rates, amortization schedule assumptions, and contingency factors applied across project components.
- Cash and Accrual Forecast Outputs – Capital forecast outputs produced on both Cash and Accrual accounting bases, structured to meet the requirements of different DND and Treasury Board audiences.
- Real-Time Scenario Analysis Capability – A scenario layer that allows analysts to accelerate or delay project timing, modify contingency factors, and exclude or re-introduce projects without reloading source data, generating modified scenarios in under five minutes.
Performance Benchmark Documentation – Documentation confirming the tool’s performance against the prior four-hour baseline, including the validated reduction to under 10 minutes for full data compilation.
The Impact
Bronson’s work converted a slow, manual capital forecasting exercise into a fast, defensible, scenario-ready capability for the Department of National Defence. Specifically, the engagement delivered:
- A 96% reduction in capital forecasting data compilation time, from approximately four hours to under 10 minutes, enabling real-time capital planning at DND scale.
- Real-time scenario development in under five minutes, allowing analysts to explore investment timing, contingency, and project-exclusion scenarios without reloading source data.
- Fifteen automated data quality checkpoints that identify irregularities across hundreds of multi-contributor project submissions with detailed per-project remediation reporting, confirmed by DND as significantly more effective than prior manual troubleshooting.
- Cash and Accrual basis outputs that meet the reporting requirements of different DND and Treasury Board audiences from a single tool.
- A defensible analytical foundation supporting DND’s 25-year Strong, Secure, Engaged Defence Policy investment plan.
The result is a working capital planning capability rather than a static modelling exercise. DND can now compile data, validate quality, model scenarios, and report on both accounting bases in the same working session, supporting investment planning at the speed and scale that a 25-year defence policy demands.
