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

Any questions?
Check out the FAQs

Still have unanswered questions and need to get in touch?

Contact Us Now

Microsoft Excel Data Analysis and Business Modeling

$279.99

Buy Now

Related Courses

All Courses