“One of the main challenges when working with the SSEDP of the Department of National Defence was to align data inconsistencies, omissions, and errors using a series of Alteryx workflows.”
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.
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.