I have to present a lot of data to people, but I find PowerPoint, a corporate workhorse, to be a poor vehicle for presenting data. I am not an acolyte of Tufte — a famous PowerPoint critic — I just don’t like PowerPoint presentations. Recently, I had to prepare a presentation on the cost of various options for a product. I decided that I wanted to create selectable product scenarios that would create a dynamic bill of material using Excel’s AutoFilter feature. AutoFilter is a really useful feature, but it does not allow me to predefine scenarios. Here is where a bit of Visual Basic for Applications (VBA) helped out. This spreadsheet was easy to present to a room full of staff, and it allowed our Marketing and Systems folks to try out product feature options themselves and to see just how much different features cost.
The basic idea is simple:
- create a bill of material with parts assigned to specific features (on tab called AutoFilter).
- create lists of scenarios consisting of feature sets that make sense (on tab called Definitions)
- assign the scenarios to radio buttons.
Here is the link to the file. I hope you find it useful.