trainee workbook62f10e0162f5f0afe756-b636a0949b3e058367a6f2ae35c88177.r63... · web viewuse the...

USER GUIDE

Data Migration using Atlas for AX 2012 R2.

COPYRIGHT NOTICECopyright © 2013, Globe Software Pty Ltd, All rights reserved.

TRADEMARKSDynamics AX, IntelliMorph, and X++ have been registered as or are under registration as trademarks of Microsoft Corporation. Microsoft Office System 2010, Microsoft Office System 2007, Windows 2003 and Windows 2008 are registered trademarks of Microsoft Corporation.

SOFTWARE RELEASEThis documentation accompanies Atlas version 5.0.3000 or higher and which is suitable for Microsoft Dynamics AX V4.0 SP2, Dynamics AX 2009 SP1 and Dynamics AX 2012 or higher and Microsoft Office 2007 or higher.

PUBLICATION DATE10 June 2013

READER COMMENTSAny comments or suggestions regarding this publication are welcomed and should be addressed to the attention of: [email protected]

TABLE OF CONTENTS1 INTRODUCTION 2

1.1 WELCOME 2

1.2 ABOUT THIS GUIDE21.2.1 Outline 21.2.2 Audience21.2.3 Objectives2

2 GENERAL LEDGER3

2.1 OUTLINE 3

2.2 SUPPORTING TEMPLATES 4

2.3 PRE-REQUISITES 42.3.1 Outline 42.3.2 Configurable items to be prepared before you start42.3.3 Chart of account cross reference 5

2.4 MAIN TABLES 72.4.1 Outline 72.4.2 Currency exchange rates 72.4.3 Main accounts 92.4.4 Dimensions 12

2.5 TRANSACTIONS 172.5.1 Outline 172.5.2 Year start – Opening balances172.5.3 Period Trial Balance upload using general journal202.5.4 Transaction postings202.5.5 Adjustments to balances and movements212.5.6 Accruals 212.5.7 Budgets 23

2.6 VERIFICATION REPORTS 272.6.1 Outline 272.6.2 Trial balance 272.6.3 Budget spread 282.6.4 Balance sheet 292.6.5 Income Statement29

2.7 AFTER POSTING ACTIVITIES312.7.1 Outline 312.7.2 Blocking accounts from use in the general journal312.7.3 Dimension sets 31

1 INTRODUCTION

1.1 WELCOMEUse this document and associated templates to assist with the migration of master and transactional data from your legacy system to Microsoft Dynamics AX. The document includes a section for General Ledger only.

1.2 ABOUT THIS GUIDE1.2.1 OUTLINE

This document provides you with specific templates that will assist you with the migration of data from a legacy system to Microsoft Dynamics AX. These templates support the uploading of master and transactional data as well as reporting templates that can be used to verify the data loaded. A list of templates is provided for each functional area.

1.2.2 AUDIENCE

This guide is intended for functional specialists who are tasked with building and implementing Microsoft Dynamics AX where data from an existing system needs to be migrated. It is expected that students have a good understanding of Microsoft Excel and have excellent knowledge in relation to the Microsoft Dynamics AX domain of this document. 1.2.3 OBJECTIVES

The objective of this course is to provide you with the ability to: Prepare Microsoft Dynamics AX modules for the data migration Understand the main master data elements to be loaded Understand the transactional elements to be loaded Understand cross-system reconciliation Use the templates to upload master data Use the templates to load transactional data Use the templates to verify the loaded data Undertake basic system reconciliation

2 GENERAL LEDGER

2.1 OUTLINEThis document deals with the loading of reference (Main table) and transactional data for the General ledger module. Uploads include: Chart of accounts Main accounts Dimensions Exchange rates Trial balance Period movements

Once the upload is complete, verification is needed to ensure that the data loaded into Microsoft Dynamics AX reconciles to that provided by the legacy system. To this end, you can use Atlas to verify balances on an account, period and dimension basis. In this guide, the reconciliation reports to assist with this include: Main Account Trial Balance Dimension Trial Balance

The following demonstrates the flow of the data migration process as outlined in this document. At various stages in this flow, you will be required to build or configure elements inside Microsoft Dynamics AX. These are also included for your reference.

Finally, considerations around access to legacy system transaction histories are discussed.

GENERAL LEDGER

2.2 SUPPORTING TEMPLATESIn addition to the step-by-step guide provided by this document, a set of supporting templates is available. Before you begin please check that you have the following:

