Subscribe to this feed

Navigation

Recent Posts

Archive

Popular Tags

Click tag to retrieve blogs
  1. Excel (9)
  2. Oracle (6)
  3. batch files (5)
  4. csv (5)
  5. Apache (3)
  6. DOS (3)
  7. SQL*Plus (3)
  8. Macro (3)
  9. bat (3)
  10. PHP (3)
  11. SQLPLus (3)
  12. VBA (3)
  13. Microsoft (2)
  14. Windows (2)
  15. new systems (2)
  16. SQL (2)
  17. data migration (2)
  18. System Implementation (2)
  19. MySQL (2)
  20. Project plans (1)
  21. .bat (1)
  22. CSS (1)
  23. IF (1)
  24. financial data (1)
  25. Systems (1)

Migrating Financial Data to New System

Tuesday 16 Jan, 2007 - 17:22pm | 0 comments |

Migrating Financial Data

This blog is also published on new IT system and is a blog on the subject of implementing new systems, in particular migrating financial data. I have seen countless examples of this being done incorrectly, in such a way that weakens the internal control environment and means the accountants have to burn the midnight oil. This is an approach that works, and which I use time and again.

When you're migrating your financial data to your new system you will want to accomplish a number of key objectives, no less of which include

  1. A clean cut off
  2. A clear audit trail from one system to the next
  3. No loss of control over balance sheet control accounts
  4. No impediments to collecting debt, i.e. transactions on statements are still understood by customers

Open and Closed Transactions

To achieve this we need to distinguish between the open and closed transactions in our data. The open transactions comprise unpaid invoices, unallocated payments and uncleared receipts and payments, typically represented by our aged debtors, aged creditors, and the uncleared items on the cashbook.

Closed items can be mapped into the new system as balances rather than taking in detailed transactions, for example, a balance on a GL account. We will treat our control accounts differently.

Importing

To retain control over our control accounts we should import the open items  as transactions rather than balances.  We should be able to extract from our legacy system the open transactions, such as a detailed aged debt in file format. We will then map these transactions, ensuring we follow the field conventions in the new tables, for example the transaction type may be known as "SI" in the new system and something else in the legacy system.  Remember to also set the VAT code for all transactions to "out of scope" as we do not want to create any VAT transactions in our import file.

The Import File

Our import file may have three transaction types; sales invoices, sales credit notes and unallocated receipts. We might have to import these as three separate files. To retain accurate ageing we might have further imports to do depending on how the period system works. By doing so the transactions in your statements will continue to be a key tool in the cash collection process. It is likely, anyway, that we will have to split the file to spread the data transfer load and speed up the import process if errors occur, so creating separate file for each period, if we have to, is not an added overhead. 

If you have debt in the last financial year, ensure you understand whether this can be imported in or whether you might have to amend the transaction date and import into the current financial year (using another field to keep the actual transaction date). If you have only a handful of transactions on a file, you might be better off to just key them in.

Check Totals

Before you begin to import anything ensure your list of transactions and the sum of your import files comes back to the debtors balance. There might be some odd transaction type you've overlooked in the mapping of your data.  When importing use full rollback, i.e. if a validation error occurs no transactions will be imported. Fix the error and import the whole file again. Make sure you do this on the test system first.

Double Entry

If we are importing open transactions as well as the balances on our TB we need to ensure we don't double count. Lets explore how we do this concentrating on debtors, creditors and the cashbook. We'll use the following abbreviated trial balance to work through the numbers.

xyz Ltd
Trial Balance 31/08/06
Dr £'000 Cr £'000
Fixed Assets 150
Trade Debtors 500
Other Debtors 40
Bank 75
Trade Creditors 420
Other Creditors 140
Reserves 205
Totals 765 765

 

Method

Usually at the end of you chart of accounts you will have a range of codes you can use as suspense accounts. We will use one of these to control our migration. We will use it to post our transactions and to also post the Trial Balance. If it doesn't equal zero at the end of the exercise then the migration contains an error.

Bank

As we want to retain the integrity of our bank reconciliation on our new system we need to disaggregate our TB bank balance. The balance is comprised of 

