Tenant Move in Inspection Sheet

TENANT MOVE-IN INSPECTION FRAMEWORK

Document ID: SOP-OPS-RE-042
Version: 4.2.0
Author: Senior Financial Architect & Operations Lead
Classification: Institutional Operations / Property Management

---

1. WORKBOOK ARCHITECTURE & TAB BREAKDOWN

The Move-In Inspection Workbook is engineered as a controlled, audit-ready operational framework for multi-family and commercial asset management. It eliminates manual data entry errors through strict schema enforcement, dynamic relational lookups, and automated conditional formatting.

+-------------------------------------------------------------------------+
|                        WORKBOOK ARCHITECTURE                            |
+-------------------------------------------------------------------------+
|                                                                         |
|  [01_CONTROL] ---> Configuration, Constants, Asset Metadata             |
|       |                                                                 |
|       v                                                                 |
|  [02_INSPECTION_MASTER] ---> Core Operational Engine (12-15+ Line Items)|
|       |                                                                 |
|       +---> [03_EXECUTIVE_DASHBOARD] ---> KPI Cards & Portfolio Health  |
|       |                                                                 |
|       +---> [04_AUDIT_LOG] -------------> Immutable Change Tracking     |
|                                                                         |
+-------------------------------------------------------------------------+

Tab Breakdown & Data Flow

Tab 1: 01_CONTROL

• Purpose: Houses global configuration data, property identifiers, validator lists, and cost-basis constants.
• Data Flow: Acts as the single source of truth for dropdown lists (Condition Ratings, Room Types, Repair Categories) utilized across operational data entry sheets.

Tab 2: 02_INSPECTION_MASTER

• Purpose: The primary operational ledger where inspectors record spatial conditions, aesthetic anomalies, and financial liabilities at lease inception.
• Data Flow: Pulls static parameters from 01_CONTROL via Data Validation. Feeds calculated cost variances and repair metrics directly into 03_EXECUTIVE_DASHBOARD.

Tab 3: 03_EXECUTIVE_DASHBOARD

• Purpose: High-level summary interface designed for Asset Managers, providing immediate visibility into unit habitability, capital expenditure requirements, and tenant liability exposure.
• Data Flow: Aggregates line-item metrics from 02_INSPECTION_MASTER using robust statistical and logical array formulas.

Tab 4: 04_AUDIT_LOG

• Purpose: Immutable record of adjustments, timestamp logs, and sign-off verifications required for compliance and security deposit dispute resolution.
• Data Flow: Captures user actions and status shifts from 02_INSPECTION_MASTER.

---

2. MASTER DATA TABLE (02_INSPECTION_MASTER)

The following operational ledger tracks spatial elements, baseline condition, move-in status, and financial chargebacks.

---

3. EXPLICIT FORMULA GLOSSARY

To ensure financial accuracy and operational integrity, all calculations within the framework rely on strict programmatic syntax.

=SUM(F2:F100)

• Purpose: Aggregates total estimated remediation and repair costs across all inspected line items.
• Context: Utilized in the Executive Dashboard KPI card to display total exposure.

=AVERAGEIF(D2:D100, "<>N/A", F2:F100)

• Purpose: Calculates the mean repair cost for valid, active inspection items, filtering out null or non-applicable entries.
• Context: Benchmarks average anomaly severity per unit.

=XLOOKUP(C2, '01_CONTROL'!$A$2:$A$50, '01_CONTROL'!$B$2:$B$50, "Unassigned", 0, 1)

• Purpose: Relational lookup searching for standard baseline component cost or vendor assignment based on the component description, defaulting to "Unassigned" if missing.
• Context: Automated data enrichment in operational data entry.

=IFERROR(INDEX(I2:I100, MATCH("Pending", I2:I100, 0)), "All Cleared")

• Purpose: Scans the resolution status column to isolate the first unresolved item, returning an operational clear flag if all tasks are finished.
• Context: Quick-status indicator for property managers clearing a unit for occupancy.

=COUNTIFS(D2:D100, "Poor", G2:G100, "Yes")

• Purpose: Counts the number of severely damaged items ($D = \text{"Poor"}$) that are explicitly designated as tenant-liable ($G = \text{"Yes"}$).
• Context: Determines direct security deposit withholding exposure.

=SUMIFS(F2:F100, G2:G100, "Yes", I2:I100, "<>Resolved")

• Purpose: Calculates total unresolved financial liabilities assigned specifically to the incoming tenant.
• Context: Drives final move-in billing and deposit reconciliation statements.

---

4. DATA VALIDATION & CONDITIONAL FORMATTING RULES

Data Validation Parameters

+-------------------+---------------------------+-----------------------------------------------+
| Target Range      | Validation Criteria       | Allowed Values / Source Range                 |
+-------------------+---------------------------+-----------------------------------------------+
| D2:D100           | List (In-Cell Dropdown)   | "Excellent, Good, Fair, Poor, N/A"            |
| E2:E100           | List (In-Cell Dropdown)   | ='01_CONTROL'!$B$2:$B$15 (Action Categories)  |
| F2:F100           | Decimal                   | Greater than or equal to 0                    |
| G2:G100           | List (In-Cell Dropdown)   | "Yes, No"                                     |
| I2:I100           | List (In-Cell Dropdown)   | "Pending, Scheduled, Resolved, Disputed"      |
+-------------------+---------------------------+-----------------------------------------------+

Conditional Formatting Rules

Rule 1: Critical Defect Highlighting (Rows where Condition is "Poor")

• Applies To: $A$2:$J$100
• Formula Logic: =$D2="Poor"
• Fill Color: Soft Crimson (#FADBD8)
• Font Color: Dark Red (#78281F, Bold)
• Operational Intent: Immediately highlights major structural, aesthetic, or functional deficiencies requiring immediate capital attention or contractor dispatch.

Rule 2: Financial Liability Flag (Tenant-Liable Unresolved Costs)

• Applies To: $F$2:$F$100
• Formula Logic: =AND($G2="Yes", $I2<>"Resolved")
• Fill Color: Soft Amber (#FCF3CF)
• Font Color: Dark Amber (#7D6608, Bold)
• Operational Intent: Flags items impacting security deposit deductions that require ongoing tracking and tenant notification.

Rule 3: Resolution Status Tracker (Completed Items)

• Applies To: $I$2:$I$100
• Formula Logic: =$I2="Resolved"
• Fill Color: Soft Emerald (#D4EFDF)
• Font Color: Dark Green (#145A32)
• Operational Intent: Visually mutes completed work orders to let operators focus on pending tasks.

---

5. EXECUTIVE SUMMARY KPI CARDS (03_EXECUTIVE_DASHBOARD)

The Executive Dashboard consolidates operational performance into clean, high-level metrics for asset review and compliance reporting.

Dashboard Layout Schematic

+-------------------------------------------------------------------------+
|                      EXECUTIVE DASHBOARD METRICS                        |
+--------------------------------+----------------------------------------+
| [ Total Inspection Items ]     | [ Total Remediation Cost ]             |
|   14                           |   $2,165.00                            |
+--------------------------------+----------------------------------------+
| [ Tenant Liability Exposure ]  | [ Critical Defects (Poor) ]            |
|   $945.00                      |   4                                    |
+--------------------------------+----------------------------------------+
| [ Resolution Rate ]            | [ Operational Status ]                 |
|   42.86%                       |   ACTION REQUIRED (Pending Sign-off)   |
+--------------------------------+----------------------------------------+

---

End of Framework Specification.