The Dynamic Duo
Management Reporter and Excel
By: Michael Scalamogna
If you are a Dynamics GP user or consultant I’d be willing to bet the house you’ve crossed paths with both Management Reporter and Microsoft Excel. Did you know that the two can be used together? There’s a feature in Management Reporter called “Link to Excel” that’s rarely used and has the potential to add a lot of value to your financial reporting process.
One of our customers recently acquired another company and folded three entities into one Dynamics GP company database. They engaged Opal to help them produce financial statements with prior year comparisons.
To start, I needed a clean consolidated monthly trial balance. I was able to create this using trial balances, a cross reference provided by the client and some good ol’ fashioned Excel-ing. When this file was complete, each row corresponded to one GL account (summarized at the natural account level), and each column corresponded to a period (with the date in a MM/DD/YYYY format). Now that I had the data in digestible format, I needed to find the right tool to crunch the numbers and spit out the report.
Initially, I thought the best path forward was to essentially recreate their financials in Excel. This approach would have worked, but it would have added unnecessary complexity and risk to the process.
Enter the “link to Excel” feature. This feature allows you to store information offline in Excel and map to Management Reporter row and column definitions. In addition to producing consolidated financials after an acquisition, this feature can be useful in the following situations:
- Producing consolidated financials for companies with one or more entities that do not use GP
- Comparing budget to actual when budgets are maintained outside of GP
- Performing management accounting allocations on financials that are more complex than a simple fixed or variable allocation (or not recorded on ledger)
Follow these steps to link an Excel worksheet to an existing Management Reporter report:
- Update or create a new row definition
- Create a tree
- Update or create a column definition
First, create a link on the row definition.
- Open the row definition, click Edit >> Row Links.
- Click “new” to assign a link name. In this example I used the External Worksheet link type. This option allows one row to contain information from the both the spreadsheet and GP.
- In the “Link to External Worksheet” column, map to the applicable row on your Excel file. Your mapping should reference the cell in your worksheet where the account number is stored. Management reporter will key off of this cell to lookup the account and period. I used the /CPO (Column period offset) option here to let Management Reporter know that my Excel file has multiple columns that represent different periods.
Next, set up a tree.
To be honest, when I got to this step I was a little confused. When I started to think about it, I realized that a tree is just a hierarchy used by Management Reporter to aggregate/disaggregate information. Instead of defining relationships between segments in the chart of accounts, in this scenario the tree defines relationships between data sources.
Your tree might look a little different. Here’s how I set up mine.
I only created one level on my tree because I was working with information summarized at the natural account level. It points Management Reporter to the location on the network where the file is stored, as well as the worksheet where my prior year data is stored.
The tree I created looked like this:
- Company: XXX (company database mnemonic)
- Unit Name: All Activity
- Unit Description: All Activity
- Dimensions: (blank)
- Row Definitions: pick the row definition you created in the first step
- Financial Dimension link: GL1 (default name)
- Worksheet Link: dropdown menu, corresponds to the “Name” I assigned in the “Edit Row Links” window
- Workbook or Report Path: UNC Path to the Excel file
- Worksheet Name: Name of the worksheet where your data is (i.e., sheet1)
To me, the tree was the most complex part of this process but once I wrapped my mind around how it was being used it was easy.
Once you finish setting up the tree, the final step is to set up the column.
The column definition functions largely the same as it would without a link to Excel. There are only two differences between a normal row definition and one with a worksheet link:
- In the “Column Type” field, select “WKS” instead of “FD”
- Leave the “Book Code/Attribute Category” row blank.
That’s it! This was really simple to set up. Once I had the prior year trial balance completed, the row, column and tree took me less than an hour to create. I thought I’d run into issues with date formatting on the source file that I had created, but it worked the first time around.
Stay tuned for the next challenge: expanding the report to include a prior year comparison by division (another GL segment).
In addition to the above steps, you should work with your accountant to implement the appropriate controls around any Excel spreadsheets used in your financial close reporting that could result in a material misstatement.
