Course Description

This Microsoft Excel training consists of several major blocks:

• Excel for Beginners
• Excel for Intermediate users: Shortcuts, Navigation, Formatting tools, Functions
• Excel for Advanced users: Sophisticated functions, Financial modeling tools

In addition, we will provide you with two complete Case Studies. Everything that has been taught in the videos will be applied in practice. Step by step. WIth no steps skipped.

Excel is the #1 productivity software in the world. You need it if you want to get a job in Business Development, Corporate Finance, Consulting, Marketing, Data analysis, Accounting, Banking, and pretty much everywhere.

If you want to become…

• a Financial Analyst
• an Accountant
• an Auditor
• a Business Analyst
• a Financial Controller
• a Financial Manager
• a CFO
• a CEO
• an Investment Banker
• an Equity Research Analyst
• an Investor
• an Entrepreneur
• Someone who is involved with a business and would like to be successful

Then you simply have to learn Microsoft Excel. There is no way around it.

But how can you do that if you have no prior experience and you want to do it fast?

Don't worry!

Microsoft Excel 2016: Beginner, Intermediate and Advanced Training is here for you!

The most complete and comprehensive course you will find online.

This course is for professionals within the following business functions:

• Professionals working in Business and Finance
• Bankers, Financial analysts, Accountants, Financial controllers, Private equity analysts, BI analysts, Marketing analysts
• Anyone who uses Microsoft Excel in their job and would like to improve their skills significantly

1. Course Introduction - 4 Minutes

   1. What Does the Course Cover?
   2. Excel's "Tell Me" Search Bar

2. A quick introduction to the basics of Microsoft Excel 2016 - 35 Minutes

   1. Overview of Excel 2016
   2. Excel's basic features - Course notes
   3. The Excel 2016 Ribbon
   4. Working with Rows & Columns
   5. Data Entry in Excel
   6. Data-Entry-Exercise-Text
   7. Data-Entry-Solved
   8. Creating Formulas in Excel
   9. Formulas-Exercise-Text
   10. Formulas-Solved
   11. Introduction to Formatting
   12. Formatting-Exercise-Text
   13. Formatting-Solved
   14. Microsoft Excel at Its Best - Functions
   15. Functions-Exercise-Text
   16. Functions-Solved
   17. Work Efficiently By Using Cut, Copy & Paste
   18. Copy-Cut-Paste-Exercise-Text
   19. Copy-Cut-Paste-Solved
   20. Pasting Values, Formulas and Formats with Paste Special
   21. Copy-Cut-Paste-Exercise-Text
   22. Copy-Cut-Paste-Solved

3. Professional formatting sets you apart - 14 Minutes

   1. Let's start from scratch and create a P&L sheet
   2. Cell styles allows you to be faster…

4. Be 3.0x faster than average users - shortcuts and quick navigation tools - 20 Minutes

   1. The secret of fast scrolling: navigation and keyboard shortcuts
   2. Use multiple screens simultaneously
   3. F1 to F12 - Using Excel's function keys
   4. How to select visible cells only
   5. Beginner to Pro in Excel - Shortcuts for PC
   6. Beginner to Pro in Excel - Shortcuts for MAC

5. Best practices that will help you work in a professional way - 40 Minutes

   1. This Is How You Can Start Formatting Your Sheets in a Professional Way
   2. Fast Scrolling That Would Allow You to Be 3x Faster
   3. Fixing Cell References Properly
   4. Using the Alt plus Enter combination
   5. Learn How to Organize Your Excel Data with Text to Columns
   6. Wrapping Text in Excel
   7. Create Easily Printable Documents by Using Set Print Area
   8.  Find and Select Special Types of Cells with Select Special (F5)
   9.  Learn How to Assign Dynamic Names within a Model
   10. Assigning Custom Formats to Specific Cells (Ex. Multiples)
   11. Excel Formulas that Make Sense - Named Ranges
   12. Execute 50 Commands with a Single Click - Excel Macros
   13. Create a Drop-Down List by Using Data Validation
   14. Using Custom Sort in Order to Sort Multiple Columns within a Table
   15. Freeze the Title Row of Your Table - Freeze Panes
   16. Create a Great Index Page at the Beginning of Your Financial Models
   17. Useful tips & tools - PDF version