AX2012_GL_Chart_of_Accounts_cross_reference AX2012_GL_Upload_Append_MainAccount_with_CategoryRef AX2012_GL_Upload_Append_Exchange_Rates AX2012_GL_Upload_Append_CustomDimensions AX2012_GL_General_Journal_no_offset_with_debit_credit_amount_column_OB AX2012_GL_General_Journal_no_offset_with_single_amount_column_accrual AX2012_GL_Budget_12_Period_Spread AX2012_GL_Trail_Balance AX2012_GL_Budget_12_Period_Spread_Reconciliation AX2012_GL_Summary_Balance_Sheet_with_prior_year_and_ratios_cache AX2012_GL_Income_statement_with_Current_vs_Prior_Periods_with_cache

2.3 PRE-REQUISITES2.3.1 OUTLINE

This document does not describe how to load each and every configurable item in Dynamics AX. It is assumed that a number of settings have been set up or are to be set up in due course. In addition, set up a cross reference between your legacy system general ledger and your proposed chart of accounts in Microsoft Dynamics AX. This allows you to easily convert opening balances and period movements to a form that is readily uploaded into the general journal.

2.3.2 CONFIGURABLE ITEMS TO BE PREPARED BEFORE YOU START

The following is assumed to have been completed:

Configurable item Action

Legal entity Create the company or entity in the Legal entities form. Set up any system number sequences as necessary. Change your default company accounts to reflect the legal entity you are loading into.

Currencies Define Exchange rate type for this legal entity or use a pre-existing shared type. Use the currency combination and exchange rate workbook as required. See section x.x.x

Number sequences Define number sequences for the basic elements of the module. Notably:Journal batch numberJournal numberVoucher1

Transfer General Journal number2

Journal name Define a general journal name for the opening balances and opening transactions only. E.g. OB.General Ledger -> Setup -> Journals -> Journal names

1 You might use a Voucher series specifically for data migration, allowing you to quarantine the transactions for data migration from those used operationally.2 You might use a number sequence specifically for data migration, allowing you to isolate opening transactions from operational transaction posting.4

http://62f10e0162f5f0afe756-b636a0949b3e058367a6f2ae35c88177.r63.cf2.rackcdn.com/AX2012_GL_Income_statement_with_Current_vs_Prior_Periods_with_cache.xlsx

http://62f10e0162f5f0afe756-b636a0949b3e058367a6f2ae35c88177.r63.cf2.rackcdn.com/AX2012_GL_Summary_Balance_Sheet_with_prior_year_and_ratios_cache.xlsx

http://62f10e0162f5f0afe756-b636a0949b3e058367a6f2ae35c88177.r63.cf2.rackcdn.com/AX2012_GL_Budget_12_Period_Spread_Reconciliation.xlsx

http://www.globesoftware.com.au/AtlasDocuments/AX2012_GL_Trail_Balance.xlsx

http://62f10e0162f5f0afe756-b636a0949b3e058367a6f2ae35c88177.r63.cf2.rackcdn.com/AX2012_GL_Budget_12_Period_Spread.xlsx

http://62f10e0162f5f0afe756-b636a0949b3e058367a6f2ae35c88177.r63.cf2.rackcdn.com/AX2012_GL_General_Journal_no_offset_with_single_amount_column_accrual.xlsx

http://62f10e0162f5f0afe756-b636a0949b3e058367a6f2ae35c88177.r63.cf2.rackcdn.com/AX2012_GL_General_Journal_no_offset_with_debit_credit_amount_column_OB.xlsx

http://62f10e0162f5f0afe756-b636a0949b3e058367a6f2ae35c88177.r63.cf2.rackcdn.com/AX2012_GL_Upload_Append_CustomDimensions.xlsx

http://62f10e0162f5f0afe756-b636a0949b3e058367a6f2ae35c88177.r63.cf2.rackcdn.com/AX2012_GL_Upload_Append_Exchange_Rates.xlsx

http://62f10e0162f5f0afe756-b636a0949b3e058367a6f2ae35c88177.r63.cf2.rackcdn.com/AX2012_GL_Upload_Append_MainAccount_with_CategoryRef.xlsx

http://62f10e0162f5f0afe756-b636a0949b3e058367a6f2ae35c88177.r63.cf2.rackcdn.com/AX2012_GL_Chart_of_Accounts_cross_reference.xlsx

GENERAL LEDGER

Configurable item Action

Ledger parameters Set up basic entries including base and reporting currencies, exchange rate type and calendars.General Ledger -> Setup -> Ledger

Main account categories These are used to classify the Main accounts into functional groups. Assume the default set or define your own. You should note that, these categories are used in the Main account upload.General Ledger -> Setup -> Chart of accounts -> Main Account Categories

Calendars Set up a financial calendar with financial years. Link this to the Ledger definition in the Setup menu.General Ledger -> Setup -> Calendars

Account structures Develop account structures to suit the company or companies you wish to load into. General Ledger -> Setup -> Chart of accounts -> Configure account structures

