Day 9 (28 Oct 2022)

What I did today is to continue my yesterday job, the data cleaning practice. Based on the original general ledger, there are fields that are of unnecessary, for the practice I am doing, the fields that are of unnecessary are posting key, document type, cost center, profit center, tax code and local currency, these fields are not important because we dont need them for the processing step so we can either keep it or delete it. The fields that are important are the account code, document number, document date, posting date and amount.

Second thing I need to take note is some general ledgers amount are only under one field, which means the debit amount and credit amount also under the same one field while some are two fields which one store the debit amount and another one store the credit amount. For my case, the amounts are stored under one field, so I need to use formula to separate it into two fields. After that we can double check to see whether to total debit amount is equal to the credit amount or not due to the double entry principle.

The third step is to modify the field type, we need to ensure the fields type are same for all the tabs, and also it should be tally with the format IDEA can process, the type for the field of date like posting date and document date should be in date format, the amount should be in numeric, then the others can be in general form.

Next, because there are a large number of transaction, so client usually will have 12 different excel files for the whole 12 months, so what we need to do is we need to combine the 12 files into one file if can, we can do it through excel, however excel file does have its limit on how many row it can store, so if the row exceed excel’s limit, another way to do is to use the IDEA software to merge it into one. Thao have demonstrated it to me and she mentioned to me that in future when I am given the task, I will be handling it by my ownself. Generally, I spent the whole day practicing the data cleaning for general ledgers.

---

Main things that have learnt

• Know which fields of the general ledgers are necesarry which one is not

• Utilize excel shortcut to separate the amount into two fields (one with debit amount, another one with credit amount)

• Learn how to merge all the excel files into one excel file

---

Comment/idea/opinion

What I experienced today is data cleaning is a time consuming job, I would say it is not a difficult job considering you are skillful in using all the excel shortcut, if one is skillful and experienced, I believe he can complete the data cleaning within a short time and I am looking forward to be skillful at it.