Excel For Business Analytics

Focus: Data cleaning, formatting, summarizing, and visualizing

• Raw Data, Data, Proper Data Sets and Data Terminology

  -

• Excel Data Analysis Features: Sort, Filter, Pivot Tables

  -

• Logical Formulas & Conditional Formatting to Visualizing Data

  -

• Count Transactions by Web Site Report & Chart

  11:51

• Count Transactions by Hour Report & Chart

  -

• FREQUENCY Array Function for Frequency Distribution & Histogram

  -

• Data Analysis Add-in for Frequency Distribution & Histogram

  -

• Why PivotTables Rule for Frequency Distributions & Histogram

  -

• Quiz

  -

Focus: Summary statistics, distributions, probability & Excel functions.

• Skew: Shape of Histogram, Shape of Quantitative Data

  -

• AVERAGE, MEDIAN, MODE.MULT functions & PivotTable Mode

  -

• Geometric Mean Average Compounding Rate GEOMEAN RRI Function

  -

• Variability: Variance, Standard Deviation, Z-score and more

  -

• Empirical Rule, Calculating Probability NORM.DIST & NORM.S.DIST

  -

• Percentiles, Quartiles, 5 Number Summary using Excel Functions

  -

• Box & Whisker Plot. Chart Example in Excel 2016

  -

• Quiz

  -

Focus: Cleaning and transforming large or messy data using Power Query

• Power Query: Import Multiple Text Files & Build Grade Dashboard

  -

• Clean & Transform Data: Formulas, Flash Fill, Power Query, TTC

  -

• Power Query: Import Multiple Large CSV Files Into Data Model

  -

• Excel 2016 Power Query: Data Ribbon Tab, Get and Transform

  -

• Power Query: Import Multiple Excel Files with 1 Sheet Each

  -

• Power Query Import Multiple Excel Files with Multiple Sheets

  8:58

• Power Query: Transform Many Bad Data Files into Proper Data Set

  -

• Power Query: Import & Merge Multiple Sources Access, Text, Excel

  -

• Power Query to Get Data From Web Site & Import into Excel

  -

• Power Query to Unpivot Data (Multiple Columns to Single Column)

  -

• Excel 2016 Data Tab, Get & Transform: Unpivot feature

  -

• Quiz

  -

Focus: Large-scale data handling, DAX, data models, and advanced analytics

• PivotTable from 4 Million Records with Power Query & Data Model

  -

• Import Related Tables: Power Query Merge? Data Model Relationships?

  12:18

• Introduction to PowerPivot & Data Modeling

  -

• Excel 2016: Introduction to PowerPivot & Data Model

  -

• Comprehensive PowerPivot, Data Model, DAX & Reporting Example

  -

• Visualizing Data: Table & Chart Guidelines

  -

• Quiz

  -

Focus: Predictive modelling, optimization, simulation, and advanced forecasting.

• Intro To Linear Regression & Scatter Chart

  -

• Covariance and Correlation to Measure Linear Relationship

  -

• Slope & Intercept for Estimated Simple Linear Regression Equation

  -

• SST = SSR + SSE & R Squared & Standard Error of Estimate

  -

• Data Analysis Regression feature

  -

• LINEST Array Function for Simple Linear Regression

  -

• Introduction to Multiple Regression, Data Analysis Regression

  -

• Testing Significance of Regression Relationship with p-value

  -

• Quiz

  -

• LINEST, F.DIST.RT, T.DIST.2T & ABS Functions Test of Significance

  -

• Line Charts for Time Series Data

  -

• Basic Forecasting Methods & Measures of Forecast Error

  -

• Summary of 4 Basic Forecasting Methods & Mean Square Error

  -

• Forecasting with Linear Regression: Trend & Seasonal Pattern

  -

Focus: Operations research, linear programming, Excel Solver, and probabilistic simulation.

• Excel Solver to Maximize Contribution Margin

  -

• Intro To Linear Programming using Algebra, Pencil & Paper

  27:42

• Linear Program Special Cases: Infeasible, Unbound, Alternative

  -

• Excel Solver: Minimize Transportation Costs, Integer Variable

  -

• Find Alternative Optimal Solution For Transportation Problem

  -

• Excel Solver: Maximize Portfolio Returns

  -

• Excel Solver Binary Variable Choose Projects Limited Resources

  -

• Introduction To Monte Carlo Simulation In Excel

  -

• Create Random Variables: Custom, Binomial, Uniform, Normal

  -

• Monte Carlo Simulation for New Product, 3 Uncertain Variables

  -

• Monte Carlo Simulation for Marketing Analysis Binomial Variable

  -

• Monte Carlo Simulation Histogram for Construction Project Length

  -

• Monte Carlo Simulation for Baseball World Series

  -

• Quiz

  -