xyz Ltd
Cash Book 31/08/06
Dr £'000 Cr £'000
Bank Statement 95
Uncleared Payments 28
Uncleared Receipts 8
Sub-total 103 28
TB Bank Balance 75

We will import or key in the uncleared payments and receipts. Our two entries will be

xyz Ltd
Migration Journal #1 
Dr £'000 Cr £'000
Bank 8
Migration Suspense 8
being uncleared receipts
Migration Suspense 28
Bank 28
being uncleared payments
Totals 36 36

This leaves us with a credit balance on our Bank so we have one more journal to post. This is to take in the reconciled balance from our legacy system. If we post this balance as a bank transaction rather than a pure GL journal it means we can open up our bank reconciliation program in our new system and reconcile this single entry. Our bank account, after we post the following entry, is then clean

xyz Ltd
Migration Journal #2 
Dr £'000 Cr £'000
Bank 95
Migration Suspense 95
being reconciled bank balance
Totals 95 95

At this point our Trial Balance looks like this:

xyz Ltd
Trial Balance 31/08/06 [new system]
Dr £'000 Cr £'000
Fixed Assets
Trade Debtors
Other Debtors
Bank 75
Trade Creditors
Other Creditors
Reserves
Migration Suspense 75
Totals 75 75

Debtors

Excluding the effect of provisions which we'll ignore in this example, our Debtors balance consists of all open transactions, i.e. unpaid invoices, unallocated credit notes and payments. Leading up to the go live date ensure your credit control dept. are proactively allocating cash and cleaning up the debtors. In any case you are still likely to end up with a debtors balance consisting of, for example

xyz Ltd
Debtors 31/08/06
Dr £'000 Cr £'000
Open Invoices 515
Unallocated Credits 3
Unallocated Receipts 17
Part Paid Items etc. 5
Sub-total 520

20

TB Debtors Balance 500

We will key in or import these items. Our journal entries being:

xyz Ltd
Migration Journal #3 
Dr £'000 Cr £'000
Trade Debtors 515
Migration Suspense 515
being unpaid debtor invoices
Migration Suspense 3
Trade Debtors 3
being unallocated credit notes
Migration Suspense 17
Trade Debtors 17
being unallocated receipts
Trade Debtors 5
Migration Suspense 5
being unpaid debtor invoices
Totals 540 540

At this point our Trial Balance looks like this

xyz Ltd
Trial Balance 31/08/06 [new system]
Dr £'000 Cr £'000
Fixed Assets
Trade Debtors 500
Other Debtors
Bank 75
Trade Creditors
Other Creditors
Reserves
Migration Suspense 575
Totals 575 575

Creditors

The exercise on creditors is simply the reverse of the process for Trade Debtors. There are those who argue to pay off all creditors before the go live date. This is argued on the basis of reducing the amount to import and, if there were any problems with the payment program in the new system, we would have bought ourselves some time. I'm not an advocate of this method, the main reason being the adverse effect on cash flow but also because I don't really see any advantage in doing it. The creditors should be cleaned in the same way as the debtors leading up to the go live date, and imported just the same.  Our disaggregated creditors balance is:

xyz Ltd
Creditors 31/08/06
Dr £'000 Cr £'000
Open Invoices 425
Unallocated Credits 3
Unallocated Payments 2
Part Paid Items etc.
Sub-total 5 425
TB Creditors Balance 420

The journal entries will be:

xyz Ltd
Migration Journal #4
Dr £'000 Cr £'000
Migration Suspense 425
Trade Creditors 425
being unpaid supplier invoices
Trade Creditors 3
Migration Suspense 3
being unallocated credit notes
Trade Creditors 2
Migration Suspense 2
being unallocated payments
Totals 430 430

Our trial balance now looks like this

xyz Ltd
Trial Balance 31/08/06 [new system]
Dr £'000 Cr £'000
Fixed Assets
Trade Debtors 500
Other Debtors
Bank 75
Trade Creditors 420
Other Creditors
Reserves
Migration Suspense 155
Totals 575 575

The Trial Balance

