Functions, Data Analysis, Presentation And Finance W/ Excel

Using Excel, Power Pivot and Power BI to Build Advanced Functions, Pivot Tables and Financial Models

What you'll learn
Create and write advanced functions.
Create charts and buttons
Sort and filter with regular and advanced filters
Build pivot tables and calculated columns
Use randomness for model prediction
Learn to use the Data Model and Power BI.
Requirements
None
Description
This course teaches Microsoft Excel, Power Pivot and Power BI. It starts from the beginning and goes all the way through to (and including) VBA. Excel is an extremely powerful tool, one which we will see can be used to understand, analyze, and present data. It can be used to build financial models, do data analysis via data science, and much more. It is an all-encompassing tool that blends analysis with presentation (using pivot tables and the data model). We will go through buttons, charts, data validation and conditional formatting, as well as all the regular functions. We cover every tool in Excel, starting from the basics, and review each many times. The general structure of the course includes many practice examples that allow a student to either follow along with a PDF or to follow along with a video. The concepts are put together to show full ideas, giving students the chance to integrate various different meta-analysis tools to build models and explore data.It is based on a well-worn approach of practice, practice, and more practice. Each section has videos and written instructions to hone students' skills. The beginning of the course includes about twelve different "homework" sections, then the course goes on to a college level instruction, followed by two sections that give consolidated practice via "worksheets" and "a lesson a day" type structure. After this, various topic are discussed such as Monte Carlo as used in various pricing models, etc. There is particular focus on financial models since business applications are very important in Excel. There are also many sections that work through VBA - visual basic for applications to enhance the students understanding and develop further advanced methods.

Overview

Section 1: Introduction

Lecture 1 *Introduction

Lecture 2 *How to Maximize this Course

Section 2: HW #1: Excel Tools and Organization

Lecture 3 *Basics

Lecture 4 *Introduction to Functions and Inputting Data

Lecture 5 Assistance with Section 1a

Lecture 6 Assistance with Section 1b

Lecture 7 Assistance with Section 2

Section 3: HW #2: Functions and All They are Cracked Up To Be

Lecture 8 *Basics

Lecture 9 Assistance with Section 1

Lecture 10 Assistance with Section 2

Lecture 11 Assistance with Section 3

Section 4: HW #3: Buttons and Path Generation

Lecture 12 *Basics: Stochastic Paths and an Intro to VBA

Lecture 13 Assistance with Section 1

Lecture 14 Assistance with Section 2

Lecture 15 Assistance with Section 3

Section 5: HW #4: Interactive Charts

Lecture 16 *Basics

Lecture 17 Assistance with Section 1

Lecture 18 Assistance with Section 2

Lecture 19 Assistance with Section 3

Section 6: HW #5: Creating Data in Excel with VBA

Lecture 20 *Basics

Lecture 21 Assistance with Section 1

Lecture 22 Assistance with Section 2

Lecture 23 Assistance with Section 3

Lecture 24 Assistance with Section 4

Section 7: HW #6: Financial Functions

Lecture 25 *Basics

Lecture 26 Interactive Stock Chart LookUp

Lecture 27 PMT and FV Functions

Lecture 28 Another PMT, Data Table, Goal Seek and Solver Example

Lecture 29 Bonds and Duration

Lecture 30 Amortization Table 1

Lecture 31 Amortization Table 2

Section 8: HW #7: Formatting and Financial Data

Lecture 32 *Basics

Lecture 33 Assistance with Section 1

Lecture 34 Assistance with Section 2

Lecture 35 Assistance with Section 3

Section 9: HW #8: Random Numbers, Monte Carlo and Stock Returns

Lecture 36 *Basics: Randomness and Returns

Lecture 37 Random Numbers

Lecture 38 Linear Congruential Generator

Lecture 39 Monte Carlo Type 1

Lecture 40 Monte Carlo Type 2

Lecture 41 Box Muller

