At Effigy we often undertake experiments to assess new technology and ideas. One such experiment was a challenge we set ourselves to see if Google Sheets could be used for Financial Modelling. In this blog post will tell you how we tested Sheets, what issues we experienced and resolved, and the issues that could not be resolved.
Like all sound experiments, we started with a hypothesis, test metrics and acceptance thresholds:
“We believe it’s possible to convert a pre-existing Financial Model from Excel into a functioning model in Google Sheets.”
Test Metric
Financial data
Outputs
Functionality
Acceptable Threshold
All conversion issues can be resolved to 100% accuracy
All outputs can be re-created to same or higher level
All critical functionality available and non critical functionality identified and documented
Test subject
- 3MB 3-way rolling forecast
- 3 entities modelled in AUD, USD and GBP with AUD consolidation
- 50 tabs
- Grouped rows & columns
- 900 named ranges
- Typical excel formulas (OFFSET, MIN, MAX, INDEX, MATCH, H&VLOOKUP, INDIRECT, SUMIFS, SUMPRODUCT, EOMONTH, WEEKDAY)
Testing phase
First, I converted the model into Sheets by right clicking the file from Google Drive:
Once the file had converted, and it was confirmed no #REF!s were displayed, our test metrics were applied.
Financial data reconciled
I used the consolidated cash position and the consolidated net profit to test the model. Initially the figures did not agree. Investigations found that Sheets does not automatically convert blank columns past column Z. Errors occurred as the Excel model referenced columns, which were not converted. Once these columns were added back in, the issue was resolved and the formulas could be corrected.
The financial data now reconciled to an acceptable threshold.
Outputs
Three main areas were reviewed:
- Row and column grouping
- Charting
- Formatting
Grouping, which impacts both output and functionality, was added in a Sheets update in April 2018. Prior to the upgrade, the lack of this feature had made Financial Modelling difficult in Sheets. So, it was a relief to see this has now been added.
Charting features have also improved in recent updates. Our Excel graphs formats use dark backgrounds, which were converted to white, thus losing some titles and labels. However, this issue was easily corrected. Some additional features were identified in Sheets that are currently not possible in Excel, including the ability to place total labels on stacked column charts.
Formatting was generally converted at an acceptable level, however Sheets does not currently include the “Shrink to Fit” feature, meaning some data was obscured. This was resolved by increasing column width.
The output tests were confirmed as consistent to an acceptable threshold.
Functionality
Three main areas were reviewed:
- Update of assumptions
- Hyperlinks
- Page Load (Refresh) speed
The ability to update model assumptions was critical for the experiment to be deemed successful. To test this, I entered a number of scenarios into the Excel and Sheets models and reconciled the financial outputs. Testing confirmed 100% consistency between the two models.
While running the scenarios, I needed to move between various tabs in the file. Given the large number of tabs, our standard models use hyperlinks to allow users to navigate around the file. Hyperlinks did not convert from Excel to Sheets and the functionality needed to be rebuilt natively in Sheets using the Sheets formula builder and syntax.
Through this testing phase it also became apparent that the Sheets model was very slow, taking around 30 seconds to refresh on a standard internet connection.
The Page Load speed was the first issue that I was unable to resolve. Depending on the size and complexity of the model and available internet speeds, Sheets may be unusable in certain instances.
The functionality tests were confirmed to an acceptable level, excluding the Page Load speed test.
In summary, it is possible to convert a pre-existing Financial Model from Excel into a functioning model in Google Sheets. Based on the current technology, a number of issues should be noted
- Sheets does not convert blank columns past column Z
- Graphs formatting will need to be reviewed
- Shrink to Fit formatting will be lost
- Hyperlinks will not convert automatically
- Page load speed may render the model unusable
Until the Page Load speed issue can be resolved, Google Sheets may find it difficult to find traction in teams that use large, complex models. However, Sheets is useful for smaller modelling assignments, particularly projects that can benefit from the collaboration features.
Leave a Comment