On the surface many think that Microsoft Excel is not the best reporting tool. Some would put forth that it is great for informal,one-off, individual reporting and not for enterprise-wide consumption. And to a certain degree they are right. However, from a non-profit perspective where limited budgets, limited resources, and lack of technical options and even technical know-how are prevalent, Microsoft Excel can be a powerful tool.
First, lets consider why Microsoft Excel can be a great reporting tool:
1. People know it (sorta) already - Many people are already familiar with it and can navigate through the application fairly easily. Some of the ways that I put forth in this post about using Microsoft Excel as a reporting tool are beyond the skills of many users, but because these users are already familiar with Microsoft Excel, the navigation of the dashboards and reports that are produced are still familiar to that end-user.2. Good automation and integration capabilities - Microsoft has done a pretty good job of creating features that allow for easy integration with other databases, workbooks, or data sources. Through VBA (Visual Basic for Applications), Macros, Add-Ins, SharePoint integration, and other data integration capabilities, cumbersome, time-intensive, even very difficult or impossible tasks can be accomplished at the click of a button.
3. It travels well - When offline, a database cannot be accessed. A website cannot be accessed. But if that data has been placed in an Excel spreadsheet, it can go where the end-user goes.
4. Rich interactive and drill-down ability - When the "raw-data" has been placed in a workbook, the reports, charts, and pivot tables from this data come alive, allowing an end-user to drill-down into the details from summarized dashboards.
Next lets look at the challenges of using Microsoft Excel:
1. Technical Competencies Needed - In order to really take advantage of Microsoft Excel, it will likely require someone that has a specific technical skillset that you may not have in-house. But that is likely true for any application that you are going to use to report on The Raiser's Edge.2. Stale Data - Data can become stale and can tell a story from an obsolete point-in-time if not used with caution, or implemented dynamically. So these Excel reports must be understood in the context of when they were created or they must be implemented in such a way that they can be updated or refreshed on-demand.
3. Software Limitations - There are limitations on the software itself, e.g. row limits in a workbook. In addition, the delivery of the workbooks can be challenging - email, network drive, SharePoint library, etc. all present problems because the Excel "file" could be quite large. But again, this is where implementation and design makes a huge difference: allow a user to be able to create the report on-demand.
Powerful features of Microsoft Excel:
1. Get External Data - First of all, Microsoft Excel allows the end-user to connect to multiple different types of data sources and import this data into an Excel spreadsheet. Microsoft Access, Web Services, XML Files, SQL Server, ODBC (Microsoft Query), and other sources can all have data pulled into an Excel spreadsheet, including The Raiser's Edge. This will often require some level of technical competency to be able to use and likely can only be used by those with those technical competencies.2. VBA (Visual Basic for Applications) - This is where it starts to get interesting. An Excel Macro-Enabled Workbook (.xlsm file) can have VBA code embedded which allows for all kinds of automation procedures. This is the first capability that can really improve efficiences and reduce the time it takes to generate a report.
3. Add-Ins - Microsoft Visual Studio can be used to create Excel Add-Ins which allow for application level automation. Whereas a VBA macro is related to a specific workbook (and the code goes with that workbook), an Add-In is available to all workbooks. Think in terms of writing code that automates a task and the task is executed by a user clicking a button on the Excel ribbon (just as if someone was clicking the Sort button).
4. Pivot Tables and Charts - Pivot tables and charts are the actual implementations of "reports". They allow for great analysis and visualization of data. Pie-charts, line-graphs, trends, etc. can all be visualized. In addition, these are living, breathing reports. When a the pivot table is changed or refreshed, the chart changes with the pivot table. Expanding and Collapsing off the analysis of the data is available - all pretty power stuff.
There are more features and maybe I will highlight them in follow up posts.
Finally, some practical examples/ideas for Microsoft Excel reporting of The Raiser's Edge:
1. KPI and metrics - Perhaps you have someone create some VBA code or an Add-In that queries a specific database and provides "results" in the form of KPIs (Key Performance Indicators) and metrics. These KPIs can either be business-driven (e.g. sales/revenue metrics) or data quality driven (e.g. # of records missing an address). This could then be extended to create charts and graphs showing the trend of the KPI over time. For example, you can see how your data quality improves over time if the KPIs and metrics are recorded and saved each time the tool is run. You can then evaluate your strategy to improve data quality to see if it is actually working.2. Improve those manually created excel spreadsheets - Think of a situation where you have to combine multiple spreadsheets and then do a lot of copy and pasting of data from one workbook to another. Then you need to get a report from another database and enter that data. It takes forever. If you are doing the same thing over and over, every time, then let someone write some VBA code to allow this to be automated for you. This saves from human error as well.
3. Report on future/expected revenue (recurring gifts)- Perhaps you have a situation where you want to plan future campaigns, or other revenue generating activities, and but you want to see where expected future revenue will be impacting your organization. An add-in can be created which, at the click of a button, can connect with the database and extract this information, creating pivot tables and charts, slicing and dicing this information giving you the ability to see where this future revenue will be recorded.
Update - I have created a Microsoft Excel Add-In that does just that. You can go to the blog post with details about the Microsoft Add-In and see more information about it and how it works.
4. Report on solicitor activity/details - Imagine you open Excel, go to a new ribbon, and click a button. You are automatically logged into RE, and prompted to select from a list of solicitors. Once you select the solicitor you want a report on, you get a new spreadsheet showing lots of valuable information about the performance of the solicitor: gift summary information, action summary, proposal summary, etc. No queries, no exports, no complicated crystal reports that doesn't give you all you want or need. That is the beauty, you get everything you want to see!
5. Pull data from multiple databases into 1 report - Imagine you've got Raiser's Edge data, as well as data from other third party vendors, and this data has not been pulled together in one database. You could have a report written where the data is pulled together from these disparate data sources and organized in such a way that you get the visibility across these sources. But this also begs the question of why aren't you pulling this data together in a data warehouse or The Raiser's Edge itself before doing the reporting?
Good luck in your use of Microsoft Excel. Let us know if we can help you out or talk you through how you might leverage Excel as a better reporting platform for your organization.
No comments:
Post a Comment