Excel Collaboration Use Case

Excel is one of the most widely used software applications with an estimated 750 million users worldwide. It provides users with the empowerment to manipulate their data to extract meaning from it without needing advanced technical knowledge. Love it or hate it, Excel is here to stay.

Excel is heavily used within finance departments and to build project finance models. These models involve large sums of money and are used for major projects such as building hospitals and schools. The models are put together by a range of stakeholders who add facts and figures to spreadsheets to combine their knowledge and skills.

Excel is used in a similar way across a broad range of industries, think demand planning, sales forecasting, business planning, and just about every other non-technical department in every industry.

Situation

Before

We’ll stick to the project finance use case, but this scenario fits any team collaboratively working with Excel.

A team of analysts and accountants are working on a large project finance model for the funding of a new hospital. They each have an area of expertise and are working on the same model concurrently.

The master spreadsheet is kept on OneDrive and the team work from that. Often people edit the spreadsheet at the same time and when it is saved conflicts emerge and a duplicate copy is saved on OneDrive. This creates a lengthy task of identifying the changes in each copy and transferring them to the master spreadsheet. To combat this problem, the team lead set the file so that it has to be checked out and checked in. This negatively impacts productivity as only one person can work on the file at a time. Often, a team member forgets to check the file back in which results in further delays.

If mistakes do occur and the finance model becomes corrupt or the data makes no sense, there is rudimentary version control. However, not all team members have this option turned on and not everyone’s versions are automatically saved. It is possible to roll back to a previous version of the spreadsheet, but there is no metadata (other than the date and who saved it) to advise the person rolling back what they are reverting back to.

Approval processes are ad-hoc at best, spreadsheets are often postfixed with a version number and emailed to the team to access, creating duplicate models which inevitably get saved on OneDrive and confuse all stakeholders.

Changes made are not clearly visible and cannot be compared to previous versions so data inaccuracies and mistakes creep into the model and this impacts subsequent data added. There is also little historical context to spreadsheet updates other than emails and individual knowledge, so understanding what and why something has changed is difficult to interpret.

Human error is the largest contributor to spreadsheet errors and with no concrete collaboration features within Excel, project finance models often include inaccurate forecasts impacting the project further down the line.

After

TerminusX has been developed as an extension to Excel. The extension is called VersionXL.

Users install VersionXL on their machine and it is accessible as an option within Excel. The project finance lead then sets up a team on VersionXL and invites the other members to join as collaborators. They download the Excel plugin and log in.

The team lead saves the master project finance model using the VersionXL extension and saves it to the cloud.

Now each team member can download the model locally and make their changes. When they’ve updated their parts of the model, they save it to the cloud. Here they provide some context for their changes, for example, an analyst may add ‘associated land costs added 2022 03 31’.

The team lead or any of the stakeholders working on the spreadsheet can compare different versions and quickly assess what has changed thanks to the metadata with each update. They can visually compare versions against each other to show what data has changed with a color-coded side-by-side comparison to check data accuracy and if necessary, eliminate errors.

Should errors occur and the model becomes corrupt or inaccurate, it is easy to roll back to an earlier version. With the metadata associated with each update, the user can quickly see what they’re rolling back to from the list of options.

If two team members are working on the spreadsheet at the same time and save their changes to the cloud, rather than create duplicate versions, VersionXL provides a conflict report. This enables users to ensure the latest updates are included and work is not overwritten.

Check-out and check-in are not required to work simultaneously on the same file, so there is no impact on productivity or delays when people forget to check the spreadsheet back in.

Sharing the project finance model is easy and doesn’t require emailing multiple versions of the spreadsheet. Other ‘read only’ members can be set up in the VersionXL team so they can download the model at any time to track the progress.

Pain Points

Teams working collaboratively with Excel have three major pain points: time wasted, data inaccuracy, and business decisions being made on these inaccuracies, this is caused by:

Issues working asynchronously on the same file – Collaboration via email, postfix version numbers, OneDrive, and SharePoint meant wasted time with data lost when overwritten, and time spent fixing conflicting duplicates. Due to the check-in and check-out feature that was forced on the team to eliminate work being overwritten, time delays occur as team members wait for each other to complete their tasks, and in some cases, waiting excessively when the check-in process is forgotten.

