Many analysis and planning projects involve information (“x”) that is to be presented “over time”. Whether it’s sales information (such as order volume tracking); accounting information (such as expense tracking); operational information like inventory balances; or financial information like profits or stock prices; trend reports that show monthly, quarterly, or annual changes are frequently the most popular.
Often, designing such a report can be quite straightforward, especially if the data items in the report are mathematically simple (for example, a count of the number of orders received) and/or involve reporting on only a single entity (for example, total volumes for an entire company). However, when reporting requirements are larger and involve reporting on many offices/facilities/companies, and the key reporting metric becomes more complicated, optimally structuring your data to facilitate trend reporting can become much more challenging.
Let’s use an example of a company with numerous sales offices, where the desired reporting metric is to be Average Gross Profit per Salesperson by month, and the data items available to you are Gross Sales by office by month, Cost of Goods Sold by office by month, and average number of salespersons in each office by month. At a minimum, each office will have at least four columns of data in your Excel file for every month – one column for each of the three data inputs (Sales, COGS, and number of salespersons) plus one column with the result of your reporting metric calculation ([Sales minus COGS] divided by the number of salespersons).
It might be tempting initially to put each of those four columns side-by-side in one worksheet, and for reporting on a single time period (or limited number of time periods), an appropriate report can indeed be produced in this manner. However, as the number of time periods increases, this format either requires creating a new worksheet tab in your Excel file for every new month, or replicating another set of four columns in the same worksheet month after month. The first option requires a constant growth in the number of worksheets (one for each time period) while the second option produces a worksheet which rapidly becomes unwieldy – and neither can easily facilitate trend reporting!
A more attractive structure would be to create a separate worksheet for each of the four data items – each sheet with one column of data for each time period. Each sheet would be structurally identical, like a group of matrices, and the metric calculation performed on the fourth worksheet (Average Gross Profit per Salesperson) would combine values from the same cell locations in the other three worksheets. For example, a hypothetical cell E9 on ‘Sheet4’ would have a formula like:
=(’Sheet1’!E9-’Sheet2’!E9)/’Sheet3’!E9
The number of worksheets remains static, and each worksheet grows by only one column every month in order to accommodate the new month of data. Furthermore, the fourth sheet housing the reporting metric will already have the data perfectly formatted for producing a trend report and/or sharing with others!
Leave a Reply