Data Analytics - VC Room Technologies

Master Business Analytics

Advanced Excel

Advanced Excel

Introduction to Advanced Excel | Excel sheet Creation, edit, save, Folder Creation | Project 1

Conditional Formatting & Data Validation

Cell reference, Conditional formatting| Data validation| Important functions in Excel, Remove duplicate values, Hyperlink| Filter, Unique, Sort, SortBY, Sequence, Randarray Functions|

Logical & Statistical functions

Advanced Pivot Table

Classic pivot table and chart| Slicers, timelines and calculated fields| Building hierarchies in pivot tables| Advanced Pivot table data analysis

Advanced Formula for Data Analysis

Custom view, Header and footer, Text to column function| Goal seek, Solver, Scenario manager| Lookup| Project| Match and index function, Consolidating data

Advanced Formula for Data Analysis

Sparkline chart, People graph, Data forecast, Import text file| Mail merge, Consolidating data| Form data, Auditing tool, Web data, Speak cell | Macro, Password protection

Data Preparation and Cleaning

Importing data from various sources. | Handling missing values and duplicates. | Using Text-to-Columns and Flash Fill. | Data validation and error checking for quality assurance.|

Date and Time Analysis

Working with DATE, TIME, NETWORKDAYS, and EOMONTH. | Automating rolling date ranges for dynamic reports. | Calculating time differences in hours, minutes, and seconds. | Custom formatting for date and time (e.g., “MMM-YYYY”). | Dynamic date ranges and custom date logic with WEEKNUM, WORKDAY.INTL

Power Query (Get & Transform)

Importing and cleaning data from various sources| Merging and appending queries| Advanced filtering and transforming data

Introduction to Dashboard Design

Understanding the importance of dashboards | Planning a dashboard project| Data visualization best practices| Advanced Charts

Power BI

Introduction to Power BI

Introduction to Power BI. | Power BI installation. | Main components of Power BI.

Data Import and Transformation

Power Query intro-User Interface

Power Query intro-User Interface| Data Consolidation in Power BI | Case Study -Payroll Data |

Advanced Visualizations and Data Consolidation

Advanced visualizations. | Data Modeling Intro-Start Schema| Case Study Sales Analysis| Snowflake Schema|

DAX Functions

Statistical and Text Functions

Statistical functions (SUM, SUMX, MIN, MINX, AVERAGEX). | Text functions (LEN and others). | Hierarchy reports| Filter Functions|

Designing Interactive Dashboards

Project

Top 10 Tricks in Power BI| Project 1, Project 2, Project 3

Google Forms

Module 1: Building Forms

Building a form. | Adding a title to the form. | Adding multiple-choice questions. | Adding paragraph questions. | Adding short-answer questions.

Module 2: Customization

Adding a title and description. | Adding collaborators. | Customizing the theme. | Reviewing responses.

Module 3: Advanced Features

Exploring advanced features in Google Forms.

Module 4: Projects

Feedback Form for a Workshop (Project 1):
Create a Google Form to collect feedback from participants after attending a workshop or class.

Google Sheets

Module 1: Introduction to Google Sheets

Introduction to Google Sheets. | Practical use of the IF formula in Google Sheets. | Removing duplicates in Google Sheets.

Module 2: Data Collection and Import

Data collection and importing techniques. | Split function. | Image function. | Searchable drop-down list.

Module 3: Advanced Functions and Features

Creating advanced filters using multiple conditions. | Mini calendar and date picker. | Picture lookup. | MAX & MIN functions in Google Sheets.

Module 4: Practical Applications

Creating checklists in Google Sheets. | Stock management with Google Sheets. | Creating attendance trackers in Google Sheets. | Using pivot tables in Google Sheets.

Module 5: Collaboration

Collaborating on spreadsheets with multiple users. | Importing and sharing sheets with others.

Module 6: Add-ons and Assistance

Exploring add-ons for Google Sheets. | Using ChatGPT to create complex formulas. | Utilizing the Help function effectively.

Professional Dashboard Creation

Module 1: Advanced Excel Techniques for Dashboards

Purpose and benefits of dashboards in business. | Key principles of effective dashboard design. | Structuring raw data for analysis. | Using tables and named ranges. | Data validation and error checking.

Module 2: Dashboard Projects

Project 1: Payroll Management Dashboard
Importing employee payroll data. | Handling salary components (basic, allowances, deductions). | Net pay computation. | Overtime and bonus calculations. | Tax and statutory compliance computations.

Project 2: Financial Analytics Dashboard
Importing financial statements and transaction data. | Profit and loss computations. | Cash flow analysis. | Revenue and expense visualizations. | Break-even analysis charts. | Scenario analysis using what-if parameters.

Project 3: HR Management Dashboard
Importing employee data (demographics, performance metrics). | Employee turnover and retention rates. | Performance scoring and appraisal summaries. | Training needs assessment. | Workforce analytics visuals. | Interactive filters by department, role, or location. | Employee satisfaction and engagement indicators.

Project 4: Inventory Management Dashboard
Importing inventory levels, sales orders, and purchase orders. | Stock levels and reorder points. | Inventory aging analysis. | Supplier performance metrics. | Real-time stock monitoring visuals. | Alerts for low stock and overstock situations. | Sales vs. inventory turnover graphs.

Master Business Analytics

Syllabus

VC Room Technologies