Quick Summary
The tool which was used in the preceding capital forecasting exercise took roughly 4 hours to compile project data for capital forecasting purposes. In comparison, the Alteryx solution developed by Bronson was able to accomplish a complete project load from start to finish in under ten minutes, and it could generate modified scenarios in less than five minutes. This efficiency allowed for real-time exploration of ‘what-if’ scenarios, facilitating the evaluation of various options regarding project timing and implementation.
Furthermore, the client noted that the QA reporting functionality was highly effective in identifying and resolving data quality issues and irregularities, making troubleshooting significantly less burdensome compared to earlier forecasting exercises.
Project Overview
Bronson was retained by the Department of National Defence (DND) to implement process improvement for capturing, aggregating and reporting on 25 year capital expenditure plan as part of DND’s Strong Secure Engaged (SSE) Defence Policy.
The Challenge
The main challenge was efficiently and effectively aggregating data for hundreds of projects with estimates developed by dozens of separate subject matter experts. The large number of contributors to data entry led to unavoidable inconsistencies in data schemas, as well as omissions and errors that needed to be rectified within the tool.
Alongside this raw data input, several other forecasting variables were required for entry into the tool, such as anticipated inflation rates, foreign exchange rates, assumptions regarding amortization schedules, and contingency factors pertinent to various components of the projects.
Our Solution and Impact
Bronson developed a series of workflows using Alteryx which compiled data for these projects and produced capital forecasts on both a Cash and Accrual basis for accounting purposes to satisfy the requirements for multiple target audiences.
To address observed data quality issues, a comprehensive set of 15 Quality Assurance checkpoints was established to ensure that all necessary data points were present and valid for each project before they were loaded into the master dataset. Detailed reports were generated for projects that failed any of these checks, including descriptions of the irregularities to facilitate efficient troubleshooting. Only projects that cleared all 15 checkpoints were permitted entry into the master Capital Forecasting dataset.
The tool also enabled scenario development without having to reload or modify the source project data. This allowed analysts to explore various scenarios by accelerating or delaying the implementation timing for various projects. Contingencies could also be modified on a project-by-project basis, or selected projects could be omitted entirely (or subsequently re-introduced) in favour of other priorities or constraints.
Learn more about the Alteryx platform.
