In today’s AI-driven environment, financial modelling is evolving rapidly—but so are the risks. While tools can generate models in seconds, they cannot replace a strong understanding of underlying logic, assumptions, and structure.
In a recent Cambridge Finance webinar, we explored the five most common financial modelling mistakes, how to identify them, and—most importantly—how to fix them.
Why Modelling Mistakes Matter More in the Age of AI
AI can accelerate model creation, but it often introduces hidden issues such as hard-coded values, incorrect assumptions, or flawed logic. This makes it critical for professionals to audit and validate models rather than rely on outputs at face value.
A model is only as strong as the thinking behind it.
1. Conceptual Errors: When the Logic Doesn’t Hold
Conceptual errors occur when the model does not reflect the actual business case.
A common example is forcing a deal into a model that isn’t fit for purpose—such as using an office investment model for a development project or a retail asset. Another frequent issue is misunderstanding key financial concepts, such as:
- Treating purchase price as total equity required
- Ignoring acquisition costs like stamp duty and fees
- Using Gross Development Value (GDV) as revenue without adjusting for transaction costs
- Misapplying yields (gross vs net)
How to fix it:
Before opening Excel, clearly map out the deal:
- Where is the money coming from?
- How will it be used?
- What does the cash flow look like over time?
Sketching the structure on paper and ensuring a solid theoretical foundation can prevent major downstream errors.
2. Calculation Errors: Small Mistakes, Big Impact
Calculation errors are often subtle but can significantly distort results.
Typical issues include:
- Hardcoding numbers within formulas
- Linking formulas to incorrect cells
- Misusing IRR vs XIRR or NPV vs XNPV
AI-generated models are particularly prone to hardcoding, which reduces flexibility and transparency.
How to fix it:
- Always separate inputs from calculations
- Trace formula dependencies
- Stress test the model (e.g., 0% rent, 100% vacancy)
- Investigate errors like
#DIV/0!to understand model limitations
If you don’t know when your model breaks, you don’t fully understand it.
3. Not Following Best Practices
Best practices are not just about aesthetics—they directly impact model reliability.
Common issues include:
- Lack of clear input/output separation
- No colour coding for inputs
- Overly complex, multi-line formulas
These make models difficult to audit and prone to misuse.
How to fix it:
- Clearly distinguish inputs, calculations, and outputs
- Use consistent colour coding
- Keep formulas simple and transparent
- Design models so a third party can easily understand them
A well-structured model should be intuitive and self-explanatory.
4. Poor Assumptions: Garbage In, Garbage Out
Even a technically perfect model fails if the inputs are unrealistic.
Examples include:
- Overly optimistic rental growth assumptions
- Ignoring lease-up periods or vacancy
- Assuming immediate full occupancy
- Using unsupported yield assumptions
How to fix it:
- Base inputs on market data and comparable transactions
- Document and justify every key assumption
- Ensure assumptions can withstand scrutiny from investors or lenders
If you can’t defend an assumption, it shouldn’t be in the model.
5. Inadequate Sensitivity and Scenario Analysis
A model without proper risk analysis provides a false sense of confidence.
Common mistakes:
- No downside scenario
- Testing irrelevant variables
- Overloading the model with unnecessary data tables
- Scenarios that all show positive outcomes
How to fix it:
- Focus on 2–3 key drivers (e.g., rent, yield, cost overruns)
- Build targeted sensitivity tables
- Include at least one scenario where the deal fails
- Analyse when and why equity is at risk
The key question is not just what works, but what breaks the deal.
Final Thoughts
A financial model should not be a tool for discovery—it should be a structured representation of logic you already understand.
To build robust models:
- Start with strong theoretical foundations
- Be disciplined with structure and best practices
- Use realistic, defensible assumptions
- Always test for downside risk
In an era where AI can build models quickly, the real value lies in knowing how to challenge, validate, and refine them.
