DataConsulting were approached by a client with a need, a need to improve the operation of a critical monthly financial reporting tool.
The reporting tool was spreadsheet based and the underlying issue was it had evolved over the years, requiring constant change to keep pace with the business. It relied heavily on the manual input of data in several sections of the tool, it contained formulas with hardcoded values and the tools operation took a significant time.
The whole process was quickly becoming excessive, frustrating, and time-consuming.
The client’s original reporting tool was built in Microsoft Excel and a lack of flexibility was a driving factor in seeking support to improve it.
The first task was the identification of how key metrics and relevant outputs were formulated and generated, and from what source data. Then using an Agile-like framework, consisting of weekly sprint cycles and weekly progress meetings with the client in question, the work began on developing the new reporting tool.
The decision was made early on to keep Microsoft Excel as the primary application as this ensured familiarity and comfortability with end users.
DataConsulting were able to utilise Excels Power Query, Power Pivot, and VBA Scripting capabilities to develop an improved reporting tool and designed it with a more rationalised, streamlined, easier to manage input process and one with a complete, integrated data model capable of handling the data transformation and preparation process.
The improved reporting tools data model was designed and implemented to handle the vast number of datasets involved, the multitude of data mapping activities, and the pre-defined lookup tables, and ultimately provides more accurate results as outputs are coming directly from the pivot tables that are linked directly to the data model.
Going above and beyond, value was added to the improved reporting tool with the addition of custom ribbon functionality that were more centric and specific to the client’s needs and requirements thanks to our expertise and experience in scripting with VBA.
The final solution was an improved Microsoft Excel-based financial reporting tool that was still contained within a single spreadsheet but now supported by a fully integrated data model that utilises Power Pivot and Power Query technologies. A tool that delivers outputs more accurately, can be updated more conveniently, processes input data more efficiently.
A tool more robust and resilient to future changes and a solution that reduces risk due to being less prone to manual input errors, saves significant operating time, and improves visibility for major stakeholders.
