uCertify

Microsoft Excel Data Analysis and Business Modeling

(XLS-DA-BM.AB2)
Lessons
Lab
TestPrep
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?
  • What’s New in this Edition?
2

Basic Formulas and Functions

  • Basic worksheet modeling
  • Range names
  • How can I create named ranges?
  • Remarks
  • Lookup functions
  • Syntax of the lookup functions
3

The INDEX function

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

The MATCH function

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

Text functions and Flash Fill

  • Text function syntax
  • Answers to this lesson’s questions
  • Answers to Text functions and Flash Fill questions
  • Problems
  • Problems 6
6

Date, Time, IF, and Financial Functions

7

Dates and date functions

  • Answers to this lesson’s questions
  • Answers to Dates and Date Functions questions
  • Problems
  • Problems 1
8

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

  • Answers to IF, IFERROR, IFS, CHOOSE, SWITCH, and the IS functions questions
  • Answers to this lesson’s questions
  • Problems 2
  • Problems
9

Time and time functions

  • Answers to time and time functions questions
  • Answers to this lesson’s questions
  • Problems 3
  • Problems
10

The net present value functions: NPV and XNPV

  • Answers to The net present value functions: NPV and XNPV questions
  • Answers to this lesson’s questions
  • Problems 4
  • Problems
11

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

  • Answers to The Internal Rate of Return: IRR, XIRR, and MIRR functions questions
  • Answers to this lesson’s questions
  • Problems 5
  • Problems
12

More Excel financial functions

  • Answers to this lesson’s questions
  • Problems
13

Tools for Modeling

14

Circular references

  • Answers to this lesson’s questions
  • Answers to Circular references questions
  • Problems
  • Problems 1
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

Summarizing Data

22

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

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

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

  • Answers to this lesson’s questions
  • Problems
24

Summarizing data with histograms and Pareto charts

  • Answers to this lesson’s questions
  • Problems
25

Summarizing data with descriptive statistics

  • Answers to this lesson’s questions
  • Problems
26

Summarizing data with database statistical functions

  • Answers to this lesson’s questions
  • Problems
27

Consolidating data

  • Answer to this lesson’s question
  • Problems
28

Creating subtotals

  • Answers to this lesson’s questions
  • Problems
29

Advanced Excel Functions and Form Controls

30

The OFFSET function

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

The INDIRECT function

  • Answers to this lesson’s questions
  • Problems
32

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

  • Answers to this lesson’s questions
  • Problems
33

Excel Tables and Charting

34

Conditional formatting

  • Answers to this lesson’s questions
  • Problems
35

Excel tables and table slicers

  • Answers to this lesson’s questions
  • Problems
36

Basic charting

  • Answers to this lesson’s questions
  • Problems
37

Advanced charting

  • Answers to this lesson’s questions
  • Problems
38

Filled and 3D Maps

  • Questions answered in this lesson
  • Problems
39

Sparklines

  • Answers to this lesson’s questions
  • Problems
40

Importing and Transforming Data

41

Importing data from a text file or document

  • Answers to this lesson’s question
  • Problems
42

The Power Query Editor

  • Answers to this lesson’s questions
  • Problems
43

Excel’s new data types

  • Answers to this lesson’s questions
  • Problems
44

Sorting in Excel

  • Answers to this lesson’s questions
  • Problems
45

Filtering data and removing duplicates

  • Answers to this lesson’s questions
  • Problems
46

Array formulas and functions

  • Answers to this lesson’s questions
  • Problems
47

Excel’s new dynamic array functions

  • Answers to this lesson’s questions
  • Problems
48

Validating data

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

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

  • Answers to this lesson’s questions
  • Problems
50

PivotTables, the Data Model and Power Pivot

51

Using PivotTables and slicers to describe data

  • Answers to this lesson’s questions
  • Problems
52

The Data Model

  • Answers to this lesson’s questions
  • Problems
53

Power Pivot

  • Answers to this lesson’s questions
  • Problems
54

Use Analyze Data to find patterns in your data

  • Answers to this lesson’s questions
  • Problems
55

Linear Optimization

56

An introduction to optimization with Excel Solver

  • Answers to this lesson’s questions
  • Problems
57

Using Solver to determine the optimal product mix

  • Answers to this lesson’s questions
  • Problems
58

Using Solver to schedule your workforce

  • Answers to this lesson’s question
  • Problems
59

