The Dynamic Duo: Part Two
Management Reporter and Excel
By: Michael Scalamogna
In my last blog post, I shared some tips on how to link offline Excel files to Management Reporter report definitions. The report that I created was a consolidated P&L, and it was summarized at the natural account level.
In addition to a consolidated P&L, the client was also using a tree to produce financials at the division (another segment in their chart of accounts) level. Linking the Excel file to the tree was a piece of cake. Here’s what I did:
- Updated source file (Excel trial balance)
- Updated row definition
- Updated tree
Updating the source file
Previously, my source file was summarized at the natural account level on one tab. I am sure there are other ways to do this, but I decided the most straightforward and organized way was to create a trial balance in Excel with one tab per division. Each row contained the GL account string and each column header contained a period in the “MM/DD/YYYY” format.
Updating the row definition
The row definition was very simple to update. Instead of one consolidated link to Excel, I created one link per division. The end result was a row definition with one column for each division that had activity in 2017.
Once I had the row links set up, I mapped each column to the applicable Excel worksheet/cell and hit the “save” button.
One quick note on the /CPO switch on the row definition. When you mark a line in your definition with /CPO Management Reporter looks at the base period and then finds the corresponding column number. In other words, if your base period is two and one line in your row definition is mapped to cell A4 /CPO, the results will contain whatever is in cell C4 (two columns to the right of cell A4). That said, it is important that your financial data is immediately to the right of whatever cell you reference in the row definition. In the above screenshot you can see that my reference cell was in column A. In the Excel sheet I was linking to, Column A had a GL account string, Column B had the account description and the financial results began in Column C. This caused the January column to show as blank and the February column to show all zeros.
Updating the tree
Next, I updated their divisional tree. There was already a division tree set up, so I already had a starting point.
The column definition had already been updated, so all that was left to do was cross my fingers and click the “generate” button.
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.