Budget parameters Setup number sequence for register entries and other, basic module settings.Budgeting -> Setup -> Parameters

Budget models Setup models to group or organize transactions. These may be organized by entity, region, type etc. An example might be ORIG for Original BudgetBudgeting -> Setup -> Budget models

Budget codes Use this form to define further classifications of budget entries. Each code must belong to a pre-defined type. E.g. Original Budget.Budgeting -> Setup -> Budget codes

Budget dimensions Use this form to define which financial dimensions can be recorded on budget transactionsBudgeting -> Setup -> Dimensions for budgeting

2.3.3 CHART OF ACCOUNT CROSS REFERENCE

If you are converting between a chart of accounts from a legacy system and a new chart to be used with Microsoft Dynamics AX then it is a good idea to maintain a cross reference between the two sets of codes. If possible, maintain the natural account cross-reference on the Main account table, perhaps as a custom field, otherwise use the template provided herein. Cross-referencing helps with reconciliation. Below is an example of this, showing the old and new codes.

GENERAL LEDGER

Figure 1 In the above, account 10-1005 is mapped to 401100 Sales in WA.

As described in later sections, use the legacy system account code in conjunction with the Main account whenever transactions or balances are being loaded. Complete this worksheet as follows:

Option Here you… Comment

Legacy system account Paste a list of the legacy system’s general ledger account codes

The cross reference table is automatically extended

Name Paste the name of each account into this column

Natural account Using Excel text manipulation formula functions, extract the natural account

The natural account code is the most fundamental element of an account code structure. This element represents the primary purpose of the account (e.g., Sales or Telephone Expense)

Main account Enter a corresponding Main account for each Natural account

This is the primary purpose of the account

Dimensions Enter Financial dimensions to support the description of the Legacy system account

This will include structural elements like, Department, Cost Centre etc. In this example, Business Unit and Vehicle are being used.

Save this workbook for each combination of chart of accounts. In other words, save a copy for each set of legacy system charts and each set of accounts in Microsoft Dynamics AX.

6

2.4 MAIN TABLES2.4.1 OUTLINE

Loading reference or master data is an important first step. Not all configurable items are included in this guide and it is expected that those not included are configured as either pre-requisites or as required later. This document provides details on loading: Exchange rates Chart of accounts Main accounts (Natural accounts) Total account ranges Dimensions (Department, Cost center, Business Unit, Custom list)

2.4.2 CURRENCY EXCHANGE RATES

2.4.2.1 OUTLINE

If you are not using an existing exchange rate type, and plan to create a new type for this and similar legal entities, then you will need to use the following workbook:

Figure 2 Currency pairs and exchange rates for type AUD

2.4.2.2 COMPLETING THE WORKBOOK

It is assumed that you have created, as part of the pre-requisites, the exchange rate type. Complete the table as follows:


Type Select from the list of defined exchange rate types

List is shown in the context task pane

Date Enter the date of the rate

From Enter the currency from which the exchange rate applies

To Enter the base currency of the ledger

Rate Enter an exchange rate With respect to the Factor

Factor Use 100 if you want the rate expressed if you had 100 units of the From currency

USD -> AUD 100USD = 97.2186 AUD

Use the tab key at the bottom right cell of the table to give you another line of the table.

GENERAL LEDGER2.4.2.3 UPLOADING THE EXCHANGE RATES

Upload by using either the Batch task Append exchange rates by currency combination or by individually loading the Table Append templates of Exchange rate currency pair followed by Exchange rate.By batch task:


Atlas ribbon Select Batch tasks Ensure mode is Standard

List of recorded batch tasks

Tick to select Append exchange rates by currency combination

Loads the two Table Append templates in the correct sequence

Start Batch tab

Double-click to open

Start Batch tab

Select Run to start the process

This process loads the combinations and the rates into Microsoft Dynamics AX. A successful load will appear as follows:

Figure 3 Successful exchange rate upload

By individual template:


Atlas ribbon Select Table -> Append Ensure mode is Standard

Document template list Tick to select Exchange rate currency pair

Loads the currency pair into Microsoft Dynamics AX

Confirmation tab Double-click to open

Confirmation tab Click to select Upload

Follow this by running the Exchange rate upload, but you need to refresh the workbook (Alt+Ctrl+F9) first, then:



Document template list Tick to select Exchange rate Loads the exchange rate per currency combination



2.4.3 MAIN ACCOUNTS

2.4.3.1 OUTLINE

Typically, Main Accounts are natural accounts in that they represent the most fundamental element of the account code structure. This element represents the primary purpose of the account (e.g., Sales or Telephone Expense). Main 8

