Bill Prescott, CFO of the Jacksonville Jaguars , called me in to see if I could complete some FRx reports that his Accounting Manager was working on. She was trying to create NFL-conforming schedule templates in FRx. I started by spending a few hours reviewing what she had already done, and then I compared her templates to the schedules and a working trial balance they had pulled into Excel and coded with the NFL-schedule and -line code.
As I looked at FRx, I was seeing hours of having to manually reconcile the trial balance to the coding. This would require exporting the details and then mapping them to a coded spreadsheet. This didn’t seem like a productive use of anyone’s time. Not to mention, every year they added around a hundred accounts and poor Brian, their staff accountant, would have to repeat the whole process for every one of them. Plus, in some reports I could see the Accounting Manager was struggling with trees and the way segments were pulled together, which equaled even more hours of trying to jam a square peg into a round hole.
Eager to simplify the process (and end the drudgery), I thought, There has to be an easier way—90% of the coding has already been done in the spreadsheet!
It appeared that each year the Accounting Manager would take the working trial balance and export it out to Excel, then probably do a VLOOKUP to map the codes from the previous year’s spreadsheet and manually add the codes for the new accounts. The spreadsheet would be subtotaled by schedule and code, and then manually inserted into the NFL template and tweeked and tuned until all the numbers accurately reflected the audited P&L and Balance Sheet.
So I thought if I could get the codes from the General Ledger (GL), I could recreate the reports altogether. Each GL account has four user-defined fields. I figured I could use these fields to enter the NFL template codes, populating “User-Defined 1” with the NFL schedule and “User-Defined 2” with the line code. Using integration manager, I ran a GL update integration which would, at the very least, allow me to generate the system’s trial balance and pull the codes.
But I wanted to use those fields to create a report. FRx, unfortunately, does not allow you to create reports based on the user-defined fields, but Management Reporter does: It lets you take the user-defined fields and create them as another dimension of the overall account structure, kind of like a new segment to the account.
Perfect! Now I could create the reports in Management Reporter. The Jaguars were in the process of upgrading from Version 8 to GP2010. I know big jump, but what better way to test a new functionality and the potential value of the new Management Reporter than to run a test upgrade and create the reports.
I started with the detailed schedules. First, I spent some time developing a shell format, and since we were under a time crunch and the process to create the simple schedules was fairly easy, I trained two of their staff members to finish building those reports. Then I focused my attention on the rolled-up schedules. Yes, these schedules were a little more difficult because I could not simply pull a schedule as a line in the rolled-up schedule. Well, at least not yet. Maybe that functionality is there in some form, but new product, new learning curve.
The rolled-up schedules required some pieces of certain schedules, primarily ticketing and sponsorship revenues, to be rolled up in different ways. Since I could use any (or all) of the user-defined fields to create additional dimensions for each account, I was able to concentrate on the various line items. Yes, I did have to do some difficult work reconciling of the accounts to the trial balance, but now it is a reusable product. As new accounts are added, they can be coded and reports can be run during the year to balance the P&L and Balance Sheet.
They also do a working trial balance by tax code to use for the tax return. So why not use the third user-defined field for that, too. I ran a GL update integration on this new dimension and now they can run a Working Trial Balance in Management Reporter for tax codes, as well.
It was a down-to-the-wire day, but we made the NFL’s deadline (which made everyone happy), and along the way, discovered a much more user-friendly and time-saving way of building reports. If you haven’t worked with the new Management Reporter, I strongly recommend you give it a shot. It has some great new features that might help you find long-term, simple solutions to some of those frustrating reports. It worked for the Jaguars!
Pingback: NFL team winning with Management Reporter - Dynamics Corporate Performance Management - Community Home - Microsoft Dynamics Community
Pingback: How Management Reporter Steps in for FRx to Create NFL Team’s Schedule Templates - MSDynamicsWorld.com GP News - GP Non-Technical Blogs - Microsoft Dynamics Community
Pingback: Everything Dynamics GP #36 - About Dynamics, Development and Life - GP Technical Blogs - Microsoft Dynamics Community