Case Study One: Month and Year‐to‐Date Reporting
Neale Blackwood · 2014
The first case study demonstrates techniques that can be used together to produce month and year-to-date reporting. The data that are used are from a report from the accounting system. The case study goes through the various processes that you need to identify to build the reports. Those processes are then demonstrated and explained in the creation of the reports. The various structures of the sheets, tables, and range names and how they interact to provide a stand-alone reporting model are explained. The model includes actuals, budgets, forecasts, and last year's data. The model also includes statistics. Some of the data that come into the file require data cleansing, and this is done with formulas. Structured range names are used throughout the model. A single formula in one of the reports will extract actuals, budgets, forecasts, and last year's figures. This is enabled through structured range names and helper cells. The dashboard is created using Sparkline charts.