4 concepts from software development to make you better at financial planning
Finance teams can draw on tools from software development to solve some of the most fundamental challenges in financial modeling.
Collaborating with finance teams on their financial models has allowed our team at Francis to observe the many unique ways finance teams manage their work in Excel and Sheets.
By far, the most interesting takeaway for me is how surprised our software developers have been about how finance builds and maintains financial models.
For instance, we recently onboarded a client whose financial model covered seven entities in Google Sheets. The client didn’t feel 100% confident in the Sheet model’s output as it was initially built by a consultant before the finance team joined and had since been tweaked many times to accommodate new questions from management.
With many duplicated tabs, revised versions, and custom reports, the client feared any change might produce wrong numbers or break the model.
While this may seem like an extreme example, in our experience, even the most capable finance teams spend a lot of time and mental energy worrying about updates and hidden errors in their models.
Our software developers are surprised because, in their work, they have tools to mitigate these issues.
Below, I’ll discuss those tools and their application in finance.
As we’ve come to realize, developing financial models in Excel and Sheets is similar to developing software. Both consist of handling large data sets, modeling relationships, and crunching numbers.
Software developers use programming languages and databases. Finance teams have tabs, cells, formulas, and functions at their disposal.
For the same reasons, Microsoft CEO Satya Nadella categorizes Excel as its own programming language.
The activities related to managing a financial model can also be framed using software terminology:
New features: Changing business needs may require expanding the model, such as adding new data imports, scenarios, or reports.
Maintenance: The model requires regular updates such as adding new general ledger accounts, replacing forecasts with actuals, or updating comparison periods. These updates are necessary to keep the model functioning.
Testing: Financial models must be tested for accuracy by validating assumptions, verifying calculations, and stress-testing scenarios to ensure reliable outputs.
Bug fixing: Formula errors, incorrect data inputs, or logical flaws in the model must be identified and corrected.
Refactoring: As more features and fixes are added to a model over time, it can become overly complex and harder to manage. Refactoring involves reorganizing and cleaning up the model to improve its structure and efficiency without changing its output.
Rewrite: When a financial model becomes too complex and unmanageable, starting over and developing a new model from scratch may be necessary.
From our conversations with hundreds of finance teams, we’ve learned that they lack tools available in software development to perform these activities efficiently and with peace of mind. Consequently, financial modeling consumes great resources and poses a significant business risk.
You have to ensure the numbers are correct. In the worst case, miscalculating cash flow can lead to bankruptcy and the end of careers. However, you must also preserve resources to contribute to strategy, operations, etc.
This description from the Secret CFO summarizes the issue well: "CFO jobs are about more than numbers. But they are also about numbers."
Even though software developers also have to allocate resources to new features, maintenance, testing, etc., they can build more durable and reliable systems that far exceed the complexity observed in financial models.
They can achieve this because they have developed tools to ensure the integrity of their systems. These tools are born out of necessity, as failure in software systems can be orders of magnitude more severe than failure in financial models.
While a miscalculation in an Excel model might blow up in management’s face and impact the trajectory of the business, a bug in a software system can affect millions of people and lead to fatal outcomes.
Consequently, software developers have created tools to ensure the reliability and durability of their systems. Without these tools, they would be as prone to failure as financial models.
In our experience, a common example of the difference between developers and finance teams is their approach toward duplicates.
Finance teams duplicate everything:
Duplicate their workbook to create a new forecast
Duplicate their workbook to save a version
Duplicate their workbook to revise old versions
Duplicate their workbook to collect input from contributors
Duplicate their workbook to share their model
Duplicate tabs and sections to use them as templates
Software teams don’t duplicate. As quoted by our CTO, “Duplication is the root of all evil.”
Instead, they emphasize keeping code DRY (Don’t Repeat Yourself). This makes maintaining it much easier, as you only have to update things once.
But how do software developers avoid these bad habits? They deploy the four tools below. Let’s review them and see how they can be applied to finance.
Version control
One of the biggest innovations in software development was Git in 2005. Simply speaking, Git is a version control system that makes managing many versions in a continuously evolving system easy. Git is centered around branches, which are a way to present versions visually over time.
With Git, you can track edits over time, making it easy to revert to previous versions if needed. You can also “branch out,” meaning you can edit saved versions in new directions.
The Git manual describes it as a "stupid content tracker,” which I believe should be understood in the best way possible. You don’t need to go far before versioning becomes challenging in finance. Git can help keep it manageable.
Let’s look at another client example to exemplify common version challenges. In this case, the client had just one entity, managed in Excel. Here’s how January and February went:
And so it goes on. The team already had four versions to manage two months into the year. Very quickly, you end up with multiple decoupled versions that are hard to navigate, revise, and compare.
Software developers work with versions in one integrated version control system (Git). That way, they can always account for all versions and changes. One of the great missions of our software team at Francis is to introduce similar smart versioning to finance teams.
Synchronized templates
The thinking in finance is that duplicating tabs or workbooks is an efficient way to expand your model. Contrary to that, software teams never duplicate. They know it’ll create headaches down the line.
To illustrate how this plays out, let’s say you created a P&L template and duplicated it across six departments, along with a consolidation to tie it all together. This is quick to set up.
However, any subsequent change must be applied to all copies once duplicated, and the consolidation must be updated. Otherwise, the six instances will wander in different directions, making them hard to reconcile with the consolidation.
I’ve seen this scenario play out more times than I can count. The cognitive load of remembering all dependencies as you change parts of your model is really tough. This often leaves only one person able to make changes and updates to the model. It's not an ideal state for most teams.
By deploying synchronized templates, you can reduce maintenance to one place. It works by having a system that ensures that changes to the template carry through to underlying instances.
Tests and warning systems
Over the decades, software teams have learned that they need tests to ensure their code behaves as expected. The fundamental idea is that tests validate that the system still works as expected every time a change is introduced.
After conversations with finance teams, we recognized that they typically leverage two tests in their models:
Balance sheet checks (assets = liabilities + equity)
Sanity checks
Sanity checks are a highly effective way to check the correctness of numbers, but you mostly spot obvious mistakes and miss the ones hidden by subtle changes.
Disciplined teams might do more tests, but we rarely see this.
At Francis, we’ve started embedding tests directly into our platform to support finance teams and warn them about critical mistakes. Some of these tests include:
Warn when P&L and balance sheet accounts are mixed
Notify when new accounts have been created in your accounting system
Warn if these accounts are not yet accounted for in your model
Warn if different currencies are mixed
In Excel and Sheets, you’d typically define tests using formulas and functions. In Francis, we’ve embedded a warning system telling you whether you are in the “green” or “red” at any given point.
Collaborative workflows
Financial models typically have one person “owning” the model and contributors who provide input and/or consume output from the model. Having contributors provide input is a classic challenge in financial modeling.
Collaboration challenges have historically been closely tied to version challenges. Sharing different Excel workbooks made merging changes from different people a pain. Live collaboration in Excel and Google Sheets has at least ensured that contributors work on the same version.
However, once live collaboration became the standard, two more challenges arose:
1. Ensuring that contributors only see what they should see
Since there is no way to restrict users to a specific part of an Excel or Google Sheets model, we see that finance teams effectively return to creating multiple workbooks to gather input, leading to the same historical challenges.
Software teams often work with sensitive data and systems, which presents similar confidentiality challenges. To mitigate this, they often use the Least Privilege Principle, which dictates that users should be granted the minimum levels of access - or permissions - necessary to perform their job functions.
Leveraging this in a finance setting means limiting access to specific sections of the financial model. For instance, sales teams can only view and input data related to sales forecasts, while the finance team maintains control over the overall model structure and core financial data.
While this is, unfortunately, hard to do in Excel and Sheets, new FP&A platforms support this functionality.
2. Approving changes made by collaborators
Allowing multiple contributors with editor rights into your spreadsheet opens the gates to unintended changes. In our experience, finance teams must approve changes to ensure models stay functional and input is aligned with management.
This can be compared to open-source software projects with few editors and many contributors. Here, contributors can make suggestions that the editors can then review and choose to accept at their will.
The clearest analogy is probably the track changes and approve suggestion features known from Google Docs.
Approval workflows can help make planning a decentralized, collaborative process and remove the finance team’s burden of being a bottleneck.
Conclusion
Both finance and software are highly capable teams with similar workflows. Many software tools that stood the test of time can be applied to finance to improve efficiency and quality.
At Francis, we’re trying to bridge the two worlds by providing finance teams with new powerful tools. In future posts, we’ll describe in more detail how we leverage these tools in Francis.