Populating data
During the planning of a Dynamics GP implementation, decisions should be made about what type of data needs to be populated into the system prior to, or possibly after, Go Live. Data can be grouped into three categories: master data, historical data, and open transactions. Each should be discussed in detail and planned for separately.
Master data
Master data includes customers, vendors, inventory items, fixed assets, and General Ledger accounts. There is no question that you need to populate this data as part of the implementation. However, there are a few considerations you must plan for, such as where to get the data, how much of it has to be cleaned up or changed, and how to accomplish actually getting it into Dynamics GP.
Implementing a new ERP system is a great opportunity to re-examine the existing master data and do some renumbering or general cleanup. At this stage of the planning, you should put together a list of what master data needs to be populated into Dynamics GP and details about each type of data. Here is a sample list for the NJW company:
As part of the data gathering process for master data, you may find it helpful to ask for lists of the existing data. Looking at these will help you determine if there is additional data cleanup needed past what you already know. For example, some software packages keep the entire address for a customer or a vendor in one field. Dynamics GP has separate fields for three address lines plus city, state, zip, and country. If the address needs to be separated into individual fields for 2,000 customers and 700 vendors, that will require some time and effort and you should plan for it accordingly.
Once you have your list of master data and tasks, decide who will perform any cleanup or renumbering needed. Depending on the volume of each type of data, you should also decide how this data will be populated into Dynamics GP. For the sample list we created previously showing NJW's master data, the recommendation would be to import everything except the salespeople, which should be entered manually. Usually, the determination between whether to import or manually enter records depends on the combination of the total number of a particular type of data and how much cleanup is needed. Anything over 100 records may be a good candidate for importing.
Have a plan for how to handle master records that may be added during the implementation. For example, let's say you have the following plan:
- Export the list of vendors from the current system on June 9
- Have someone work on cleaning it up and renumbering it until June 15
- Import a new list of vendors into Dynamics GP on June 15
- Go Live will be on July 1
What happens if a new vendor is added between the export on June 9 and the Go Live on July 1? If a vendor needs to be added prior to the import into Dynamics GP, there may still be time to add them to the import list. If that is not an option or the vendor is added after the import is done, that vendor will need to be manually entered into Dynamics GP. Communicate this plan to the users that may be adding any new master records so they are aware that there will be an additional step needed for any new master records added during the implementation.
Historical data
Historical data includes any transactions that have already been completed, paid, or settled. This data typically includes the following:
- Prior year General Ledger transactions
- Fully paid Accounts Payable transactions
- Received and invoiced Purchase Order Processing transactions
- Fully paid Accounts Receivable transactions
- Fulfilled and posted Sales Quotes, Orders, and Invoices
- Reconciled cash transactions
- Prior Inventory transactions
While all of this data can be imported into Dynamics GP during (or even after) your implementation, choose wisely when deciding how much historical data to bring in. Importing historical data generally adds a high level of complexity, time, and cost to implementations for what is usually determined to be of very little value as quickly as one or two months after the Go Live. Consider also any renumbering you may be planning as part of the implementation. Anything renumbered will need to be remapped prior to import, adding yet another time and cost component.
When discussing historical data with the implementation team, ask why each particular type of historical data may be needed. The following are the three most common reasons for importing historical data into Dynamics GP with some thoughts about each:
- Looking up details on individual transactions: If the historical data is needed for looking up individual transactions, then suggest keeping the old system available for this for a period of time after the Dynamics GP implementation. In my experience, after the first few months, details of individual transactions are very seldom needed, and thus if the old system is available for infrequent lookups, there is no need to import the detailed history into Dynamics GP.
- Reporting: The company may require reporting that includes past history either for General Ledger transactions, sales, purchases, or inventory movements. Most of this is only needed on a monthly basis in summary and there are options in most modules of Dynamics GP to enter historical summary data without having to import detailed transactions. Depending on where the existing data is and exactly what type of reporting is needed, creating a report that spans both the old system and Dynamics GP could be an option. Finally, it may be easier to create custom tables in Dynamics GP to store summary historical data for use in reporting.
- Audits: No matter how much historical data is imported, most audits for the first year after an ERP implementation will require going back to the old system for data. If any GL accounts, customers, vendors, or inventory numbers are changed during the Dynamics GP implementation, then any historical printed reports may not match up with what is in the new system. After spending a considerable amount of time to map and import the history, there may still be a need to revisit the old system. With that given, consider how much time and effort should be spent to import historical data into Dynamics GP.
The preceding discussion does not mean that you should not import any historical data. There are some situations where it is the right course of action. Certainly for the General Ledger, it is recommended that at least one or two years of summary data be imported to facilitate comparative financial reporting. However, consider all the implications and the effort needed carefully when deciding whether to import historical data and, if so, how much.
Open transactions
Open transactions include all transactions not completed, paid, or fully applied by the cut-off date and any outstanding bank transactions after the last bank reconciliation. Open transactions also include all GL transactions for the open fiscal year. Cut-off dates should be determined for each module, and as much clean-up as possible should be done in the old system to prepare the data that will be exported and then populated into Dynamics GP.
Open transactions will be entered or imported into Dynamics GP as regular transactions, typically with a description indicating that they have been imported from the previous system. For every different type of transaction that needs to be populated, consider the following five questions:
- What is the process for exporting this data out of the old system? Is it straightforward to export the required data into an electronic file in a format you can use? If not, you need a plan to gather this data. Get samples of the export files for each type of data so that you can address any issues ahead of time and be prepared for any data manipulation that may be needed.
- Are there many old transactions that require cleanup? Consider any transactions over a few years old that are still open. If they need to be written off, perhaps it is better to do this in the old system. This work can start as soon as these items are identified, well ahead of the Dynamics GP implementation.
- Are there unapplied credit memos or payments that can be applied to open invoices? Looking at the sample lists you have gathered, are there invoices and credit memos or payments for the same customers or vendors that can be applied to each other? Plan to do this cleanup prior to exporting the final lists during your implementation.
- Taking all the cleanup into consideration, how many open transactions are left? The answer to this will determine whether to manually enter or import this type of transaction. Typically, anything under 100 transactions may be a good candidate for manual entry, especially for Purchase Order Processing and Sales Order Processing transactions, as they are usually fairly complex to import. Another determining factor should be how much importing experience the implementation team has versus how many resources are available to manually enter transactions. Some companies may also decide to use manual entry of open transactions as a way to complement user training during an implementation.
- What is a reasonable cut-off date? For each type of open transaction, determine when it would be practical to stop entering transactions into the old system and export the list for population into the new system.
Let's go through the typical list of open transactions and some additional considerations for each of the core modules in Dynamics GP and look at a few examples.
General Ledger
The General Ledger is somewhat of a special case and almost always requires a combination of historical and open year data to be imported to facilitate comparative financial statements as well as ongoing inquiries. This is very straightforward in Dynamics GP, and importing the historical and open year data can be done at the same time.
For example, NJW wants to start using Dynamics GP on July 1, 2013. NJW is on a calendar fiscal year and plans to close June 2013 in their old system. They expect the 2012 fiscal year will have been audited and closed by Go Live. However, June's closing numbers will not be completed until sometime in July. NJW would also like to be able to print comparative financial reports going back to December 2010.
NJW has decided that with the old system available for lookups and audits through the beginning of 2013, there is no reason to spend the time and effort to import detailed 2013 GL transactions into Dynamics GP. Keeping in mind that the GL account numbers may change during the GP implementation, here is the task list for NJW's GL transactions:
- Create the 2010, 2011, 2012, and 2013 fiscal years in Dynamics GP.
- Export the ending 2010 GL account balances from the old system.
- Export the monthly GL account net changes for 2011, 2012, and 2013 (through May) from the old system.
- Once the new General Ledger Chart of Accounts is finalized, create a mapping of the old to new GL accounts.
- Update the exported 2010 ending balances and all the monthly net changes with the new GL account numbers.
- Import the ending balances as of December 31, 2010 and the net changes for each month of 2011, 2012, and 2013 through May 31, 2013 into Dynamics GP prior to Go Live.
- Import the June 2013 net change once the month of June is closed in the old system.
- Post all imported transactions and close the 2010, 2011, and 2012 years to update Retained Earnings and roll forward all the Balance Sheet account balances. This should be done one year at a time so that balances can be checked against previously generated financial statements and fixed if needed before closing each year. Even though some account numbers may change, at the summary level everything should match. For example, total assets on the Balance Sheet should be the same and the monthly net gain or loss should stay the same.
Starting to use Dynamics GP and then importing the General Ledger numbers for the month of June should not cause any problems, although correct financial reports and account balances for 2013 will not be available in Dynamics GP until all data has been imported.
General Ledger balances and net changes are typically straightforward to export out of the previous system. However, this is definitely something to check, as it may require additional work. The minimum data columns needed to enter or import GL transactions into Dynamics GP are:
- Transaction Date: If importing monthly net changes, use the last day of the month. If your fiscal periods are not calendar months, use the last day of each fiscal period.
- Account Number: Use the new GL account number in Dynamics GP if you are changing account numbers.
- Amount: This can either be one column with positive numbers for debits and negatives for credits, or two separate columns, one for debits and another for credits.
Payables Management
Open transactions for Payables Management include all unpaid or not fully applied payables transactions as of the cut-off date.
Note
The open portion of these transactions is what is still unpaid or unapplied. If an invoice was originally $5,000, but you have already paid $2,500 of it, this invoice should be brought into Dynamics GP as $2,500.
One option that companies may consider is entering summary open balances into Dynamics GP. This method results in one transaction per vendor for the entire balance due to that vendor, whether that is made up of one invoice or fifteen. This is not recommended as it is usually necessary to specify what invoices are being paid on each check to a vendor, which will not be possible with this method. So the time saved by this approach will be used up by constantly referring to the old system for details. Best practice is to import an individual transaction into Dynamics GP for every existing unpaid transaction in the current system.
The following are the minimum data columns needed and some important notes for entering or importing payables transactions into Dynamics GP:
- Vendor ID: This will be the new Vendor ID in Dynamics GP. If you have decided to change these, after exporting the data from the current system, you will need to update all the transactions with the new Vendor IDs.
- Transaction Date: If you have a number of old transactions that are still open, you may want to consider using the first date of the earliest year you have set up in GP and entering a note with the actual date. For example, if you have a transaction dated May 25, 2008 and the first year set up in Dynamics GP will be 2010, use January 1, 2010 as the date for this transaction. This will avoid any possible issues caused by having transactions in non-existing years. You can add the original date from the old system into a Note or Description field.
- Transaction Type: It is recommended to only import Invoices and Credit Memos. If you have unapplied payments, enter them as credit memos. Entering payments will cause unwanted transactions to be created in the Bank Reconciliation module that will have to be cleaned up afterwards.
- Document Number: This will typically be the invoice number, but some systems allow you to have transactions without a number or duplicate transaction numbers per vendor. The transaction number is required in Dynamics GP and should be unique per vendor.
Tip
You can keep track of a reference or invoice number from the old system for each transaction in the Description or Note.
- Amount: Dynamics GP will not accept a negative transaction amount. The transaction type controls whether you owe the vendor or the vendor owes you. If your current system allows for negative amounts on invoices, you will need to enter these into Dynamics GP as credit memos. Remember that the amount should be the open balance for each transaction and not the original amount.
- Currency ID: If using Multicurrency, the currency of the transaction is needed. If not using Multicurrency, this is not required.
Receivables Management
If you are planning to use the Sales Order Processing module, it may be tempting to import detailed line items for open receivables invoices. This is certainly an option, although most companies do not choose to do this because it will typically lead to a lot of extra and possibly unneeded work. Instead, all open balances are typically brought into the Dynamics GP Receivables Management module without any line item detail. The considerations and options for receivables transactions are very similar to those for payables transactions.
The following is a list of the minimum information required and some additional guidelines for importing or entering receivables transactions into Dynamics GP:
- Customer ID: This will be the new Customer ID in Dynamics GP. If you have decided to change these, after exporting the data from the current system, you will need to update all the transactions with the new Customer IDs.
- Transaction Date: If you have a number of old transactions that are still open, you may want to consider using the first date of the earliest year you have set up in Dynamics GP. You can add the original date from the old system into a Note or Description field.
- Transaction Type: It is recommended to only import Invoices, Debit Memos, Credit Memos, or Returns. Credit memos and returns are almost identical, both indicate a negative receivable. For statistical and reporting purposes, it may be useful to separate them. The same idea applies to invoices and debit memos. Both indicate that the customer owes the company money, but may be useful to separate different types of transactions. If you have unapplied payments, enter them as credit memos. Entering payments will cause unwanted transactions to be created in the Bank Reconciliation module that will have to be cleaned up afterwards.
- Number: This will typically be the invoice or credit memo number from the current system. Some systems allow for transactions without a number, especially for debit and credit memos, or they allow for duplicate transaction numbers. Dynamics GP requires transaction numbers and these must be unique per receivables transaction type (not just per customer).
Tip
You can keep track of a reference or invoice number from the old system for each transaction in the Description or Note.
- Amount: This has to be a positive number, as Dynamics GP will not accept a negative number. The transaction type will control whether the customer owes you or you owe the customer. If your old system allows for negative amounts on an invoice, this will need to be entered into Dynamics GP as a credit memo or return. Keep in mind that the amount should be the open balance on each transaction and not the original amount.
- Currency ID: If using Multicurrency, the currency of the transaction is needed. If not using Multicurrency, this is not required.
Inventory
Existing inventory quantities will need to be populated into Dynamics GP. In an ideal situation, a physical inventory count would be performed prior to the implementation. However, this is not always possible at the time of the implementation, so use the current system, or whatever inventory tracking methods are available, to export the existing inventory quantities and populate them into Dynamics GP.
It is recommended to enter or import inventory quantities prior to entering any new Sales Order Processing transactions. The following are the required fields for entering or importing inventory quantities into Dynamics GP:
- Date: This should be the date of the inventory export or stock count.
- Item Number: If inventory item numbering changes as part of your implementation, you will need to update these with the new Item Numbers for Dynamics GP.
- Unit of Measure: This may be optional if the default unit of measure for the item is set up in Dynamics GP and that is what is being used for the transaction.
- Site ID: This is the warehouse or inventory location in Dynamics GP where the item is located. If only one inventory location will be set up in Dynamics GP, this can defaulted and does not need to be part of the import.
- Quantity: Quantity in the unit of measure specified.
- Unit Cost: The unit cost per item in the unit of measure specified.
Tip
Changing accounting systems may seem like a good time to change your inventory valuation methods. Before considering anything like this, you will want to check with your auditors and/or tax consultants to make sure this will conform to GAAP and IRS regulations.
Purchase Order Processing
If purchase orders were used in the old system, there will typically be a number of open (not yet received) purchase orders. Depending on how many open POs there are and how many line items are on them, a decision needs to be made on whether to populate these into the Purchase Order Processing (POP) module in Dynamics GP and, if so, whether to enter them manually or import them. If open purchase orders are not entered into Dynamics GP, it will still be possible to enter receipts and invoices for these POs as they are received.
If you are bringing in open purchase orders into Dynamics GP, keep in mind that both the vendor and inventory item numbering may have been changed. A decision will also be needed as to whether to enter only line items that were not received yet or all line items. The recommendation is to only enter line items and quantities that have not yet been received. Even though the resulting POs may not accurately reflect the original POs, this will save a lot of time in updating the inventory and PO statuses, and will easily allow for accurate reporting on open PO items only.
There are many details that can be entered or imported for purchase orders. The following are the minimum data columns required for POs in Dynamics GP:
- PO Number: Even if you start a new numbering scheme for purchase orders in Dynamics GP, this should be the existing PO number. PO numbers in Dynamics GP must be unique.
- Vendor ID: This will be the new Vendor ID in Dynamics GP.
- PO Date: The date that was printed on the purchase order.
- Currency ID: If using Multicurrency, the currency of the transaction is needed. If not using Multicurrency, this is not required.
- Item Number: If item numbering will change as part of your implementation you will need to update these with the new Dynamics GP Item Numbers.
- Unit of Measure: This may be optional if the default purchasing unit of measure for the item is set up in Dynamics GP and that is what is on the PO.
- Site ID: This is the warehouse or inventory location in Dynamics GP where the item will be received. This can also be defaulted if there is only one location.
- Quantity: The recommendation is to enter the remaining (not yet received) quantity only.
- Unit Cost: The unit cost per item in the unit of measure specified.
Sales Order Processing
Any open customer sales orders, back orders, or quotes in the old system can be entered into the Sales Order Processing (SOP) module in Dynamics GP. Similar to purchase orders, SOP orders, back orders, and quotes are not required to be able to enter sales invoices into Dynamics GP. If bringing in open SOP transactions, decisions will need to be made as to whether to import or enter manually, and what exactly to bring in for partially fulfilled transactions.
It is recommended to enter or import open SOP transactions after inventory quantities have been populated into Dynamics GP so that any back order or inventory availability issues can be avoided. There are many details that can be imported for SOP transactions. The following is the minimum information needed for SOP transactions in Dynamics GP:
- Customer ID: This will be the new Customer ID in Dynamics GP if customers' numbers have been changed.
- Bill To Address ID: This will default from the customer if not specified. Note this is not the actual address, but an address ID, so the addresses will need to be populated for customers for this to work.
- Ship To Address ID: This will default from the customer if not specified. This can be the actual address instead of the address ID; in that case this address will be stored with the transaction only and may or may not exist on the customer record in Dynamics GP.
- Date: The date of the transaction.
- Transaction Type: Hopefully, it is possible to post all Invoices and Returns in the old system prior to cutover. In that case, only Quotes, Orders, or Back Orders will need to be entered or imported into Dynamics GP.
- Document Number: This is required in Dynamics GP and has to be unique per transaction type.
- Currency ID: If using Multicurrency, the currency of the transaction is needed. If not using Multicurrency, this is not required.
- Item Number: If items are renumbered as part of your implementation this will need to be the new Dynamics GP Item Number.
- Unit of Measure: This may be optional if the default selling unit of measure for the item is set up in Dynamics GP and that is what is being used.
- Site ID: This is the warehouse or inventory location in Dynamics GP that the item will ship from. This can also be defaulted if there is only one location.
- Quantity: For any partially fulfilled orders or back orders, the recommendation is to only enter the remaining quantity to ship.
- Unit Price: The unit price per item in the unit of measure specified.
Bank Reconciliation
Any outstanding or unreconciled bank transactions will need to be populated into the Dynamics GP Bank Reconciliation module if it is part of your implementation. In most cases, bank statements are monthly and companies can download statements from the bank on the first of the following month to finish reconciling their cash quickly. If planning to use Bank Reconciliation, it is recommended that you do this from the start. For companies that have a lot of bank accounts and a high volume of transactions, not starting to use Bank Reconciliation from the onset will typically cause a lot of frustration and additional work when the decision is made to start using the module.
Often, we find that bank reconciliations are being done manually (in Excel) with the older accounting systems. This should make it easy to obtain the outstanding transaction list. The following lists the information required for entry or import of bank transactions:
- Transaction Type: It is recommended to only use the following:
- Check: Transactions that will show up on the bank statement with a check number.
- Withdrawal: Transactions that will show up as cash coming out of the bank account, but not a check. Typically, this is used for wires, as well as any electronic payments or bank fees that are not entered through payables.
- Decrease Adjustments: Any money coming out of the bank account that might need to be differentiated from checks or withdrawals. This is not used too often.
- Increase Adjustments: For outstanding transactions, use this to record any money coming into the bank account. This will avoid having to go through an additional step of depositing these transactions in Dynamics GP.
- Transaction Date: For checks, this should be the date the check was written. For all other transactions, it should be the date the cash was withdrawn from or deposited into the bank account.
- Checkbook ID: The Dynamics GP equivalent of a bank account. Every bank account will have a unique Checkbook ID.
- Number: For checks this should be the check number, for other transactions this is typically a reference number assigned to the transaction. While Dynamics GP will allow non-unique numbers for bank transactions, I recommend using something that will help quickly identify these transactions during back statement reconciliation. If this is not present in the old system, you can come up with a simple numbering scheme to use. For example, a monthly bank fee on June 30, 2013 can be
130630 BANK FEE. - Paid to/Received from: An optional field, but highly recommended as it is helpful to have this information available for lookups and searches in Dynamics GP.
- Amount: This should be the outstanding amount, although for bank transactions this will most likely be the same as the original transaction amount. All amounts should be positive, as the transaction type determines whether the money is coming in or going out of the bank account.
Have a plan for what to do if an open transaction needs to be added after the cut-off date. This is often needed for payables checks—a check may need to be cut right away and this can happen after the payables open transactions have been exported from the current system and are being prepared for import. Any transaction needed after the cut-off will need to be entered into both the old system and Dynamics GP in the same way. Communicate this to any users that may need to enter transactions and make sure they know to keep detailed records of any transactions like this so they can be entered into Dynamics GP once it is available.
How to import
After all of the discussion about whether to manually enter or import data, you may be wondering how to actually accomplish importing data into Dynamics GP.
The recommended tool for importing master data, historical data, and open transactions into Dynamics GP during an implementation is Integration Manager. Integration Manager is a separately installed application that imports data into Dynamics GP while preserving the same business logic as if you were manually entering that data using the Dynamics GP user interface. Integration Manager does not require any coding and is generally geared towards a technical end user. The limitations of Integration Manager include speed (it may take considerable time to import large sets of data) and a limited number of master data and transaction types that are available for importing.
Any new license of Dynamics GP sold currently includes a 240-day conversions license for Integration Manager. For situations where Dynamics GP was purchased previously and you are either past that initial 240 days after purchase or are performing a reimplementation, Integration Manager can be purchased separately. With the new Perpetual Licensing for Microsoft Dynamics GP 2013 it is part of the Customization Pack. Detailed instructions for using Integration Manager to import data into Dynamics GP are provided in Chapter 9, Populating Initial Data.
Other options available for importing data into Dynamics GP are:
There is no reason that a company cannot mix and match importing tools as needed. You may find that Integration Manager is the best tool for the initial implementation and some ongoing imports. For future projects, you may decide to add eConnect as a more automated import tool.