Net Cash Flow Report 

Industry: Multifamily

Company Profile: Developer and Operator of commercial, multifamily and student housing portfolios

Business Challenge:

Calculating cash flow at the property and property group level was labor-intensive and prone to errors. Multiple P&L and Balance Sheet reports had to be run, and the data exported to Excel, and then with complex Excel formulas and macros, the cash flow reports were created.

As the portfolio grew, the Excel files became more complex.

Solution:

Metric-X utilized our API and database knowhow to automate the creation of the Cash Flow reports.

We connected to the property management system (Rent Manager) through its API, and extracted the P&L and Balance Sheet report for each property.

The line items involved in the Net Cash Flow calculation are shown below.

The calculation involved looking up beginning balances in prior period, and this increased the complexity and required additional data validation effort.

Cash Flow Line Item  Data Source 
 Revenue  P&L 
 Expense  P&L 
 Net Bad Debt   P&L 
 NOI  P&L 
 Replacement Reserve  Balance Sheet 
 Non Operating Income   P&L 
 Capital Expenditure  P&L 
 Total Corporate Expenses  P&L 
 Non Operating Expenses  P&L 
 Mortgage Balance Sheet (Change in Mortgage Balance) P&L (Mortgage Expenses) 
 Distribution  Balance Sheet 
 Excess  Calculation 

 

Benefits:

Automating the Net Cash Flow calculations delivered many benefits: 

  • Historical data now resides in a single repository and reduces reliance on old Excel files
  • The cash flows can easily be compared to targets and proformas
  • Rolling up from property to property group and funds is simple
  • Cash flow trends can be visualized in charts
  • Chances of introducing manual errors is eliminated
  • Changes to underlying data can be easily reflected in the reports

Tools:

  1. RentViewer by Metric-X 
  2. SQL database for storing the data 
  3. SQL code for calculating the cash flows and looking up prior period balances 
  4. API of the Rent Manager Property Management System 

 

Visualize the Net Cash Flow Dashboard

Visit the RentViewer website to see the Net Cash Flow Dashboard.