🔧 From Lease Calculations to Engineered Insights

A Structured Excel Approach to Financial Modelling

This report showcases more than just the numbers behind an Electric Vehicle novated lease — it highlights the Excel modelling techniques that make this tool transparent, flexible, and scalable.

We start with a real question:
What’s the financial difference between buying a vehicle outright and leasing it through salary sacrifice?

To answer that properly — across states, car models, tax brackets, and lease terms — we needed to build something far more robust than a basic spreadsheet. We needed a modular, auditable engine. That meant leveraging the very best of Excel's modelling architecture.

Each page in this report walks through the tools that made this possible — and shows how they build on one another to deliver meaningful insights.

📌 Page 1: Named Ranges — Building with Context

The journey starts with a dedicated Config sheet, where all editable parameters are stored in one place — highlighted in yellow and defined using named ranges like GST, LCT, or TaxRate.

• ❌ No more C7 * D10

• ✅ Now: Cost * GST

This dramatically improves readability, helps new users find what to change, and gives auditors full traceability of assumptions.

📌 Page 2: Tables — Structure without Complexity

Static datasets — like stamp duty by state or vehicle pricing — are stored as Excel Tables.

• Auto-expanding rows

• Column names instead of cell references

• Easy filtering, referencing, and downstream calculations

No Power Query here. The data rarely changes, so we keep it simple and performant — but still structured.

📌 Page 3: Tabular Formulas — Transparent Logic

From there, we layer in logic using structured references. Our formulas read like English, pulling from column headers and table names with built-in autocomplete.

=INDEX(Vehicles, MATCH(Model, Vehicles[Model], 0), MATCH("Cost", Vehicles[#Headers], 0))

• Easy to read

• Easy to trace

• Easy to adapt

This approach ensures that all calculations are driven by table logic, not hidden in tangled formulas.

📌 Page 4: Data Tables — Insight at Scale

The final layer is the most powerful: Excel’s Data Tables.

With one core formula, we run hundreds of permutations across vehicle models and lease durations — calculating net present value, discount percentages, and total savings in one sweep.

• Powerful for negotiations, budgeting, and capital planning.

• Ideal for rapid scenario analysis

• Completely eliminates duplicated logic

🎯 Why This Model Works

By layering these tools, we transformed a single financial question into a flexible, audit-friendly decision engine.

✅ Named Ranges = clarity
✅ Tables = structure
✅ Tabular Formulas = logic transparency
✅ Data Tables = scalable analysis

In a world of fragile spreadsheets and “do not touch” workbooks, this model shows how Excel can be used as a proper engineering tool — building tools that work, grow, and tell a clear story.