Excel is one of the most popular tools for report creation. Compared to other solutions, it allows for flexibility of the report production. How to leverage spreadsheets to satisfy all report stakeholders?
Why do companies automate Excel reports?
Reports are often delivered at pre-defined timeframes - weekly, monthly, quarterly... That makes them an easy choice for automation, and thus many companies produce them in a highly structured environment.
Standardised reports allow maintaining a high level of quality and comparability when run for different timeframes. They are low-hanging fruit when it comes to scaling company operations. Whether you produce expense reports from templates, monthly KPI reports or write client reports based on data insights; it pays off to optimise them and use automated builders and generators.
Who are the Excel reporting stakeholders?
Usually, there are 3 or 4 types of reporting system stakeholders:
The report developers - those that create the report template or generator. This could be your internal IT team, a skilled VBA programmer from your team or external Excel consultants like us
The report users - those that produce the reports and usually have the responsibility of the system administration
The report data providers - people responsible for originating raw data used in the reports. In larger companies, it's the database architects and managers. In some cases, it may be external providers or the data source may not involve human work when automatically captured
The report recipients - those that will use the reports to make decisions. Usually, the company management, clients or colleagues from other teams.
Different stakeholders interact with the reporting template or generator in various ways. They also require different access and permissions, which stresses the importance of structuring the report creation system. One of the simple and timeless ways is the three-layered system.
Use three-layered report generators
This is a system that separates the raw data from the production and presentation of the report.
It usually contains at least three tabs of three different types:
Raw data tabs - worksheets and tables with unprocessed data which is loaded manually or imported with macros or queries
Technical and administrative sheets - a control panel for the report creator or administrator. Depending on reporting complexity, it may just contain a template and global data settings (e.g. reference tables or standardised reporting assumptions) or detailed setup details regarding reporting cell ranges, conventions and even colour coding criteria
Dashboards and output report - tabs and files that the recipients will see. Depending on the setup, you can either embed the outputs in the same file you use as a report maker or have them saved in a separate file, e.g. in PDF or PowerPoint format.
Carefully planning the breakdown of the report builder in these three categories assures it fits the purpose. Deploying report generators is the next step after optimising the use of templates. To make the reports even more useful for the knowledgeable recipient that may need to investigate specific graphs or data sets, it's useful to assume the top-down approach.
The top-down reporting approach works best in Excel
It is quite obvious yet often forgotten how reports are consumed. They provide the C-suite, decision-makers or clients with a set of concentrated, often visually presented insights they act on. If everything stays within reason, they would probably draw a couple of conclusions. Sales are too low for a specific category- increase the marketing spend for it. Energy expenses are too high - check available providers with procurement.
Quite often, though, the report recipients will like to investigate their areas of interest to get a more granular view on things. And that's where the top-down approach succeeds. It doesn't force an overly complex picture but rather summarises the most important insights and allows for tracking them all the way to the source data.
An example of this approach would be a sales report aggregated on regions and product categories. A neat dashboard with bar and pie charts along line graphs will let the recipients eyeball the sales results. Then, allowing them to access more granular data in separate, hidden tabs will provide insights on the performance of specific products and explain unexpected changes. It will also provide the comfort of having control over data and will save time on requesting additional insights.