Guiding Principles for an Effective Financial Model

A best practice financial model communicates a story and as a result clarity is critical

At Pivotal180 we have adopted three characteristics and ten guiding principles of best practice financial models.

1. Clear and concise
2. Simple and transparent
3. Robust and flexible

A clear and concise model is one that is well presented, includes relevant inputs, remember financial models are built for decision makers. A best practice financial model needs to be simple to use and be transparent A model with this characteristic will be easy to read and review and most importantly, allow any mistakes easier to be easily identified. It will be robust and flexible, ensuring accurate results and allowing changes to be made within the model effortlessly.

Top Ten Principles for an Effective Financial Model        

1. Separate inputs from calculations, and calculations from results
2. Clear and consistent use of cell and number formats
   • Use different formats to distinguish cell use (i.e., assumptions vs calculations)
   • Include units on all values, define a format for numbers (i.e., %, USD M, $ etc.)
3. Show critical and realistic business ‘what-if’ analysis (sensitivities)
   • Build a scenario manager
4. Well-presented outputs with required information for the decision makers
5. Build logical calculation flows: from left to right, top to bottom
   • A time period (i.e., calendar year) is in the same column on every sheet (except outputs)
6. Keep formulas simple and constant across a row
   • Formulas should be no longer than one line of an Excel formula bar
   • Limit the use of named ranges
7. Minimize circular logic and limit macros where possible
   • The iterations function in Excel may not calculate as desired with circular logic
8. Use control accounts and binary flags
   • Control accounts to record volumes
   • Binary flags to show different business phases (i.e., construction vs operations)
9. Never hard-code an assumption into a formula
   • Models should update automatically when underlying assumptions change
10. Models must be easy to audit and have all components visible
    • Build in error checks to your work
    • Do not hide columns or rows. Use collapsible grouping as required

Some Examples of our Top Ten Principles for an Effective Financial Model        

1 Separate inputs, calculations, and outputs by tabs

• The inputs are manually entered into a model in one tab
• Calculations where all the formulas are held are in another tab
• Outputs which provide the results and dashboard of the financial model are in yet another tab

This use of separate tabs is assisting the user to navigate through the model and to find out where to make changes, amend the calculations and to analyze the results in different ways.

2 A model needs consistent use of cell and number formats, so what does this mean?

• We can use specific backgrounds and font colors to for each cell type.
• The use of this consistent number formatting greatly enhances the model and provides further consistency.
• This includes using units on all numerical terms and the same formats throughout the model. It is not necessarily important which format you use as long as you are consistent. For example, a zero could be formatted in a cell as a dash, a zero or even a blank.

6 Keep formulas simple

• The simpler the calculations, the more clearly it can be read and reviewed.
• The use of simple formulas such as IF, SUM, multiplication, may enhance your model.
• Formulas such as offset, index, match, and V look up, may add an unnecessary complexity.

Pivotal180 Models

Our courses are not limited to financial modeling and best practices in Microsoft Excel.  We are committed to explaining how the numbers align with underlying financial and engineering concepts, transaction structures, legal documentation, market conditions and risk management approaches. We do not just teach how to build models. We teach how to transact.

In our financial modeling courses we will refer many times to our guiding principles and to use them effectively when building  your own best practice financial models.

Learn more about our renewable energy project finance course

Build a best-practice financial model from scratch for a renewable energy project finance deal.

This course teaches participants how to develop a best-practice financial model and optimize it for both debt and equity investors for a renewable energy project finance deal.  Using a case study of a wind project, participants learn the concepts required to develop a financial model related to wind and solar.

This course will teach the participants concepts including how to size debt, how to structure the repayments and to model the output covenants in the deal, how to calculate generation curtailment and degradation, how to forecast wind and the different probabilities.

Learn more about our project finance and infrastructure course.

Build a project finance model from scratch using an infrastructure project case study

This course teaches participants how to develop a best-practice financial model and optimize it for both debt and equity investors for an infrastructure project finance deal.  Using a case study of a PPP hospital project, participants learn the concepts required to develop a financial model related to Public Private Partnerships (PPPs / P3s) for both social and economic infrastructure.

Learn more about our Tax Equity financial modeling course.

Build a tax equity model from scratch using a US renewable energy project as a case study.

This is an intensive course providing an overview of the tax equity structures commonly used in U.S. renewable energy investments, with a focus on the development of a financial model.

See links below to see our financial model demos in our free resource section of Pivotal180 website.

https://pivotal180.com/renewable-energy-course-model-familiarization-video/

https://pivotal180.com/project-finance-and-infrastructure-modeling-model-familiarization-video/

https://pivotal180.com/tax-equity-model-familiarization-video/