What is on the Path to Greatness: Best Practice Financial Modeling

What is on the Path to Greatness: Best Practice Financial Modeling

In the previous series “Are We There Yet?, we explored the time it takes to progress from a beginner to building good—and eventually great—financial models. Along the way, certain best practices stand out as essential.

If you missed these, part one  Are We There Yet? – The time it takes to be a financial modeler  part two  Are We There Yet? – The time it takes to build a (good) financial model and part three  Are We There Yet? – The time it takes to build a (great) financial model

In two archived blogs, our CFO, Alison Leckie, outlines some guiding principles of financial modeling  Guiding principles for an effective financial model  and Best practice financial modeling.

Building on these discussions, let’s revisit and expand on the core attributes of a great model and some of best practices needed to get there.

Models

At Pivotal180, our models all have these three attributes:

1. Clear and concise
   • A financial model that is easy to review and understand. The layout, units, formatting, labels, and formulas avoid clutter, jargon, confusing abbreviations, and unnecessary complexity. Users can grasp key insights quickly and reliably.
2. Simple and transparent
   • A financial model where presentation, structure, and calculations are straightforward, logical, and easily traceable. Consistent formatting allows for easy identification of inputs and outputs. Complex formulas should be broken down into simple steps for easy auditing.
3. Robust and flexible
   • A financial model that presents a wide range of scenarios without errors or miscalculations. The scenarios should reflect the modeler’s understanding of the users’ needs. The model can be modified without disruption to the integrity of the calculations or outputs.

Executed with patience, skill, and time, these attributes form the foundation of a truly great model.

Best Practice for Modelers

For the modelers building these models, here are 5 best practices that will help you in building models both good and great.

1. Simple is Good.
   • Simple is actually great.
   • Simple is elegant, fast, and easy on the eyes.
   • The true decision makers who use financial models are rarely the ones who build them. A great model can be opened, reviewed, and presented to key stakeholders without strain.
2. Separate inputs from calculations from results from presentation.
   • The financial model is telling a full story. Like any story, it needs a clear opening, some gripping middle action, a clear conclusion, and a really engaging dust jacket!
   • Unless you are William Faulkner or Thomas Pynchon, the segments of your story should be clearly designated and follow a direct linear order.
   • These segments can be linked with master “check” cells in each tab, combo boxes, and intuitive navigation.
3. Use binary flags and control accounts.
   • One of the beautiful things about excel is that rows are free. Of course, there is a limit to how many flags we may need but to start we should use as many as seems necessary.
   • Flags (1s and 0s) are simple, visual tools to turn calculations on or off Used with some conditional formatting, flags are easy visual tools for any user to follow.
   • Control accounts are simple as well. The structure allows you to track complex in-period movements concisely with the results clearly displayed in the ending balance.
   • Like flags, control accounts should be used whenever you are tracking a value with an opening and closing balance.
4. Test, Test, and Test Again
   • Build a line. Test it. Build a section. Test it. Look for odd outcomes.
   • Ask how this might break? Try and break it. If it breaks, consider exactly what broke.
   • Repeatedly test major outputs and the intermediate steps that feed into them.
5. Limit circular logic and macros where possible.
   • Use them only when necessary or unavoidable. Both can be cumbersome to your model.
   • Part of limiting circular logic is spending ample time understanding why the calculation is circular in the first place. It is often possible to break calculations apart and discover ways to solve the issue dynamically.
   • Macros can be very useful but can quickly spiral in number and complexity. It is obnoxious to create something designed for speed and then have it slow a model down.

In our courses, we cover other best practices to improve your models and modeling.

Training 

Want to learn to be a financial modeler and understand how to evaluate and optimize your projects, no matter which way the economic or legislative winds are blowing?

Enroll in a Pivotal180 course! We offer a range of training programs for modelers of different backgrounds and experience levels:

Renewable Energy Project Finance Modeling

Project Finance Modeling

Introduction to Project Finance Modeling

Financial Modeling Fundamentals 

Tax Equity & Hybrid Financial Modeling