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:
- RentViewer by Metric-X
- SQL database for storing the data
- SQL code for calculating the cash flows and looking up prior period balances
- 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.