uCertify

Microsoft Excel Data Analysis and Business Modeling Lab

(XLS-DA-BM.AB1.0L0)
Lessons
Lab
TestPrep
AI Tutor (Add-on)
Get A Free Trial

Skills You’ll Get

Download Course Outline

1

Introduction

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

Basic worksheet modeling

  • Answers to this lesson's questions
  • Problems
3

Range names

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

Lookup functions

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

The INDEX function

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

The MATCH function

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

Text functions and Flash Fill

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

Dates and date functions

  • Answers to this lesson’s questions
  • Problems
9

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

  • Answers to this lesson’s questions
  • Problems
10

Time and time functions

  • Answers to this lesson’s questions
  • Problems
11

The net present value functions: NPV and XNPV

  • Answers to this lesson’s questions
  • Problems
12

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

  • Answers to this lesson’s questions
  • Problems
13

More Excel financial functions

  • Answers to this lesson’s questions
  • Problems
14

Circular references

  • Answers to this lesson’s questions
  • Problems
15

The Paste Special command

  • Answers to this lesson’s questions
  • Problems
16

Three-dimensional formulas and hyperlinks

  • Answers to this lesson’s questions
  • Problems
17

The auditing tool and the Inquire add-in

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

Sensitivity analysis with data tables

  • Answers to this lesson’s questions
  • Problems
19

The Goal Seek command

  • Answers to this lesson’s questions
  • Problems
20

Using the Scenario Manager for sensitivity analysis

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

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

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

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

  • Answers to this lesson’s questions
  • Problems
23

Summarizing data with histograms and Pareto charts

  • Answers to this lesson’s questions
  • Problems
24

Summarizing data with descriptive statistics

  • Answers to this lesson’s questions
  • Problems
25

Summarizing data with database statistical functions

  • Answers to this lesson’s questions
  • Problems
26

Consolidating data

  • Answer to this lesson’s question
  • Problems
27

Creating subtotals

  • Answers to this lesson’s questions
  • Problems
28

The OFFSET function

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

The INDIRECT function

  • Answers to this lesson’s questions
  • Problems
30

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

  • Answers to this lesson’s questions
  • Problems
31

Conditional formatting

  • Answers to this lesson’s questions
  • Problems
32

Excel tables and table slicers

  • Answers to this lesson’s questions
  • Problems
33

Basic charting

  • Answers to this lesson’s questions
  • Problems
34

Advanced charting

  • Answers to this lesson’s questions
  • Problems
35

Filled and 3D Maps

  • Questions answered in this lesson
  • Problems
36

Sparklines

  • Answers to this lesson’s questions
  • Problems
37

Importing data from a text file or document

  • Answers to this lesson’s question
  • Problems
38

The Power Query Editor

  • Answers to this lesson’s questions
  • Problems
39

Excel’s new data types

  • Answers to this lesson’s questions
  • Problems
40

Sorting in Excel

  • Answers to this lesson’s questions
  • Problems
41

Filtering data and removing duplicates

  • Answers to this lesson’s questions
  • Problems
42

Array formulas and functions

  • Answers to this lesson’s questions
  • Problems
43

Excel’s new dynamic array functions

  • Answers to this lesson’s questions
  • Problems
44

Validating data

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

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

  • Answers to this lesson’s questions
  • Problems
46

Using PivotTables and slicers to describe data

  • Answers to this lesson’s questions
  • Problems
47

The Data Model

  • Answers to this lesson’s questions
  • Problems
48

Power Pivot

  • Answers to this lesson’s questions
  • Problems
49

Use Analyze Data to find patterns in your data

  • Answers to this lesson’s questions
  • Problems
50

An introduction to optimization with Excel Solver

  • Answers to this lesson’s questions
  • Problems
51

Using Solver to determine the optimal product mix

  • Answers to this lesson’s questions
  • Problems
52

Using Solver to schedule your workforce

  • Answers to this lesson’s question
  • Problems
53

Using Solver to solve transportation or distribution problems

  • Answer to this lesson’s question
  • Problems
54

Using Solver for capital budgeting

  • Answer to this lesson’s question
  • Problems
55