GENERAL LEDGER account data migration involves loading these natural accounts into the general ledger. In doing this, accounts are assigned to a chart of accounts and each is assigned to a type (Profit and loss, Balance sheet, Total etc.) and a category. In this guide, Main accounts are loaded in a three step process: Identify Chart of accounts and Account structures Load Main Accounts Map Total accounts to Main Account ranges

2.4.3.2 IDENTIFICATION OF THE CHART OF ACCOUNTS

The workbook for loading Main Accounts has three tabs, the first of which allows you to identify the chart of accounts and select the Account Structures you will use with these Main Accounts:

Figure 4 Chart of Accounts identification

Complete as follows:


Chart of Accounts

Enter a name for the chart

E.g. Atlas Main Accounts

Description

Enter a full description of the chart

Optional

Main Account Mask

Enter a format or pattern that describes any positional structure in the Main Accounts

Optional. Use the Cell Comment to see formatting rules

Account structure table

Select from the list of active account structures

Ensure Standard mode is selected

Account structure table

Add rows to include as many structures as you need

In this example a structure for Balance sheet and Profit Loss accounts is


included

Upload these details as follows:


Atlas ribbon

Select Batch tasks

Ensure mode is Standard


Tick to select New chart of accounts


Start Batch tab

Double-click to open

Start Batch tab

Select Run to start the process

2.4.3.3 LOADING MAIN ACCOUNTS

Although this template does not contain all columns from the Main account table, it does cover many of the frequently used columns. It also contains a column for Legacy natural account, use this column to allow the template to be used as a cross-reference.The template once opened, appears as follows:

GENERAL LEDGER

Figure 5 Template with sample natural account codes



Chart of Accounts Set for you from the chart of accounts tab

E.g. Atlas Main Accounts

Main Account Enter Natural Account code for each account to be used in your Microsoft Dynamics AX entity

If you already have a list of accounts, paste that list into this column. The table will expand to fit the list

Type Select a suitable account type. The cell comment describes available choices.

E.g. Profit and Loss, Balance Sheet, Total etc.

Reporting type Enter Header or leave blank Header is used when the Type is Reporting

Category reference Select an account category for this account

Account categories are set up as part of the pre-requisites and are used to classify the main accounts according to functional groups.

Debit/Credit proposal Enter Debit, Credit or leave blank For posting accounts, optionally select what the normal sign will be for this account during general journal entry

Debit/Credit Requirement Enter Debit, Credit or leave blank Optional. Determines whether a transaction on this account must be of a particular sign

Currency Revaluation Enter Yes or leave blank

Monetary account Enter Yes or leave blank Only complete if Currency revaluation is set to Yes

Currency Enter a currency code or leave blank

E.g. AUD. Used as a default on journal entry

Consolidation Account Enter an account code that represents the account used to report this account upon consolidation

This is not validated here

10

GENERAL LEDGER


Blocked Enter Yes or leave blank Not loaded using Main Account template but separately after transaction entry has been completed

Close Enter Result, Balance, Capital or leave blank

Used in Year-end closing

Legacy natural account Enter a natural account or leave blank

Optional. Use the cross-reference workbook to associate the Main Account to be used in Microsoft Dynamics AX with the natural account from your legacy system

Load as follows:



Document template list Tick to select Main account append



2.4.3.4 TOTAL ACCOUNTS

These are non-posting accounts which are used to aggregate postings made to other main accounts. An example of this kind of account might be Total Sales. Total accounts are different from other accounts in that they include a list of Main account ranges to describe the elements to be aggregated. Total accounts are added with other Main accounts by using the Main account append template, but to describe the account ranges for aggregation, use the template on the Total accounts tab of the Main accounts workbook, thus:

Figure 6 From/To ranges define the natural accounts for each Total accountOn the Total accounts tab:

GENERAL LEDGER


Main Account List each Total account define in your chart of accounts

E.g. 501900

From Enter a Main Account that marks the start of the range

To Enter a Main Account that marks the end of the range

Invert Enter Yes or leave blank Use this if you wish to exclude a range of accounts from a Total

Load these using a Batch task:




Tick to select Total account ranges


Start Batch tab Double-click to open

Start Batch tab Select Run to start the process

2.4.4 DIMENSIONS

2.4.4.1 OUTLINE

Organizational units and custom dimension lists can be loaded using Atlas. The template described here, has tabs to accommodate Department, Cost center, Business unit and any custom dimension you wish to define. For custom dimensions, you must first create the dimension type in the Financial Dimensions form in Dynamics AX. (General ledger -> Setup -> Financial Dimensions -> Financial Dimensions).The following sections describe loading each type of dimension: Custom dimensions12

GENERAL LEDGER Department Cost center Business unitThe final section describes using a batch task to load all dimensions at once.

