KNOWLEDGE CENTER

Data analytics for

DUMMIES

KNOWLEDGE CENTER

Data analytics for

DUMMIES


By Claire Worledge

Data analytics is only for IT people right?
Wrong!

Recently I was working for a CFO of a drinks company who couldn’t master anything to do with data or Excel. Problem was, all of his figures were produced in Excel by his staff (who didn’t speak English- or at least pretended not to). Obviously everyone was cheating. But he had to sign off the accounts, or face fines… but if he signed off the wrong accounts, he could face – prison?

But you might be thinking that you cannot possibly get into data analytics now because you don’t have time. I get you. I’ve been living in Vietnam for five years and, although, it would be much better experience if I spoke Vietnamese, I don’t. So what do you do? Well, you get dashboards. Ok, ok, but consulting companies are always trying to sell you dashboards for lots of money, and since you don’t know much about data analytics, you aren’t sure who is ripping you off more.

So, a little time spent understanding the basics can help you to choose the right vendors, understand the solutions that are necessary and be aware of the risks that you are facing every day.

Let me let you into a little secret...

Most top management out there, over 40, pushing 50, are not really aware about what data analytics means. 

A while back, I was trying to persuade the head of internal audit from a leading retail group about data analytics and data visualization. 

But there was a disconnect. 

Despite the fact that we had successfully completed another of his projects for him in the past, he could not understand why he would need data analytics when he already has detailed supplier balances that he can review from SAP. 
Furthermore, he was having a hard time to trying to convince his audit managers why they should change from sampling to data analytics.

There is a lot of hype around data analytics, but the truth is, 60% of the internal auditors out there still have not grasped the reason why they should be done, or how they can provide benefit.

But you don't need to worry. You are not alone. 

Even in the Big 4 audit firm where I used to work ten years’ ago, we hit a road-block. The partners in the audit firm just didn’t want to let us do more data analytics. Maybe that’s natural resistance to change. Maybe it's also because they are not aware of all of the benefits that data analytics brings.

This article is aimed at helping you to understand how data analytics does actually help you in many ways to get your work done quicker and in a more interesting and impactful way than ever before. In reality, data analytics changes everything.

Let me let you into a little secret...

Most top management out there, over 40, pushing 50, are not really aware about what data analytics means. 

A while back, I was trying to persuade the head of internal audit from a leading retail group about data analytics and data visualization. 

But there was a disconnect. 

Despite the fact that we had successfully completed another of his projects for him in the past, he could not understand why he would need data analytics when he already has detailed supplier balances that he can review from SAP. 
Furthermore, he was having a hard time to trying to convince his audit managers why they should change from sampling to data analytics.

There is a lot of hype around data analytics, but the truth is, 60% of the internal auditors out there still have not grasped the reason why they should be done, or how they can provide benefit.

But you don't need to worry. You are not alone. 

Even in the Big 4 audit firm where I used to work ten years’ ago, we hit a road-block. The partners in the audit firm just didn’t want to let us do more data analytics. Maybe that’s natural resistance to change. Maybe it's also because they are not aware of all of the benefits that data analytics brings.

This article is aimed at helping you to understand how data analytics does actually help you in many ways to get your work done quicker and in a more interesting and impactful way than ever before. In reality, data analytics changes everything.

You may be thinking, “I’ve heard this before”...

... consulting companies are always trying to sell me data analytics software. 

And you are right. Those huge consulting companies will try and sell you data analytics services and software and unbelievable prices, despite the fact that they have already done the same thing, hundreds of times before. They would rather copy-paste the information to you, or, despite the fact that they don’t actually have the skills available, they will outsource it to India, but still give you a very high price tag, and an extremely long delivery time. 

If anyone is trying to sell you data analytics for SAP, and they reckon that the time delay for delivery is over one month, then you should be concerned. 

So, is that because data analytics is easy? Not really. 

But it is because data analytics has actually been around for a long time, and like in our company, since we have been making data analytics dashboards for SAP for over 10 years, we already have hundreds of them ready to deploy and we have already optimized the configuration process.

But, you say, “if I get some pre-designed dashboards, that is not going to be exactly what I want”. “Its not going to be relevant to our company”. This is true. You are totally right. This is why it is also great if you can get to a stage where you can understand the data analytics process and then be able to communicate with those making your dashboards so that they can tweak them in the right direction.

