Post date: Jul 28, 2018 12:44:34 AM

What are Integrity Checks?

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:

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.

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%")

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.

Conditional Format Specifications

Assignments

• 1. Read Conditional Formatting.

  2. Read HYPERLINK function.

  3. Read CELL function.

  4. Complete the Lab and add tblIC to your model.

  5. Complete the Quiz

Quiz

• 1. The difference between Integrity Checks (IC) and Data Validation (DV) is:

     1. DV is intended to prevent entry errors. IC is intended to report model errors.

     2. DV is applied to individual cells. IC monitors groups of cells but isn't applied to any cells.

     3. DV can offer a list of options for the user to select from. IC can't.

     4. DV can provide pop-up text when its cell is clicked. IC doesn't.

     5. IC can take us to remote problem cells. DV can't.

     6. All of the above

     7. None of the above

  2. Tables auto-extend formulas so we shouldn't use them for model checks.

• • 1. TRUE

    2. FALSE

    3. 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..