2.4.4.2 LOADING CUSTOM DIMENSIONS

In the custom dimension worksheet, you can see that a custom dimension of Vehicle is the basis for the upload:

Figure 7 A list of vehicle registrations

To add custom dimensions using this worksheet, simply:


Value Enter a list of possible values Paste a list and the table will expand to suit

Description Enter a description of the value This is normally a name or description. In this case it is a registration plate number

Legacy account Optionally enter the legacy system equivalent

To load:



Document template list Tick to select Custom financial dimension



GENERAL LEDGER2.4.4.3 Department dimension

The Department dimension is loaded in a similar fashion to the Custom list. The upload, in this case, specifically relates to the Department Operating Unit.

You should note that any code or value used must be unique across all operating unit dimensions. In other words, you cannot define a department code that is used again in the cost center or business unit dimensions.

The template appears as follows:

To add custom dimensions using this worksheet, simply:



Description Enter a description of the value This is normally a department name


To load:



Document template list Tick to select Operating unit - Department



2.4.4.4 COST CENTER DIMENSION

The Cost center dimension is loaded in a similar fashion to the Department list. The template appears as follows:14

GENERAL LEDGER

To add cost centers using this worksheet, simply:





To load:



Document template list Tick to select Operating unit – Cost Center



2.4.4.5 BUSINESS UNIT DIMENSION

The Business unit dimension is loaded in a similar fashion to the Department list. The template appears as follows:

To add Business units using this worksheet, simply:

GENERAL LEDGER





To load:



Document template list Tick to select Operating unit – Business Unit



2.4.4.6 Loading all at once

Done individually, there are four individual uploads need to load these different dimensions. If you wish to load all at once, use the Batch task – Add all dimensions:




Tick to select Add all dimensions


Start Batch tab Double-click to open

Start Batch tab Select Run to start the process

16

2.5 TRANSACTIONS2.5.1 OUTLINE

In this case, transactions relate to those posted via the general journal and the budget system. Consideration is given to the types of transaction you might want to upload: Year start (Opening balances) Period movements (Trial balance by period) Transactions (One-for-one with legacy system postings)From a budget perspective, this will be in the form of a period spread.

2.5.2 YEAR START – OPENING BALANCES

2.5.2.1 OUTLINE

Opening financial positions can be loaded into Microsoft Dynamics AX in the form of a general journal but based on a closing Trial Balance generated by a legacy system. Normally, this Trial Balance will be as at the closing period of the prior financial year.To load this into Dynamics AX you will need to do the following steps: Import or have made available a copy of the Trial Balance capable of being read by Microsoft Excel.

If you are migrating from earlier versions of Microsoft Dynamics AX, you can use Atlas to get these numbers for you. Atlas 6+ will offer the ability to extract these figures from legacy systems other than Microsoft Dynamics AX.

Re-code any legacy system account codes with the Main accounts to be used by Microsoft Dynamics AX 2012. You can use the Legacy Natural Account column as a cross reference to the main account if you wish.

Transpose the re-coded Trial Balance into the Atlas general journal upload template

2.5.2.2 IMPORT LEGACY SYSTEM TRIAL BALANCE

Many legacy systems can save reports to a file that can be read by Microsoft Excel. Save the Trial Balance to such a file and open using the relevant options in Excel. In most cases you will need to run this report at a sufficiently detailed level so that necessary transactional analysis is included. If you are migrating from an earlier version of Microsoft Dynamics AX, use Atlas to do task for you.

Figure 8 Trial Balance from legacy system2.5.2.3 RE-CODE THE TRIAL BALANCE REPORT TO REFLECT NEW ACCOUNT STRUCTURES

Once the Trial Balance is in an Excel worksheet, you can re-format and using the cross-reference, re-code it to suit the account new structures. Ensure all Trial Balance entries have a corresponding Microsoft Dynamics AX main account and if financial dimensions are being used, ensure these are coded correctly.

GENERAL LEDGERUse this as the basis for building a general journal.

2.5.2.4 BUILD THE ATLAS TRIAL BALANCE JOURNAL

Use the re-coded Trial Balance as the basis for the upload journal. Typically, there will be one transaction line for each Trial Balance entry. Below is a completed sample of this journal:

Using the re-coded Trial Balance as a basis, complete as follows:


Company Select the target company you wish this journal to be posted into.

E.g. CEU

Description Enter a short description of the journal

E.g. Opening Balances 2013

Date Enter a year end date E.g. 30/06/2012

Voucher series Enter a number sequence you wish to use as a voucher

Journal series Enter a number sequence you wish to use as a journal

Main Account Enter Natural Account code for each account that appears in the Trial Balance

Paste this list from your re-coded trial balance

Legacy account (Description)