Using Solver to solve transportation or distribution problems

  • Answer to this lesson’s question
  • Problems
60

Using Solver for capital budgeting

  • Answer to this lesson’s question
  • Problems
61

Using Solver for financial planning

  • Answers to this lesson’s questions
  • Problems
62

Nonlinear Optimization

63

Using Solver to rate sports teams

  • Answer to this lesson’s question
  • Problems
64

Warehouse location and the GRG Multistart and Evolutionary Solver engines

  • Answers to this lesson’s questions
  • Problems
65

Penalties and the Evolutionary Solver

  • Answers to this lesson’s questions
  • Problems
66

The traveling salesperson problem

  • Answers to this lesson’s questions
  • Problems
67

Understanding relationships between two variables

68

Estimating straight-line relationships

  • Answers to this lesson’s questions
  • Problems
69

Modeling exponential growth

  • Answers to this lesson’s questions
  • Problems
70

The power curve

  • Answers to this lesson’s questions
  • Problems
71

Using correlations to summarize relationships

  • Answers to this lesson’s questions
  • Problems
72

Multiple Regression and ANOVA

73

Introduction to multiple regression

  • Answers to this lesson’s questions
  • Problems
74

Incorporating qualitative factors into multiple regression

  • Answers to this lesson’s questions
  • Problems
75

Modeling nonlinearities and interactions

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

Analysis of variance: One-way ANOVA

  • Answers to this lesson’s questions
  • Problems
77

Randomized blocks and two-way ANOVA

  • Answers to this lesson’s questions
  • Problems
78

Introduction to Probability and Random Variables

79

An introduction to probability

  • Answers to this lesson’s questions
  • Problems
80

An introduction to random variables

  • Answers to this lesson’s questions
  • Problems
81

The binomial, hypergeometric, and negative binomial random variables

  • Answers to this lesson’s questions
  • Problems
82

The Poisson and exponential random variable

  • Answers to this lesson’s questions
  • Problems
83

The normal random variable and Z-scores

  • Answers to this lesson’s questions
  • Problems
84

Using the lognormal random variable to model stock prices

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

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

  • Answers to this lesson’s questions
  • Problems
86

Forecasting

87

Using moving averages to understand time series

  • Answer to this lesson’s question
  • Problem
88

Ratio-to-moving-average forecast method

  • Answers to this lesson’s questions
  • Problem
89

Making probability statements from forecasts

  • Answers to this lesson’s questions
  • Problems
90

The Winters method and the Forecast Sheet tool

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

Forecasting in the presence of special events

  • Answers to this lesson’s questions
  • Problems
92

Monte Carlo Simulation

93

Introduction to Monte Carlo simulation

  • Answers to this lesson’s questions
  • Problems
94

Calculating an optimal bid

  • Answers to this lesson’s questions
  • Problems
95

Simulating stock prices and asset-allocation modeling

  • Answers to this lesson’s questions
  • Problems
96

Fun and games: Simulating gambling and sporting-event probabilities

  • Answers to this lesson’s questions
  • Problems
97

Using resampling to analyze data

  • Answer to this lesson’s question
  • Problems
98

Advanced sensitivity analysis

  • Answer to this lesson’s question
  • Problems
99

Applications to Finance, Operations, and Marketing

100

Pricing stock options

  • Answers to this lesson’s questions
  • Problems
101

Determining customer value

  • Answers to this lesson’s questions
  • Problems
102

The economic order quantity inventory model

  • Answers to this lesson’s questions
  • Problems
103

Inventory modeling with uncertain demand

  • Answers to this lesson’s questions
  • Problems
104

Queuing theory: The mathematics of waiting in line

  • Answers to this lesson’s questions
  • Problems
105

Estimating a demand curve

  • Answers to this lesson’s questions
  • Problems
106

Pricing products by using tie-ins

  • Answer to this lesson’s question
  • Problems
107

Pricing products by using subjectively determined demand

  • Answers to this lesson’s questions
  • Problems
108

Nonlinear pricing

  • Answers to this lesson’s questions
  • Problems
109

Macros and Custom Functions

110

Recording macros

  • Answers to this lesson’s questions
  • Problems
111

The LET and LAMBDA functions and the LAMBDA helper functions

  • Answers to this lesson’s questions
  • Problems

1

Basic Formulas and Functions

  • Performing Mathematical Calculations using Formulas
  • Accumulating Data Using the VLOOKUP Function
2

