Model Audit

What is a Model Audit?

A financial model audit is an important task in financial modeling that helps ensure that model or spreadsheet errors are eliminated or reduced.

Also called a model review, a financial model audit is commonly requested by banks and corporations in order to make sure the calculations contained within their models are accurate and maintain mathematical integrity, reassuring lenders and investors that the models can be relied upon to make informed decisions.

Scope of a Model Audit

A model audit is done in order to make sure that spreadsheet errors are corrected. Thus, it needs to be specific with its scope and purpose to ensure that the relevant tasks are completely and correctly carried out. The audit’s scope includes the following:

• The audit should successfully review the logic used in the model.
• An audit should check for the model’s consistency in terms of calculations and documentation.
• The audit should make sure that the model is consistent and works in parallel with accounting and taxation rules.
• The audit should check for how sensitive the model is.

Purpose of a Model Audit

The audit should be able to yield accurate results, and the auditor needs to provide assurance to the party seeking the audit that such results will be obtained. To achieve the audit’s objectives, the auditor offers an amount of liability that guarantees a level of reliance, which may be equal to a multiple of the audit fee.

Should there be any mistakes in the audit as a result of the auditor’s negligence, the company that requested the model audit can sue the auditor for damages (although there may be a pre-defined liability cap to limit the auditor’s financial exposure).

Basically, the audit aims to ensure that the financial model used by a business is accurate and that the assumptions contained within it are effective. It confirms or denies that the financial model does what it is expected to do and that any errors are minimal and will not cause any material impact on the business’s projects.

Categories of a Model Audit

There are basically two broad categories of a model audit — the high-level review and the formal model audit.

1. High-level review

A high-level review provides added assurance to clients about the financial model that the clients are using. Aside from just correcting errors on a spreadsheet, a special feature of the model audit is that it is sensitive to spotting errors without checking every single detail of the model in question. It makes the process less time-consuming, while still maintaining a high accuracy of results, and it includes a check of the financial model’s logic.

The focus of this audit is the “big picture.” In other words, does the financial model do what it purports to do at a high level, without having to perform a detailed review of every formula and function?

2. Formal model audit

A formal audit is what stakeholders and investors may require even after the completion of an audit and submission of a report saying that the model is good enough to use. Often, the stakeholders require an audit that is formal and very comprehensive; the formal model audit satisfies this requirement.

After a rigorous checking, the auditor submits a report that contains a detailed list of the errors in the model (if there are, in fact, errors) so that the business can solve the issues before it takes a prospective action.

Cost and Duration of a Model Audit

The audit is completed usually within one to five weeks, depending on how extensive the review is and the agreed scope of the audit.

The cost of the audit depends on the following factors:

• Scope of the review
• Complexity of the model checked
• Volume and complexity of the documentation to be reviewed
• The seniority of the staff who performs the audit

How to Conduct a Model Audit

In order to perform the audit, it is important to recognize that a financial model is typically produced by using Microsoft Excel spreadsheets and, therefore, contains data, figures, functions, and formulas. The auditors usually conduct a bottom-up review, or a cell-by-cell check of every formula. They can also do a “top-down” analysis of the model.

Common Model Errors

• Improper or inconsistent formatting: As a best practice, we recommend using a blue font for hardcoded numbers and black font for formulas and calculations. This makes the model much easier and quicker to audit. However, this best practice is often ignored or applied inconsistently, which could lead to a substantial error or errors.
• Using long, complex formulas: Another best practice that is often ignored is to use shorter, less complex formulas. In general, the longer and more complex the formula the more prone to error the formula is. By breaking up a long formula into multiple, simpler formulas, errors can be avoided. A model audit engagement will take longer the more complex the financial model is.
• Incorrect sign: Many financial models do not maintain a consistent sign convention. For example, does the financial model use negative numbers for expenses or does it use positive numbers? If a proper sign convention is not adhered to, it’s possible the model may miscalculate certain metrics. Additionally, switching positive and negative signs around are really difficult to catch during model audits.

Famous Financial Model Errors

1. The “London Whale”

Probably the largest and most famous financial model error was involved in the infamous London Whale incident. The London Whale, a JPMorgan trader named Bruno Iksil, entered into a series of aggressive transactions involving credit default swaps (CDS).

Once the CDS market figured out it was a single, aggressive trader making these bets, the market moved against the London Whale. In total, JPMorgan announced a loss of more than $6 billion. Part of the aggressive trading strategy was due to mistakes in an Excel-based financial model.

2. TransAlta’s Model Misalignment

Canadian power company TransAlta lost $24 million after an employee misaligned rows after copying and pasting data into an Excel model. This reportedly reduced TransAlta’s 2003 profit by 10%.

Additional Resources

CFI offers the Financial Modeling & Valuation Analyst (FMVA)™ certification program for those looking to take their careers to the next level. To keep learning and advancing your career, the following CFI resources will be helpful:

• Financial Modeling Best Practices
• Forensic Audit Guide
• Types of Due Diligence
• List of Financial Modeling Courses
• Learn How to Audit a Model Using Macabacus
• See all financial modeling resources