Dynamics GP Consolidation in Financial Reporting

There is special tool: Enterprise Reporting (often referred as Solver) – this tool is outside of the scope of this publication.  We will concentrate on more traditional and well known Financial Reporting Tool: FRx.  Theoretically you can use other popular tools, such as Crystal Reports, Excel Reporting, SSRS (Microsoft SQL Server Reporting Services), but the amount of work would be substantially higher, comparing to FRx, where you already have connector to Great Plains General Ledger Chart of Accounts and Transaction tables, plus you have all the necessary tools to handle multicurrency and consolidated reporting:
1. FRx Building Blocks.  You begin your FRx introduction with Row Format (where you specify one account per row, or summary of the account group, plus roll-up summary lines for your Financial Statement section, such as Assets, Liabilities, Equity in Balance Sheet).  When Row Format is ready, you deal with Column Layout (for example, if you like your Profit & Loss Statement to have 12 months spreadsheet form – these twelve columns and running summary column you define in Column Layout).  Next element is Report Catalog (where you combine Row Format and Column Layout to form end report, such as Balance Sheet, P&L or Statement of Cash Flow).  You can read more in FRx documentation or other articles about FRx, let’s now move to Consolidated Report design
2. Reporting Tree.  This construction allows you to specify company (Dynamics GP database) for Tree Unit, for example Headquarters, Brazilian or Mexican subsidiary.  Reporting Tree could be optionally specified in the Catalog, see description above.  If Reporting Tree is specified, when you print your report, you will get dialogue to include the whole tree or pick subset of tree units.  From Consolidated Financial Reporting theory you probably remember to exclude intercompany sales and purchasing (Due to and Due From GL Accounts)
3. Multicurrency.  We recommend you to accept defaults for FRx for Microsoft Dynamics GP, where multicurrency is taken from GP multicurrency setup.  If you like more flexibility (and related additional data entry work directly in FRx on exchange rates – this is also an option)
4. International Aspects of Financial Consolidation in FRx.  If all your international subsidiaries run Microsoft Dynamics GP (and you are very organized IT manager and all these GP companies are hosted on the same Windows Server in your Headquarters and open to international users via Citrix) – just follow the guidelines above.  But, it might be the case, that your international subsidiaries are in the countries, where Dynamics GP is not localized: Brazil, China, Russia (you can continue the roll).  If that’s what you’ve got, you may consider two strategies.  First one is to create separate company with the overseas subsidiary currency and only enter (or import via Integration Manager) posted GL transactions (this would be GL level shadow company strategy) – it is usually relatively simple to export General Ledger entries from any Accounting application and tune GL integration via Dynamics GP Integration Manager.  Second approach is even more simple – in FRx Row Format you can specify Excel Worksheet as data source for the cell in a Row.  And then you export from your overseas accounting Excel Worksheet (typically in the form of Balance Sheet or Income Statement or Trial Balance) – be sure the format is always the same in Excel (the same number of Columns, Rows and positioning of the cells) – and this is who you consolidate your foreign branch.  Second approach is often preferred, when you have very small overseas branch and do not even have Accounting system there (all is done in Excel).  Paragraphs below are more technical and you may review them with your IT consultant or computer support personnel
5. FRx Implementation and Maintenance.  Comparing to general Great Plains Dynamics implementation in large corporation, FRx part is several percent of the consulting budget to expect.  Controllers or Financial Analysts should pick up FRx training very smooth and you may expect reports design to be done internally.  In some situations you outsource Consolidated Financial Statements design to FRx Consultant, especially where you have numerous currencies, more than ten companies to consolidate.  FRx usually works without major maintenance procedures required.  If you have very large number of FRx reports, you may need case-to-case FRx re-haul service (rebuilding specification sets, printing report exception sheets – usually to identify duplicate accounts in the report and other FRx data repair services)
6. Upgrading older FRx and Dynamics GP/Great Plains Dynamics versions.  For older Great Plains Dynamics update to current version (September 2010) 2010/11.0 or 10.0, please see our dedicated publications.  Current version of FRx is 6.7 and upgrade from earlier versions is fairly straightforward, except when you migrate from older no-longer supported DB platform, such as Pervasive SQL 2000/Btrieve or Ctree/Faircom (in that case you will need FRx consultant help to specify new DB type and ODBC connection to migrated Great Plains Dynamics GP to the Microsoft SQL Server)
7. Moving FRx to the new Windows Server.  FRx has its metabase, which is implemented on the Microsoft Access DB platform (the file with .frl extension actually is MS Access DB file).  You will need to identify your FRx Sysdata folder location on the old Server and move it to the new hardware (Windows 2008, 2003 Servers are perfect, including 64 bit versions).  FRx consultant help is recommended, especially when you do not want to take the responsibility for something you are not really familiar or at least when you do not feel, that this is something you will do in the future couple of more times
8. FRx Registration Key.  If you are planning to upgrade FRx, you will need new version reg key.  If you are redeploying the same version, regkey is in the Sysdata folder and you should be OK (without bothering).  New version key – please call your Microsoft Dynamics GP Partner or Consultant.  If you are frequent visitor of Microsoft Business Solutions CustomerSource, you can find your reg key right there (assuming that you have current annual enhancement/service plan with MBS).  But even is by now your Service contact is expired, you can still call your Dynamics GP Reseller and have them look it up for you on the MBS PartnerSource (if you are not sure who these people are, feel free to call us directly)
9. FRx custom programming.  From time to time we see Dynamics GP customers request to read FRx MS Access database tables in order to migrate FRx catalogs to something like SSRS.  This is a bit luxury approach, and probably coming from the company, where you may carry several SQL Server DBA certification level employees.  FRx MS Access DB is password protected, however there are tools, including shareware and freeware to crack the password, where we are not really the authority to recommend this approach.  However we are comfortable to work with you on the subject of FRx catalog switching from one account wildcard to another in the VBA modal dialogue (behind the scenes we are making Account Segment update in FRL database file to remap your report to the new GL account segment).  We would say, that FRx DB table structure is very simple and open for all the kinds of modifications, just let us know what you need.  Usually FRx programmer deploys MS Visual Studio DOS or Web project to read FRx database and update it via ADO or ADO.Net
10. For further help or information, please call us 1-866-528-0577, email us help@albaspectrum.com  We serve you USA, Canada nationwide plus internationally via remote support and web sessions.  Local service is available in Chicago, San Diego, Los Angeles, Houston, Orange County, CA, Western Michigan