Continuing our series on using dashboards to enhance nonprofit financial reporting, we would like suggest a basic working categorization of dashboards as a frame of reference. Staying within the realm of what is practical for most organizations, there are basically two types:
- Externally generated using Excel
- Internally generated from the accounting software
In this article, we’ll take a closer look at externally generated dashboards using Excel, the most common methodology. In a later article, we will discuss what you can expect from more robust accounting systems in using dashboards built-in to the software itself.
Using Excel to display financial and nonrelated financial data in tabular and graphical forms is relatively easy to learn and use repetitively as part of the financial reporting package presented to management or the Board of Directors. Managers and Board members generally do not have a strong financial background and tend to get lost in detailed tabular financial statements.
We advise our clients to take a top-down approach in financial reporting and to use dashboards at the very top of the hierarchy to provide a snapshot of the organization’s financial health. The dashboards may contain summary tables and graphs of the financial position compared to the previous year, actual vs. budgeted revenue and expenses compared to the budget, cash balances, and key ratios. As we stressed in the first article, the content depends on the needs and priorities of the organization. Although the financial reports presented to the Board are the responsibility of management there content and format should be fully vetted by the BOD Finance and Executive committees. It has been our experience that reporting summary financial information using dashboards at the Board level elevates the dialog to what is exceptional, both the good and the bad. Of course as questions arise, management must be prepared with more detail information. The Finance and Executive committees would have the benefit of reviewing the complete financial reporting package prior to presenting it to the full Board.
Constructing dashboards with Excel is relatively easy for an experienced Excel user with some basic knowledge of its graphing capability. Normally, the various financial statements are exported from the source accounting software into an Excel workbook consistently from month to month in terms of order and alignment of rows and columns. Each financial statement occupies a ‘named’ tab or worksheet in the workbook. Dashboards can be inserted among the exported tabs and in the appropriate order for printing, but again consistently from month to month. That way the source financial reports are always in the same named worksheet and the dashboard always refer to the same cells in source worksheets.
We have provided an example set of financial statements that contain dashboards. Download the workbook entitled Non-Profit B Financial Reports, and review the tabs named SnapshotFinPos and SnapshotRevExp. Click on the cells in the summary tabular reports and note that they contain formulas that pull information from the more detail, exported report worksheets. Note the graphs are based in the example on the tabular summaries, although they could be constructed from data anywhere in the workbook. The important thing to remember is that the tabular summaries and graphs on the dashboard worksheets always reference the same cells in the source worksheets. When the format of a source worksheet changes, hopefully not too often, the dashboard may have to be modified as well. Otherwise the only monthly maintenance required to the graphs should be cosmetic (e.g., legend labels).
Please contact us if you have any question or suggestions about dashboard or require assistance in getting started