Integrity checks are tests for model conditions that, if present, require alerting anyone using the project.

Integrity checks can save our careers. integrity checks alert modelers to errors that should be resolved before handing models over to customers and they alert customers to issues that need to be addressed before basing decisions on model results. Adding integrity checks to models is a best practice.

Things we Should Check

I chose to write this post because I could find only a few resources for this subject. By leveraging those few resources and engaging some of the best minds in modeling I was able to cobble together a pretty good list to inspire our thinking. I divided the list into to two major categories: Errors Alerts and Performance Alerts.

Error Alerts

These indicate an invalid or non-functioning model. Should any of these conditions exist, modelers should not release their work to customers, and if already in the hands of customers, customers must not accept the model's results. Errors can be further divided into these groups.

Technical Errors

These include:

• • Errors in Formulas - Such as #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!. These can be tested for using the ISERROR() function.

  • NOTE! I know some excellent modelers use these errors in their models. I don't. I argue that if an error is used for logic, leverage the IFERROR() function inside the cell to process the error so the ISERROR() function can be applied over an entire calculation block to look for un-handled and unexpected errors.

  • Standards Violations - I follow the standard that every cell in a calculation block must contain formulas. They must not contain empty cells or hard coded cells (cells with literals). This standard can be verified using Excel's ISFORMULA() function.

  • Row or Column Counts - Dynamic models must be checked to make sure there are enough columns for periods or rows for items.

Model Errors

These include:

• • Allocations that do not add up to 100%

  • Balance Sheets that do not balance

  • Fund sources that do not match fund uses

  • Balances that can't go negative but do

Performance Alerts

These indicate undesirable outcomes. Our model is valid but its requirements show assumptions creating conditions our customers cannot accept. These conditions may be imposed by our customers, such as KPI standards, or imposed by others such as debt service coverage ratios demanded by creditors. Should any of these conditions exist, our model's customer must be alerted.I found a lot of material for these alerts which I divide into Financial Ratios, Manufacturing KPIs, Sales KPIs, and Supply Chain KPIs.

Financial Ratios

• • Liquidity Ratios

  • Acid-test Ratio (aka Quick Ratio) = (Current Assets – Inventories) / Current Liabilities

  • Cash Ratio = Cash and Cash Equivalents / Current Liabilities

  • Current Ratio = Current Assets / Current Liabilities

  • Operation Cash Flow Ratio =Operating Cash Flow / Current Liabilities

  • Leverage Financial Ratios (aka Solvency Ratios)

  • Debt Ratio = Total Debt / Total Assets

  • Debt service coverage ratio (DSCR) = Operating income / Total debt service

  • Debt to Capital Ratio = Debt / (Debt + Shareholders’ Equity)

  • Debt to Equity Ratio = Total liabilities / Shareholder’s equity

  • Equity Multiplier = Total Assets / Total Shareholder’s equity

  • Interest Coverage Ratio = Operating income / Interest expenses

  • Efficiency Ratios

  • Asset turnover ratio = Net sales / Total assets

  • Days sales in inventory ratio = 365 days / Inventory turnover ratio

  • Inventory turnover ratio = Cost of goods sold / Average inventory

  • Operating Ratio = (Operating Expenses + Cost of Goods Sold) / Net Sales

  • Receivables turnover ratio = Net credit sales / Average accounts receivable

  • Return on Invested capital (ROIC) = Net Operating Profit After Tax / Invested Capital

  • Profitability Ratios

  • Earnings per share (EPS) = (Net Income - Preferred Dividends) / End-of-period Common Shares Outstanding

  • Gross margin ratio = Gross profit / Net sales

  • Return on assets (ROA) ratio = Net income / Total assets

  • Return on equity (ROE) ratio = Net income / Shareholder’s equity

• Market Value Ratios

  • Book value per share ratio = Shareholder’s equity / Total shares outstanding

  • CAPE Ratio = Share Price/ Ten Year Average Inflation adjust earnings

  • Dividend Payout Ratio = Dividends Paid/Net Income

  • Dividend yield ratio = Dividend per share / Share price

  • Earnings per share ratio = Net earnings / Total shares outstanding

  • Price to Book (P/B) Ratio = Market Price per Share / Book Value per Share (BVPS)

  • Price to Sales (P/S) Ratio = Market Price per Share / Sales per Share

  • Price-earnings ratio = Share price / Earnings per share

  • Retention Ratio = Retained Earnings / Net Income

