Advanced Excel Modeling - Integrity Checks
Post date: Jul 28, 2018 12:44:34 AM
In the previous section we discussed several different Table Types. This section discusses a special table type I call Integrity Checks.
Lesson Objectives
At the end of this lesson you will be able to:
Describe the difference between Integrity Checks and Data Validation
Create formulas to check for problems in our models
Apply Conditional Formatting to make problems obvious
Use HYPERLINK() to take users to the problem area.
Create tblIC to contain/structure all of our model's integrity checks.
This is under construction.
What are Integrity Checks?
Integrity Check – Tests for model conditions that if present require alerting anyone viewing the project.
Integrity checks alert everyone to problems before our model's results can be considered valid. I place a formula at the top of every worksheet conveying the overall integrity check status.In this example (above) something has failed and we are alerted to:
Adjust Seasonal Product Demand for each month to total 100%
In the example we can also see that month 1 of the Seasonal Product Demand table (right side of picture) totals 90% and also glows red. When all integrity checks pass the formula displays Integrity checks passed. In any case the formula uses HYPERLINK() to send us to where I keep individual integrity check formulas: tblIC.
Best Practices
Keep Integrity Checks Centralized
Separate integrity checks from inputs and calculations to keep those sections uncluttered and thus, easier to read, audit and maintain. Keep integrity checks together so there is one place to manage them. Place integrity checks after the sections they monitor to maintain "reading order" of our model structure.
Make Alerts Actionable
Create alert messages that describe the problem and advise, as best we can, how to correct it.
Make Alerts Visible Everywhere
Problems anywhere must be visible everywhere. Place an overall status message at or near the top of each worksheet and above the freeze pane line. Add a freeze pane line if at all appropriate to keep the overall status message visible at all times.
Make Problem Areas Obvious
Conditionally format problem areas to help guide corrective actions.
Hyperlink Alerts to Problem Locations
Add hyperlinks to alerts to quickly take us to problem areas.
Integrity Checks Table
I name the integrity checks table tblIC. Here is where I test for all conditions that I can think of that might go wrong. It includes things like:
Make sure balances balance
Make sure allocations add up to 100%
Make sure tables contain the proper number of rows to accommodate user requirements such as the number of periods in the model, the number of customers, or the number of products.
tblIC contains just two columns: Passed and Corrective Action
Passed
This column holds a formula that checks for a model problem such as a balance sheet that does not balance or allocation percentages that do not add up to 100%. Formulas must evaluate to TRUE or FALSE. Here are some sample formulas addressing common situations:
Accounts must balance
Row count must match user entry
Allocation must total 100%
=SUM(tblBS[Assets])=SUM(tblBS[Liabilities & Shareholder''s Equity])
=ROWS(tblPrd)=Products
=MAX(tblSPD[[#Totals],[1]:[12]])*MIN(tblSPD[[#Totals],[1]:[12]])=1
To make conditions obvious we can apply conditional formatting so when the formula evaluates to TRUE (passed the test) it glows green and when FALSE (failed the test) it glows red.
Corrective Action
This column holds either a formula or text that, should the integrity check find a problem, tells the user what is wrong in a way that informs them how to correct the situation.
Error alerts must suggest a corrective action even if that action is to call the developer for help or a fix.
Here are some examples:
Tell modeler the Balance Sheet does not balance and take them to the balance sheet table.
=HYPERLINK(CELL("address",tblBS),"Assets must equal Liabilities + Shareholder Equity")
Tell users seasonal demand entries need to total 100% and take them to the seasonal demand table.
=HYPERLINK(CELL("address",tblSSD[1]),"Adjust Seasonal Size Demand for each month to total 100%")
Data Validation vs Integrity Checks - Data validation can only check user entries in single cells. It cannot check formula results. Integrity checks can. And integrity checks can find problems in groups of cells or entire structures (ex. balance sheet that doesn't balance).
How To:
Conditional Formatting
Objective: Make problems stand out.
We will need to apply Conditional Formatting to the Passed column's cells. To apply Conditional Formatting first select the cells to format then use Excel's ribbon path Home tab > Styles group > Conditional Formatting icon. For this particular conditional format we will use Highlight Cells Rules > Equal To..
To create the effect we want we need to create two rules. One applies Green Fill with Dark Green Text when our model passes a particular integrity check and Light Red fill with Dark Red Text when it fails.
If Passed = TRUE, format green; otherwise, format red.
HYPERLINK() Function
Objective: Take users to where problems exists so they can correct them (when appropriate).
We want problems corrected swiftly and with as little effort as possible. We can help by providing a hyperlink from our model integrity checks table to the cells where the problem exists. HYPERLINK() gives us that functionality. HYPERLINK() needs two things: the cell's address to take us to; and text to click. Here is an example:
=HYPERLINK("#" & CELL("address",tblLP), "Loan Properties Table")
This formula display "Loan Properties Table" as clickable text that, when clicked, takes us to tblLP. Notice the CELL() function. The CELL() function with its info_type parameter set to address translates its reference parameter to a cell address. This allows us to put a table's object name (tblLP) in our HYPERLINK() formula to help self-document it. Now that we know what CELL() does we can easily see HYPERLINK() will take us to tblLP when we click on Loan Properties Table.
Overall Status Notification
Objective: Alert everyone to the overall status of integrity checks form every worksheet.
Step 1 - Create Checks Passed property.
We want everyone to know what the overall status of integrity checks are no matter which worksheet they are looking at. To accomplish this I create a property labeled Checks Passed in a calculated properties table. The property's formula uses the AND operator over every cell in the Integrity Checks Passed column. If they are all TRUE then the overall status is passed. This is the formula I use:
=IFERROR(AND(tblIC[Passed]),FALSE)
Step 2 - Create alert formula
With the property Checks Passed created and named we can use a simple formula to alert everyone to the overall status of integrity checks. It uses the HYPERLINK() function to direct anyone to the integrity checks table where they can see every integrity check's individual status. If nothing is wrong the text reads "Integrity checks passed." If any check fails a VLOOKUP() function finds the first failed status and displays its corrective action text. Since VLOOKUP always returns the first matching record we should take care to place integrity checks that depend on other checks last and the simplest checks first. Here is the formula I place at the top of every worksheet:
=HYPERLINK(CELL("address",tblIC),IF(Checks_Passed,"Integrity checks passed",
VLOOKUP(FALSE, tblIC,2,FALSE)))
Step 3 - Place Step 2's formula at the top of every worksheet (just under the title) and apply conditional formatting.
Lab
In our new model we can have any number of loans but whatever number of loans we intend to model we must have that many rows in tblLP (Loan Item Properties table) and tblProcess (Process table). If not, we need to alert users to this problem.
Extending Excel's tables, while simple, is beyond most users' skill set. Later on we will automate this. Even with automation we want this model check in place in case something goes wrong with the automation.
Create table tblMC (Model Checks) as shown above. Use the specification tables below as guides for adding formulas, styles, and conditional formatting. Each formula checks to make sure the number of rows in either the loan item properties table or process table are appropriate for the number of loans indicated by the user in our model's properties table.
Table Row Specifications
Excel's tables normally do not like different formulas in the same column. But in this case we want different formulas in each row. So when we see Excel correcting us by automatically replicating a formula to all rows in the same column just use CTRL-Z to undo the replication. This will leave our formula intact.
Conditional Format Specifications
Assignments
Read Conditional Formatting.
Read HYPERLINK function.
Read CELL function.
Complete the Lab and add tblIC to your model.
Complete the Quiz
Quiz
The difference between Integrity Checks (IC) and Data Validation (DV) is:
DV is intended to prevent entry errors. IC is intended to report model errors.
DV is applied to individual cells. IC monitors groups of cells but isn't applied to any cells.
DV can offer a list of options for the user to select from. IC can't.
DV can provide pop-up text when its cell is clicked. IC doesn't.
IC can take us to remote problem cells. DV can't.
All of the above
None of the above
Tables auto-extend formulas so we shouldn't use them for model checks.
TRUE
FALSE
Use CTRL-Z to ...
Summary
As we create our models we need to look for things that can go wrong and the best way to mitigate those problems. If we can't avoid them completely we need to tell the user how to correct them..