The INDEX function

  • Extracting Data Using the INDEX Function
3

The MATCH function

  • Finding the Required Data Using the MATCH Function
4

Text functions and Flash Fill

  • Creating Email Addresses Using the Excel Text Functions
5

Dates and date functions

  • Calculating the Number of Workdays Using a Date Function
6

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

  • Computing Annual Sales Using the IF Function
7

Time and time functions

  • Calculating Race Timings Using the Time Functions
8

The net present value functions: NPV and XNPV

  • Calculating Net Present Value Using the NPV Function
9

More Excel financial functions

  • Determining Depreciation Using Excel Financial Functions
10

The Paste Special command

  • Using the Paste Special Command to Convert Data
11

Three-dimensional formulas and hyperlinks

  • Summarizing Data Using Three-Dimensional Formulas
12

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

  • Counting Cells with Criteria Using COUNTIF and COUNTIFS Functions
13

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

  • Calculating with Criteria Using the COUNTIF and SUMIF Functions
14

Summarizing data with histograms and Pareto charts

  • Creating Bin Ranges Using Histograms
15

Summarizing data with database statistical functions

  • Summarizing Data
16

Consolidating data

  • Consolidating Data
17

Creating subtotals

  • Creating a Subtotal using the SUBTOTAL Function
18

The OFFSET function

  • Using the OFFSET Function to Create Lagged Values
19

The INDIRECT function

  • Using the INDIRECT Function to Tabulate Data
20

Excel tables and table slicers

  • Using Excel Tables to Perform Calculations
21

Basic charting

  • Creating a Scatter Chart
22

Sparklines

  • Creating Sparklines
23

Importing data from a text file or document

  • Importing Data from a Text File
24

The Power Query Editor

  • Using the Power Query Editor to Transform Data
25

Sorting in Excel

  • Sorting Data
26

Array formulas and functions

  • Performing Calculations Using Array Functions and Formulas
27

Using PivotTables and slicers to describe data

  • Creating a PivotTable and PivotChart
28

The Data Model

  • Using the Distinct Count Option for Calculation
29

Using Solver to determine the optimal product mix

  • Determining the Profit-Maximizing Product Mix Using Solver
30

Using Solver to solve transportation or distribution problems

  • Finding an Optimal Solution Using Solver
31

Using Solver for capital budgeting

  • Obtaining Maximum NPV using Solver
32

Using Solver for financial planning

  • Determining the Monthly Payment Using Solver
33

The traveling salesperson problem

  • Solving the Traveling Salesperson Problem
34

Estimating straight-line relationships

  • Creating a Scatter Chart and Adding a Trendline
35

Modeling exponential growth

  • Creating an Exponential Trend Curve
36

The power curve

  • Creating a Power Curve
37

Using correlations to summarize relationships

  • Using Correlations to Find the Relationship Between Variables
38

Introduction to multiple regression

  • Using Multiple Regression to Find the Optimal Forecasting Equation
39

An introduction to random variables

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

The binomial, hypergeometric, and negative binomial random variables

  • Computing Binomial Probabilities
41

The Poisson and exponential random variable

  • Computing Poisson Distribution
42

The normal random variable and Z-scores

  • Calculating Z-Scores
43

Using the lognormal random variable to model stock prices

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

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

  • Determining Probability Using the Beta Random Variable
45

Using moving averages to understand time series

  • Creating a Moving Average Graph
46

Ratio-to-moving-average forecast method

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

The Winters method and the Forecast Sheet tool

  • Estimating Smoothing Constants
48

Introduction to Monte Carlo simulation

  • Simulating the Values of a Normal Random Variable
49

Calculating an optimal bid

  • Determining the Optimal Bid using Simulation
50

Simulating stock prices and asset-allocation modeling

  • Determining Asset Allocation
51

Fun and games: Simulating gambling and sporting-event probabilities

  • Simulating the Outcome of a Sporting Event
52

Using resampling to analyze data

  • Implementing Resampling
53

Advanced sensitivity analysis

  • Creating a Spider Plot
54

Pricing stock options

  • Using Formula Protection in a Worksheet
55

Determining customer value

  • Determining Customer Value
56

Inventory modeling with uncertain demand

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

Estimating a demand curve

  • Plotting a Linear Demand Curve
58

Pricing products by using subjectively determined demand

  • Finding the Optimal Price Using Subjectively Determined Demand

Related Courses

All Courses