Retail property and CAM reconciliation documents on a landlord operations desk.

!Retail property and CAM reconciliation documents on a landlord operations desk.

If you manage a small NNN retail portfolio, the year-end CAM reconciliation usually does not fail because one formula is wrong. It fails because the spreadsheet never captured the right inputs in the first place.

By the time December closes, you are trying to answer questions that should have been tracked all year: what is recoverable, which tenants have caps or exclusions, what was billed monthly, and what support you can send if a tenant challenges the reconciliation.

A spreadsheet can still be useful. For many landlords, it is the first system of record. But it has to be built around how NNN reconciliation actually works, not just around accounting categories.

Use this checklist before year-end so your CAM spreadsheet is ready for reconciliation, tenant billing, and dispute prevention.

Start With the Lease, Not the Expense Report

The most common CAM spreadsheet mistake is starting with a general ledger export and trying to make the lease fit afterward.

That is backwards.

Before you sort expenses, create a lease-control tab for every tenant. This tab should summarize the rules that decide whether an expense is recoverable, capped, excluded, allocated, or subject to notice requirements.

At minimum, track:

Do not rely on memory here. Two tenants in the same strip center can have different CAM caps, different exclusions, and different audit windows. If your spreadsheet treats them the same, the reconciliation will be fast but wrong.

Build a Clean Expense Detail Tab

Your expense tab should not be a dump of every property-related cost. It should be a controlled worksheet where each line can survive a tenant question.

Recommended columns include date, vendor, invoice number, expense category, property, total invoice amount, recoverable amount, non-recoverable amount, allocation method, lease basis, support link, and notes for unusual treatment.

The key column is not the total invoice amount. It is the recoverable amount.

For example, a landscaping invoice may be fully recoverable. A roof repair may be excluded, capitalized, amortized, or partly recoverable depending on the lease. A legal bill may relate to landlord financing, a tenant default, or a general operating issue. The spreadsheet needs to separate those treatments before the annual true-up.

If you wait until year-end to make those calls, you increase the chance of inconsistent treatment across tenants.

Separate Estimates, Actuals, and Recoveries

CAM reconciliation is not just a list of actual expenses. It is a comparison between what tenants paid during the year and what their lease says they should owe based on actual costs.

Your spreadsheet should keep these three items separate:

1. Budgeted or estimated CAM 2. Actual recoverable CAM 3. Amounts already billed or collected

For each tenant, track monthly estimated CAM charges separately from base rent. If your accounting system exports rent and CAM together, split the line items before reconciliation.

Useful columns for the tenant billing tab:

This lets you answer the basic reconciliation question cleanly: did the tenant overpay or underpay compared with actual recoverable costs?

Track Pro-Rata Share Inputs Carefully

A surprising number of CAM disputes start with the denominator.

If one tenant believes the shopping center is 28,000 square feet and your spreadsheet uses 31,500 square feet, the rest of the reconciliation becomes suspect. If a vacancy, expansion, contraction, or pad tenant is handled incorrectly, the pro-rata share can drift without anyone noticing.

Your spreadsheet should include a dedicated area for:

Do not overwrite old square footage values when something changes mid-year. Add effective dates. The audit trail matters because CAM is often calculated over a defined period, not just the current rent roll.

For multi-tenant retail, also confirm whether every expense uses the same allocation base. Taxes, insurance, common area maintenance, utilities, trash, and shared services may not all follow the same formula.

Flag Caps, Exclusions, and One-Off Lease Rules

CAM caps are where simple spreadsheets break down.

A cap may apply only to controllable expenses. It may exclude snow removal, utilities, insurance, taxes, or government charges. It may compound annually, reset after an option period, or apply only after the first full calendar year.

Create a cap-control section with columns for:

The same approach applies to exclusions. A lease may exclude capital repairs, reserves, landlord overhead, leasing costs, penalties, environmental costs, or costs caused by another tenant. Those exclusions should be tagged before you allocate expenses.

The goal is not to make the spreadsheet complicated. The goal is to stop hidden lease terms from becoming surprise credits after a tenant objects.

