Microsoft Excel Data Analysis and Business Modelling

Get a hands-on experience in Microsoft Excel data analysis with uCertify’s course Microsoft Excel Data Analysis and Business Modeling. This course is designed for candidates whose job role involves summarizing, reporting, and analyzing data. This course might also involve building analytic models to help your employer increase profits, reduce costs, or manage operations more efficiently. The course contains interactive objective-based lessons with quizzes, flashcards, and labs to give candidates a live experience of working with Excel data analysis and business modeling.

Lessons

97+ Lessons | 300+ Exercises | 189+ Quizzes | 111+ Flashcards | 111+ Glossary of terms

TestPrep

60+ Pre Assessment Questions | 2+ Full Length Tests | 60+ Post Assessment Questions | 120+ Practice Test Questions

Hand on lab

60+ LiveLab | 60+ Video tutorials | 02:12+ Hours

Lessons 1: Introduction

• What you should know before reading this course?
• How to use this course?

Lessons 2: Basic worksheet modeling

• Answers to this lesson’s questions
• Problems

Lessons 3: Range names

• How can I create named ranges?
• Answers to this lesson’s questions
• Remarks
• Problems

Lessons 4: Lookup functions

• Syntax of the lookup functions
• Answers to this lesson’s questions
• Problems

Lessons 5: The INDEX function

• Syntax of the INDEX function
• Answers to this lesson’s questions
• Problems

Lessons 6: The MATCH function

• Syntax of the MATCH function
• Answers to this lesson’s questions
• Problems

Lessons 7: Text functions and Flash Fill

• Text function syntax
• Answers to this lesson’s questions
• Problems

Lessons 8: Dates and date functions

• Answers to this lesson’s questions
• Problems

Lessons 9: IF, IFERROR, IFS, CHOOSE, SWITCH, and the IS functions

• Answers to this lesson’s questions
• Problems

Lessons 10: Time and time functions

• Answers to this lesson’s questions
• Problems

Lessons 11: The net present value functions: NPV and XNPV

• Answers to this lesson’s questions
• Problems

Lessons 12: The internal rate of return: IRR, XIRR, and MIRR functions

• Answers to this lesson’s questions
• Problems

Lessons 13: More Excel financial functions

• Answers to this lesson’s questions
• Problems

Lessons 14: Circular references

• Answers to this lesson’s questions
• Problems

Lessons 15: The Paste Special command

• Answers to this lesson’s questions
• Problems

Lessons 16: Three-dimensional formulas and hyperlinks

• Answers to this lesson’s questions
• Problems

Lessons 17: The auditing tool and the Inquire add-in

• Excel auditing options
• Answers to this lesson’s questions
• Problems

Lessons 18: Sensitivity analysis with data tables

• Answers to this lesson’s questions
• Problems

Lessons 19: The Goal Seek command

• Answers to this lesson’s questions
• Problems

Lessons 20: Using the Scenario Manager for sensitivity analysis

• Answer to this lesson’s question
• Remarks
• Problems

Lessons 21: The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK functions

• Answers to this lesson’s questions
• Remarks
• Problems

Lessons 22: The SUMIF, AVERAGEIF, SUMIFS, AVERAGEIFS, MAXIFS, and MINIFS functions

• Answers to this lesson’s questions
• Problems

Lessons 23: Summarizing data with histograms and Pareto charts

• Answers to this lesson’s questions
• Problems

Lessons 24: Summarizing data with descriptive statistics

• Answers to this lesson’s questions
• Problems

Lessons 25: Summarizing data with database statistical functions

• Answers to this lesson’s questions
• Problems

Lessons 26: Consolidating data

• Answer to this lesson’s question
• Problems

Lessons 27: Creating subtotals

• Answers to this lesson’s questions
• Problems

Lessons 28: The OFFSET function

• Answers to this lesson’s questions
• Remarks
• Problems

Lessons 29: The INDIRECT function

• Answers to this lesson’s questions
• Problems

Lessons 30: Spin buttons, scrollbars, option buttons, check boxes, combo boxes, and group list boxes

• Answers to this lesson’s questions
• Problems

Lessons 31: Conditional formatting

• Answers to this lesson’s questions
• Problems

Lessons 32: Excel tables and table slicers

• Answers to this lesson’s questions
• Problems

Lessons 33: Basic charting

• Answers to this lesson’s questions
• Problems

Lessons 34: Advanced charting

• Answers to this lesson’s questions
• Problems

Lessons 35: Filled and 3D Maps

• Questions answered in this lesson
• Problems

Lessons 36: Sparklines

• Answers to this lesson’s questions
• Problems

Lessons 37: Importing data from a text file or document

• Answers to this lesson’s question
• Problems

Lessons 38: The Power Query Editor

• Answers to this lesson’s questions
• Problems

Lessons 39: Excel’s new data types

• Answers to this lesson’s questions
• Problems

Lessons 40: Sorting in Excel

• Answers to this lesson’s questions
• Problems

Lessons 41: Filtering data and removing duplicates

• Answers to this lesson’s questions
• Problems

Lessons 42: Array formulas and functions

• Answers to this lesson’s questions
• Problems

