FSI – crosspollination – examples
The Green Confidence Index (GCI) is a collaboration between Earthsense, Survey Sampling and GreenBiz. The GCI is an index score that tracks the changes in consumer attitudes and behaviors on the environment, and their perceptions on corporations and other institutions.
FSI automated the monthly process of exporting the survey response data from SPSS to SQL Server, and then generating more than a dozen Excel reports, each with over 50 data points and metrics.
Phoenix Healthcare conducts an international tracking study (now in its sixth year) for a major pharmaceutical client. The study analyzes customer satisfaction of a drug treatment. After the analysis is complete, the deliverable is a 200 slide PowerPoint file for each of 7 countries, full of graphs and tables.
The analyst has the overwhelming job of populating the 300 graphs, 70 tables and hundreds of variables embedded in the text for each PowerPoint file. Not only does this take a huge amount of time but is prone to human error.
The challenge was to find a process that was flexible enough to handle the changing environment as graphs/tables were modified at the client’s request. The automation process was complicated, since of all the Microsoft Office applications, VBA is rarely used to automate PowerPoint.
FSI built an innovative system using Excel and PowerPoint to manage the automatic population of the thousands of variables in each of the seven reports. It was the best solution for this challenging situation.
FSI teamed with its partner Catenate Consulting as Experian’s “spatial analytical” team. During that relationship FSI/Catenate developed a methodology to use customer transaction files to create targeted mailing lists for Experian.
FSI automated the process to run the “hot spot” routine, which analyzed a million-plus record customer list and mapped it for over 250 stores.
The result was to increase return rates for direct mail campaigns for customers such as Williams Sonoma, Borders Books and Pottery Barn.
Here’s a common dilemma: Managers create “rogue” reports that give them metrics they need, and aren’t easily available by the corporate reporting system. They are usually Excel reports that start small, but, over time, grow so big that they’re challenging to maintain. If managers reach out to the IT department for help, they rarely find a warm response.
FSI found just this situation when it was asked to help some managers in two divisions of JP Morgan Chase. They created several detailed reports in Excel that were updated on a weekly or monthly basis. The reports had several data sources, including corporate data (Oracle) and several external data exports as text files.
Staff had to update these reports with a dozen metrics down to the bank branch level. IT had already conducted a formal analysis and said it would be much too expensive to duplicate the reports in the enterprise reporting system.
FSI kept the reports as is, which were quite complex and elegant, and used VBA to automate the update process. The managers had pull down menus or buttons to easily update or manipulate the data.
The time for each update was reduced from a full FTE day to fifteen minutes.