Enter the legacy system’s account code in this column

Paste this list from the legacy system’s trial balance. This provides a useful on-transaction cross reference and can be used in reconciliation between systems

Type Select Ledger

Currency Enter a currency code to match that of the base currency of the ledger

E.g. USD

Rate Enter an exchange rate. E.g. 100 or 1

Debit Enter the debit amount for this transaction

Leave blank if credit

18

GENERAL LEDGER

Credit Enter the credit amount for this transaction

Leave blank if debit

Document Enter a reference for this transaction

E.g. FY2012

Document date

Enter a year end date E.g. 30/06/2012 or leave blank

Financial dimensions

Enter dimension codes to suit each main account

Paste values from the re-coded trial balance

Upload this into Microsoft Dynamics AX.


Atlas ribbon Select Journal Ensure mode is Standard

Document template list Tick to select Year End Trial Balance Journal



This sends the journal to Microsoft Dynamics AX. If it is successfully loaded, the journal number in cell I5 is updated and the number of records, along with the journal number is shown in the template confirmation tab.

Figure 9 Loading a trial balance

In Microsoft Dynamics AX this journal is represented as follows:

GENERAL LEDGER

Figure 10 Journal GLJ000011 in Microsoft Dynamics AX

2.5.3 PERIOD TRIAL BALANCE UPLOAD USING GENERAL JOURNAL

2.5.3.1 OUTLINE

Once the Year End journal is uploaded, you can roll that year into the current or next year. Current year postings up to the point of system operation (“Go Live” if you like) can be taken on in the form of period movements or transaction by transaction. This section deals with period MOVEMENTS.

2.5.3.2 PREPARING AND POSTING A PERIOD MOVEMENT USING GENERAL JOURNAL

This process is the identical to that used to take on the closing balances in section 2.4.2. Follow the steps therein, using the period end date as the accounting or transaction date. For example, if your intention is to start operating your new Microsoft Dynamics AX system on the 30th April, and you have a financial year of January to December, you might load an opening balance journal as at 31 December and then load period balances to 30 th April. On the 1st May, transaction posting would commence.This method of data migration is least costly in terms of time to migrate without losing reporting integrity.

When posting period balances, it is important to post to a financial dimension level that does not compromise your reporting in the current year.

2.5.3.3 SAVING YOUR WORK

Save each period as a separate workbook. So, in the January to April example, you’d have a workbook for January, February, March and April.

2.5.4 TRANSACTION POSTINGS

2.5.4.1 OUTLINE

In some organizations, posting of transactions is undertaken for the current year. This typically means that the volume of transactions posted to the general ledger is significantly higher than if postings were made at period movement or on a daily level. Posting at transaction level offers some further cross-system references but is prone to error (You have to be more diligent), it takes longer to do; both in terms of data conversion, load time and reconciliation. For these reasons alone, we do not normally recommend migrating transaction-for-transaction.20

GENERAL LEDGER

2.5.4.2 CONSIDERATIONS

The following table discusses some considerations:

Item Consideration/Options Comment

Voucher Use Document reference to record the legacy system’s equivalent of voucher. When it changes a new can be generated. You’ll need to sort by this and ensure you have In connection with balance selected at the voucher level. Each voucher will need to balance.

Many systems have the concept of a posting unit that groups balanced sets of debits and credits. In Microsoft Dynamics AX it is voucher. Other systems have a similar concept.

Transaction Date Use Date as the transaction date or use Document Date as the transaction date and Date would become the posting date per your data migration strategy.

In Microsoft Dynamics AX a voucher must be share one date only.

Third party Recording Vendor, Customer and/or Person can be achieved provided that a special, custom financial dimension is added for this purpose.

This will not provide you with the ability to produce customer/vendor statements, but it will allow P&L analysis by these external parties.

Transaction description Use this to record the legacy system’s account code. This provides a natural in-line cross reference. If you wish to use this for the source system transaction description; possibly including invoice numbers etc, you should consider merging in the legacy system account too.

Alternatively, you can combine the account code and description. E.g. “10-4107 MARCH ELECTRICITY”

2.5.5 ADJUSTMENTS TO BALANCES AND MOVEMENTS

Balances and period movements may need to be adjusted once posted. To do this, you should use the upload templates described above but include transactions that affect the adjustments only. This might involve posting transactions between accounts and financial dimensions. Unless the circumstances warrant it, we do NOT recommend removing transactions to re-do the transaction upload.

2.5.6 ACCRUALS

Accrual transactions are posted at the end of or during the “go live” month and are reversed in the following month. Depending on the volume of these transactions; use either the standard journal features of Microsoft Dynamics AX or use the Atlas template here:

GENERAL LEDGER