Using Solver for financial planning

  • Answers to this lesson’s questions
  • Problems
56

Using Solver to rate sports teams

  • Answer to this lesson’s question
  • Problems
57

Warehouse location and the GRG Multistart and Evolutionary Solver engines

  • Answers to this lesson’s questions
  • Problems
58

Penalties and the Evolutionary Solver

  • Answers to this lesson’s questions
  • Problems
59

The traveling salesperson problem

  • Answers to this lesson’s questions
  • Problems
60

Estimating straight-line relationships

  • Answers to this lesson’s questions
  • Problems
61

Modeling exponential growth

  • Answers to this lesson’s questions
  • Problems
62

The power curve

  • Answers to this lesson’s questions
  • Problems
63

Using correlations to summarize relationships

  • Answers to this lesson’s questions
  • Problems
64

Introduction to multiple regression

  • Answers to this lesson’s questions
  • Problems
65

Incorporating qualitative factors into multiple regression

  • Answers to this lesson’s questions
  • Problems
66

Modeling nonlinearities and interactions

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

Analysis of variance: One-way ANOVA

  • Answers to this lesson’s questions
  • Problems
68

Randomized blocks and two-way ANOVA

  • Answers to this lesson’s questions
  • Problems
69

An introduction to probability

  • Answers to this lesson’s questions
  • Problems
70

An introduction to random variables

  • Answers to this lesson’s questions
  • Problems
71

The binomial, hypergeometric, and negative binomial random variables

  • Answers to this lesson’s questions
  • Problems
72

The Poisson and exponential random variable

  • Answers to this lesson’s questions
  • Problems
73

The normal random variable and Z-scores

  • Answers to this lesson’s questions
  • Problems
74

Using the lognormal random variable to model stock prices

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

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

  • Answers to this lesson’s questions
  • Problems
76

Using moving averages to understand time series

  • Answer to this lesson’s question
  • Problem
77

Ratio-to-moving-average forecast method

  • Answers to this lesson’s questions
  • Problem
78

Making probability statements from forecasts

  • Answers to this lesson’s questions
  • Problems
79

The Winters method and the Forecast Sheet tool

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

Forecasting in the presence of special events

  • Answers to this lesson’s questions
  • Problems
81

Introduction to Monte Carlo simulation

  • Answers to this lesson’s questions
  • Problems
82

Calculating an optimal bid

  • Answers to this lesson’s questions
  • Problems
83

Simulating stock prices and asset-allocation modeling

  • Answers to this lesson’s questions
  • Problems
84

Fun and games: Simulating gambling and sporting-event probabilities

  • Answers to this lesson’s questions
  • Problems
85

Using resampling to analyze data

  • Answer to this lesson’s question
  • Problems
86

Advanced sensitivity analysis

  • Answer to this lesson’s question
  • Problems
87

Pricing stock options

  • Answers to this lesson’s questions
  • Problems
88

Determining customer value

  • Answers to this lesson’s questions
  • Problems
89

The economic order quantity inventory model

  • Answers to this lesson’s questions
  • Problems
90

Inventory modeling with uncertain demand

  • Answers to this lesson’s questions
  • Problems
91

Queuing theory: The mathematics of waiting in line

  • Answers to this lesson’s questions
  • Problems
92

Estimating a demand curve

  • Answers to this lesson’s questions
  • Problems
93

Pricing products by using tie-ins

  • Answer to this lesson’s question
  • Problems
94

Pricing products by using subjectively determined demand

  • Answers to this lesson’s questions
  • Problems
95

Nonlinear pricing

  • Answers to this lesson’s questions
  • Problems
96

Recording macros

  • Answers to this lesson’s questions
  • Problems
97

The LET and LAMBDA functions and the LAMBDA helper functions

  • Answers to this lesson’s questions
  • Problems

1

Basic worksheet modeling

  • Performing Mathematical Calculations using Formulas
2

Lookup functions

  • Accumulating Data Using the VLOOKUP Function
3

The INDEX function

  • Extracting Data Using the INDEX Function
4

The MATCH function

  • Finding the Required Data Using the MATCH Function
5

Text functions and Flash Fill

  • Creating Email Addresses Using the Excel Text Functions
6

