Version Chaos
How many meetings have you attended that open with a mechanical discussion of which spreadsheet file you’re all talking about? Have you ever worked late into the night, only to realize you were working on the wrong version of a file? What are the risks to your business of various decision-makers all operating off of different versions of data and/or business logic? And if you need to change that data or business logic quickly and uniformly, could you do it?
Read More
These are just some of the reasons you need a Single Source of Truth (“SSOT”). But in addition to that, you need to control who can do what, to what, when, and how. This implies permissioning and processes that manage how the SSOT changes over time.
Relevant Case Studies
Eye-watering Complexity
Even within a Single Source of Truth, you might have variations of a given model that are only different in minor ways. Perhaps 95% of the business logic wants to be the same between ten different models. Coding up those variations between all the different versions can be done within the formulas themselves, but it will lead to all kinds of conditional logic that increases the size of the spreadsheet, slows down its compute speed, and makes things harder to understand.
Read More
Within any given model, performing sensitivity analysis, or creating and comparing scenarios can be complicated – so much so that many (most?) people simple don’t do it. With ClearFactr, these things are built-in and support simultaneous, collision-free workflows. You’ll wind up with models that are smaller, faster, easier to understand, easier to maintain, and present dramatically less operational risk.
Relevant Case Studies
Mysterious Workflows
Have you ever been asked to make a copy of a spreadsheet for someone, only to wonder later if they’ve actually done anything with it? If ten prospects asked for a quote on the same thing, who should you follow up with first, and how? Have you ever forgotten who actually has access to the spreadsheet, and with what type of access?
Read More
These are just some of the reasons ClearFactr provides a Model Engagement panel for every model that you have access to. Some people refer to this panel as ClearFactr’s “Virtual Data Room”, recreating how the manager of an old-time physical data room might control who has access to what, who’s been in the room and for how long, what items in the room they seem to be interested in, etc.
This panel is also the place where fine-grained, per-user, per-model permissioning is actioned. Roles for users span from read only, through limited editing, to the ability to make structural changes and more, and also include the abilities to merge scenarios and export the model back to a native Excel file. Management and auditing of the latter is crucial as this an opportunity for data and business logic to be transferred from a highly-controlled environment to an uncontrolled one.
Relevant Case Studies
Trapped Data
From the moment it was easy to organize data into the rows and columns of a spreadsheet grid, it was easy to use spreadsheets for data management. Throw in breakthroughs like import/export and copy/paste, and suddenly you could never have enough rows in a spreadsheet file. For a single maintainer and user of that data, perhaps this was truly incredible. But what if someone else wanted that same data, not just to enforce a Single Source of Truth, but for an entirely different purpose? And what if that purpose was computationally intense, something that everyone agreed would ideally NOT be done in a spreadsheet? You could move the data to a database, but then where would it leave the spreadsheets and the value they were delivering to their audiences? And who would do the custom coding to and from the database, and what might that cost, in time and money?
Read More
A common use-case for legacy spreadsheets involves processing a portion of a much larger dataset that is held within one or more sheets/tabs in the model. Comparatively, the actual calculation portion of the workbook is only a small fraction of the total number of cells in the model. Many of these cells contain conditional logic that serve to filter down the portion of data being run through “the model.” There may even be intermediary stages of processing performing this filtering and aggregating in successive stages, physically structured across multiple sheets. Over time, more and more data might get added to various portions of the workbook. The entire workbook itself might get copied into multiple files simply to isolate certain datasets and/or for file-size reasons. Once that stage is hit, maintaining a consistent model across multiple files becomes increasingly difficult.
With ClearFactr, all audiences and consumers of that data can be happy.
ClearFactr’s approach for this situation would involve moving as much of this data into one or more tables within one or more databases in a Cloud Data Warehouse.
- For inspecting uniquely identifiable items in these tables, ClearFactr’s CFSQL function, used in “find” mode, brings back the desired properties of a single table row.
- For subsets up to the row count limits of a given sheet, CFSQL’s “select” mode is used. Ideally, as much of actual data ordering, summarization and aggregation work is performed directly on the database itself – that’s what those products were built for.
- More advanced data processing can be done with stored procedures that can be parameter-driven from ClearFactr using specific cell values. These cell values can be hand entered, or formulaically-derived, even from other upstream CFSQL calls.
When CFSQL returns the data in the form of a two-dimensional grid, additional modeling with that data can be performed via traditional formulas and functions.
Contact Us to see this in action.
Trapped Logic
Imagine a large piece of custom software developed and delivered over a long period of time. As the needs of the business change, so will the software. Many teams pride themselves on being able to turn around simple fixes or upgrades in a day or two, but many still take weeks or more. And then you might have smart, curious end-users who always wanted to better understand “what the system is doing, ” and even to fool around with those behaviors in a safe manner. They might even try to replicate part of it in a spreadsheet!
Read More
With ClearFactr, the end-users and the custom software developers can decide what parts of the overall “system” should be safely exposed to the end users through a familiar spreadsheet interface – and yet still drive that same larger system. These portions of the overall system are implemented via one or more ClearFactr models, and the custom software interacts with these models via the ClearFactr APIs.
Then, with fine-grained permissions and full auditability, the end-users themselves can modify portions of the overall system, simply by making changes to the ClearFactr model(s). The moment a new version of the ClearFactr model is saved – with approvals as necessary – all users of the overall system will start to get the corresponding new behaviors. All of this is done without needing to annotate the model, register it into some central service, recreating API endpoints/services, possibly recompiling the larger system, etc.
Relevant Case Studies
Analytical Dead Ends
How many times have you seen a model with “base”, “best” and “worst” cases, or variations thereof? And what did it take to get the model to compute and compare those different cases? Many times, especially with highly complex models depicting conditional events, simple variations just won’t properly explain what could happen, or the chances of it happening. Then, getting the inputs just right so that some optimal base-case was computed, perhaps subject to some constraints, can represent a nightmare for the spreadsheet developer, and maybe even the custom software developer.
Read More
Monte Carlo simulation and optimization has been a long-standing solution to these needs, but it was typically only available to the spreadsheet user via expensive plugins. Great for you if you were able to buy that plugin, but possibly game-over if your spreadsheet audience wanted that same power, but didn’t have the plugin.
Because of the unique power of these tools, ClearFactr has had them since day one. Like the other ClearFactr analytics, every user can use them simultaneously without affecting other users.
Contact Us to see this in action and learn more.