So now our control accounts are complete we can journal in the rest of the balance sheet. As we have already taken in the debtors, creditors and bank balances we do not need to journal these in. Our remaining journal will be as follows.

xyz Ltd
Migration Journal #5
Dr £'000 Cr £'000
Fixed Assets 150
Migration Suspense 150
being opening TB Fixed Assets
Other Debtors 40
Migration Suspense 40
being opening TB other debtors
Migration Suspense 140
Other Creditors 140
being opening TB other creditors
Migration Suspense 205
Reserves 205
being opening TB Reserves balance
Totals 535 535

Our Trial Balance now looks like this:

xyz Ltd
Trial Balance 31/08/06 [new system]
Dr £'000 Cr £'000
Fixed Assets 150
Trade Debtors 500
Other Debtors 40
Bank 75
Trade Creditors 420
Other Creditors 140
Reserves 205
Migration Suspense
Totals 765 765

Conclusion

Our new trial balance agrees to our old trial balance. We've retained the audit trail of our file imports and journal entries and printed a hard copy of our new TB. Users on the legacy system should now only have read only access to it. Our control accounts and ageing reports agree to the legacy system, and because we've taken the bank balance in as a file import and as a bank transaction our bank reconciliation program works from day one. Many implementations treat the bank balance as though it were an ordinary GL account. Post implementation this can lead to a drain on qualified resource and a weakening of the internal control system.

Tags:financial data |data migration |new systems |system implementation

Comment
 | Link | Back to top | del.icio.us digg it furl reddit

Converting data for a New System

Tuesday 16 Jan, 2007 - 16:19pm | 0 comments |

Converting Data

This blog is also published on new IT system and is a blog on the subject of implementing new systems and preparing the underlying data using excel. There are many tools you can use to help in data migration but excel is useful to illustrate the types of issues you may face.

Migrating data between applications is not a clean mapping exercise, field sizes can be different, field types can differ, the format of data extracted will more than likely need to be changed before being loaded into a new system. Although there are many conversion tools we will look at a few examples of data which we're going to manipulate in excel. Even if you use another tool to clean data an understanding of the types of problems you may encounter will help you debug should a file you are importing into a new system fail to load correctly.

Date Formats

The date format could be any combination, i.e.

  • YYYYMMDD
  • DDMMYYYY
  • MMDDYYYY
  • etc...

Let's look at an example where the format of the date is DMMYYYY. The date is a field in our sales invoice file and we need to convert it into the format DD/MM/YYYY before we can import it into our new system.

The first thing we need to do to the string (a linear sequence of characters, in this case DMMYYYY) is to convert the field into DDMMYYYY, i.e. make all the fields consistent so that the 1st of January would appear 01012007 rather than 1012007 as it currently does. Move your mouse over cell B1 in the example below to reveal the formula.

Make Date Field Consistent
A B C
1 1012007 01012007
2
3

In this example we use the text formula to convert the date field into a field with eight characters.

=(TEXT(A1,"00000000"))
....converts the field to eight character length

Once we have the field in a consistent format we can then more easily manipulate the contents. We could now split the string into days, months and years. The days is a simple left formula. The years are a right formula and the month is a combination of both. As usual moving your mouse over the cells below will show the formula

Extract Date Elements
A B C D E F
1 1012007 01012007 01 01 2007
2
3

Here we use the LEFT and RIGHT formulas to extract the components of the date.

=LEFT(B1,2)
....extracts the first the day from the string

=LEFT(RIGHT(B1,6),2)
....extracts the first the month from the string

=RIGHT(B1,4)
....extracts the first the year from the string

Finally we can use the DATE formula to convert to the format we need to import our transaction file

Convert to Date Format
A B C D E F
1 1012007 01012007 01 01 2007 01/01/2007
2
3

The DATE fomula works as DATE(year, month, day)

=DATE(E1,D1,C1)
....converts to date format dd/mm/yyyy

Although we have taken several steps to achieve our result we could have equally taken just one, though we are left with an ungainly looking formula

