Data Management Course Overview

Data Management Course

• The course is 30 hours long, employing a "learning by doing" approach.
• Topics include data manipulation and visualization, data analysis, optimization, and scenario management.
• Exercises using Excel are incorporated into class sessions.
• Evaluation includes a group project (40%) to be submitted on the 10th session, an individual evaluation (60%) due on session 10, with a total weighting of 100%.
• The learning objectives of the course include using MS Excel for versatile projects.

Learning Goals

• Understanding Microsoft Excel.
• Creation and saving of workbooks.
• Data manipulation techniques.
• Basic Excel formulas.

What is Microsoft Excel?

• A powerful spreadsheet program used for data organization, analysis, and visualization.
• Widely used in diverse fields, including marketing, supply chain, finance, accounting, business, and project management.
• Key features include data entry and organization, formulas and functions for complex calculations and data manipulation, data visualization through charts and graphs, and automation of repetitive processes via macros.
• Data analysis tools, like PivotTables, Conditional Formatting, and Solver, support complex tasks.
• Excel improves operational efficiency, productivity, and data-driven decision-making for businesses.

Creation and Saving Workbooks

• XLSX: The default Excel file format, supporting all modern Excel features, and suited for most general uses.
• XLSM: Excel workbooks that include macros. Opening these requires adjustments if macros need running.
• CSV: Files used for importing and exporting tabular data.
• PDF: Exported Excel spreadsheets are viewable and printable.

Predefined Workbooks

• Predefined workbooks can serve as templates to create new workbooks quickly.
• These pre-built templates allow users to manage time and save effort.
• They provide a format for producing professional documents, automating and customizing workbooks.
• Examples include pay slips, invoices, and project schedules.

Model Creation

• Building the workbook involves defining elements and common formatting characteristics.
• Defining necessary protections for sheets, cells, and the entire workbook itself.
• Saving templates as .xltx or .xltm formats.
• Customizing templates with company logos, style guidelines, and other specifics.

Using the Model

• Creating a new workbook from a template.
• Inserting data into the workbook.
• Saving the workbook in a suitable format (such as XLSX or XLSM).

Data Manipulation: Cells Formatting

• Data types include number, currency, date, time, and special data types (for a phone number, etc.).

Data Validation

• Data validation helps ensure data entry accuracy by checking against data types, values, or lists of appropriate values.

Basic Formulas

• Formulas perform mathematical operations, retrieve values, calculate dates, and complete financial calculations
• Examples: SUM total of values, AVERAGE average of values, IF/COUNTIF conditional statements, COUNTA counts numeric and text values, COUNTBLANK counts blank cells.

Basic Formulas: Additional Formulas

• SUMIF calculates the sum of a range based on whether a criterion is met.
• AVERAGEIF calculates the average of a range based on whether a criterion is met.
• COUNTIF determines how often a value occurs within a criteria range
• LEN determines the number of characters in a string
• NOW returns the current date and time.

Exercice: Basic Formulas

• Calculating average grades for students in different subjects while accounting for subject-specific weightings.
• Determining highest and lowest scores within individual subjects.
• Calculating student averages and total scores.

Exercice: Basic Formulas (Budgeting)

• Creating a provisional budget from October to February. -Calculating monthly revenue, expenses, initial balance, and final balance. Formatting financial entries as currency.

Data Manipulation: Consolidating Data

• Data consolidation automatically calculates summary data across different sources and saves time.
• Consolidate data across different workbooks or spreadsheets.
• Applying different types of summary functions and specifying desired formulas.
• Using Excel tools like the consolidate function to combine information.

Take Away

• Summary of Excel's main uses: spreadsheet software for workbooks, data manipulation, and its basic formulas.
• The next session of the course covers data import and conditional formatting.