Problem
A public policy organization had embarked on a comprehensive project to model how affordable it was for a family to live in various counties, cities and towns throughout New York State. A highly detailed model was developed in Excel that took dozens of expense factors into account and then determined how much income would be required to live in the particular locality. The model incorporated pricing data specific to the given locality as well as regional tax policy differences.
What started as a single Excel file over time grew to over 250. Any particular locality could be compared to any other one, but only via opening two or more Excel files simultaneously. More problematically, the model itself got increasingly sophisticated, but as the number of files grew, keeping them all consistent with each other became an impossible task.
Solution
A ClearFactr solution was developed that replaced all of these Excel files with a combination of a single model and hundreds of scenarios. The ClearFactr team was able to build a custom importer to create all the scenarios from the universe of Excel files because they were reasonably well-structured and consistent in how they modeled the input data.
Key Results and Benefits
- The organization regained its ability to improve the model incrementally and have all localities pick the business logic changes in a consistent way.
- Explaining how the model worked to the org’s constituents became dramatically easier. The new model was developed in a way that maximized the ability for the formulas to render nicely in natural language. Likewise, the cell ancestry features made following business logic easier than ever.
- Using the scenario comparison tool’s automatic-graphing feature, up to six localities could be easily compared at a time on various aspects of the model.
- Note that newer proprietary functions like CFSCENARIOVIEW would now enable customizable subsets of scenario values to be made in grid format, via a single function call.
- Changes to the business logic over time were essentially self-documenting via using the versioning tools and its own comparison features.
