Albert School - Data Analytics for Business Audit #4 v2.pdf

Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché 1st of December Introduction to management control 2h00 15th of December Deep dive in financial reporting 2h00 Data Analytics for audit and compliance 2h00 Python for financial analysis 3h00 Setting up a performance manag...

Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché 1st of December Introduction to management control 2h00 15th of December Deep dive in financial reporting 2h00 Data Analytics for audit and compliance 2h00 Python for financial analysis 3h00 Setting up a performance management dashboard in Power BI 6h00 19th of January 19th and 26th of January 2nd of February and 8th of March Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché Performance Management Dashboard A performance management dashboard is a visual and interactive tool used to track, analyze and display key performance indicators (KPIs) relating to business management, particularly in the field of management control. It enables managers to measure and monitor the company's financial and operational performance in real time. Example: Instant access to information Ease of use ▪ A cash flow dashboard can help track incoming and outgoing cash flows, identify trends and anticipate future cash requirements. ▪ A sales performance dashboard can help identify the most profitable products, sales trends, and growth opportunities. ▪ A budget dashboard can help monitor expenses, identify budget overruns and make decisions to improve financial efficiency. Real-time tracking In-depth analysis ! Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché Overly detailed analyses can slow down the decision-making process 4 Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché Steps for building a dashboard F&T designSign off Tests signoff Dev Functional design Technical Tests Final deployment Technical design Define functional and technical user stories. Agile approach Deploy Hypercare Support Functional tests Transversal steps: 1. 2. Training Governance definition Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché 6 Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché Design relevant data models (reminder) Multi-dimensional modeling : Star Diagram Star modeling is a technique used in database design for Business Intelligence and Data Warehousing. It is a method that structures data in a way that facilitates analysis and reporting. Products Dim Customers Why is this model used? Facts Simplicity Performance Date Business indicators (sales, quantities sold, etc.) Dim Flexibility Optimization for analysis Location Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché 8 Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché Cardinalities A few examples One-to-one (1:1): In this relationship, the two linked tables contain only unique values. Example: a country has only one capital, and a capital belongs to only one country. Source : Stack Overflow Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché Cardinalities A few examples One to many (1:*) or many to one (*:1): This is the most common relationship. This cardinality means that one table contains only single values, and the other table contains multiple values. Both cardinalities are based on the same principle, but the meaning is important. Brand Product ID Product Product Name Price ID Brand * Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché 1 ID Brand Brand Name Variety Example: a brand has several products, but a product belongs to only one brand. Cardinalities A few examples Many-to-many (*:*): This relationship means that a value can be present in several occurrences on both sides of the relationship. This type of cardinality is trickier to manage. GL Mapping Compte Compte PCG Agrégat financier * Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché * Compte PCG N Ecriture Solde Example: Duplicate accounts in an accounting mapping Attention In practice, this relationship is (technically) correct. However, the result may not correspond to our expectations, leading to potentially erroneous values and aggregations. Whenever possible, avoid this type of relationship. In this example, we therefore need to remove the duplicates to arrive at a 1-tomany relationship. Leader on BI : Microsoft Power BI Power BI is designed for all business sectors and all areas of a company. Banking, insurance, construction, retail, SMEs, VSEs, multinationals, purchasing, sales, logistics - whatever the business, data is generated and can be modeled and analyzed in a dashboard. The choice of Power BI as a data visualization tool lies in the fact that it is : ▪ Market leader ▪ Easily integrated with Office suite tools ▪ An inexpensive license ▪ Regular updates and great potential for development Example of a dashboard: https://app.powerbi.com/view?r=eyJrIjoiNzE4MGUwYzgtMmI4My00ZTI4LTgxZjctOW M0OTIwNmIyOGM5IiwidCI6ImE2ZDBlNTBlLTQ1NjQtNGM4NS05NmIwLTY5NzIzNz c1YjEwNiIsImMiOjl9&pageName=ReportSection 14 Leader on BI : Microsoft Power BI Power BI consists of three main elements - Power BI Desktop, Power BI service and Power BI Mobile - a set of software services, applications and connectors that work together to transform your data into interactive information. Power BI Desktop, a Windows desktop application, enables comprehensive data analysis and reporting from multiple data sources. Power BI service, a Saas (Software as a Service) service supporting lightweight dashboard editing and cross-team collaboration. Power BI Mobile, a mobile app on iOS, Android and Windows 10, for viewing published dashboards. 15 Leader on BI : Microsoft Power BI With Power BI Desktop, you can : ▪ Connect to different data sources; ▪ Shape your data with queries that create powerful data models; ▪ Use data models to create visualizations and reports; ▪ Share your achievements so that your colleagues can exploit, enrich and share them in turn. 16 Leader on BI : Microsoft Power BI Steps in developing a dashboard with Power BI ETL Power Query Collect, clean, prepare Power BI Desktop Modeling and DAX Design vizualisation Power BI Service Publish Consult 17 DAX: Calculated measures and columns, syntax, etc. Data Analysis Expressions (DAX) is a library of operators and functions that can be combined to build formulas and expressions in Power BI. DAX can be used to create measures, calculated columns or calculated tables. Calculated columns To note A calculated column is a column added to an existing table defined by its DAX formula. The values in this column are calculated for each row in the table. A calculated column is attached to a table (it's a table column), whereas a measure is not directly attached to a table. Measures With DAX, you can bring your data to life in Power BI. A measure is a calculation that is performed according to the viewing context. Unlike a calculated column, a measure is calculated on the fly, not line by line. Introduction to DAX: Calculated measures and columns, syntax, etc. Express yourself in DAX: DAX uses a syntax similar to that of Excel functions, but with more power for data processing. Une formule DAX se décompose en plusieurs parties. 6 5 1 3 2 4 1: Column name 2: Equal operator (=) indicates the start of the formula 3: SUM function is called 4: Parentheses enclosing function parameters 5: Reference to "GL 2021 - 2023" table 6: Reference to "Balance" column To note Although DAX may seem familiar to Excel users, it offers far greater depth of analysis. Data types in DAX As with Power Query, it's important to know your data types. In DAX, we find the same types: Text (String) Number (Decimal, Whole number) Date/Time Boolean (True/False). To note Choosing the right type of data guarantees calculation accuracy and optimizes performance. When importing data, be sure to check and, if necessary, convert data types to ensure consistency. This is what we did in Power Query. Current DAX functions There are over 250 functions in DAX and, of course, they're not all used with the same frequency. In the following sections, I'll present the main functions you need to know about. Here are the key aggregation functions: Add, Average, Count with DAX SUM Objective: Evaluate the sum of a numerical column. Syntax : Measure = SUM ( table[column] ) column: The table column containing the numbers to be added. Example: Total Sales = SUM(Sales[Amount]) Calculates total sales. AVERAGE COUNT Objective: Evaluate the average (arithmetic mean) of all the numbers in a column. Purpose: Counts the number of rows in the table where the specified column has a non-empty value. Syntax : Syntax : Measure = AVERAGE ( table[column] ) Measure = COUNT ( table[column] ) column: The table column containing the numbers to be averaged. column: The table column containing the numbers to be counted. Example: Average Age = AVERAGE(Customers[Age]) - Gives the average age of customers. Please note Aggregation functions are crucial for creating high-level KPIs and metrics. Current DAX functions Key text functions: formatting strings with DAX Please note For all aspects of data cleansing and transformation, it's best to do it as early in the process as possible, i.e. in Power Query. But sometimes it's out of our hands. UPPER Purpose: Converts a string into uppercase letters. Syntax : Measure = UPPER ( table[column] ) column: The table column containing the text to be capitalized. Example: Name = UPPER(Customer[Name]) Converts customer name to uppercase. LEFT Purpose: Returns the specified number of characters from the beginning of a text string. Syntax : Measure = LEFT ( table[column], Number) column: The table column containing the text. Example: First Letter = LEFT(Customer[Name] , 1) - Returns the first letter of the Name. Same logic with RIGHT CONCATENATE or & Purpose: Combines two text strings into one. Syntax : Measure = CONCATENATE ( table[column1], table[column2] ) column: Table columns containing the texts to be combined. Example: Full Name = CONCATENATE(Customers[FirstNa me], Customers[LastName]) Merges first name and last name to obtain the full name. Current DAX functions Below are the key Date functions: mastering time with DAX YEAR DATEDIFF WEEKDAY (column) Purpose: Returns the year of a date as a four-digit integer. Target: Returns the number of units (unit specified in Interval) between the two dates entered. Purpose: Returns a number identifying the day of the week of a date. The number is between 1 and 7 or between 0 and 6, depending on the choice of return parameter. Syntax : Measure = YEAR ( Calendar[Date] ) Date: The Calendar column containing the date. Example: Sale Year = YEAR(Sales[Date]) Extracts the year of a sale date. Example: Months Since = DATEDIFF(Customers[ArrivalDate], TODAY(), MONTH) Calculates the number of months elapsed since a customer joined. Example: Day number = WEEKDAY(Calendar[Date] , 2) Returns 1 for Monday, and 7 for Sunday. If I change the 2 to 1, I'll get 1 for Sunday. Please note Time-based analyses are at the heart of many BI dashboards and reports. Work with dates to generate time-based analyses, such as trends or year-on-year comparisons. Advanced DAX functions: CALCULATE The functions presented in the previous section are the standard Power BI functions. In this new section, we take a closer look at DAX and present the advanced functions. CALCULATE Purpose: Modifies the calculation context of an expression. Syntax : Measure = CALCULATE(expression, [filter1], [filter2],...) expression: A DAX expression to be evaluated. filter(s): The filters to be applied for the calculation. Example: CALCULATE(SUM(Sales[Amount]), Sales[Country] = "France") Sum of sales for France. Please note CALCULATE is undoubtedly the most frequently used function in DAX. It is therefore very important to understand how it works. Bonus: ALL Let's take DAX functions one step further. ALL Purpose: Removes all filters from a table or column. Syntax : ALL(column_or_table) column: The column or table to be "scrolled". Example: CALCULATE(SUM(Sales[Amount]), ALL(Sales)) - Sum of total sales without any filter. Even if a filter is applied to the sales table, the measurement will ignore it. The REMOVEFILTERS function is similar and achieves the same result Focus on data visualization and best practices The right graphics and colors are essential for readability, so it's important to build your pages with a storytelling approach. To sum up, here are the 6 keys to a successful dashboard: Interaction Simplicity Less is more. Avoid overloading your graphics with too much information. Your goal is to make the information easy to understand. Where possible and relevant, offer interactive visualizations to let your audience explore the data. Narration Relevance Use your visualizations to tell a story. This will help your audience to understand and remember information. Choose visualizations adapted to the data you want to present. A bar chart to compare, a curve chart to show a trend, a pie chart to show a distribution. ! Legibility Make sure your graphics are clear. Use contrasting colors to distinguish between different data, and make sure the text is large enough to be read easily. Accuracy Make sure your data is accurate and upto-date. A visualization incorrect or obsolete can lead to bad decisions. Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché USE CASE - Act as a Data Analyst for Audit Steps to be implemented (Power Query): 1. Load CSV Files from FEC_source Case Study: Implementing a Performance Management Dashboard in Power BI 2. Load Plan_comptable_general file Background: The case involves a retail corporation, XYZ Inc., aiming to enhance its decision-making process. Facing challenges in tracking and managing performance metrics, the company seeks to establish an efficient performance management dashboard in Power BI. 4. Append the 2 csv files to create a new query “FactFinance” Objective: The main goal is to develop a dashboard that provides real-time insights into key performance indicators (KPIs), sales trends, and operational efficiency. This will enable stakeholders to make informed strategic decisions, optimize processes, and improve overall performance. 3. Add a column to indicate the type of the source 3. Load Prévisionnel file Clean and Prepare “FactFinance” 1. Create a new column called “Solde” 2. Using : Text.Start, Text.End, Text.Middle transfom dates Clean and Prepare “Prévisionnel” 1. Clean and prepare the dataset to have the dates as rows instead of columns 2. Prepare the dataset to append it with “FactFinance” Clean and Prepare “Mapping Customer” 1. Clean and prepare the datasets so we can easily create a relationship with FactFinance Transformation in “FactFinance” 1. Create new columns to have first 2 and 3 caracters of “CompteNum” 29 Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché USE CASE - Act as a Data Analyst for Audit Steps to be implemented (Power BI Desktop): 1. Create a calendar table (using CALENDAR DAX function) 2. Create measures: turnover, turnover LY, turnover YTD (based on SIG_2 column = 1 – Production de l’exercice) 3. Create measures: Net Profit, Net Profit LY, Net Profit YTD (based on SIG_7 column = 8 – Résultat de l’exercice) 4. Build visuals by Periods, Customer with a split Acutal and Forecast 30 Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché Albert Global Data School - Data Analytics for Business Audit - Antoine Bosché