Quick Facts
- The tool which was used in the preceding capital forecasting exercise took roughly 4 hours to compile project data for capital forecasting purposes. The Alteryx tool which was developed by Bronson completed a full project load from start to finish in less than 10 minutes, and could develop modified scenarios in less than 5 minutes. This then enabled exploring ‘what-if’ scenarios in real time whereby various options for project timing and implementation could be explored and evaluated.
- The client also indicated that the QA reporting functionality was very effective at facilitating the identification and resolution of data quality issues and irregularities. Troubleshooting these issues had been significantly more onerous in previous forecasting exercises.
Project Description
Bronson was retained by 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.
Business 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 number of individuals involved in data entry resulted in inevitable data schema inconsistencies, omissions and errors which had to be addressed through the tool.
In addition to this raw data input, multiple other forecasting variables had to be entered into the tool, including forecast inflation and foreign exchange rates, assumptions relating to amortization schedules and contingency factors for various project components.
Our Solution and Outcome
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.
In order to address observed data quality issues, a series of 15 Quality Assurance checkpoints were configured to ensure that required data points were both present and valid for each project as they were loaded into the master dataset. Detailed reporting was generated for projects failing any one of these checks, with descriptions of observed irregularities to facilitate troubleshooting. Projects had to pass all 15 checkpoints prior to being loaded 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.
