Creating Decision Support Systems with Excel
Our organization, the Pacific Institute, has traditionally researched and written reports on public policy and environmental issues. In recent years, we’ve gotten more involved in creating tools for others to use to help them make better management decisions. This type of software sometimes falls under the heading of “decision support systems” or “expert systems”.
One of the first Excel-based programs the Institute created (before I joined the staff) was the Water-to-Air Model which helps water utilities to understand how their operations contribute to air pollution, and how water conservation can reduce pollution and bring about air quality improvements. I gained a lot of experience with creating Excel-based models in grad school at Tufts. For my MS Thesis, among other things, I created an Excel-based hydrologic and water quality model for predicting bacteria concentrations in lakes and rivers (download the zipped .xls file here).
The model was created in Microsoft Excel, which is a decent platform for this type of software. It’s ubiquitous: almost everyone in industry or government has a copy. Users know the interface: they already know what the menus and buttons do, and don’t have to learn a whole new set of conventions. It overs certain advantages to the programmer as well. You can use Excel’s built-in capabilities rather than creating routines for data entry, financial analysis, charting, etc.
Excel is not perfect for these applications. Recently, I’ve become more and more convinced that the best way to deliver this kind of software is via the web. Spreadsheets are hard to share and can usually only be used by one person at a time. It’s hard to make updates and push new versions out to users. It’s also hard to collect information about who is using your software and how they are using it.Lastly, Excel is not free, and not everyone has it, especially in poor countries. The web can overcome many of these obstacles, which is one reason we’re moving increasingly in that direction, for example with the forthcoming Household Water-Energy Calculator or the prototype Community Choices Water and Sanitation Decision Support System.
Still, I think that Excel will continue to be used for these kinds of applications for many years to come. It will continue to be of use for prototyping and what some developers call rapid application development. Here are some links and resources I’ve collected for creating Excel-based decision support systems:
- Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel and VBA. I have this book and it is quite good for the arcana of how to do just about everything with Excel.
- A Master’s thesis by a Masters student at the University of Florida in 2004 titled, Principles of Designing and Developing Spreadsheet-Based Decision Support Systems.
- A book called Developing Spreadsheet-Based Decision Support Systems. No idea if it’s any good, but the companion website has a bunch of powerpoint presentations on the topic that look interesting.