Blog · Board and management reporting · Finance and FP&A teams
Build a commercial property dashboard from occupancy, rental billing, cash applications and operating expense reports. Use a checked Excel layout to compare properties without mixing physical occupancy, collections and accounting results.
A real estate dashboard in Excel should compare commercial property occupancy, rental collections and operating costs for the same month. Build it from the underlying property reports, with separate measures for physical space, billed rent and cash. A property can be occupied and still have overdue rent, so one headline percentage cannot stand in for the others.
Use a month-end rent roll or occupancy report, a rental-charge register, a cash-application report, a tenant receivables report and an operating-expense export. Match them using a stable property ID and the same reporting month. Retain source references so each summary can be checked against the original report.
For occupancy, this layout uses physically occupied rentable square feet at month end. It does not substitute signed leases for physical occupancy. For collections, it measures cash applied to that month's rental charges; total receipts are reported separately. For expenses, it uses a defined list of property operating accounts and excludes capital spending.
The broader real estate dashboards guide covers the management questions behind the report. This page provides the workbook layout and its checks. Keep valuation and investment-return measures outside this operating report.
Create one summary row per property and month, with these columns in an Inputs sheet. Keep any transaction detail on separate sheets so repeated tenant rows do not duplicate area totals.
| Column | Field | How to fill it |
|---|---|---|
| A | Property ID | Consistent identifier in every source report |
| B | Month | First day of the reporting month, stored as a date |
| C | Rentable area | Approved month-end area in square feet |
| D | Occupied area | Physically occupied rentable square feet at month end |
| E | Opening rental receivables (USD) | Prior month's closing rental balance |
| F | Rental charges (USD) | Current-month charges, net of billing adjustments |
| G | Rental cash applied (USD) | Cash applied to rental balances during the month |
| H | Rental write-offs (USD) | Separately identified reductions in receivables |
| I | Operating cost (USD) | Selected property operating expense accounts |
| J | Cash applied to current charges (USD) | Subset of G settling the month's charges |
The receivables bridge assumes no other movements. If transfers, reversals or reclassifications occur, add explicit columns and include them in the bridge rather than forcing a balancing adjustment.
For a property named Oak in Dashboard!A2 and a month in Dashboard!N1, rental charges can be summarized from this sheet with:
=SUMIFS(Inputs!$F$2:$F$1000,Inputs!$A$2:$A$1000,$A2,Inputs!$B$2:$B$1000,$N$1)
Microsoft's SUMIFS documentation describes summing values that meet multiple criteria. Use the same property and month filters for every amount, and extend the range to cover all input rows.
These October 2026 figures describe two US commercial properties. The first table contains the occupancy inputs and the rental activity; all monetary amounts are USD.
| Property | Rentable area (sq. ft.) | Occupied area (sq. ft.) | Opening receivables (USD) | Rental charges (USD) | Rental cash applied (USD) | Write-offs (USD) |
|---|---|---|---|---|---|---|
| Oak | 50,000 | 45,000 | 5,000 | 90,000 | 86,000 | 0 |
| Pine | 30,000 | 24,000 | 3,000 | 48,000 | 45,000 | 1,000 |
| Total | 80,000 | 69,000 | 8,000 | 138,000 | 131,000 | 1,000 |
Use these additional inputs to distinguish current collections from total rental cash and to compare operating costs:
| Property | Cash applied to current charges (USD) | Cash applied to prior balances (USD) | Operating cost (USD) |
|---|---|---|---|
| Oak | 84,000 | 2,000 | 32,000 |
| Pine | 44,000 | 1,000 | 18,000 |
| Total | 128,000 | 3,000 | 50,000 |
Cash applications check in both directions: Oak's 84,000 + 2,000 = 86,000 and Pine's 44,000 + 1,000 = 45,000. The portfolio's 128,000 + 3,000 = $131,000 ties to the rental cash-applied column. Reconcile that amount to receipts after separately identifying deposits, advances and unapplied cash.
In columns K:N of Inputs, calculate the following for row 2 and fill down:
| Column | Measure | Excel formula |
|---|---|---|
| K | Physical occupancy | =IF(C2=0,"",D2/C2) |
| L | Closing rental receivables (USD) | =E2+F2-G2-H2 |
| M | Current-charge collection rate | =IF(F2=0,"",J2/F2) |
| N | Billed rent less selected operating cost (USD) | =F2-I2 |
The last subtotal is deliberately labeled by its inputs. It is not automatically recognized rental income or net operating income.
| Property | Physical occupancy | Closing receivables (USD) | Current-charge collection rate | Billed rent less selected operating cost (USD) |
|---|---|---|---|---|
| Oak | 90.00% | 9,000 | 93.33% | 58,000 |
| Pine | 80.00% | 5,000 | 91.67% | 30,000 |
| Total | 86.25% | 14,000 | 92.75% | 88,000 |
Portfolio occupancy is 69,000 / 80,000 = 86.25%. Current-charge collections are 128,000 / 138,000 = 92.75%, rounded to two decimals. Recompute both ratios from their total inputs; do not average the property percentages.
The receivables proof is 8,000 + 138,000 - 131,000 - 1,000 = $14,000. Match it to the rental receivables report. The operating subtotal also reconciles: 138,000 - 50,000 = $88,000, equal to 58,000 + 30,000.
An independent area-weighted check of occupancy is:
=SUMPRODUCT(C2:C3,K2:K3)/SUM(C2:C3)
Microsoft documents SUMPRODUCT as multiplying corresponding array values and summing the products. Here it returns the same 86.25% as total occupied area divided by total rentable area.
Show occupancy, collection rate and operating cost separately, with property rows below them. For the chart mechanics, use a pivot chart in Excel. If operating expenses include utilities, an energy dashboard in Excel can support the separate consumption review; lower cash collections alone do not explain energy usage.
Covirage is not affiliated with Microsoft.
Bring your commercial property occupancy, rental collections and operating-cost workbook. Covirage maps the columns; its tools compute and check the comparisons and reconciliations, while the external AI model explains the result with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.
See Excel analysis for the supporting context.
Choose the basis that matches the property and keep it consistent. For office and industrial properties, occupied rentable area divided by total rentable area avoids giving differently sized spaces equal weight. State whether occupied means physically occupied, leased or rent-producing; those definitions can differ.
Cash received this month can settle earlier bills or include advance payments. A current-month collection rate needs cash applied to the current month's charges. Keep total cash receipts separate and reconcile their application to current charges, prior balances and any unapplied amount.
Not necessarily. Billing is not automatically recognized rental revenue, and the expense extract may omit items included in your property's accounting definition. Label the subtotal precisely and reconcile it to the management accounts before presenting it as net operating income.