Keep Supporting Evidence Close to the Numbers

A CAM reconciliation spreadsheet is not complete if the backup lives in five different places.

For each material expense, your spreadsheet should point to the support: invoice, contract, tax bill, insurance premium notice, vendor statement, or ledger detail. A simple file-name column is better than nothing. A document link is better.

Evidence matters most for:

When tenants object, they often do not object to every dollar. They object to categories that look unusual, unsupported, or outside the lease. If the spreadsheet already ties the number to the document, the response is faster and more credible.

Reconcile by Tenant, Not Just by Property

A property-level reconciliation can tell you whether the center was over or under budget. It cannot tell you what each tenant owes.

Your spreadsheet should generate a tenant-level true-up showing pro-rata share, recoverable expense pool, tenant share of actual recoverable expenses, amounts already billed, cap or exclusion adjustments, prior-year carryforward if allowed, and the final credit or balance due.

This is also where you should note any tenant-specific issues before statements go out. For example, one tenant may have an unresolved maintenance dispute. Another may require notice by certified mail. Another may have a short objection window that starts when the statement is delivered.

The spreadsheet should make those differences visible before the reconciliation package is sent.

Check Timing Before You Send Statements

Many leases include deadlines for annual reconciliations, tenant objections, audits, or supporting documentation. Missing a timing requirement can create unnecessary leverage for the tenant, even when the math is right.

Track these fields before year-end:

This is an operational checklist, not legal advice. If a deadline is ambiguous or financially meaningful, review the lease with counsel or your property manager before relying on a spreadsheet interpretation.

Run a Pre-Close Review in Q4

Do not wait until the CPA closes the books to inspect CAM.

In Q4, run a pre-close review: confirm recurring vendor invoices are posted, major costs are not miscoded, tax and insurance changes are understood, reimbursements are in the right accounts, landlord-only costs are separated, capital items are flagged, and next year's estimates are not obviously stale.

This review gives you time to clean up the ledger before the reconciliation becomes tenant-facing. It also helps with the next year budget, because large variances should be explained before you roll forward monthly estimates.

Spreadsheet Red Flags Before Year-End

If any of these are true, your reconciliation spreadsheet needs attention before statements go out:

None of these means the reconciliation is doomed. They just mean the spreadsheet is carrying too much risk.

Where PigJet Fits

We built PigJet because this is exactly where landlord spreadsheets start to strain: CAM logic, lease-specific rules, tenant-level true-ups, and the backup needed when someone challenges a charge.

If you are still using a spreadsheet, use the checklist above to make it tighter. If you are managing multiple NNN tenants and the workbook is becoming the bottleneck, PigJet is built for landlords who need CAM reconciliation tied back to leases, expenses, tenant communication, and QuickBooks activity.

Final Checklist Before Sending a CAM Reconciliation

Before you send annual CAM statements, confirm:

The best reconciliation is not just mathematically correct. It is organized enough that a tenant, property manager, bookkeeper, or advisor can follow the trail from lease language to invoice to final true-up.

That is what your spreadsheet needs to prove before year-end.

FAQ

What should a CAM reconciliation spreadsheet include?

A useful CAM reconciliation spreadsheet should include lease terms, recoverable expense detail, pro-rata share inputs, estimated billings, actual expenses, cap and exclusion calculations, tenant true-ups, and links to supporting invoices or statements.

Can landlords use QuickBooks for CAM reconciliation?

QuickBooks can help track income and expenses, but most landlords still need a lease-aware reconciliation process outside the general ledger. CAM calculations usually depend on tenant-specific lease language, caps, exclusions, and pro-rata share rules that accounting categories alone do not capture.

When should landlords prepare for CAM reconciliation?

Start before year-end. A Q4 pre-close review gives landlords time to clean up expense coding, confirm recoverable categories, review tax and insurance changes, and identify missing support before tenant-facing statements are prepared.

What causes tenant disputes over CAM charges?

Common dispute triggers include unsupported expenses, unclear pro-rata share calculations, excluded or capped costs, large year-over-year increases, late statements, and charges that do not appear to match the lease language.