Lessons 43: Excel’s new dynamic array functions

• Answers to this lesson’s questions
• Problems

Lessons 44: Validating data

• Answers to this lesson’s questions
• Remarks
• Problems

Lessons 45: Importing past stock prices, exchange rates, and…tocurrency prices with the STOCKHISTORY function

• Answers to this lesson’s questions
• Problems

Lessons 46: Using PivotTables and slicers to describe data

• Answers to this lesson’s questions
• Problems

Lessons 47: The Data Model

• Answers to this lesson’s questions
• Problems

Lessons 48: Power Pivot

• Answers to this lesson’s questions
• Problems

Lessons 49: Use Analyze Data to find patterns in your data

• Answers to this lesson’s questions
• Problems

Lessons 50: An introduction to optimization with Excel Solver

• Answers to this lesson’s questions
• Problems

Lessons 51: Using Solver to determine the optimal product mix

• Answers to this lesson’s questions
• Problems

Lessons 52: Using Solver to schedule your workforce

• Answers to this lesson’s question
• Problems

Lessons 53: Using Solver to solve transportation or distribution problems

• Answer to this lesson’s question
• Problems

Lessons 54: Using Solver for capital budgeting

• Answer to this lesson’s question
• Problems

Lessons 55: Using Solver for financial planning

• Answers to this lesson’s questions
• Problems

Lessons 56: Using Solver to rate sports teams

• Answer to this lesson’s question
• Problems

Lessons 57: Warehouse location and the GRG Multistart and Evolutionary Solver engines

• Answers to this lesson’s questions
• Problems

Lessons 58: Penalties and the Evolutionary Solver

• Answers to this lesson’s questions
• Problems

Lessons 59: The traveling salesperson problem

• Answers to this lesson’s questions
• Problems

Lessons 60: Estimating straight-line relationships

• Answers to this lesson’s questions
• Problems

Lessons 61: Modeling exponential growth

• Answers to this lesson’s questions
• Problems

Lessons 62: The power curve

• Answers to this lesson’s questions
• Problems

Lessons 63: Using correlations to summarize relationships

• Answers to this lesson’s questions
• Problems

Lessons 64: Introduction to multiple regression

• Answers to this lesson’s questions
• Problems

Lessons 65: Incorporating qualitative factors into multiple regression

• Answers to this lesson’s questions
• Problems

Lessons 66: Modeling nonlinearities and interactions

• Answers to this lesson’s questions
• Problems for Lessons 51–53

Lessons 67: Analysis of variance: One-way ANOVA

• Answers to this lesson’s questions
• Problems

Lessons 68: Randomized blocks and two-way ANOVA

• Answers to this lesson’s questions
• Problems

Lessons 69: An introduction to probability

• Answers to this lesson’s questions
• Problems

Lessons 70: An introduction to random variables

• Answers to this lesson’s questions
• Problems

Lessons 71: The binomial, hypergeometric, and negative binomial random variables

• Answers to this lesson’s questions
• Problems

Lessons 72: The Poisson and exponential random variable

• Answers to this lesson’s questions
• Problems

Lessons 73: The normal random variable and Z-scores

• Answers to this lesson’s questions
• Problems

Lessons 74: Using the lognormal random variable to model stock prices

• Answers to this lesson’s questions
• Remarks
• Problems

Lessons 75: Weibull and beta distributions: Modeling machine life and duration of a project

• Answers to this lesson’s questions
• Problems

Lessons 76: Using moving averages to understand time series

• Answer to this lesson’s question
• Problem

Lessons 77: Ratio-to-moving-average forecast method

• Answers to this lesson’s questions
• Problem

Lessons 78: Making probability statements from forecasts

• Answers to this lesson’s questions
• Problems

Lessons 79: The Winters method and the Forecast Sheet tool

• Answers to this lesson’s questions
• Remarks
• Problems

Lessons 80: Forecasting in the presence of special events

• Answers to this lesson’s questions
• Problems

Lessons 81: Introduction to Monte Carlo simulation

• Answers to this lesson’s questions
• Problems

Lessons 82: Calculating an optimal bid

• Answers to this lesson’s questions
• Problems

Lessons 83: Simulating stock prices and asset-allocation modeling

• Answers to this lesson’s questions
• Problems

Lessons 84: Fun and games: Simulating gambling and sporting-event probabilities

• Answers to this lesson’s questions
• Problems

Lessons 85: Using resampling to analyze data

• Answer to this lesson’s question
• Problems

Lessons 86: Advanced sensitivity analysis

• Answer to this lesson’s question
• Problems

Lessons 87: Pricing stock options

• Answers to this lesson’s questions
• Problems

Lessons 88: Determining customer value

• Answers to this lesson’s questions
• Problems

Lessons 89: The economic order quantity inventory model

• Answers to this lesson’s questions
• Problems

Lessons 90: Inventory modeling with uncertain demand

• Answers to this lesson’s questions
• Problems

Lessons 91: Queuing theory: The mathematics of waiting in line

• Answers to this lesson’s questions
• Problems

Lessons 92: Estimating a demand curve

• Answers to this lesson’s questions
• Problems