Lecture 42 Stock Bonds Returns

Lecture 43 Multi Asset Portfolio

Section 10: HW #9: The "IF" Functions, Database Functions, Filters, and Trendlines

Lecture 44 *Basics

Lecture 45 CountIf and AverageIf Examples

Lecture 46 Database Functions

Lecture 47 Advanced Filter

Lecture 48 Practice Worksheet

Lecture 49 Creating Trendlines with TREND

Lecture 50 Evaluating Trendlines

Lecture 51 Statistical Analysis in Excel

Section 11: HW #10: Data Validation and More VBA

Lecture 52 *Basics

Lecture 53 Introduction

Lecture 54 Data Validation 1

Lecture 55 Data Validation 2

Lecture 56 Using Data Tables for Advanced Decisions

Lecture 57 Filling in a Column with Advanced Choices

Lecture 58 Using VBA to Set Information in Columns

Lecture 59 Nested Formulas

Lecture 60 Vlookup

Lecture 61 Iferror Function

Lecture 62 Creating a Unique ID with a Hash Function

Lecture 63 Practice Worksheet

Section 12: HW #11: Charting and Grouping

Lecture 64 *Basics

Lecture 65 Assistance with Charting

Lecture 66 Grouping Chart Data

Lecture 67 Correlation Rank and Data Bars

Lecture 68 Grouping with Arrays

Lecture 69 Grouping with Pivot Tables

Section 13: HW #12: Pivot Tables

Lecture 70 *Basics

Lecture 71 Importing Data

Lecture 72 Looking at the Data Model

Lecture 73 Tables and Pivot Tables

Lecture 74 Pivot Tables

Lecture 75 Clustering Data

Lecture 76 Clustering Example

Section 14: Excel Tutorial/Summary/Review

Lecture 77 Introduction

Lecture 78 Names

Lecture 79 Sorting and Filtering

Lecture 80 Functions

Lecture 81 Reviewed Examples with IF Function

Lecture 82 Text

Lecture 83 Dates

Lecture 84 Countif, Sumif, Averageif

Lecture 85 Tables, Database Functions, Advanced Lookup, Grouping and Subtotal

Lecture 86 Vlookup, Lookup, Index/Match

Lecture 87 Specialized Lookup Examples

Lecture 88 Financial Formulas

Lecture 89 Arrays

Lecture 90 Charts and Trendlines

Lecture 91 Pivot Tables

Lecture 92 Conditional Formatting and Data Validation

Lecture 93 Data Table and Solver

Section 15: Worksheets

Lecture 94 Worksheet #1a

Lecture 95 Worksheet #1b

Lecture 96 Worksheet #2

Lecture 97 Worksheet #3

Lecture 98 Worksheet #4

Lecture 99 Worksheet #5

Lecture 100 Worksheet #6

Lecture 101 Worksheet #7

Lecture 102 Worksheet #8

Section 16: 10 Days of Excel Projects

Lecture 103 Day 1

Lecture 104 Day 2

Lecture 105 Day 3

Lecture 106 Day 4

Lecture 107 Day 5

Lecture 108 Day 6

Lecture 109 Day 7

Lecture 110 Day 8

Lecture 111 Day 9

Lecture 112 Day 10

Section 17: Monte Carlo

Lecture 113 *Basics

Lecture 114 Full Discussion of Random Numbers and Applications

Lecture 115 Basics of Monte Carlo - Concept

Lecture 116 Basics of Monte Carlo - Excel

Lecture 117 M.C. Example with Options (and done in VBA with Data Tables)

Lecture 118 Monte Carlo with Options

Lecture 119 Monte Carlo in Depth (Advanced)

Lecture 120 Delta Hedging VS Stop Loss, Euler Method for Pricing Options (Advanced)

Section 18: The Data Model: Example 1

Lecture 121 Loading Data and Calculated Columns

Lecture 122 Basic DAX Functions and Pivot Tables

