Collaborative Budgeting and Forecasting

Problem

Many teams and firms use legacy spreadsheets for budgeting and forecasting purposes. Any given spreadsheet file might provide extremely useful analytical and summarization capabilities. But the starting points of the calculations likely need to be hand entered, and perhaps debated between multiple people with different viewpoints. Imagine there’s one person running the process (the “coordinator”) who sends copies of the original spreadsheet to five different people (budget process “participants”): “I need each of you to make your forecasts and requests and send things back by Friday evening.” That coordinator then needs to consolidate the changes from the five files into one place. The coordinator is trying to assemble and maintain a Single Source of Truth.

Inevitably some budget requests are accepted, others are denied, and the process needs to repeat for some reason. For example: “I need each of you to shave 2% from your respective areas – how you do it is up to you.” The coordinator sends the newly consolidated file to each of the five people.

But inevitably, one or more of the five people might use the version of the file that they originally received, edited, and sent back. They reduce their budget by 2% and send back a new version. The problem is that they never picked up additional changes made to their own numbers made by the coordinator and other management. When to coordinator opens this new file, things will be out of sync, and it will be difficult if not impossible to notice how or why. For these and a myriad of other possible reasons, it’s easy to see how this overall situation can turn into a logistical and risk-laden disaster.

Solution

With ClearFactr, a solution to the above would look like this:

1. The coordinator would create the initial version of the model and share it with a Team that the five above people belong to.
2. The coordinator would use the Cell Permissions tool lock down the model and control which budget/forecast items people are able to edit. This could assure that no other “cell editor” can change any of the analytics or summation business logic in the overall budget/forecast.
3. The five users make their budget requests and forecasts via ClearFactr’s Scenario Tool. Each user in theory could propose multiple changes via creating multiple scenarios.
4. There’s still a review/approval/incorporation phase, but this is done via ClearFactr’s Scenario Comparison and Merge features. Each time a Merge is performed, the latest version of the model – the Single Source of Truth – is updated automatically.
5. Note that via Composite Scenarios, the coordinator can look at the combined effects of two or more scenarios simultaneously and selectively merge portions of each.

Key Results and Benefits

1. A Single Source of Truth is seamlessly maintained through the entire process. Everyone is always assured they are starting from the same, blessed, place. Because no files are being slung back and forth, no one can accidentally use the wrong information, or lose the changes made by others.
2. The coordinator can follow the workflows of each of the downstream participants via the Model Engagement panel. Any participants who haven’t yet created any scenarios can be followed-up with as needed.
3. The versioning tools can be used by anyone at any time to monitor what changes have been made, by whom, and when.
4. As changes are incorporated, each of the participants can use the Scenario Comparison tools to see how their changes have been incorporated, with all changes being colored coded and displayed in context of the overall model.