A four-layer design for an Excel-based dashboard

When producing dashboards and reports in Excel it is tempting to build them as a single entity. There are good reasons to take a more modular approach. The modules can be visualised as a series of connected layers;

In their book Excel Dashboards and Reports, Michael.Alexander and John Walkenbach recommend a data model with three layers: data, analysis and presentation.The "analysis" layer is seen as a staging area.

The four level model set out here is similar, but  a separation is made been calculation and collation.

Where possible it is best to avoid having any data calculations in the Presentation Layer, This allows alternative versions of the Presentation to use the same data with limited risk of introducing inconsistencies.

Calculations are made as close as possible to the source Data

Although the data model has four layers, this does not necessarily mean that it is constructed in four different files. The physical design will depend upon the size and complexity of  the dashboard. It would be possible to have all four layers within a single Excel file.

Bibliography

Dashboards for Excel. by Jordan Goldmeier and Purnachandra Duggirala. Kindle Edition. APress 2015

Excel Dashboards and Reports, Michael.Alexander and John Walkenbach .