Data Reconciliation: Matching Source to Summary

Part 1: The "Grand Total" Check

The simplest and quickest form of reconciliation is to compare the grand totals in your summary against the totals from your source data. This is your first line of defense against major errors.

Let's try this with the KPIs you built in a previous lesson on your Summary sheet.

1. Navigate to your Summary sheet. Find the KPI for "Total Incidents" that you created using COUNTA or a similar function. Note the number.
2. Now, go to your Tracker sheet. Click on the header of the Incident ID column in your Excel Table to select all the incident IDs.
3. Look at the Status Bar at the bottom of your Excel window. You should see a Count that tells you the total number of non-blank cells.
4. Do these two numbers match? They should.

Now let's check a conditional total:

1. On the Summary sheet, find your KPI for "Open Incidents", which you calculated with COUNTIFS. Note the number.
2. Go back to the Tracker sheet. Click the filter dropdown arrow on the Status column header and filter for "Open" status only.
3. Select the Incident ID column again.
4. Check the Count in the Status Bar. Does it match your KPI?

This manual check is excellent for a quick validation, but it's not dynamic. If you change your data, you have to remember to perform the check again. Let's build something more robust.

Part 2: Formula-Based Reconciliation Checks

A more reliable method is to build a small, dedicated "Reconciliation" or "Validation" section right on your Summary sheet. This section will contain formulas that automatically check your KPIs against the source data.

Let's create a validation check for your main KPIs. On your Summary sheet, find some empty space and set it up like this:

Now, let's fill it in:

1. Summary Figure column: In this column, simply link to the cells that contain your existing KPI calculations. For "Total Incidents", the formula might just be =B2 (if your KPI is in cell B2).
2. Source Figure column: In this column, you will write new formulas that calculate the figures directly from the Tracker sheet.
   • For "Total Incidents": =COUNTA(Incident_Tracker[Incident ID]) (assuming your table is named Incident_Tracker).
   • For "Open Incidents": =COUNTIF(Incident_Tracker[Status], "Open")
   • For "Closed Incidents": =COUNTIF(Incident_Tracker[Status], "Closed")
3. Difference column: In this column, subtract the Source Figure from the Summary Figure. For the first row, the formula would be =B10-C10 (adjust cell references as needed).

If your report is accurate, the "Difference" column should show 0 for all metrics. This section now acts as a "health check" for your report. If a zero ever changes, you know there's a discrepancy that needs immediate investigation.

The article below discusses this best practice of building in validation checks to ensure data accuracy.

This method is great for validating summary figures against a single source table. But what if you need to compare two separate lists?

Part 3: The PivotTable Method for Comparing Two Datasets

This is an incredibly powerful technique used by analysts everywhere. Imagine you receive a list of incidents from another department and you need to compare it to your own tracker to find what's missing or what's different. This is a common task for an MIS Executive.

The core idea is to:

1. Combine both lists into one large table.
2. Add a "Source" column to label which list each row came from.
3. Use a PivotTable to count occurrences of each item by source.

Let's simulate this.

Create a Second Dataset

1. On your Tracker sheet, select about 20 rows of your data and copy them.
2. Create a new worksheet and name it Team_B_List. Paste the data there.
3. Now, introduce some discrepancies to simulate a real-world scenario:
   • Delete 2-3 rows entirely from the Team_B_List.
   • On one row, change the Severity from "High" to "Medium".
   • On another row, change the Incident ID to something new (e.g., change INC-005 to INC-999).

You now have two similar, but not identical, lists. Let's reconcile them.

Execute the Reconciliation

The following video provides a perfect walkthrough of the method. While it uses a bank reconciliation as an example, the steps are universal. We will adapt it for our incident lists.

After building the PivotTable, you can instantly see the discrepancies:

• Items with a 1 under MyTracker and a 1 under TeamB are matched.
• Items with a 1 under one source but a blank under the other are present in one list but missing from the other.
• By filtering the PivotTable to show only the mismatches, you can quickly generate a list of items to investigate.

Here is a static image using a VLOOKUP to achieve a similar row-by-row comparison, another valid reconciliation technique for when your data is structured side-by-side.

Conclusion

Excellent work. You have just learned one of the most important quality control processes in MIS reporting. Building a report is one thing; proving it is correct is another, and it is a skill that builds immense trust with your stakeholders.

Key Takeaways:

• Reconciliation is Essential: It's a non-negotiable step to ensure the accuracy and credibility of your reports.
• Start Simple: Always perform a quick "Grand Total" check as a first pass.
• Automate with Formulas: Create a dedicated validation section in your reports that uses formulas to provide a "live" check of your KPIs against source data.
• Master the PivotTable Method: For comparing two separate datasets, combining them with a "Source" column and using a PivotTable is the most efficient and powerful technique.

You have now completed the module on building dynamic MIS reports. You have learned to structure data, write formulas, analyze trends, and now, validate your work.

In our next lesson, we will begin the exciting process of bringing all this work together. We will start Module 9: Creating Interactive Dashboards by planning a one-page dashboard layout to display your KPIs and charts in a professional, user-friendly format.