Day 11 (1 Nov 2022)

Today I continue my CAATs practice by doing the aging test practice. Before I start, what I heard from my senior, for the aging test, it can be difficult if in case the company is following exactly the date of the month like January is 31 days while February is 28 or 29 days, it depends on the company, some might standardize to have 30 days for all the months, why it is important to note on this matter because different ways company using, we might need to modify our formula to comply with company’s practice, as at the end we need to compare our calculation with the one company prepared, the aging summary, so if we are using a different way in calculating the date in month with the company, then we will end up having a lot of variances.

I start the practice by first going through the raw data and starting to plan what should I do. Client’s given raw data include an aging summary that includes customer code, customer name, and the total owing balance, then there are fields being labeled with the months and each field contains the amount the customer owns for that particular month, this is the part at the end we need to compare with our own calculation.

The next raw material is the detail outstanding invoices, this includes all the individual outstanding invoices together with its details, we will need to use these invoices to re-calculate the owing amount each month, and then compare it with the client’s aging summary.

Before that, because the outstanding invoice list given by the client is messy and is not in an easily readable style, so we need to conduct a data cleaning process on it in order to let it become more organized. The filter function is used, we can click the ‘ctrl’,’ shift’, and ‘L’ buttons to activate it, it helps us to filter out all the blank cells, and after that, the data show the total outstanding invoice it has, after the data is cleaned, I move it to an excel file called ‘formula to check to age’, that is prepared by BDO, in this excel file we can just copy all the outstanding invoice details that we have cleaned just now, then under the ‘financial year end’ column we out the company’s financial year-end, it will then classify all the outstanding invoice to how long is the duration it is owned, it can be less than 30 days, between one and two months, etc. After that, we will need to summarize it, and here will use the pivot table function, to sum up, all the individual outstanding invoices, we will copy the result to the report and also the client is given an aging summary, the excel formula will then compare all the columns and flag out any variances.

---

Main things that have learned

• Complete CAATs aging test practice

• Get some advice and tips from seniors on doing the aging test

---

Comment/idea/opinion

Like what my senior told me, for the aging test, sometimes it can be complicated if the company is using different ways to calculate the dates in a month, I am looking forward to accepting this challenge and am willing to learn more about it.