CCNA 200-301 Pearson uCertify Network Simulator
Cisco 200-301-SIMULATOR.AB1
CCNA 200-301 Pearson uCertify Network Simulator
ISBN: 978-1-61691-837-8Cisco 200-301-SIMULATOR.AB1
Microsoft Excel Data Analysis And Business Modeling
Learn the core concepts of MS data analysis and business modeling to develop the right skills for transforming worksheet data into actionable insights.
(XLS-DA-BM.AB1) / ISBN : 978-1-64459-368-4This course includes
Lessons
TestPrep
Hands-On Labs
AI Tutor (Add-on)
313
Review
About This Course
In today’s data-driven world, every business (big or small) thrives on its ability to transform data into action items. This Excel Business Modeling and Data Analysis course teaches you how to use MS Excel for analyzing data and building financial models that help businesses make informed decisions. Other than learning basic excel formulas, functions, data manipulation and chart creation, you’ll also learn how to use data analysis tools like PivotTables, data tables, scenario analysis (what-if situations), and data consolidation. Use of analytical techniques for building financial models like budgets and forecasts is also covered in the Excel data analysis training course. To make it a fun learning experience for you, we have also included interactive activities like hand-on labs, flashcards, and quizzes along with the course content.
Skills You’ll Get
- Translating Excel basics to relatable analytics
- Using Power Query for cleaning and connecting data
- Create custom functions with LAMBDA (without VBA)
- Utilizing new charts and data types for visualizing data like a pro
- Utilizing data manipulation techniques like advanced XLOOKUP function
- Using 3D Maps for highlighting geographical trends
- Confidently building powerful business data models
Get the support you need. Enroll in our Instructor-Led Course.
Interactive Lessons
97+ Interactive Lessons | 180+ Exercises | 188+ Quizzes | 111+ Flashcards | 111+ Glossary of terms
Gamified TestPrep
60+ Pre Assessment Questions | 60+ Post Assessment Questions |
Hands-On Labs
60+ LiveLab | 60+ Video tutorials | 02:12+ Hours
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
2
Basic worksheet modeling
- Performing Mathematical Calculations using Formulas
4
Lookup functions
- Accumulating Data Using the VLOOKUP Function
5
The INDEX function
- Extracting Data Using the INDEX Function
6
The MATCH function
- Finding the Required Data Using the MATCH Function
7
Text functions and Flash Fill
- Creating Email Addresses Using the Excel Text Functions
8
Dates and date functions
- Calculating the Number of Workdays Using a Date Function
9
IF, IFERROR, IFS, CHOOSE, SWITCH, and the IS functions
- Computing Annual Sales Using the IF Function
10
Time and time functions
- Calculating Race Timings Using the Time Functions
11
The net present value functions: NPV and XNPV
- Calculating Net Present Value Using the NPV Function
13
More Excel financial functions
- Determining Depreciation Using Excel Financial Functions
15
The Paste Special command
- Using the Paste Special Command to Convert Data
16
Three-dimensional formulas and hyperlinks
- Summarizing Data Using Three-Dimensional Formulas
21
The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK functions
- Counting Cells with Criteria Using COUNTIF and COUNTIFS Functions
22
The SUMIF, AVERAGEIF, SUMIFS, AVERAGEIFS, MAXIFS, and MINIFS functions
- Calculating with Criteria Using the COUNTIF and SUMIF Functions
23
Summarizing data with histograms and Pareto charts
- Creating Bin Ranges Using Histograms
25
Summarizing data with database statistical functions
- Summarizing Data
26
Consolidating data
- Consolidating Data
27
Creating subtotals
- Creating a Subtotal using the SUBTOTAL Function
28
The OFFSET function
- Using the OFFSET Function to Create Lagged Values
29
The INDIRECT function
- Using the INDIRECT Function to Tabulate Data
32
Excel tables and table slicers
- Using Excel Tables to Perform Calculations
33
Basic charting
- Creating a Scatter Chart
36
Sparklines
- Creating Sparklines
37
Importing data from a text file or document
- Importing Data from a Text File
38
The Power Query Editor
- Using the Power Query Editor to Transform Data
40
Sorting in Excel
- Sorting Data
42
Array formulas and functions
- Performing Calculations Using Array Functions and Formulas
46
Using PivotTables and slicers to describe data
- Creating a PivotTable and PivotChart
47
The Data Model
- Using the Distinct Count Option for Calculation
51
Using Solver to determine the optimal product mix
- Determining the Profit-Maximizing Product Mix Using Solver
53
Using Solver to solve transportation or distribution problems
- Finding an Optimal Solution Using Solver
54
Using Solver for capital budgeting
- Obtaining Maximum NPV using Solver
55
Using Solver for financial planning
- Determining the Monthly Payment Using Solver
59
The traveling salesperson problem
- Solving the Traveling Salesperson Problem
60
Estimating straight-line relationships
- Creating a Scatter Chart and Adding a Trendline
61
Modeling exponential growth
- Creating an Exponential Trend Curve
62
The power curve
- Creating a Power Curve
63
Using correlations to summarize relationships
- Using Correlations to Find the Relationship Between Variables
64
Introduction to multiple regression
- Using Multiple Regression to Find the Optimal Forecasting Equation
70
An introduction to random variables
- Using Variance and Standard Deviation to Measure the Spread of Data
71
The binomial, hypergeometric, and negative binomial random variables
- Computing Binomial Probabilities
72
The Poisson and exponential random variable
- Computing Poisson Distribution
73
The normal random variable and Z-scores
- Calculating Z-Scores
74
Using the lognormal random variable to model stock prices
- Calculating the Future Price of a Stock Using a Lognormal Variable
75
Weibull and beta distributions: Modeling machine life and duration of a project
- Determining Probability Using the Beta Random Variable
76
Using moving averages to understand time series
- Creating a Moving Average Graph
77
Ratio-to-moving-average forecast method
- Using the Ratio-to-Moving-Average Forecasting Method
79
The Winters method and the Forecast Sheet tool
- Estimating Smoothing Constants
81
Introduction to Monte Carlo simulation
- Simulating the Values of a Normal Random Variable
82
Calculating an optimal bid
- Determining the Optimal Bid using Simulation
83
Simulating stock prices and asset-allocation modeling
- Determining Asset Allocation
84
Fun and games: Simulating gambling and sporting-event probabilities
- Simulating the Outcome of a Sporting Event
85
Using resampling to analyze data
- Implementing Resampling
86
Advanced sensitivity analysis
- Creating a Spider Plot
87
Pricing stock options
- Using Formula Protection in a Worksheet
88
Determining customer value
- Determining Customer Value
90
Inventory modeling with uncertain demand
- Determining the Economic Order Quantity (EOQ)
- Determining the Reorder Point
92
Estimating a demand curve
- Plotting a Linear Demand Curve
94
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 NowBesides working on the latest excel features, you’ll also learn to use advanced tools like XLOOKUP and LAMBDA functions.
Most businesses (big or small) need meaningful data insights for making informed decisions, which is why there is a growing need of skilled Data Analysts across all industry verticals. With uCetify’s Microsoft Excel Data Analysis and Business Modeling course you can develop the right skill set needed for this job profile.
At uCertify, we provide 24/7 tech support to the users for all of our courses.
Definitely Yes. All those wanting to upskill themselves and learn advanced excel tools for data manipulation and analysis will benefit greatly from this course.
Completing this course will open doors to some exciting job opportunities like data analyst, financial analyst, management consultant, business, market research analyst, supply chain analyst, human resource analyst, and data journalist.