Figure 11 Insurance accrual to be reversed in June



Company Select the target company you wish this journal to be posted into.

E.g. CEU

Name Enter the journal name to be used E.g. ACCRL

Description Enter a short description of the journal E.g. May Accruals

Date Enter a year end date E.g. 30/06/2012

Journal sequence

Enter a number sequence you wish to use as a journal E.g. ATL_GJL

Voucher series Enter a number sequence you wish to use as a voucher E.g. ATL_ACCRL

Main Account Enter Natural Account code for each account in the accrual journal

Description (Txt)

Enter a description for the accrued transaction

Date Enter the date of the accrual transaction E.g. 31-May-2013

Type Select Ledger

Currency Enter a currency code to match that of the transaction E.g. AUD

Rate Enter an exchange rate. E.g. 100 or 1

Amount Enter the debit (positive number) amount or credit (negative number) for this transaction

Normally debit the expense, assuming expense is the Main account, above.

Offset Enter Natural Account code representing the balancing entry for the voucher

E.g. Balance sheet pre-payments

Document Enter a reference for this transaction E.g. May Accrl

Document date

Enter a date for the transaction E.g. 31-May-2013 or leave blank

Reversal date Set for you. Set to be 1st day of following month If transaction date is 31-May-2013, then this is calculated as 01-June-

22

GENERAL LEDGER


2013

Financial dimensions

Enter dimension codes to suit each main account

Load as follows:



Document template list Tick to select Reversing Journal


Confirmation tab Click to select Upload Journal number is pasted to cell I5

The resulting confirmation is as follows:

Figure 12 Accrual journal confirmation

2.5.7 BUDGETS

2.5.7.1 OUTLINE

The loading of management budgets and forecasts is an important aspect of data migration in the General Ledger. Budgets form an important part of reporting and performance management of any organization. In many ways, the process is similar to that used to load actual transactions and requires existing budgets from legacy systems to be re-coded and formatted for the upload to be successful. Before you can load any budget transactions into Microsoft Dynamics AX using Atlas, you must satisfy the pre-requisite settings describe above.

2.5.7.2 USING THE PERIOD SPREAD TO LOAD A FULL YEAR BUDGET OR FORECAST

Re-code the budget from the legacy system and use this as the basis for the upload journal. Typically, the worksheet will spread the budget or forecast values per main account/dimension combination per period across the sheet. Only account and period combinations that have a value will be loaded using this template. Below is a completed sample of this journal:

GENERAL LEDGER

Figure 13 Expense budgetComplete the worksheet as follows:


Company Select the target company you wish this journal to be posted into. E.g. CEU

Year Enter a fiscal year E.g. 2013

Model Select a model from the task pane E.g. BUDG

Budget code Select a budget code from the list E.g. ORIG

Entry # Enter a number sequence you wish to use as a reference E.g. ATL_BUD

Currency Enter a currency code used to describe the amounts in the budget E.g. USD

Main Account Enter Natural Account code for each account in the accrual journal

Comment (Legacy account)

Enter the legacy system account code for this budget entry

Type Enter Expense or Revenue

Financial dimensions Enter dimension codes to suit each main account

Jul3 Enter the debit (positive number) amount or credit (negative number) for this month

Normally debit the expense, assuming expense is the Main account, above.

Aug Enter the debit (positive number) amount or credit (negative number) for this month

Sep Enter the debit (positive number) amount or credit (negative number) for this month

Oct Enter the debit (positive number) amount or credit (negative number) for this month

Nov Enter the debit (positive number) amount or credit (negative number) for this month

3 Where the year is Jan to Dec, move the columns so that Jan is first, then feb, mar etc.24

GENERAL LEDGER


Dec Enter the debit (positive number) amount or credit (negative number) for this month

Jan Enter the debit (positive number) amount or credit (negative number) for this month

Feb Enter the debit (positive number) amount or credit (negative number) for this month

Mar Enter the debit (positive number) amount or credit (negative number) for this month

Apr Enter the debit (positive number) amount or credit (negative number) for this month

May Enter the debit (positive number) amount or credit (negative number) for this month

Jun Enter the debit (positive number) amount or credit (negative number) for this month

Total Sum of amounts from Jul to Jun

Load as follows:



Document template list Tick to select Budget Register Entries


Confirmation tab Click to select Upload Journal number is pasted to cell E10

The resulting confirmation is as follows:

GENERAL LEDGER

Figure 14 145 entries to loaded model BUDG

In Microsoft Dynamics AX the entries appear as follows:

Figure 15 Draft budget entries ready for posting.

26

2.6 VERIFICATION REPORTS2.6.1 OUTLINE