• Valuation Methods

  • Discounted Cash Flow = CFyear11/(1+WACC ) + CFyear22/(1+WACC ) + CFyearnn/(1+WACC )

  • Weighted Average Cost of Capital (WACC) = Equity/(Equity + Debt) * Cost of Equity + Debt/(Equity + Debt) * Cost of Debt * (1 - Tax Rate

Manufacturing KPIs

• • Avoided Cost = Assumed Repair Cost + Production Losses – Preventative Maintenance Cost

  • Cash to Cash Cycle Time = Inventory Sale Date – Inventory Purchase Date

  • Changeover Time = Net Available Time – Production Time

  • Customer Return Rate = (# of Products Returned * 100) / Total # of Products Shipped

  • Cycle time = Process End Time – Process Start Time

  • Employee Turnover Rate (%) = (Employees who left * 100) / Avg. # of Employees

  • Ending Work-in-Process = Beginning WIP + Manufacturing Costs – Cost of Goods Manufactured

  • Energy Cost Per Unit = Total Energy Cost / # of Units Produced

  • Health and Safety Incidence Rate = Number of Incidences * 200,000 / # hours worked by all employees

  • Inventory Turns = Cost of Goods Sold / Avg. Inventory

  • Non-Compliance Events = # of Non-Compliance Events / Specified Period of Time (Annually)

  • On-Time Delivery = # Units Delivered On-Time * 100 / # Units Delivered

  • Production Attainment = # of Periods Production Target Met / Total Time Periods

  • Return on Assets (ROA) = Net Income / Avg. Total Assets

  • Scrap Rate = # of Scrap Units / Total # of Units

  • Takt Time = Net Available Time / Customer’s Daily Demand

  • Throughput = # of Units Produced / Time (hour or day)

  • TMC Per Unit Ex Materials = TMC Per Unit – Material Cost Per Unit

Lean Manufacturing KPIs

• Capacity Utilization = Actual Factory Utilization / Total Productive Capacity

• Downtime to Operating Time = Downtime / Operating Time

• First Pass Yield Rate = Quality Units / Total Units Produced

• Machine Downtime Rate = Downtime Hours / (Downtime Hours + Operational Hours)

• Maintenance Cost Per Unit = Total Maintenance Cost / # of Units Produced

• Manufacturing Cost Per Unit = Total Manufacturing Cost / # of Units Produced

• Material Yield Variance = Actual Material Use / Expected Material Use

• Overall Equipment Effectiveness (OEE) = Uptime % * Capacity % * Yield %

• Overtime Rate (Percentage) = (Overtime Hours * 100) / Regular Hours

• Percentage Planned Maintenance (PPM) = (# Planned Maintenance Hours * 100) / # Total Maintenance Hours

Sales KPIs

• • Average Conversion Time = (First Sale Date – Lead Receipt Date) / New Customers

  • Average Purchase Value = Sales / Transactions

  • Customer Acquisition Cost (CAC) = Marketing Costs / Customers Acquired

  • Customer Churn Rates = Account Cancellation or non-renewals/Active Customer count

  • Customer Lifetime Value = Lifetime Value = Gross Margin % X ( 1 / Monthly Churn ) X Avg. Monthly Subscription Revenue per Customer

  • Lead to Sales Ratio = Leads Received / Sales

  • Monthly Recurring Revenue (MMR) = # of paying customers * average customer sales

  • Sales Closing Ratio = Quotes Generated / Sales

Supply Chain KPIs

• Dock to Stock = (Put Away Time – Receipt Time)/Items received

• On-Time Delivery = Ontime deliveries / total order count

• Order Accuracy = Orders processed without errors / total orders processed

Guiding Principles

Once we have determined what we want to test, we need some guidance on how to test. These base concepts will help us implement best practices.

Keep Integrity Checks Centralized

Put all integrity checks on a separate worksheet away from calculations and outputs to help remove clutter and make those sections easier to read, audit and maintain.

Make Problem Areas Obvious

Add conditional formatting to cells of concern to help guide correction efforts.

Make Alerts Visible Everywhere

Problems anywhere must be visible everywhere so place an overall integrity check status at or near the top of every worksheet. Include a freeze pane somewhere below the status message so alerts are always visible no matter which section of our worksheet we are working on.

Make Alerts Actionable

Create alert messages that describe the issue AND the corrective action.

Mark Issue Locations

Knowing we have a problem is important but we must also know where the problem is before we can address it.

Implementation Guide

This section covers how to implement our guiding principles. .

Keep Integrity Checks Centralized in an Integrity Checks Table