Integrating Pro Forma Data
For organizations anticipating the purchase of a new company, being able to see the new company’s effects on the organization’s books is important. Often, it involves loading data into an Excel workbook and then adjusting formulas on multiple tabs to include the numbers. This process can have some or more of these issues:
- The changing of formulas can result in broken links.
- The process can be slow.
- Multiple new companies can exacerbate these issues.
- Financial reports must be updated to reflect the new information.
There are applications to help organizations handle this situation, such as the cloud-based Solver Corporate Performance Management (CPM) solution. BKD clients have been using this solution for many years for reporting and budgeting. Using its data warehouse to store data along with reporting and planning functionality, it enables data from multiple sources to be integrated and then reported together.
Integrating Pro Forma Data
If your organization plans to continually acquire new companies, establishing a set of templates for your staff to complete is the most efficient method. Combining the templates with repeatable integration mappings enables your staff to more quickly integrate the data. Using templates establishes a uniformity to the data.
Typically, pro forma data is created at a summary level per period. It may encompass several periods and years. It also involves data for which you may not have a one-to-one match to your existing accounts. However, you may be able to fit the data into categories of accounts or new categories if the type of business is new.
In this example, there are three tabs in an Excel file for gathering information. The first tab is the list of new entities. The second tab is the list of accounts. The third tab is the periodic data for each company. All the data can be combined into one sheet if you desire.
As for the accounts, you most likely do not have or do not care about the specific accounts in the acquired company. In addition, you want to make it easy for the person importing the data. Thus, instead of trying to map data to your existing chart of accounts, you can use accounts specifically created for handling pro forma data. If the accounts are categorized correctly, they will be included in the right locations in your financial reports.
Here is a simple example of a New Entities tab. There may be other data that is important, but for this example, we are keeping it simple.
The next table shows an example of accounts that can be used.
The next tab will contain the period amounts.
Note that additional data may be needed. For example, you would most likely identify the data as pro forma data. That information can be hard-coded in the integration.
Once the accounts are established, they do not need to be imported on successive runs. Similarly, the entities can be entered manually. However, if you have a well-defined template and integrations that use it, users only need to select the file and import the data.
Once the template is created, the integrations for the three tabs can be created. The next image shows three integrations based on the three tabs.
Going forward, all the user must do is save the template, select it, and run the mapping. The highlighted item is the integration created in the previous image.
It also may be necessary to incorporate the new entities into one or more reporting trees. For example, you may have a tree that groups the entities by the type of products they sell. Another example would be a tree based on geographic location. Trees can be manually edited and imported.
Once the data is imported and the tree is updated, the user runs the reports, publishes them for interested parties, and—since everything above was so easy—moves on to the next task. No break needed!
This article walked through a process using an Excel template to integrate pro forma data into the Solver CPM solution. It showed the Excel template as well as the integration in the Solver solution. It showed that integrating pro forma data into reports can be as simple as completing the template, running the integration, and publishing the reports.
If you would like more information about this solution, please contact your BKD Trusted Advisor™ or use the Contact Us form below.