Most businesses, at some time or the other, use Excel to build financial models to compile budgets, financial statements, or financial projections.
Excel is the simplest and most flexible platform to build financial forecasts. You can keep it simple or add as much complexity as you need, depending on the purpose, the amount of time you have and the level of Excel skills at your disposal.
But when you add complexity to your model, you rapidly increase the time required to build, check and test your model. Keeping track of the correct version or ensuring the continuing integrity of your model requires continual checking every time an adjustment is made or any input is revised. It takes away, much of the benefit and confidence you can have in the accuracy of your projections.
Key problems include:
Error-Prone Calculations: Excel is susceptible to human errors, especially when dealing with large datasets and complex formulas. A simple typo or misplaced cell reference can lead to significant miscalculations, potentially resulting in flawed financial models. These errors can have serious consequences, leading to incorrect financial decisions and misleading projections.
Lack of Version Control: Excel lacks robust version control capabilities, making it difficult to track changes and maintain an accurate audit trail. When multiple users collaborate on a financial model, it becomes challenging to keep track of revisions, leading to confusion and potential data integrity issues. This lack of version control can hinder transparency and increase the risk of errors going unnoticed.
Limited Scalability: Excel has limitations when it comes to handling large datasets and complex calculations. As financial models grow in complexity, Excel's performance may suffer, leading to slow calculations and increased processing time. This limitation can hinder the ability to analyze and make timely decisions based on the model's outputs.
Inflexibility and Lack of Automation: Excel requires manual input and formula adjustments, making it time-consuming and prone to human error. Financial models built on Excel often lack the flexibility and automation capabilities needed to adapt to changing business scenarios. This inflexibility can hinder the ability to perform scenario analysis and make quick adjustments to the model.
Data Integrity and Security Risks: Excel files are vulnerable to data corruption, accidental deletions, and unauthorized access. Without proper data protection measures, financial models built on Excel can be exposed to security risks, compromising the integrity and confidentiality of sensitive financial information. This poses a significant concern, especially for organizations dealing with confidential data.
What alternatives do businesses have?
Building cashflow projections from budgets, especially for trading businesses, requires relatively few assumptions, but a lot of complex calculations. And for a variety of reasons, none of our friends in trading business had built a model to project cashflows and balance sheets.
Use the budget to estimate the year-end balance sheet and add 50% to the borrowing requirement. Mostly, use EBITDA as a cashflow alternate, and assume working capital remains constant over the year The funding requirement is then the balancing figure, after increasing shareholder funds with the amount of profit.
Download historic data using various apps and extrapolate forecasts, based on historic relationships between income and balance sheet items. This does suffer from trying to explain what business assumptions have been used to project the balance sheet and the difficulty of introducing new products, new markets in new business plans.
Use various other apps that promise to make building and maintaining financial models much easier than having to build a model from scratch in Excel. Which one works for you is a matter of trial and testing and takes time and effort.
None of the alternatives above take account of letters of credit, the time taken between ordering your goods and having them ready for sale or allow you to restructure your capital , so that both you , your investors and your bankers are comfortable with the level of risk.
Trafic started out as a very complex Excel model, but in addition to addressing all the issues listed above, we designed it such that it would be both simple and easy to use by decision makers themselves. That it should work with only with assumptions that make sense to business owners and managers. That is should not require downloading data, that it should not require any systems or implementation time and costs, that it should not require any customization or trial periods. That it should be as simple as a calculator, albeit with a few more inputs.