NAIC Model Audit Rule: Spreadsheet Control Objectives

Print Version

In 2006, the National Association of Insurance Commissioners (NAIC) adopted changes to the Model Audit Rule (MAR). The revised MAR, called the Annual Financial Reporting Model Regulation, incorporates best practice corporate governance and elements of the Sarbanes-Oxley Act of 2002 (SOX).

The new requirements came into effect at the end of 2010 and oblige insurers with over $500 million in written premiums to file detailed internal control assessment reports with the state insurance departments.

In determining improvements to their controls infrastructure, businesses can benefit from the experiences of companies who have previously implemented SOX compliance. These requirements include the need for demonstrable spreadsheet control. Just as with SOX, initial approaches with insurers have focused on manual controls. However, the cost and inconsistency of manual approaches is leading many to buy technology solutions that can automate all of the compliance requirements listed below, in addition to providing new business insight from the data captured.

Control ClusterSeven Description

Change Control

All changes are highlighted and may be reported via dashboards, emails or reports. ClusterSeven can distinguish normal user activity (e.g. data sorts) to avoid excessive change reporting

Version Control

Automated version control for all files even when they are updated by folder and name e.g. /Jan/Report31.xls changing to /Feb/Report01.xls

Access Control

Access to the file may be prevented at the file level. ClusterSeven also reports on changes to Access Control Lists

Input Control

All inputs can be monitored against definable tolerance levels. These may be absolute thresholds or relative to previous values.

Security and Integrity of Data

Cells/ ranges/ sheets and files may be protected to restrict access and thereby protect the data and formulas embedded in spreadsheets. ClusterSeven can report specifically on changes to protection settings and protected content.


Facilitates the preparation of documentation on the objectives and functions of the spreadsheet and ensures that it is maintained.

Development Lifecycle

The full software development lifecycle is supported.

Back Ups

Spreadsheet back up is automated as part of the Version Control process


Files may be archived according to corporate retention policies in a protected segregated location

Logic Inspection

ClusterSeven automates independent logic analysis on bulk inventories or individual files for both cell- and VBA-based content

Segregation of Duties

Procedures such as ownership and multi-level sign-off are supported in ClusterSeven

Overall Analytics

A complete risk assessment may be automatically applied to bulk inventories or individual files to expose poor spreadsheet practices that may lead to error or fraud.

Print Version

"This report [for state insurance departments] will include a statement by management whether these controls are effective to provide reasonable assurance regarding the reliability of the statutory financial statements and disclosure of any un-remediated material weaknesses in internal control over financial reporting. In addition, the proposed revisions require the insurer to file with the state insurance department the independent certified public accountant’s communication regarding any un-remediated material weaknesses noted during the course of an audit."

NAIC/AICPA Working Group

“Part of our financial close process used to take hours, now it takes minutes”

Financial controller, Global insurer (ClusterSeven client)

“This is the first time I can show the rest of the business how it all works. ”

Head of Actuarial, Lloyds managing agent (ClusterSeven client)

“We will use data dictionary terms within our spreadsheets and then automatically upload validated information into our corporate BI solution.”

UK General Insurer (ClusterSeven client)