Strata Financial Management Verification Tool

' STRATA COLLECTIONS VERIFICATION TOOL
' This Excel workbook serves as a central verification tool for collections across multiple systems
' Structure and key formulas provided below

'==============================================
' WORKSHEET: Import_Controls
'==============================================
' Purpose: Tracks import status from multiple systems and standardizes data
' Key components:

' Import Status Tracking Table
' Columns: System_Name | Last_Import_Date | Records_Imported | Exceptions_Found | Status

' Data Source Configuration Table
' Columns: System_Name | Report_Type | Standard_Headers | Column_Mapping

' Import Instructions (Protected cells)
' Step-by-step guide on extracting reports from each system and importing to standard format

'==============================================
' WORKSHEET: Master_Verification
'==============================================
' Purpose: Central verification worksheet with all collections across systems

' Data Table Structure:
' Transaction_ID | System_Source | Property_Name | Unit_Number | Owner_Name | Collection_Type | Collection_Date |
' Amount | Payment_Method | Receipt_Number | Entered_By | Verification_Status | Exception_Code | Notes

' Key Formulas:

' Duplicate Detection (Column N):
=IF(COUNTIFS($C$2:$C$1000,C2,$D$2:$D$1000,D2,$F$2:$F$1000,F2,$H$2:$H$1000,H2)>1,"DUPLICATE","")

' Share Unit Calculation Check (Column O):
=IF(F2="Maintenance_Fee",IF(ABS(H2-(VLOOKUP(D2,Share_Units!$A$2:$B$1000,2,FALSE)*VLOOKUP(C2,Fee_Rates!$A$2:$B$1000,2,FALSE)))>0.1,"AMOUNT_ERROR",""),"")

' Required Field Check (Column P):
=IF(OR(ISBLANK(C2),ISBLANK(D2),ISBLANK(F2),ISBLANK(G2),ISBLANK(H2),ISBLANK(J2)),"MISSING_DATA","")

' Combined Verification Status (Column L):
=IF(OR(N2"",O2"",P2""),"EXCEPTION","VERIFIED")

'==============================================
' WORKSHEET: Daily_Reconciliation
'==============================================
' Purpose: Daily balancing and reconciliation

' Reconciliation Table Structure:
' Date | System_Name | Total_Collections | Maintenance_Fees | Sinking_Fund | Other_Collections |
' Bank_Deposit_Amount | Variance | Reconciliation_Status | Reconciled_By

' Key Formulas:

' System Totals (Columns C-F):
=SUMIFS(Master_Verification!$H$2:$H$1000,Master_Verification!$B$2:$B$1000,B2,Master_Verification!$G$2:$G$1000,A2)

' Variance Calculation (Column H):
=G2-C2

' Reconciliation Status (Column I):
=IF(ABS(H2)