Lessons 93: Pricing products by using tie-ins

• Answer to this lesson’s question
• Problems

Lessons 94: Pricing products by using subjectively determined demand

• Answers to this lesson’s questions
• Problems

Lessons 95: Nonlinear pricing

• Answers to this lesson’s questions
• Problems

Lessons 96: Recording macros

• Answers to this lesson’s questions
• Problems

Lessons 97: The LET and LAMBDA functions and the LAMBDA helper functions

• Answers to this lesson’s questions
• Problems

Hands-on LAB Activities

Basic worksheet modeling

• Performing Mathematical Calculations using Formulas

Lookup functions

• Accumulating Data Using the VLOOKUP Function

The INDEX function

• Extracting Data Using the INDEX Function

The MATCH function

• Finding the Required Data Using the MATCH Function

Text functions and Flash Fill

• Creating Email Addresses Using the Excel Text Functions

Dates and date functions

• Calculating the Number of Workdays Using a Date Function

IF, IFERROR, IFS, CHOOSE, SWITCH, and the IS functions

• Computing Annual Sales Using the IF Function

Time and time functions

• Calculating Race Timings Using the Time Functions

The net present value functions: NPV and XNPV

• Calculating Net Present Value Using the NPV Function

More Excel financial functions

• Determining Depreciation Using Excel Financial Functions

The Paste Special command

• Using the Paste Special Command to Convert Data

Three-dimensional formulas and hyperlinks

• Summarizing Data Using Three-Dimensional Formulas

The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK functions

• Counting Cells with Criteria Using COUNTIF and COUNTIFS Functions

The SUMIF, AVERAGEIF, SUMIFS, AVERAGEIFS, MAXIFS, and MINIFS functions

• Calculating with Criteria Using the COUNTIF and SUMIF Functions

Summarizing data with histograms and Pareto charts

• Creating Bin Ranges Using Histograms

Summarizing data with database statistical functions

• Summarizing Data

Consolidating data

• Consolidating Data

Creating subtotals

• Creating a Subtotal using the SUBTOTAL Function

The OFFSET function

• Using the OFFSET Function to Create Lagged Values

The INDIRECT function

• Using the INDIRECT Function to Tabulate Data

Excel tables and table slicers

• Using Excel Tables to Perform Calculations

Basic charting

• Creating a Scatter Chart

Sparklines

• Creating Sparklines

Importing data from a text file or document

• Importing Data from a Text File

The Power Query Editor

• Using the Power Query Editor to Transform Data

Sorting in Excel

• Sorting Data

Array formulas and functions

• Performing Calculations Using Array Functions and Formulas

Using PivotTables and slicers to describe data

• Creating a PivotTable and PivotChart

The Data Model

• Using the Distinct Count Option for Calculation

Using Solver to determine the optimal product mix

• Determining the Profit-Maximizing Product Mix Using Solver

Using Solver to solve transportation or distribution problems

• Finding an Optimal Solution Using Solver

Using Solver for capital budgeting

• Obtaining Maximum NPV using Solver

Using Solver for financial planning

• Determining the Monthly Payment Using Solver

The traveling salesperson problem

• Solving the Traveling Salesperson Problem

Estimating straight-line relationships

• Creating a Scatter Chart and Adding a Trendline

Modeling exponential growth

• Creating an Exponential Trend Curve

The power curve

• Creating a Power Curve

Using correlations to summarize relationships

• Using Correlations to Find the Relationship Between Variables

Introduction to multiple regression

• Using Multiple Regression to Find the Optimal Forecasting Equation

An introduction to random variables

• Using Variance and Standard Deviation to Measure the Spread of Data

The binomial, hypergeometric, and negative binomial random variables

• Computing Binomial Probabilities

The Poisson and exponential random variable

• Computing Poisson Distribution

The normal random variable and Z-scores

• Calculating Z-Scores

Using the lognormal random variable to model stock prices

• Calculating the Future Price of a Stock Using a Lognormal Variable

Weibull and beta distributions: Modeling machine life and duration of a project

• Determining Probability Using the Beta Random Variable

Using moving averages to understand time series

• Creating a Moving Average Graph

Ratio-to-moving-average forecast method

• Using the Ratio-to-Moving-Average Forecasting Method

The Winters method and the Forecast Sheet tool

• Estimating Smoothing Constants

Introduction to Monte Carlo simulation

• Simulating the Values of a Normal Random Variable

Calculating an optimal bid

• Determining the Optimal Bid using Simulation

Simulating stock prices and asset-allocation modeling

• Determining Asset Allocation

Fun and games: Simulating gambling and sporting-event probabilities

• Simulating the Outcome of a Sporting Event

Using resampling to analyze data

• Implementing Resampling

Advanced sensitivity analysis

• Creating a Spider Plot

Pricing stock options

• Using Formula Protection in a Worksheet

Determining customer value

• Determining Customer Value

Inventory modeling with uncertain demand

• Determining the Economic Order Quantity (EOQ)
• Determining the Reorder Point

Estimating a demand curve

• Plotting a Linear Demand Curve

Pricing products by using subjectively determined demand

• Finding the Optimal Price Using Subjectively Determined Demand