Data Management Session 1 - KEDGE Business School

Data management 05/12/2024 N. KAFA – O. OMRI 1 Course structure and organisation 30h , learning by doing Data manipulation & visualization. Finding and analyzing data. Optimisation & scenario management. Exercises to do in classes using EXCEL Evaluation...

Data management 05/12/2024 N. KAFA – O. OMRI 1 Course structure and organisation 30h , learning by doing Data manipulation & visualization. Finding and analyzing data. Optimisation & scenario management. Exercises to do in classes using EXCEL Evaluation 05/12/2024 N. KAFA – O. OMRI 2 Introduction to the course The « Data management » course dedicated specifically to learning how to use MS EXCEL to use it in versatile projects. Data manipulation & visualization (clean data, charts , PivotTable…) Finding and analyzing data.( filtering, sorting,..) Optimisation & scenario management. ( What-if, Solver…) Learning By 30-hours doing 05/12/2024 N. KAFA – O. OMRI 3 Evaluation Data Management Understanding of the course Project to be submitted Application of our knowledge in EXCEL Evaluation Points Final Project (40%) (group). To be submitted on 20 points Learn the day of the session 10 Individual evaluation (60%) , session 10 20 points Total (100%) 20 points 05/12/2024 N. KAFA – O. OMRI 4 Let’s play & get knowing you  https://app.wooclap.com/events/IUJGPM/questions/66f15cd4cc4f46b324d50924 05/12/2024 N. KAFA – O. OMRI 5 Learning Goals What is Microsoft Excel? Creation and saving workbooks Data manipulation Basic formulas 05/12/2024 N. KAFA – O. OMRI 6 What is Mircrosoft EXCEL ? Overview: Microsoft Excel is a powerful spreadsheet program developed by Microsoft, used for data organization, analysis, and visualization. It's widely applied in fields like marketing, supply chain, finance, accounting, business, data analysis, and project management. Key Features: Data Entry and Organization: Store and organize large datasets in rows and columns. Formulas & Functions: Perform calculations, data manipulation, and analysis using built-in formulas. Data Visualization: Create charts and graphs to visualize data trends and insights. Automation: Use Macros to automate repetitive tasks. Data Analysis Tools: PivotTables, Conditional Formatting, and Solver for advanced analytics. Why Learn Excel? Streamline business operations. Increase productivity. Essential tool for data-driven decision making. 05/12/2024 N. KAFA – O. OMRI 7 What is Mircrosoft EXCEL ? 05/12/2024 N. KAFA – O. OMRI 8 What is Mircrosoft EXCEL ? 05/12/2024 N. KAFA – O. OMRI 9 Creation and saving a workbook 1. XLSX: This is the default Excel file format, supporting all modern Excel functions. 2. XLSM: They are Excel workbooks that contain macros. Excel supports the opening of these files, but the execution of macros may require security adjustments. 3. CSV: They are commonly used for importing and exporting tabular data. 4. PDF : Excel can export spreadsheets in PDF format for viewing and printing. 05/12/2024 N. KAFA – O. OMRI 10 Creation and saving a workbook Predefined workbooks Objectives: Can be used as a starting point for creating new workbooks. Save time rather than creating everything from scratch, start your project quickly. Give your document a more professional look. Automate and customize workbook creation. Examples: Pay slips, Factures, Timetable … 05/12/2024 N. KAFA – O. OMRI 11 Creation and saving a workbook Model creation 1- Building the workbook : Elements common to all future workbooks formatting formula 2- If necessary, define protections : Sheet Cell (format  protection) Workbook 3- Save as a template: File format: Xltx, or Xltm Custom Office template 05/12/2024 N. KAFA – O. OMRI 12 Creation and saving a workbook Using the model 1- New workbook File -> New model -> Personal 2- Data insertion 3- Saving the workbook (Xlsx or Xlsm) 05/12/2024 N. KAFA – O. OMRI 13 Data manipulation Cells formatting Several categories & types of data: Number, currency, date, time,Special (tel, number...). 05/12/2024 N. KAFA – O. OMRI 14 Data manipulation Data validation Objective: Check data entry (data type, values, etc.) 1- Select cell 2- Search tab: Validation 3- Options 4- List 05/12/2024 N. KAFA – O. OMRI 15 Basic formulas Formula and functions Objectives :Perform mathematical operations, search for values, or even calculate dates and times, make financial calculations... 1. Select the formula tab 2. Insert a formula 3. Select a formula 4. Select cells If you already know the name of the function you're going to use select the cell and type “= sum (B1 : B3)” 05/12/2024 N. KAFA – O. OMRI 16 Basic Formulas Formula Description =SUM(A1:A5) Adds the values in cells A1 through A5. Calculates the average of cells B1 =AVERAGE(B1:B10) to B10. =IF(C1>100, "Yes", "No") Logical test; returns "Yes" if true, otherwise "No". =COUNT(D1:D10) Counts the number of numeric values in range D1 to D10. =COUNTA(D1:D10) Counts the values (numeric values+ text) in range D1 to D10. =COUNTBLANK(D1:D10) Counts the “empty” values in range D1 to D10. 05/12/2024 N. KAFA – O. OMRI 17 Basic Formulas Formula Description =SUMIF(C2:C15;">545";D2 Calculates the sum of cells D2 to :D15) D15 with condition C2 to C15 more than 545. Calculates the average of cells D2 ==AVERAGEIF(C2:C15;">4 to D15 with condition C2 to C15 00";D2:D15) more than 400. =COUNTIF(E2:E11;14) COUNT the times we have 14 of cells E2 to E11 =LEN(F1) Returns the length of the text in cell F1. =NOW() Returns the current date and time. 05/12/2024 N. KAFA – O. OMRI 18 Exercice : Basic formulas This table corresponds to the students' marks in 3 subjects: Calculate the average per student, considering the coefficients. Calculate the average by subject. Give the total points, calculate the highest score, the lowest score, for each discipline NB: Functions (SUM, Average, MAX, MIN, COUNTA) 05/12/2024 N. KAFA – O. OMRI 19 Exercice : Basic formulas A student wishes to establish a provisional budget for the period October-February. It starts from the following data: At the beginning of October, the student's account is positive, up to € 105. Every month, he receives € 475 in fixed income, to which is added € 150 in scholarship every two months: 0 in October, 150 in November, and so on. In December, he receives a bonus of € 95. Each month, the expenses break down as follows: 215 € in accommodation (except 250 in October) 150 € in food (except 200 in November) 50 € in transport every month Outings: € 90 in October, December and January € 15 in November and € 75 in February Various: € 60 in December, € 30 in February Format the cells to currency Calculate for each month: the total revenue, the total expenses, the initial balance, and the final balance 05/12/2024 N. KAFA – O. OMRI 20 Data manipulation Consolidate data Objective: Automate the calculations and gain the time ( Data > Consolidate data) 1. Cell select 2. Search tab: Consolidate data 3. Function choice 4. Reference ( the data you want to consolidate) 05/12/2024 N. KAFA – O. OMRI 21 Exercice : Data manipulation - Consolidate You work for a Para-pharmacy which has two stores (one in Bordeaux and one in Paris) and sells a variety of skincare and beauty products. You have two data tables, the first representing product sales per month for the Bordeaux store, and the second for the Paris store. Your task is to consolidate these data into a single table to obtain an overall view of sales. 05/12/2024 N. KAFA – O. OMRI 22 Take away & Coming What is EXCEL ? Creation and saving a workbook. Data manipulation. Basic formulas. Session 2: Data import, Conditional formatting … 05/12/2024 N. KAFA – O. OMRI 23