6. Key functions in Excel - 42 Minutes

   1. Key Functions in Excel: Count, Counta, Countif, Countifs
   2. Key Functions in Excel: Sum, Sumif, Sumifs
   3. Key Excel Functions: Average & Averageif
   4. Working with Text in Excel: Left, Right, Mid, Upper, Lower, Proper, Concatenate
   5. Find the Highest and the Lowest Values in a Range: Max & Min
   6. Transferring Data Efficiently: Vlookup & Hlookup
   7. Use Index & Match as a Substitute of Vlookup
   8. Create Flexible Financial Models with the Choose Function
   9. Include Round in Your Financial Models
   10. Iferror Allows You to Deal Easily with Error Mesages
   11. Excel functions - PDF

7. Building a Profit & Loss Statement from scratch - 60 Minutes

   1. Introduction to the Case Study
   2. This Is the data that we will use for our Exercise
   3. Let's Reorder our Excel sheet and create a clear structure!
   4. Order-a-worksheet-Unsolved
   5. Order-a-worksheet-Solved
   6. Introducing a Code: The best way to work with large amounts of data
   7. Creating-a-code-Unsolved
   8. Creating-a-code-Solved
   9. Finishing Touches of our Database
   10. Creating-a-database-Unsolved
   11. Creating-a-database-Solved
   12. Using Vlookup in Order to Transfer Data
   13. Vlookup-Unsolved
   14. Vlookup-Solved
   15. Applying Sumif in Order to Populate the Database with Numbers
   16. Sumif-Unsolved
   17. Sumif-Solved
   18. Demonstrating How Index & Match Can Successfully Replace Vlookup
   19. Index-Match-Unsolved
   20. Index-Match-Solved
   21. Mapping the Items Within Our Database - A Key Exercise For Financial Analysts
   22. Mapping-Unsolved
   23. Mapping-Solved
   24. Creating the Structure of the P&L Statement
   25. 11-Building-A-P-L- Unsolved
   26. Building-a-P-L-Solved
   27. Time for Some Formatting! The Secret of Creating Awesome Looking Excel Sheets
   28. Formatting-the-P-L-Unsolved
   29. Formatting-the-P-L-sheet-Solved
   30. Populating the P&L sheet with figures
   31. Populating-the-P-L-sheet-Unsolved
   32. Populating-the-P-L-sheet-Solved
   33. How do We Find Mistakes in the easiest way possible?
   34. Countif-Unsolved
   35. Countif-Solved
   36. Finishing Touches: Calculating Year-on-Year Growth and Percentage Incidence
   37. Adding-variations-Unsolved
   38. Adding-variations-Solved

8. Invaluable tools that will turn you into a proficient user - 27 Minutes

   1. Include Sensitivity Analysis In Your Model Through Data Tables
   2. Find the Result that You Are Looking For with Goal Seek
   3. Highlight key data with Excel Conditional Formatting
   4. Recording Macros - The perfect way to avoid repetitions
   5. Formatting Cells Part I - Working with data in Excel
   6. Formatting Cells Part II - Customize numbers the way you like
   7. Filter by color

9. Excel mechanics - A collection of indispensable tools - 28 Minutes

   1. Grouping Excel rows and columns - The correct way to do it!
   2. Working on multiple sheets at the same time
   3. Find & Replace - Our favourite Excel tool
   4. A great way to apply Find & Replace
   5. What Are Circular References in Excel?
   6. Circular References - An example
   7. Trace precedents - Display the relationship between formulas and cells

10. Advanced Excel functions - Part 1 - 35 Minutes

    1. What is a nested function?
    2. Advanced Excel functions: Index, Match, Match
    3.  Advanced Excel functions: Indirect; Vlookup & Indirect
    4. Advanced Excel functions: Rows; Columns; Vlookup & Columns
    5. Advanced Excel functions: Vlookup & Match
    6. Advanced Excel functions: Choose; Vlookup & Choose
    7. Advanced Excel functions: Offset; Offset & Match

