Balance Sheet Reporting Without Closing YTD in Dynamics GP

It’s almost that time again. Soon it will be time to complete another year-end close in GP. Since GP will only allow adjusting entries to the SINGLE LAST closed year in GP, it can sometimes be a scary process for users. Due to ongoing audits or other various factors, it may be inevitable that adjustments will be needed for more than just one historical year. Without closing a year, the amounts for balance sheet accounts will not roll over as beginning balances for the next year. For those instances, here is a little trick that will allow for accurate balance sheet reporting, without having to complete the official year-end process in GP.

Below is an example of a Management Reporter column definition. In this example, column B is mapped to pull YTD data from the last month of the last fiscal year. Column C is mapped to pull YTD information for the current fiscal year. Both columns B and C are configured as “non-printing” columns. Finally, column D is a calculation that adds columns B and C. Voila! Accurate balances without closing the year in GP.


balance sheet reporting

Multicurrency Reporting in Microsoft Dynamics AX 2012

Reporting in multicurrency in Microsoft Dynamics AX 2012 is achieved through the use of currency translation within the consolidations function.  For example, a subsidiary operating in functional and reporting currency of EUR for local statutory requirements, but required to report in USD for its parent company in the US.

First a consolidation legal entity must be created with functional and reporting currency of USD:

a)      Navigate to Organization administration>Setup>Legal Entity: check “Use for financial consolidation process”

b)      Navigate to General Ledger>Setup>Ledger to configure the Ledger for the consolidation company;

  •  Assign the same COA and calendar as that of the subsidiary, assign USD for functional and reporting currencies.

c)      Navigate to General Ledger>Setup>Posting>Accounts for automatic transactions

  •  Define the main account for the transaction types “Profit and loss account for consolidations differences and “Balance account for consolidation differences.” These are the accounts where the exchange rate adjustment will be posted.

Exchange rate types for average, period end, and historical rates must be defined between USD and EUR:

d)      Navigate to General Ledger >Setup>Currency>Exchange rate types

  • Create rate types of average, period end and historical. Enter the related exchange rates for each type by selecting the “Exchange rate” button. These exchange rate types are assigned to a range of main accounts in consolidation for balance sheet, income statement and equity in order to achieve currency conversion results in accordance with GAAP.

With the consolidation company created, the consolidation and currency translation process must be configured.

e)      Navigate to General Ledger>Periodic>Consolidate>Consolidate [Online]

    1. Criteria Tab> select  the account range for all main accounts to be translated and consolidated, the period , check      include actuals and include budgets if budgets exist.Multicurrency Reporting
    2. Financials Dimension Tab> select the financial dimensions to be consolidated
    3. Legal Entities Tab> Select the subsidiary EUR and which account, Balance or P&L, for the exchange rate adjustment. This correlates to the setup from step c) aboveconsolidate-balance
    4. Description tab> this description will be the default text  for the voucher created in the consolidation
    5. Elimination tab is utilized for the processing of eliminating transactions which is not a requirement for currency translation in consolidation and can be left blank
    6. Currency translation tab> define which currency exchange rates will be utilizes with various main accounts.
          1. Enter the subsidiary name (source legal entity name), the functional currency (source accounting currency) the main account range (from account /to account) and the associated exchange rate type to be applied to the local currency during the consolidation process (Exchange rate type).
          2. For each account range in 1 above, select the Exchange rate date which indicates if the exchange rate should be based on the transaction date or the date of the consolidation.
          3. A specific rate can also be used by entering the specific exchange rate in the Exchange rate field. This will override all other rate selections.

Consolidate EUR

Run consolidation by selecting “Ok”.

To view the consolidation results navigate to General Ledger>Inquiries>Consolidations. Select “Transactions” to view the voucher entries created by the consolidation. Results can also be reviewed through the numerous general ledger standard reports such as the trial balance and ledger transaction list.

How to Copy and Paste into Microsoft Dynamics GP

Advtrex’s Titanium GP brings the ease of copying and pasting into Microsoft Dynamics GP.

Imagine a user wanted to copy and paste 50 lines of data from an Excel worksheet into a General Journal Entry in Dynamics GP. Normally, this would require using Microsoft importing tools that take a heavy amount of time depending on the size of the data files. With Titanium GP, a user can easily and immediately copy and paste import detail lines.

1. Highlight data in your computer file
2. Right click to copy or hold down “CTRL+C”
3. In GP, hit “Paste”.

Voila! The data is immediately pasted from Excel or Word into GP.
To make the process even faster, use keyboard shortcuts to copy/paste:

• To paste in Dynamics GP, hit “CTRL+T”
• To view in Grid form that allows the user to copy lines, hit “CTRL+G”

Pictured below is the grid view (CTRL+G).

How to Copy and Paste into Microsoft Dynamics GP 2013

When you copy/paste from Excel or Word to GP, the order of the columns is flexible.  As long as the columns titles match the respective fields in the GP window, the function will recognize the proper fields to copy/paste data.

Notice below that the Debit, Credit and Reference columns have been switched.

Titanium GP

Nonetheless, we are still able to successfully paste the data into the GP window.

Additionally, this tool allows copying and pasting from GP into an Excel or Word document. To save a GP document (or export it) to Excel, open a document in GP and use the menu command to “Save to Excel.” You will then be prompted to indicate the path and name to save the file.

The COPY/PASTE functionality can be used with the following windows:

• Journal Entries

• SOP Entry

• Purchase Orders

• Receivables Transaction

• Inventory Transaction

• Inventory Transfer

• Inventory Kits

Contact us at Tridea Partners to learn more about this tool.

Consistency Check Tool in Dynamics AX 2012

Tables in Dynamics AX 2012 are divided into different categories: Main tables such as Customers, Ledger Accounts, Vendors, Items, etc, Transaction headers such as Sales headers, Purchase order headers, etc, and transaction details such as Sales lines, PO lines, etc. These tables are all related to each other through keys. For example when a sales order is created for customer A123, the customer, sales header and sales line are all linked to each other.

Occasionally one or more of these transaction records can be abandoned, meaning the parent data was deleted but the transaction still exists. We call these orphans because they exist in the database but are not connected to any other record. While these orphans by themselves cause no problem, they take up space in the database and given enough time, a lot of these orphaned records can cause performance issues.

Dynamics AX 2012 has a tool to spot and remove these orphans. It’s called the Consistency Check tool. It basically runs through the entire database and locates transaction records that do not have any parent record associated with them. The Consistency check tool is located under System Administration > Periodic > Database > Consistency Check

AX Consistency check

When running this tool, you will first be presented with a series of options such as which modules you would like to check for orphans in, what to do with orphans found (check or fix) and a starting date to perform the search on (if you run this tool every 90 days you can select a day just 90 days back in order to speed up the search process).

Dynamics AX Consistency check

The tool will take an hour or more to run, be sure to run this after hours.

Dynamics AX

AX does a great job of keeping track of transaction data and removing automatically when any parent data is removed, but, as with any ERP, different scenarios may come up in which records are left orphaned. Using the Consistency Check tool on a regular basis is a great way to clear these records and keep your system running smoothly.