Get in Touch

Rental reconciliation and collection analysis tool

Home / Case Study / Rental reconciliation and collection analysis tool

Industry: Real Estate

Client : A US-based REIT (listed on NYSE) seeking help to track tenant deferrals through the COVID pandemic and build a data efficiency tool to reconcile the project managers logs to the accounting system and forecast the collection periods.

Problems

The project managers used an Excel file to manually record all tenants rent details for actual and forecast months, The Excel file had:

  • No process of reconciling the rent roll data entered by the project managers with the data recorded in their property management software called Yardi
  • No differentiation between rent abatements and write-offs for missing rental payments resulting in inaccurate reporting of future rent collections
  • No bad debt report to track the tenants with delayed payments
  • No tracking of over/under collection of deferred rent schedule
  • Inaccurate monthly reporting of new deferrals regarding write-offs, abatements and collections amounts
  • No reconciliation between the list of properties in Yardi and the Excel file

Solution

FAB designed and built a new Excel tool from scratch with:,

  • Process to import the different datasets from Yardi and project manager data into one Excel file,
  • This allowed highlighting differences for every tenant using automated checks and helped in reconciliation,
  • Workspace to categorize any missing payment as abatement or write-off,
  • Improved bad debt provisioning and reporting by categorizing each tenant’s payment delays as either Red, Amber, or Green and tracked any changes in the categories over time,
  • A report to track actual receipt against the expected deferred payment schedule,
  • Dashboard to report deferrals – additional deferrals, repayment abated, repayment written-off, and deferral recoveries by individual or group of properties and tenants,
  • Process to track missing properties on every update in the project managers workbook through an automated property list ,

Client Benefits

  • FAB team used the new tool to reconcile all 800+ rent roll differences between Yardi and the Project manager’s file with proper reasoning for each.
  • This ensured a smooth quarterly audit for the client.
  • Extremely efficient process to update the new rent roll model from different data sources and publish visually stunning reports.
  • Accurate deferrals and rent collection tracking after categorization of each missing payment and reconciliation of Yardi and project managers logs.

Client Testimonial

FAB assisted us with integrating our operating system data from multiple sources into easy-to-use financial models.

The models did both, reported accurately, and were integral to the management decision-making process.

The quality of the work provided by FAB Analytics is first-class.

The team was always looking to create client efficiencies and were quick to provide multiple options on how to proceed.

Projects are managed well, on time, and under budget, always delivering as discussed and expected.

Their ability to use their financial acumen and data analytic skills in a different functionality areas is invaluable for process driven analytical financial team.

Chief Finance Officer.

Problems