11. Advanced Excel Functions - Part 2 - 30 Minutes

    1. Financial functions: FV and PV 
    2. Discounting Cash flows and calculating Net Present Value (NPV)
    3. Financial functions: IRR
    4. Financial functions: PMT and building a complete loan schedule
    5. Date functions

12. Excel Tips & Tricks - 27 Minutes

    1. Excel tips & tricks: Part 1
    2. Excel tips & tricks: Part 2
    3. Excel tips & tricks: Part 3
    4. Excel tips & tricks: Part 4

13. Financial modeling fundamentals - 23 Minutes

    1. What is a financial model?
    2. Why use a financial model?
    3. Financial modeling Don'ts - Worst practices we should avoid
    4. Financial modeling Do's - This is what solid modelers do
    5. The different types of models
    6. What is the right level of detail in a financial model?

14. Forecasting future financials - A cornerstone of financial modeling - 20 Minutes

    1. Forecasting guidelines: apply these principles when forecasting financials 
    2. How to build a complete model
    3. Forecasting Income statement financials 
    4. Forecasting Balance sheet financials - Part one
    5. Forecasting Balance sheet financials - Part two

15. Building a complete 3-statement model - 30 Minutes

    1. Introduction to the Exercise
    2. Let's create a mapping of financials 
    3. Building an output P&L sheet
    4. Filling in the output P&L sheet with historical financials 
    5. Calculating percentage variances and applying conditional formatting 
    6. Building an output Balance sheet
    7. Adding the forecast period
    8. Using Index, Match, Match to fill in the output Balance sheet

16. Let's build a flexible model with multiple scenarios - 18 Minutes

    1. Calculating historical percentage ratios and use Index and Match for scenarios
    2. Building a flexible model with Choose & Match
    3. Building a flexible model with Vlookup & Columns
    4. Calculating historical DSO, DPO, DIO, Other assets %, and Other liabilities %
    5. Forecasting DSO, DPO, DIO, Other assets and Other liabilities 

17. Completing the 3-statement model - 25 Minutes

    1. Building a Fixed asset roll forward
    2. Building a Financial liabilities schedule
    3. Building an Equity schedule 
    4. Preparing a Cash flow structure
    5. Calculating Cash flows and completing the model & Congratulations

18. Excel's Pivot Tables in depth - 20 Minutes

    1. Pivot Tables: An Introduction
    2. Here is how you can create Pivot Tables
    3. Improving the formatting of Pivot Tables
    4. Obtaining the desired Pivot Table
    5. GetPivotData is essential when you work with Pivot Tables
    6. An Introduction to Slicers - The Moden Day Pivot Table Filters

19. Building an FMCG Model from Scratch - 40 Minutes

    1. Introduction to the exercise
    2. Exercise-Build-an-FMCG-Model-before
    3. Working with an SAP data extraction
    4. What we will have to do with the data before starting the exercise
    5. Creating an output sheet
    6. Exercise-Build-an-FMCG-Model-Structure
    7. Working on the layout of the FMCG report
    8. Exercise-Build-an-FMCG-Model-Layout
    9. Working faster through calculation automations
    10. Еxercise-Build-an-FMCG-Model-Adding-Calculations
    11. Here is how we can create a Master Pivot Table
    12. Exercise-Build-an-FMCG-Model-Creating-a-Pivot-Table
    13. GetPivotData is awesome! This is how we can extract data from the Master Pivot Table
    14. Exercise-Build-an-FMCG-Model-GETPIVOTDATA
    15. The combination of Slicers and GetPivotData - a hidden gem!
    16. Exercise-Build-an-FMCG-Model-Inserting-Slicers
    17. Improving the layout of your Pivot Table slicers
    18. Exercise-Build-an-FMCG-Model-Complete
    19. Here is how top management will use the report
    20. Exercise-Build-an-FMCG-Model-Complete