📌 Tabular Formulas — Transparent, Traceable Logic

"Formulas that explain themselves."

This model leans heavily on structured referencing with Excel Tables, transforming complex logic into readable, auditable formulas.

Rather than relying on A1-style references (C7*D8), the model uses expressions like:

=INDEX(Vehicles, MATCH(Model, Vehicles[Model], 0), MATCH("Cost (AUD, excl. GST)", Vehicles[#Headers], 0))

Or:

=INDEX(DealerFees, MATCH(State, DealerFees[State/Territory], 0), MATCH("Percentage Rate", DealerFees[#Headers], 0)) * Cost

🔍 Why It Matters

• ✅ Readability: Anyone reviewing the model can understand the logic at a glance — “Dealer Fees times Cost,” not “E7 * B14”

• ✅ Auditability: Formulas point to clearly named tables and columns, reducing the risk of hidden dependencies

• ✅ Scalability: Add a row to any input table and it automatically integrates with the formula model

• ✅ Efficiency: Excel’s auto-complete in the formula bar surfaces all available table names and column headers, accelerating development and reducing typos

🛠 Best Practices Highlighted

• 📎 All input tables are stored in the Name Manager and clearly labeled (Vehicles, DealerFees, StampDuty, etc.)

• 🗂 Formula cells are isolated from inputs, making the sheet easy to extend or modify without breaking logic

• 🔄 Repeating logic across rows/columns ensures consistency and traceability for downstream calculations