Lecture 123 The X (iterator) functions and Calculate

Lecture 124 VALUES, ALL, FILTER

Section 19: The Data Model: Example 2

Lecture 125 Adding Basic Measures

Lecture 126 CALCULATE and ALL

Lecture 127 Related and Unrelated Tables

Section 20: Power BI: Intro Examples

Lecture 128 Intro Example 1

Lecture 129 Intro Example 2a

Section 21: Data and Visualizations Overview

Lecture 130 Visualizations 1: Choosing an Effective Visual

Lecture 131 Visualizations 2: Getting Rid of Clutter and Focusing Attention

Lecture 132 Visualizations 3: General Design

Lecture 133 Doughnut Charts

Section 22: Advanced Excel Review

Lecture 134 *Basics

Lecture 135 Dynamic Comparison

Lecture 136 Nested Ifs

Lecture 137 Drop-down list and dynamic chart with checkboxes

Lecture 138 Option Button

Lecture 139 Nested ifs, option buttons, lookups

Lecture 140 Stochastic Path Generation choosing MU and SIGMA - Buttons

Lecture 141 Stochastic Path Generation and Check Buttons

Lecture 142 Spin Buttons and Loans

Lecture 143 ComboBox Excel

Lecture 144 Solver Example

Lecture 145 Data Tables, Uniform and Normal Random Numbers

Lecture 146 Forecast Sheet and Analyze Data

Section 23: VBA Slides, Concepts, and Introduction

Lecture 147 General Slides Introduction to VBA

Lecture 148 General Slides Intro to Decisions and Loops

Lecture 149 General Slides Intro to Arrays

Lecture 150 General Introduction to Buttons and Ranges

Lecture 151 General Introduction to Financial Applications with VBA

Section 24: VBA Overview

Lecture 152 Intro Discussion of VBA

Lecture 153 General Overview of VBA #1

Lecture 154 General Overview of VBA #2

Lecture 155 Basics of VBA

Lecture 156 Many ways to do the same thing

Lecture 157 For, while and if practice examples

Lecture 158 Introduction to Arrays in VBA

Lecture 159 Practice with Arrays 1

Lecture 160 Wide Ranging Example with VBA

Lecture 161 Lots of Practice Steps

Lecture 162 VBA Functions Examples #1

Lecture 163 VBA Functions Examples #2

Lecture 164 Sorting

Lecture 165 Functions and Sorting Practice

Lecture 166 Userforms (1)

Lecture 167 Userforms (2)

Lecture 168 Userforms (3)

Section 25: VBA Practice

Lecture 169 Practice Set 1

Lecture 170 Practice Set 2

Lecture 171 Practice Set 3

Lecture 172 Practice Set 4

Lecture 173 Practice Set 5

Section 26: Statistics and Analysis

Lecture 174 Basic Probability

Lecture 175 Analysis Example - EPA

Section 27: Financial Analysis

Lecture 176 Regression (1)

Lecture 177 Regression (2)

Lecture 178 Arrays, Linear Algebra, Bonds with Data Tables and Solver

Lecture 179 Regression (3)

Lecture 180 Portfolio Optimization 1

Lecture 181 Portfolio Optimization 2

Lecture 182 Pricing Swaps

Section 28: Final Project Ideas

Lecture 183 Ideas for Final Projects - Teaching

Lecture 184 Ideas for Final Projects - Business

Lecture 185 Ideas for VBA 1

Lecture 186 Ideas for VBA 2

Lecture 187 Assistance with a Cover Page

Section 29: Multiple Choice Review Section

Lecture 188 Review for Test 1: a

Lecture 189 Review for Test 1: b

Lecture 190 Review for Test 1: c

Lecture 191 Review for Test 2: a

Lecture 192 Review for Test 2: b

Lecture 193 Review for Test 2: c

Lecture 194 Review for Test 2: d

Lecture 195 Review of VBA for Test

Everyone