# Microsoft Excel Data Analysis and Business Modeling

(XLS-DA-BM.AB1) / ISBN : 978-1-64459-368-4

## About This Course

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.

### Skills You’ll Get

Get the support you need. Enroll in our Instructor-Led Course.

## Lessons

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

## TestPrep

60+ Pre Assessment Questions | 60+ Post Assessment Questions |

## Hands-On Labs

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

### Introduction

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

### Basic worksheet modeling

- Answers to this lesson's questions
- Problems

### Range names

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

### Lookup functions

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

### The INDEX function

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

### The MATCH function

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

### Text functions and Flash Fill

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

### Dates and date functions

- Answers to this lesson’s questions
- Problems

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

- Answers to this lesson’s questions
- Problems

### Time and time functions

- Answers to this lesson’s questions
- Problems

### The net present value functions: NPV and XNPV

- Answers to this lesson’s questions
- Problems

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

- Answers to this lesson’s questions
- Problems

### More Excel financial functions

- Answers to this lesson’s questions
- Problems

### Circular references

- Answers to this lesson’s questions
- Problems

### The Paste Special command

- Answers to this lesson’s questions
- Problems

### Three-dimensional formulas and hyperlinks

- Answers to this lesson’s questions
- Problems

### The auditing tool and the Inquire add-in

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

### Sensitivity analysis with data tables

- Answers to this lesson’s questions
- Problems

### The Goal Seek command

- Answers to this lesson’s questions
- Problems

### Using the Scenario Manager for sensitivity analysis

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

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

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

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

- Answers to this lesson’s questions
- Problems

### Summarizing data with histograms and Pareto charts

- Answers to this lesson’s questions
- Problems

### Summarizing data with descriptive statistics

- Answers to this lesson’s questions
- Problems

### Summarizing data with database statistical functions

- Answers to this lesson’s questions
- Problems

### Consolidating data

- Answer to this lesson’s question
- Problems

### Creating subtotals

- Answers to this lesson’s questions
- Problems

### The OFFSET function

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

### The INDIRECT function

- Answers to this lesson’s questions
- Problems

### Spin buttons, scrollbars, option buttons, check boxes, combo boxes, and group list boxes

- Answers to this lesson’s questions
- Problems

### Conditional formatting

- Answers to this lesson’s questions
- Problems

### Excel tables and table slicers

- Answers to this lesson’s questions
- Problems

### Basic charting

- Answers to this lesson’s questions
- Problems

### Advanced charting

- Answers to this lesson’s questions
- Problems

### Filled and 3D Maps

- Questions answered in this lesson
- Problems

### Sparklines

- Answers to this lesson’s questions
- Problems

### Importing data from a text file or document

- Answers to this lesson’s question
- Problems

### The Power Query Editor

- Answers to this lesson’s questions
- Problems

### Excel’s new data types

- Answers to this lesson’s questions
- Problems

### Sorting in Excel

- Answers to this lesson’s questions
- Problems

### Filtering data and removing duplicates

- Answers to this lesson’s questions
- Problems

### Array formulas and functions

- Answers to this lesson’s questions
- Problems

### Excel’s new dynamic array functions

- Answers to this lesson’s questions
- Problems

### Validating data

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

### Importing past stock prices, exchange rates, and...tocurrency prices with the STOCKHISTORY function

- Answers to this lesson’s questions
- Problems

### Using PivotTables and slicers to describe data

- Answers to this lesson’s questions
- Problems

### The Data Model

- Answers to this lesson’s questions
- Problems

### Power Pivot

- Answers to this lesson’s questions
- Problems

### Use Analyze Data to find patterns in your data

- Answers to this lesson’s questions
- Problems

### An introduction to optimization with Excel Solver

- Answers to this lesson’s questions
- Problems

### Using Solver to determine the optimal product mix

- Answers to this lesson’s questions
- Problems

### Using Solver to schedule your workforce

- Answers to this lesson’s question
- Problems

### Using Solver to solve transportation or distribution problems

- Answer to this lesson’s question
- Problems

### Using Solver for capital budgeting

- Answer to this lesson’s question
- Problems

### Using Solver for financial planning

- Answers to this lesson’s questions
- Problems

### Using Solver to rate sports teams

- Answer to this lesson’s question
- Problems

### Warehouse location and the GRG Multistart and Evolutionary Solver engines

- Answers to this lesson’s questions
- Problems

### Penalties and the Evolutionary Solver

- Answers to this lesson’s questions
- Problems

### The traveling salesperson problem

- Answers to this lesson’s questions
- Problems

### Estimating straight-line relationships

- Answers to this lesson’s questions
- Problems

### Modeling exponential growth

- Answers to this lesson’s questions
- Problems

### The power curve

- Answers to this lesson’s questions
- Problems

### Using correlations to summarize relationships

- Answers to this lesson’s questions
- Problems

### Introduction to multiple regression

- Answers to this lesson’s questions
- Problems

### Incorporating qualitative factors into multiple regression

- Answers to this lesson’s questions
- Problems

### Modeling nonlinearities and interactions

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

### Analysis of variance: One-way ANOVA

- Answers to this lesson’s questions
- Problems

### Randomized blocks and two-way ANOVA

- Answers to this lesson’s questions
- Problems

### An introduction to probability

- Answers to this lesson’s questions
- Problems

### An introduction to random variables

- Answers to this lesson’s questions
- Problems

### The binomial, hypergeometric, and negative binomial random variables

- Answers to this lesson’s questions
- Problems

### The Poisson and exponential random variable

- Answers to this lesson’s questions
- Problems

### The normal random variable and Z-scores

- Answers to this lesson’s questions
- Problems

### Using the lognormal random variable to model stock prices

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

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

- Answers to this lesson’s questions
- Problems

### Using moving averages to understand time series

- Answer to this lesson’s question
- Problem

### Ratio-to-moving-average forecast method

- Answers to this lesson’s questions
- Problem

### Making probability statements from forecasts

- Answers to this lesson’s questions
- Problems

### The Winters method and the Forecast Sheet tool

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

### Forecasting in the presence of special events

- Answers to this lesson’s questions
- Problems

### Introduction to Monte Carlo simulation

- Answers to this lesson’s questions
- Problems

### Calculating an optimal bid

- Answers to this lesson’s questions
- Problems

### Simulating stock prices and asset-allocation modeling

- Answers to this lesson’s questions
- Problems

### Fun and games: Simulating gambling and sporting-event probabilities

- Answers to this lesson’s questions
- Problems

### Using resampling to analyze data

- Answer to this lesson’s question
- Problems

### Advanced sensitivity analysis

- Answer to this lesson’s question
- Problems

### Pricing stock options

- Answers to this lesson’s questions
- Problems

### Determining customer value

- Answers to this lesson’s questions
- Problems

### The economic order quantity inventory model

- Answers to this lesson’s questions
- Problems

### Inventory modeling with uncertain demand

- Answers to this lesson’s questions
- Problems

### Queuing theory: The mathematics of waiting in line

- Answers to this lesson’s questions
- Problems

### Estimating a demand curve

- Answers to this lesson’s questions
- Problems

### Pricing products by using tie-ins

- Answer to this lesson’s question
- Problems

### Pricing products by using subjectively determined demand

- Answers to this lesson’s questions
- Problems

### Nonlinear pricing

- Answers to this lesson’s questions
- Problems

### Recording macros

- Answers to this lesson’s questions
- Problems

### The LET and LAMBDA functions and the LAMBDA helper functions

- Answers to this lesson’s questions
- Problems

### 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