Best Practices: Integrity Checks
Post date: Sep 2, 2020 4:32:07 PM
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
As I implement this concept, I have a table named tblIC (pictured above). It has six columns. They are (listed in order of importance):
Make Problem Areas Obvious by Highlighting Problem Cells
When a test fails, we can have Excel automatically highlight cells associated with the test through conditional formatting by following these steps.
1. For the test in the Integrity Checks table, select its first, and (if applicable) second reference’s cells (not the cells in the table but the cells those references point to).
2. On Excel’s ribbon click Home > Conditional Formatting > New Rule
3. Select Use a formula to determine which cells to format.
4. Enter =NOT(<Passed>) where <Passed> is the Integrity Check’s Passed cell’s address for this alert.
If we delete a test, or change its reference we should also remove its conditional formatting. If using BXL Integrity Checks click Remove Cond. Format to remove all existing integrity check based conditional formats. We can then use Add Cond. Formats to replace everything we deleted
Make Alerts Visible Everywhere by Adding an Overall Status Alert
If we have a problem anywhere, it must be visible everywhere. Only highlighted cells in our monitor's screen are visible and chances are, that in a complex model with many worksheets occupying far more cells than can fit in our monitor's screen, problem areas are somewhere other than where we are looking. We need an overall status alert that, when a problem exists, displays on our screen no matter which section of our model we are looking at.
Location
Add the overall status alert near the top of each worksheet. I prefer to put the alert below the worksheet’s title and above the worksheet’s freeze pane line.
Content
Include the first failed test’s name and counter measure. Make it hyperlink back to the integrity check table where we can find what type of test was used and where each reference is located.
I use an Excel table to hold integrity checks named tblIC. Tables afford us structured references which make formulas self documenting. Self documenting formulas are longer than cell references but much easier to understand, thus, my formula recommendation (below) is rather long. BXL Integrity Checks automates entering this for us.
Formula
I use this formula with my integrity check table (tblIC) in cell A2 of every worksheet.
=HYPERLINK("#" & CELL("address",tblIC),
IF(AND(tblIC[Passed]),
"Checks Passed",
INDEX(tblIC[Name],MATCH(FALSE,tblIC[Passed], 0)) & " failed. " &
INDEX(tblIC[Corrective Action], MATCH(FALSE, tblIC[Passed], 0))))
It uses MATCH() to find the first failed test and INDEX() to get and display the test's name and test's corrective action message.
Conditional Format
When all tests in the integrity checks succeed format the status font green (no fill); otherwise, format with light red fill and dark red font. Below is the conditional format formula for a failed test. For a successful test just remove the NOT() function
=NOT(AND(INDIRECT("tblIC[Passed]")))
Conditional formatting does not like structured references. To use structured references in conditional formats we must use INDIRECT() to convert structured references to acceptable cell addresses.
NOTE! INDIRECT() is NOT volatile when used in conditional formatting or data validation
Make Alerts Actionable
It is easy just to say what went wrong like "Balance Sheet doesn't balance". While that's informative, it isn't very helpful. We can improve this a bit by adding the words "Adjust model so " and then add the first thing that was compared ("Total Assets") how it was compared ("equal") and what it was compared to ("Total Liabilities and Shareholder Equity"). The resulting message is: "Adjust model so Total Assets equal Total Liabilities and Shareholder Equity".
Mark Issue Locations and Take us There with Hyperlinks
I add each reference used in a test to the Integrity Checks table under Reference 1 and (if applicable) Reference 2 columns. That shows us where the problem is. I also add hyperlinks to each reference so we can click the reference and be taken directly to the problem. Here is an example of the formula I use:
=HYPERLINK("#" & CELL("address", Tests!$E$8:$H$8),"Tests!$E$8:$H$8")
Summary
Creating best-in-class integrity checks can save our reputation and our customers some money. Adding and managing them is worth the effort. To save effort I created BXL Integrity Checks add-in. I offer it for just $10 so my friends in less affluent areas can afford it. It is available at Eloquens.Com. Want to see it in action? Here is its video tutorial.
Test Types
Through research and help from other professionals, I have identified several test types and their associated formula template. Each test type works well for specific kinds of tests. These formulas go in the Integrity Checks table Pass column.
<A> (First Reference) Replace with First Reference
<B> (Second Reference) Replace with Second Reference
<M> (Minus) If your standard applies minus signs to credits replace with + else -
<O> (Operator) Replace with =, >, >=, <, <=, <> as appropriate
<P> (Plus) If your standard applies minus signs to credits replace with - else +