=DATE(RIGHT(TEXT(A1,"00000000"),4),LEFT(RIGHT(TEXT(A1,"00000000"),6,2),LEFT(TEXT(A1,"00000000"),2))
....converts our original sring of dmmyyyy to date format dd/mm/yyyy

Changing Dates

We may need to change the dates in an export file as the transactions are too old to import into the current database. This may arise where we have some open invoices going back to previous financial years. There might be too much of an overhead associated with opening and closing financial years in the new system, in order to import just a few transactions; or the new system hasn't been set up to accept transactions beyond a certain date

To change the date we need to first see if it meets our condition, if it does we change the date. If it doesn't the date stays the same. Let's use an example where we have an invoice dated 31/12/2004. Our accounting year ends in December. Our new system is set up with one back year, i.e. 2005 is listed as year one. In order to post the transaction we would therefore need to modify the date (making sure to keep the old date, for reference, in a different field in the new database). Hover your mouse over cell B2 to see the formula.

Modify a Date
A B C
1 31/12/2004 DATE(2005,01,01),A1,DATE(2005,01,01))>01/01/2005
2
3

The formula is a conditional argument. If the date is earlier that 01/01/2005 change the date to 01/01/2005, otherwise keep the date the same.

=IF(A1>DATE(2005,01,01),A1,DATE(2005,01,01))
....changes the date if the date is earlier than the condition

Fixed Field Lengths

When you export fixed length fields you can end up with whitespace forming part of the string. This may occur on, for instance, business partner codes from your legacy system. The field may have been defined with, for example, a fixed length of 7 characters but the code used was only 6. You will need to get rid of the whitespace to reduce the characters to six. To do this we will use the TRIM formula.

Trim Whitespace
A B C
1 ACCE01 ACCE01
2
3

=TRIM(A1)
....removes whitespace from the end of a fixed length field

Creating a New Coding System

For one reason or another you may want to change your coding system. You may have outgrown your old system or you may just want something simpler. Lets assume you're going to change your supplier coding system from four alpha plus two numeric to one alpha plus three numeric. Given that we have our supplier database in excel how can we automatically generate the codes?

To do so we're going to use a combination of the formula we've used above. Obtaining the one alpha character is a simple exercise. We use the LEFT formula on the Supplier Name.

To automatically generate the numeric value of the code we need to determine how many of a given letter have already been used, so we can then allocate a new number. To do this will use a derivative of the IF formula. This is the COUNTIF formula. Hover your mouse over the cells in column C to see how we use the formula to obtain our result. Notice how in column c, using the COUNTIF formula we freeze the cell in the first part of the argument i.e. $B$1 but in the second part we only freeze the column, not the row, i.e. $B2

Create New Code
A B C D
1 Alpha Ltd A 001 A001
2 Alpha 2 Ltd A 002 A002
3 Alpha 3 Ltd A 003 A003
4 Beta Ltd B 001 B001

The formula in column C counts the number of records, in the dataset above, which start with the letter in the adjacent row cell. The formula in column D simply adds the two strings together using the ampersand &.

=TEXT(COUNTIF($B$1:$B2,B2),"000")
....counts the number of fields begining with the letter in cell B2 in the data range $B$1 to $B2. The result is returned as a text field with a length of three characters

Field Lengths

The character length of fields in your legacy system might differ to the corresponding field length in the new system. If you try to import a file and the text in the field is greater than the field length in the new database, the file import will fail. Typically, this might arise on description fields, such as your product descriptions.

To identify which records we need to amend, before importing a file, we can use the LEN formula.

Determine Length of Field
A B C D
1 Product x Description 21 20),"Fix this","")'>Fix this
2
3

In cell C1 we've added to the formula to highlight only those records which meet a condition. Our condition checks if the length of the field is greater than twenty characters. If it is "Fix this" is displayed in the cell. If it isn't, nothing in displayed.

=LEN(A1)
....returns the number of character in cell A1

=IF(LEN(A1)>20,"Fix this","")
....checks to see if the number of characters in cell A1 is greater than or less than twenty. If its greater, text is displayed.


Tags:Excel |data conversion |data migration |new systems |System Implementation

Comment
 | Link | Back to top | del.icio.us digg it furl reddit

© Eriginal Ltd 2011, all rights reserved