You may also be interested in:

Articles

Financial Modeling Best Practices

  • By Rob Trippe
  • Published: 1/19/2021
financial model 3

Our last article discussed model governance and risk management, the background to successful development. If we think about building a model as a product development process, we can apply best practices from decades of research and the current trends.

THE AGILE PROCESS

In 2001, a group of software engineers published “The Agile Manifesto” to break free from prior rigid methodologies. The Agile development movement proclaims that solutions evolve through “collaboration between self-organizing, cross-functional teams utilizing the appropriate practices for their context,” and then sets out four values and 12 principles.

Software development is very much like financial modeling and the Agile approach can add a dramatic improvement to an FP&A function’s modeling processes and quality of output. As financial modeling matures, it is apparent by the number of organizations which have emerged that financial modeling, even if just in Excel, is best practiced with a set of codified rules and processes for developing and managing models and spreadsheets. There will be an increased implementation of more formal frameworks for how models are developed and employed. Agile is one example.

Agile likes to think it is a mindset and not a specific process; it has gained popularity for its customer (downstream user) focus and for the fact that it views model development as a circular and iterative process, not a one-time event. This is entirely consistent with SR 11-7’s guidance on ongoing model monitoring, which discussed in an earlier paper.

Here is the “Agile Manifesto” and how it can align to top shelf FP&A practices:

AFP-21_FP&A_Article_Agile_Chart_2

MODELING STANDARDS FOR SPREADSHEETS

Often, when we think of models, we think of Excel. And when we think of Excel we think of a blank sheet of paper. To a formal financial modeler, this is not an invitation to develop work any way we choose and prefer. Rather, a formal financial modeler will be bound by numerous accepted structures, sets of rules and development processes. When we combine model standards such as FAST or BPM with commonly used financial statement and analysis structure together with SR 11-7 guidance, our models don’t have much room for creativity and subjective preferences at all. And that is good for building models that are effective and sustainable.

For better or worse, Finance still runs on spreadsheets; with more than 750 billion installations, Excel is the default tool for finance, and a recent AFP survey noted that 97% of FP&A practitioners with dedicated planning tools still use spreadsheets in their planning processes!

Numerous organizations have developed modeling rules for the use of Excel to gain the effectiveness and cost benefits outlined in the first article of this series. These rules provide structure and commonality among spreadsheet design and use. Modeling standards reduce model risk by enhancing model clarity and transparency for the author, validators and other users, and improving the outcomes. Common modeling rule categories include the following:

  • Workbook and worksheet design: How workbook and worksheets are organized and unified.
  • Line-item design: How financial data is displayed line by line, such as the use of negative signs and precise wording of financial statement items.
  • Cell design: Rules for which formulas are used, how cell references are established, and the complexity a formula may contain.
  • Assumptions: How and where assumptions are displayed and handled.
  • Features: Functions, commands Most importantly, which to use and which not to.
  • Formatting: Consistency in presentation to facilitate readability.
  • Naming protocol and version: How workbooks are named and stored.
  • Risk and error: How workbooks and worksheets are managed to ensure minimum risk and error.

Numerous organizations offer courses and certifications in modeling, and there is a tremendous benefit to learning these at early in your career. One popular modeling framework is “FAST”, a four-point framework on how to produce a better work project and product, whether I am in Excel or not. The FAST Standard Organization is a nonprofit charged with protecting, promoting, and developing the FAST Standards. Similar groups include Smart Financial Modeling and BPM (Best Practice Modeling).

Rob Trippe is M&A, corporate finance advisor and analyst for Corporate Finance Consulting & Advisory. Read the other parts of this series here.


PARTNER CONTENT:


Optimize your cash flow.

Our treasury management and payment services can help you manage cash flow, control risk and become more efficient. Member FDIC.

Let’s get started.

Copyright © 2021 Association for Financial Professionals, Inc.
All rights reserved.