Inability to see what has changed between updates – Assessing and approving updates is difficult when unable to see what has changed without manually investigating between versions. Data inaccuracies and changes in unexpected places can impact the end result of the spreadsheet’s purpose. When errors or corrupt files occur, rolling back to a previous version is guesswork, the rudimental version control mechanism provides little clarity as to what version the user is reverting back to.

Data inaccuracy – Spreadsheets inevitably get big and with updates occurring over time, ensuring data accuracy is difficult. Mistakes creep in without the ability to see what has changed with each version and as a result, inaccurate data has a cascading impact on future changes making the model, plan, or forecast inaccurate and costly.

How TerminusDB solved the pain points

With TerminusX as the backend to VersionXL, collaborative Exel users are more productive and accurate and the business makes better decisions. TerminusX solves the issues around –

Working asynchronously on the same file

Version control and collaboration – As the backend of VersionXL, TerminusX converts spreadsheets into a database and stores the data as JSON documents. TerminusX is an immutable database with updates stored as deltas. These deltas along with the commit-graph form the backbone of the version control and collaboration features that enable users to work asynchronously on the same file. It also enables features like rollback and plays a part in being able to compare different versions of the same file.

Because each update is timestamped and the data within the deltas can be compared, conflicts are identified and shared with users to prevent work from being overwritten thus ensuring data integrity. When conflicts do arise, users are informed and can view and edit the conflicting versions side by side, aided by color-coded change highlights.

Spreadsheets are saved to the database in the cloud which means there are never any duplicates.

Visibility of changes and improved approval workflows

JSON diff and patch with table diff – TerminusX enables users to compare two JSON documents. The JSON diff and patch functionalities enable user interface-assisted patch operations to let them decide the best course of action for the changes.

TerminusX goes a step further and introduces table diff which generates a patch from two different matrices of values. Tables of values are what you find in an Excel spreadsheet. This enables the different versions of the spreadsheet to be compared side by side. Table diff is not the same problem as a database table difference and is an extremely complex problem to solve, we will have a blog explaining why it is NP-hard in the coming weeks.

The underlying complexity matters not to users, however. They can compare different versions against each other in Excel form to see what has been added, deleted, and edited. This enables users to check each other’s work for accuracy to maintain the integrity of the model.

Improved data accuracy

It is said that technical people dislike Excel, but non-technical people love it. TerminusX and VersionXL keep both sides of the argument happy. Files are stored in a database which provides the benefits that databases bring to storing data, but users interact and manipulate their data as they always do, in Excel.

Due to the version control and diff and patch features on the database side, data accuracy is improved by eliminating overwritten updates and providing the ability to visually see the latest updates to provide a sense check. Human error is the most common cause of spreadsheet inaccuracies, with 88% containing errors according to Forbes, so having this approval step is essential to project finance models, and any other Excel spreadsheet used for making business decisions.

Results with TerminusDB

The implementation of TerminusDB resulted in improved –

Productivity – Teams work faster by concurrently working on the same spreadsheet to build project finance models and other business-critical files. The fact that they do not have to check in or check out files means there are no delays waiting for others to finish their updates. Conflict checking ensures that any updates do not overwrite data that has been saved whilst that person was working on the spreadsheet. Duplicate conflicting files are also not generated, saving further time having to open up both documents and going through the lengthy process of comparing and fixing them.

Data accuracy – Data accuracy is improved due to a number of factors. As covered above, the fact that an update cannot overwrite someone else’s work firstly ensures that data is not lost during the process of building your model, forecast, or plan. Secondly, the ability to review updates and visually compare spreadsheets enables users to check work for accuracy. This is especially useful when the model becomes a large, multi-workbook spreadsheet, where it is not obvious where changes have occurred. Finally, should something go wrong, there is the option to roll back to a previous version, and with the associated metadata, the user has context to understand what they are reverting back to. Alternatively, a user can roll back, find the correct data, and then move forward in time again and fix the problem area.

Better business decisions – The ultimate crux of improving collaborative working with spreadsheets is to derive better business decisions from them. More accuracy leads to better decisions which ultimately helps lower costs and improve profits.