Having loaded transactional data into Microsoft Dynamics AX you will need to verify it against the source system. This reconciliation will take the form of reports at Main account and if necessary, financial dimension level and in some cases down to the voucher level.

2.6.2 TRIAL BALANCE

This report verifies the balances loaded into Microsoft Dynamics AX. If you used the on transaction cross-reference, you can prepare this report in terms of the chart of account used in Microsoft Dynamics AX and in terms of the legacy system. This is demonstrated in the following report template.

Figure 16 Trial balance using Dynamics AX chart of accounts

Figure 17 Trial balance using legacy system codes

2.6.2.1 USING THE REPORT

To run these reports, simply open the workbook and select the Trial balance worksheet and then adjust the parameters to suit:

GENERAL LEDGER


Company Select the target company from which you wish get trail balance figures

E.g. CEU

Period range Select a date range from year start to restrict the range of transactions to include in the Period movement column

E.g. 01/01/2013..31/12/2013.

Main account Enter * for all accounts Allows you to concentrate on a range instead of all if required.

Use Refresh on the ribbon to enforce update. Both worksheets are updated. Compare the returned results with those provided by the legacy system. You can use these worksheets with the re-coded trial balance to establish any variances via pivot table or via formula.

2.6.3 BUDGET SPREAD

2.6.3.1 OUTLINE

Budgets loaded into Microsoft Dynamics AX need to be verified for correctness. Use a matrix report to show by combination of account and dimension the balances loaded into each year and model. By using the legacy account code in the comment, you can prepare the report by legacy account code too.

Figure 18 Posted budgets in model BUDG for the FY 2013


Open the report and select the Budget spread worksheet. Adjust the parameters to suit and then refresh as follows:


Company Select the target company from which you wish get trail balance figures

E.g. CEU

Year Enter a year from which the budget numbers will be extracted

E.g. 2013

Model Enter a model code from which the figures will drawn

E.g. BUDG

Status Select Completed Use Draft for un-posted budget figures

28

GENERAL LEDGER 2.6.4 BALANCE SHEET

2.6.4.1 OUTLINE

This is optional. The balance sheet is a key statutory report used by all organizations. It reconciles assets, liabilities and equity. Assuming the Trial Balance is correct, the balance sheet is simply another representation of the same set of figures. Included here is a summarized balance sheet based on the chart of accounts used here.

You need to adjust this report’s settings to suit the main accounts used in your installation.


Open the workbook and change the year end date and the company identifier. The report will refresh automatically. Ensure the reported figures for Net Assets and Shareholder’s Equity agree.2.6.5 INCOME STATEMENT

2.6.5.1 OUTLINE

This is optional. Like the Balance Sheet, the Income Statement is a key document for measuring financial performance. Normally, they will compare actuals to budgets and forecasts and can be highly summarized or very detailed.

You need to adjust this report’s settings to suit the main accounts and dimensions used in your installation.

GENERAL LEDGER


Open the workbook and change the period date, the period basis and the company identifier. The report will refresh automatically. Ensure the reported figures at the end of the report agree to the retained earnings line in the balance sheet.

30

GENERAL LEDGER

2.7 AFTER POSTING ACTIVITIES2.7.1 OUTLINE

Complete the general ledger configuration as required. You should set blocking on accounts that are used by sub-ledger processes and you should create dimension sets as needed.

2.7.2 BLOCKING ACCOUNTS FROM USE IN THE GENERAL JOURNAL

Accounts not to be used in general journal posting need to be blocked from doing so. To do this you need to use the chart of accounts workbook again and mark the relevant accounts with a “Yes” in the Blocked column. Typically, these accounts will be those that receive postings from sub-ledgers. E.g. Accounts receivable or Accounts payable control accounts.To activate the blocking in Microsoft Dynamics AX:



Document template list Tick to select Blocked accounts



2.7.3 DIMENSION SETS

Build dimension sets to support reporting and on-screen inquiries. Here is an example used in this company:

COPYRIGHT NOTICECopyright © 2013, Globe Software Pty Ltd, All rights reserved.

GENERAL LEDGERTRADEMARKSDynamics AX, IntelliMorph, and X++ have been registered as or are under registration as trademarks of Microsoft Corporation. Microsoft Office System 2010, Microsoft Office System 2007, Windows 2003 and Windows 2008 are registered trademarks of Microsoft Corporation.

SOFTWARE RELEASEThis documentation accompanies Atlas version 5.0.3000 or higher and which is suitable for Microsoft Dynamics AX V4.0 SP2, Dynamics AX 2009 SP1 and Dynamics AX 2012 or higher and Microsoft Office 2007 or higher.

PUBLICATION DATE10 June 2013

READER COMMENTSAny comments or suggestions regarding this publication are welcomed and should be addressed to the attention of: [email protected]

32