Dates and date functions

  • Calculating the Number of Workdays Using a Date Function
7

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

  • Computing Annual Sales Using the IF Function
8

Time and time functions

  • Calculating Race Timings Using the Time Functions
9

The net present value functions: NPV and XNPV

  • Calculating Net Present Value Using the NPV Function
10

More Excel financial functions

  • Determining Depreciation Using Excel Financial Functions
11

The Paste Special command

  • Using the Paste Special Command to Convert Data
12

Three-dimensional formulas and hyperlinks

  • Summarizing Data Using Three-Dimensional Formulas
13

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

  • Counting Cells with Criteria Using COUNTIF and COUNTIFS Functions
14

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

  • Calculating with Criteria Using the COUNTIF and SUMIF Functions
15

Summarizing data with histograms and Pareto charts

  • Creating Bin Ranges Using Histograms
16

Summarizing data with database statistical functions

  • Summarizing Data
17

Consolidating data

  • Consolidating Data
18

Creating subtotals

  • Creating a Subtotal using the SUBTOTAL Function
19

The OFFSET function

  • Using the OFFSET Function to Create Lagged Values
20

The INDIRECT function

  • Using the INDIRECT Function to Tabulate Data
21

Excel tables and table slicers

  • Using Excel Tables to Perform Calculations
22

Basic charting

  • Creating a Scatter Chart
23

Sparklines

  • Creating Sparklines
24

Importing data from a text file or document

  • Importing Data from a Text File
25

The Power Query Editor

  • Using the Power Query Editor to Transform Data
26

Sorting in Excel

  • Sorting Data
27

Array formulas and functions

  • Performing Calculations Using Array Functions and Formulas
28

Using PivotTables and slicers to describe data

  • Creating a PivotTable and PivotChart
29

The Data Model

  • Using the Distinct Count Option for Calculation
30

Using Solver to determine the optimal product mix

  • Determining the Profit-Maximizing Product Mix Using Solver
31

Using Solver to solve transportation or distribution problems

  • Finding an Optimal Solution Using Solver
32

Using Solver for capital budgeting

  • Obtaining Maximum NPV using Solver
33

Using Solver for financial planning

  • Determining the Monthly Payment Using Solver
34

The traveling salesperson problem

  • Solving the Traveling Salesperson Problem
35

Estimating straight-line relationships

  • Creating a Scatter Chart and Adding a Trendline
36

Modeling exponential growth

  • Creating an Exponential Trend Curve
37

The power curve

  • Creating a Power Curve
38

Using correlations to summarize relationships

  • Using Correlations to Find the Relationship Between Variables
39

Introduction to multiple regression

  • Using Multiple Regression to Find the Optimal Forecasting Equation
40

An introduction to random variables

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

The binomial, hypergeometric, and negative binomial random variables

  • Computing Binomial Probabilities
42

The Poisson and exponential random variable

  • Computing Poisson Distribution
43

The normal random variable and Z-scores

  • Calculating Z-Scores
44

Using the lognormal random variable to model stock prices

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

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

  • Determining Probability Using the Beta Random Variable
46

Using moving averages to understand time series

  • Creating a Moving Average Graph
47

Ratio-to-moving-average forecast method

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

The Winters method and the Forecast Sheet tool

  • Estimating Smoothing Constants
49

Introduction to Monte Carlo simulation

  • Simulating the Values of a Normal Random Variable
50

Calculating an optimal bid

  • Determining the Optimal Bid using Simulation
51

Simulating stock prices and asset-allocation modeling

  • Determining Asset Allocation
52

Fun and games: Simulating gambling and sporting-event probabilities

  • Simulating the Outcome of a Sporting Event
53

Using resampling to analyze data

  • Implementing Resampling
54

Advanced sensitivity analysis

  • Creating a Spider Plot
55

Pricing stock options

  • Using Formula Protection in a Worksheet
56

Determining customer value

  • Determining Customer Value
57

Inventory modeling with uncertain demand

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

Estimating a demand curve

  • Plotting a Linear Demand Curve
59

Pricing products by using subjectively determined demand

  • Finding the Optimal Price Using Subjectively Determined Demand

Related Courses

All Courses