Business Analytics with Microsoft Excel
Course Overview
Master the quantitative methods to analyze data using readily available advanced excel tools. Learn to produce actionable insights from data through statistical techniques, data visualization, exploratory and predictive modeling in our business analytics training. Develop skills to define, monitor and optimize metrics to improve your team’s performance.
In our Cloudlabs, implement the statistical tools for data analysis through readily available MS Excel functions and add-ins. Be able to implement the most effective excel solution for analytics problems in your organization.
At the end of the training, participants will be able to:
- Develop analytical approach to management to utilize data, understand statistical and quantitative models
- Recognize trends, detect outliers, and summarize data sets concisely
- Gain hands-on knowledge of mathematical, statistical functions and
- Use Excel add-ins to develop data analytics & visualization solutions for your organization’s needs.
- Derive relationship between two or more variables through regression
- Leverage Analysis ToolPak to conduct a variety of statistical analyses with ease
- Create excel dashboards to track organization/project’s performance in one place
- Provide comprehensive solution to any business challenge through better data-driven decision
Pre-requisite
- Basic knowledge of any spreadsheet software
- Familiarity with data and statistical terms
- Suggested:
- Basic statistics and business analytics concepts
Duration
2 days
Course Outline
- Understanding Data
- Introduction to Data Analytics
- Introduction to Business Analytics, Business Intelligence, and Data Mining
- Analytical Decision Making
- Future of Business Analytics
- Big Data Analytics
- Social Media Analytics
- Applications in industry domains
- Methodologies
- Decision making using data
- Importing Data – Comma delimited, Fixed width, Tab delimited
- Basic Data Manipulation in Excel
- Arithmetic Formulae
- Basic Functions in Excel
- Absolute and Relative Reference
- IF and Nested IF
- VLOOKUP
- HLOOKUP
- Index-Match-Offset
- Rank, Quartile, Percentile
- Data Filters
- Pivots
- Calculated Fields and Items
- Slicers
- Line Graphs
- Bar Graphs
- Pie Charts
- Scatter Plots
- Histograms
- Type of Data
- Sampling Techniques
- Measures of central tendency and dispersion
- Basic probability
- Binomial distribution
- Business Application of the Binomial Distribution
- Poisson distribution
- Normal distribution
- Area under the curve
- Central Limit Theorem
- NORM.DIST & NORM.INV Function
- T-Tests
- T.DIST & T.INV Function
- Confidence Interval and Application
- ANOVA
- Categorical Data Analysis
- Correlation & Covariance
- Hypothesis Testing
- Steps of Hypotheses Test
- Single Tail and Two Tail Hypotheses Tests
- Type I & Type II Errors in a Hypothesis Test
- Defining a Problem
- Step through Solver trial solution
- Change how Solver finds solution
- Save and Load a Problem Model
- Goal Seek
- What-If Scenario Manage
- Load and activate the Analysis ToolPak
- Anova: Single Factor
- Anova: Two-Factor with Replication
- Anova: Two-Factor without Replication
- Correlation
- Covariance
- Descriptive Statistics
- Exponential Smoothing
- F-Test Two-Sample for Variance
- Histogram
- Moving Average
- Rank and Percentile
- Regression
- Sampling
- t-Test: Paired Two Sample for Means
- t-Test: Two-Sample Assuming Equal Variances
- t-Test: Two-Sample Assuming Unequal Variances
- z-Test
- Import Data, Create and Edit Tables
- Relationship Between Tables
- Calculations
- Hierarchies
- PivotTables & PivotCharts
- Importing Data
- Create Charts
- Filter and Highlight Data
- Slicer and Sorting
- Multiple Views
- Exploratory Data Analysis with Power Map
- Geocoding data in Power Map
- Visualization with Power Map
- Custom Maps
- Filtering
- Defining Dashboard
- Data sources & Importing Data into Excel
- KPIs
- Dashboard Elements
- Preparing data for Dashboard
- Analyze the data
- Create Charts
- Create PivotTables and PivotCharts
- Create Dynamic Reports with Filters and Slicers
- Enhancing the UI