How do you create a business dashboard?
Efficiency question :
Monthly report production rarely relies on a single data source. A report may, for example, combine information from a sales subledger, the accounting system, the customer list and plant production data.
Automation consists of connecting these different sources, consolidating and shaping the data, and then generating the report without repeating the same manual steps every month. The monthly report can then become an on-demand report: once the data is complete, a single command can refresh the information and produce the expected result, including a PDF version.
Key considerations:
The main issue is making sure the data used to produce the report is complete and corresponds to the requested period. The automation should therefore include controls that prevent, for example, a final monthly report from being produced before the month has ended or before all required data is available.
When several sources are combined, the data must also be capable of being grouped and structured consistently before the report is produced.
Challenges:
The challenge is to automate the entire process, from retrieving the data through to producing the final report. Data from different sources must first be consolidated and shaped so that it can be used together.
Control mechanisms must also be built into the refresh process. The objective is not only to produce the report quickly, but also to prevent the automation from generating a report from incomplete data.
Technology:
A particularly effective combination is an Excel utility that uses Power Query and a VBA procedure, commonly called a macro.
- Power Query, integrated into Microsoft Excel 365, can connect to different sources, consolidate the data and shape it before it is used in the report. Its M language can automate the different data-preparation steps.
- A VBA procedure can then complete the process by automating certain actions, including refreshing and generating the report and producing a PDF output.
Developer's advice:
Whenever possible, favour a complete download of the data rather than downloading only the new transactions since the previous refresh.
A differential approach requires the previously downloaded data to be retained and managed correctly, which can create problems when existing transactions are modified or the history becomes incomplete.
When the volume of data allows it, it is often simpler to replace the existing data and retrieve the entire source again at each refresh. Because the process is automated and fast, this approach simplifies the report and allows each refresh to start from a complete dataset.
To develop the skills required for this type of automation, the Excel - Power Query and Power Pivot Level 1 course is a good starting point. Power Query Level 2 can then take data preparation and transformation further. The Management Reports course is also relevant for learning how to structure information and add graphical elements to reports.