Client: Consulting boutique for work and organizational psychology
Type of engagement: Commissioned work
Objective: Redesigning and rebuilding a slow and cumbersome legacy Excel model for automatic zero maintenance slide creation with SlideFab
Situation
A German consulting boutique in the area of work psychology conducts surveys on a regular basis. Therefor it uses an off-the-shelf surveying software and a custom-tailored legacy Excel model for the >30 default survey items. Once the new data extract is added to to the legacy Excel model, the consultants need to manually copy charts and titles from Excel to PowerPoint. All custom survey items not belonging to the default set are treated manually by the consultants. This needs to be done albeit the fact that custom and default items share the very same structure. Categorical analyses are also calculated manually.
Challenge
The existing process is highly time-consuming and error prone. The legacy Excel model was built by creating the output slide for one item first and then by copying and pasting in order to create it for all other items. This led to a huge amount of redundancy. Due to complexity of the legacy Excel model no-one dared to touch it.
Solution
Fortunately, the legacy Excel model was not totally useless, the chart designs could be re-used and it served as reference for calculation correctness. However, in the brand new Excel model all redundancy was removed. As survey items are structurally similar only one calculation area was required. It was built in such a way that SlideFab now iterates the items and creates all slides. Consequently, there is no special handling of the custom items required as they are treated like the default ones. Even better: The new Excel model uses Dynamic Array Formulas and separates raw data from calculation logic in separate files such that no Excel work is required (e.g. adapting formulas, adjusting ranges) when data changes. Also category aggregate slides and comparison groups are supported with no need to change the Excel model.
Results
In the new process all manual Excel adaption steps are removed. Even the special treatment for custom items does not require the consultants to interfere with the Excel workbook anymore. Accordingly, the overall process from exporting raw data to having SlideFab start the slide creation takes only a few minutes. In particular the more complex special analyses and aggregates are now available right away. For a project involving the creation of multiple report presentations with hundreds of slides several days of Excel work can be saved.