consolidated financial reporting real estate property management

Consolidated Financial Reporting With Excel

You plan to acquire another property management company. How will you do consolidated financial reporting for the combined companies?

Here I describe the process being used at many parent property investment companies for creating consolidated financial statements. I call this the “manual”, Excel-based method because it does not use any of the purpose-built consolidated financial reporting tools such as Oracle’s Hyperion or SAP BPC with an underlying data architecture.

The Excel Based Process for Consolidated Financial Reporting

Most parent companies today generate individual reports for each company, then export the data into Excel files. These Excel files are emailed (or uploaded to a shared drive) to the Finance department whose job it is copy / paste or link the numbers from the separate files and into consolidate reports.
This is not a big deal when a few reports are involved, and when there are no last-minute revisions and back-and-forth communication.
The general flow is shown in the diagram below:

consolidated financial reporting with excel property management

This phenomenon of emailing Excel files is also described in this XLhub video that I published several years ago http://www.xlhub.com/learn/simplify-business-processes.

Excel provides very powerful features removing duplicates and shaping the data. Some of these are shown in the Excel tool bar below:

microsoft-excel-toolbar-data

 

Challenges in Consolidated Financial Reporting

There are a few challenges with this process …

Challenge #1: Chart of Accounts

It is unlikely that the acquired companies have the same chart of accounts as the parent. So there’s a need to map one chart of accounts to another. If Excel is being used, some VLOOKUPs can do the job.
When the exported reports from each property management system contain several levels of hierarchy, there’s a need to strip out the rows that contain subtotals.

Challenge #2: The Fiscal Calendar

If the property management companies have been operating on different fiscal calendars, some steps are required to translate between the fiscal calendars of the acquired company and corporate. This can also be easily done in Excel.

Challenge #3: Speed, Repeatability

The manual, Excel-based process described above is not scalable. After two three acquisitions, many companies cannot close the books or publish timely statements without hiring more people. In the few days before and after month-end close, some individuals in these companies are in “crisis mode”, with no capacity to attend to other priorities. Weak version control of Excel files sometimes leads to chaos. In many cases, after consolidated statements have been published, it is difficult to recreate them because the data was sourced from many different files, ranges and cells, and retracing the lineage of the data is too difficult.

Challenge #4: Last Minute Journal Entries

Now think of the situation where the consolidation is almost complete, and one of the companies decided to make a journal entry for the month being closed. This can also delay the monthly financial close process.

 

The Hectic Monthly Financial Close

The monthly financial close process in most companies I have worked with is usually hectic. Even companies that are using advanced tools like SAP BPC or Hyperion to consolidate their data have challenges. Those who don’t have the data pipes and consolidated general ledgers are resigned to the reality of emailing Excel files to each other, and copying/pasting information to create statements. Many people in finance and accounting have been operating in this mode throughout their careers. The pressure during the monthly financial close process is a given.

 

I call this the “manual” method for consolidated financial statements because many companies have migrated an “automated” process using data pipes instead of emailing Excel files back-and-forth. Utilizing data pipes, data warehouses and visual analytics tools opens the door to simpler, more consistent consolidated financial reporting.