You may be thinking, “I’ve heard this before”...

... consulting companies are always trying to sell me data analytics software. 

And you are right. Those huge consulting companies will try and sell you data analytics services and software and unbelievable prices, despite the fact that they have already done the same thing, hundreds of times before. They would rather copy-paste the information to you, or, despite the fact that they don’t actually have the skills available, they will outsource it to India, but still give you a very high price tag, and an extremely long delivery time. 

If anyone is trying to sell you data analytics for SAP, and they reckon that the time delay for delivery is over one month, then you should be concerned. 

So, is that because data analytics is easy? Not really. 

But it is because data analytics has actually been around for a long time, and like in our company, since we have been making data analytics dashboards for SAP for over 10 years, we already have hundreds of them ready to deploy and we have already optimized the configuration process.

But, you say, “if I get some pre-designed dashboards, that is not going to be exactly what I want”. “Its not going to be relevant to our company”. This is true. You are totally right. This is why it is also great if you can get to a stage where you can understand the data analytics process and then be able to communicate with those making your dashboards so that they can tweak them in the right direction.

So, what do you need to know, if you are new to data analytics and you feel like a “Data Analytics Dummy”? 

 Here are some pointers:

 1. The data that we are analysing, usually comes from the ERP system, that’s the “Enterprise Resource Planning” system. This system is      typically SAP (but sometimes companies give it an internal name, such as “Symphony”.

 2. End-users will enter transactions (such as purchase requests, purchase orders, logs of goods receipts, invoices, payments, etc), into the SAP system.

 3. When an end-user enters a transaction, this transaction will get input into a database.

 4. A database, is a collection of tables. A table, is basically a list. Like an Excel list. For example, in your SAP system, you have a list of purchase orders, a list of goods receipts, a list of invoices, a list of payments.

 5. In order to make sure that your SAP system works quickly and efficiently the data will be organized in the tables in a very clever, way, known as a “Relational Database”. A relational database has key aspects:

  • Each table has keys that help it to link to other tables, for easy lookup of information. For example, if you have a list of purchase orders in one table, and a list of supplier information in another table, there will be a key in each of those tables (in this case the supplier number), that links the two together.
  • Normally each table will have one key that is known as the “primary key”. This key is usually unique, within the table. Meaning that it only occurs once. For example, for the central list of suppliers in SAP, this unique key is the supplier number.
  • Some tables’ primary key is actually more than one piece of information: for example, the primary key for purchase orders is the purchase order number and the purchase order line item, used together. That’s two pieces of information, or two columns in the table, that make up the unique primary key.
  • ​In order to save disk-space, the tables will also be organized in such as way, so as to minimize the repetition of information. Typically, this means that they will be organized into “header” information and “detailed” information. For example, the list of purchase orders, is not in one table, but in two. The header purchase order table, will contain information, such as “date”, “user that entered the purchase order”. This table’s primary key will only be the purchase order number, because this table will not contain any line-item detail. The second table for purchase orders is the purchase order line items. This table will contain the amount per line item, the material number for each line item, etc. The primary key of the second table will be two columns joined together: the purchase order number and the purchase order line item.
 7. The information in your business is grouped into different modules. For example, the purchase orders, goods receipts information in SAP, is organised into the MM module (or Materials Management) module, whereas the sales orders and deliveries information is grouped together into the SD module or (Sales and Distribution) module.

 8. In SAP, as with any ERP system, the data entered in the different modules, will flow through to the final module, which is the FI module (or the Finance module). This module is like the keystone of your entire SAP system. It is from the FI module that you will print out financial reports, such as the Balance Sheet, the P&L, etc. The FI module is so important, because we can visualize the flow from all the different modules into the FI module, in order to see where any gaps occur along the way. For example, if some enters a purchase order, this purchase order will flow through to a goods receipt, and this goods receipt will flow through to an invoice and this invoice will flow through to a payment. And all of those values should match each other (unless your company allows for a threshold difference).

 9. So, any data analyst will always tell you, or should always tell you, that they have started off their project, by comparing the total values of the data, with that on the financial reports for your company.

 10. But hey, let’s not stop there. What is the real key point of validation? What really gives us 100% confidence that all of our transactions are flowing correctly, that no money has gone astray along the way, that the financial reports are correct? Well, as a CFO once explained to me about 12 years ago, the only key way, to be 100% sure that the data in your company is correct, is to compare it to the bank statement. That’s right, the bank statement (which is at the bank), not in SAP, is the best way to check if your financial statements are correct. The first thing to do, is a line-by-line comparison between your bank statement and your General Ledger.
 
 11. What is a General Ledger? I hear you say. Let’s look at this diagram to help us:

In the diagram, you can see that a sales document on the left, will flow through to the accounting document on the right. The accounting document gets put into a log, which is also known as the “General Ledger”. 

The “General Ledger” is kinda like the log of everything, including the log of cash, that goes in and out of the bank. When someone makes a supplier payment, it is logged in the general ledger (or at least should be!). When a customer pays something, it is logged in the general ledger (or again, should be!).
 12. But what do we mean by line-by-line. Well this brings us back full circle to the beginning. I’m still discussing with that head of internal audit in one of the world’s largest retail groups. And he still is not understanding why he needs data analytics. Ok, so why not? 

Well, because his managers are using detailed reports, that show the total balances day-by-day for their suppliers. Ok. But, I say to him, within those totals, what if there is a transaction that is hiding. For example, imagine that you owe your supplier, that happens to be one of the world’s leading distributors of cosmetics, 10 million USD. Ok, its normal, you always owe them about that much, because they are always delivery to you, and you are always paying them after 60 days. Everything is fine from that perspective. 

But little did you know, that within that outstanding balance of 10 million, lurks a year-end-rebate transaction. It was entered three years ago. IT means that that friendly supplier, should pay you back 1 million USD according to the contract that your purchasing team dutifully negotiated for you. But you don’t notice it. Because it is hidden within the 10 million. Its like this. Actually, you owe them 11 million, and they owe your 1 million. So the balance is 10 million. So your auditors think, well, we owe them 10 million, which is the same as last month, so everything is good, end of story. Unfortunately for your company, you don’t get to reclaim your 1 million back from them.

And that is the power of data analytics. Data analytics can help you to find the fun stuff. To isolate those areas of huge cost saving. That go way beyond the amount that you should be spending on creating those analytics in the first place.

By Claire Worledge

Claire spent 10 years working for Deloitte, managing the data analytics team. In 2010, she created Aufinia. Aufinia has worked for 50 different companies that use SAP over the years. Today, a lot of their work is for MNCs using SAP who are based in Europe, APAC and the US. Aufinia also works for some major retail companies based in China. 

Claire is a Certified Information Systems Auditor (CISA), Certified Fraud Examiner (CFE) and Certified ACL Data Analyst (ACDA). Aufinia is a global OEM partner with QLIK and also a Galvanize reseller partner for Vietnam and China. Claire has a Masters in IT from University College London, as well as a Bachelors in Biochemistry. 

So, what do you need to know, if you are new to data analytics and you feel like a “Data Analytics Dummy”? 

 Here are some pointers:

 1. The data that we are analysing, usually comes from the ERP system, that’s the “Enterprise Resource Planning” system. This system is      typically SAP (but sometimes companies give it an internal name, such as “Symphony”.

 2. End-users will enter transactions (such as purchase requests, purchase orders, logs of goods receipts, invoices, payments, etc), into the SAP system.

 3. When an end-user enters a transaction, this transaction will get input into a database.

 4. A database, is a collection of tables. A table, is basically a list. Like an Excel list. For example, in your SAP system, you have a list of purchase orders, a list of goods receipts, a list of invoices, a list of payments.

 5. In order to make sure that your SAP system works quickly and efficiently the data will be organized in the tables in a very clever, way, known as a “Relational Database”. A relational database has key aspects:

  • Each table has keys that help it to link to other tables, for easy lookup of information. For example, if you have a list of purchase orders in one table, and a list of supplier information in another table, there will be a key in each of those tables (in this case the supplier number), that links the two together.
  • Normally each table will have one key that is known as the “primary key”. This key is usually unique, within the table. Meaning that it only occurs once. For example, for the central list of suppliers in SAP, this unique key is the supplier number.
  • Some tables’ primary key is actually more than one piece of information: for example, the primary key for purchase orders is the purchase order number and the purchase order line item, used together. That’s two pieces of information, or two columns in the table, that make up the unique primary key.
  • ​In order to save disk-space, the tables will also be organized in such as way, so as to minimize the repetition of information. Typically, this means that they will be organized into “header” information and “detailed” information. For example, the list of purchase orders, is not in one table, but in two. The header purchase order table, will contain information, such as “date”, “user that entered the purchase order”. This table’s primary key will only be the purchase order number, because this table will not contain any line-item detail. The second table for purchase orders is the purchase order line items. This table will contain the amount per line item, the material number for each line item, etc. The primary key of the second table will be two columns joined together: the purchase order number and the purchase order line item.
 7. The information in your business is grouped into different modules. For example, the purchase orders, goods receipts information in SAP, is organised into the MM module (or Materials Management) module, whereas the sales orders and deliveries information is grouped together into the SD module or (Sales and Distribution) module.

 8. In SAP, as with any ERP system, the data entered in the different modules, will flow through to the final module, which is the FI module (or the Finance module). This module is like the keystone of your entire SAP system. It is from the FI module that you will print out financial reports, such as the Balance Sheet, the P&L, etc. The FI module is so important, because we can visualize the flow from all the different modules into the FI module, in order to see where any gaps occur along the way. For example, if some enters a purchase order, this purchase order will flow through to a goods receipt, and this goods receipt will flow through to an invoice and this invoice will flow through to a payment. And all of those values should match each other (unless your company allows for a threshold difference).

 9. So, any data analyst will always tell you, or should always tell you, that they have started off their project, by comparing the total values of the data, with that on the financial reports for your company.

 10. But hey, let’s not stop there. What is the real key point of validation? What really gives us 100% confidence that all of our transactions are flowing correctly, that no money has gone astray along the way, that the financial reports are correct? Well, as a CFO once explained to me about 12 years ago, the only key way, to be 100% sure that the data in your company is correct, is to compare it to the bank statement. That’s right, the bank statement (which is at the bank), not in SAP, is the best way to check if your financial statements are correct. The first thing to do, is a line-by-line comparison between your bank statement and your General Ledger.
 
 11. What is a General Ledger? I hear you say. Let’s look at this diagram to help us:

In the diagram, you can see that a sales document on the left, will flow through to the accounting document on the right. The accounting document gets put into a log, which is also known as the “General Ledger”. 

The “General Ledger” is kinda like the log of everything, including the log of cash, that goes in and out of the bank. When someone makes a supplier payment, it is logged in the general ledger (or at least should be!). When a customer pays something, it is logged in the general ledger (or again, should be!).
 12. But what do we mean by line-by-line. Well this brings us back full circle to the beginning. I’m still discussing with that head of internal audit in one of the world’s largest retail groups. And he still is not understanding why he needs data analytics. Ok, so why not? 

Well, because his managers are using detailed reports, that show the total balances day-by-day for their suppliers. Ok. But, I say to him, within those totals, what if there is a transaction that is hiding. For example, imagine that you owe your supplier, that happens to be one of the world’s leading distributors of cosmetics, 10 million USD. Ok, its normal, you always owe them about that much, because they are always delivery to you, and you are always paying them after 60 days. Everything is fine from that perspective. 

But little did you know, that within that outstanding balance of 10 million, lurks a year-end-rebate transaction. It was entered three years ago. IT means that that friendly supplier, should pay you back 1 million USD according to the contract that your purchasing team dutifully negotiated for you. But you don’t notice it. Because it is hidden within the 10 million. Its like this. Actually, you owe them 11 million, and they owe your 1 million. So the balance is 10 million. So your auditors think, well, we owe them 10 million, which is the same as last month, so everything is good, end of story. Unfortunately for your company, you don’t get to reclaim your 1 million back from them.

And that is the power of data analytics. Data analytics can help you to find the fun stuff. To isolate those areas of huge cost saving. That go way beyond the amount that you should be spending on creating those analytics in the first place.

Want to get instant access to a FREE eBook that outlines the 300 must-have data analytics? 

Click below to download our free eBook and we can guarantee it will help you become a recognised leader by starting the transformation of internal control and internal audit!

Want to get instant access to a FREE eBook that outlines the 300 must-have data analytics? 

Click below to download our free eBook and we can guarantee it will help you become a recognised leader by starting the transformation of internal control and internal audit!

Copyright © 2021 - Aufinia
For more support contact cjw@aufinia.com