Essential Excel for Business Analysts and Consultants

Focused View

Asen Gyczew

5:22:20

0 View

01 - Introduction

001 Introduction.mp4

04:16

002 About Me.mp4

01:47

003 What to do if a blurry image appears.mp4

02:04

004 How to find additional resources.encrypted.mp4

01:06

004 how to find additional resources.encrypted.zip

005 How to make the most out of the course.mp4

02:07

external-links.txt

02 - How to merge data from different sources

001 Essential-Excel-v27-upload-version.pdf

001 How to merge data from different sources.mp4

01:19

002 VLOOKUP Introduction.mp4

03:46

003 VLOOKUP with approximate match Introduction.mp4

02:33

004 What you can use the VLOOKUP for.mp4

01:38

005 Basic VLOOKUP usage.mp4

03:20

005 Basic-vlookup-Empty.xlsx

005 Basic-vlookup-Solution.xlsx

006 Some comments on basic VLOOKUP.mp4

01:58

006 Some-comments-on-vlookup-Empty.xlsx

006 Some-comments-on-vlookup-Solution.xlsx

007 How to assign categories using VLOOKUP.mp4

02:58

007 How-to-assign-categories-using-VLOOKUP-Empty.xlsx

007 How-to-assign-categories-using-VLOOKUP-Solution.xlsx

008 How to use HLOOKUP.mp4

01:37

008 How-to-use-HLOOKUP-Empty.xlsx

008 How-to-use-HLOOKUP-Solution.xlsx

009 The logic of MATCH function.mp4

02:32

010 Assigning categories based on 2 criteria using MATCH and VLOOKUP function.mp4

03:52

010 Startup-advisor-2-criteria-with-Match-Empty.xlsx

010 Startup-advisor-2-criteria-with-Match-Solution.xlsx

011 Assigning categories based on 2 criteria using HLOOKUP and VLOOKUP function.mp4

02:44

011 Startup-advisor-2-criteria-HLOOKUP-Empty.xlsx

011 Startup-advisor-2-criteria-HLOOKUP-Solution.xlsx

012 Assigning categories based on 2 criteria using VLOOKUP only.mp4

04:17

012 Startup-advisor-2-criteria-only-VLOOKUP-Empty.xlsx

012 Startup-advisor-2-criteria-only-VLOOKUP-Solution.xlsx

013 How to assign categories based on 3 and more criteria using VLOOKUP.mp4

04:46

013 Startup-advisor-many-criteria-Empty.xlsx

013 Startup-advisor-many-criteria-Solution.xlsx

014 How to assign categories using SUMIFS function Basic Usage.mp4

04:50

014 SUMIFS-for-many-criteria-Empty.xlsx

014 SUMIF-for-many-criteria-Solution.xlsx

015 Assigning customers to segments Case Introduction.mp4

03:03

016 Assigning customers to segments Data Available.mp4

01:56

016 Segmentation-using-Sumifs-Empty.xlsx

017 Assigning customers to segments Solution.mp4

04:38

017 Segmentation-using-Sumifs-Solution-v2.xlsx

018 The logic of INDEX function.mp4

02:48

019 INDEX & MATCH function Introduction.mp4

04:21

020 INDEX & MATCH function Exercise Case Introduction.mp4

01:09

020 Index-function-v2-Empty.xlsx

021 INDEX & MATCH function Exercise Case Solution.mp4

05:50

021 Index-function-v2.xlsx

03 - Example of analysis requiring merging data

001 Fuel usage analysis using the vlookup function - intro.mp4

03:46

001 Fuel-consumption-empty.xlsx

002 Fuel usage analysis using the vlookup function - tips.mp4

05:04

003 Fuel usage analysis using the vlookup function - solution.mp4

02:31

003 Fuel-consumption-Solution-v2.xlsx

003 Fuel-consumption-Solution-with-Slicer-and-charts-v2.xlsx

003 Selected-slide-fuel-usage.pptx

04 - Cleaning and unifying data

001 What problems you may face with data.mp4

02:19

002 Assign-categories-using-string-function-Solutionv2.xlsx

002 How to assign categories using string related functions.mp4

03:11

003 Dividing-Empty.xlsx

003 Dividing-Solution.xlsx

003 How to divide 1 data into 2.mp4

02:54

004 How to join 2 data into 1.mp4

01:23

004 Join-Empty.xlsx

004 Join-Solution.xlsx

005 How you can standarize data.mp4

04:01

005 Standardize-data-Empty.xlsx

005 Standardize-data-Solutionv2.xlsx

006 Example of unifying data - Saturating existing markets Introduction.mp4

01:11

007 Saturating existing markets Data for the case study.mp4

03:12

007 Saturation-point-analysis-v4-Empty.xlsx

007 Saturation-point-analysis-v4-Empty-with-structure.xlsx

008 Saturating existing markets Number of Stores.mp4

03:09

008 Saturation-point-analysis-v4.xlsx

009 Saturating existing markets Stores by concepts.mp4

03:04

010 Saturating existing markets Concept & Space.mp4

03:46

011 Saturating existing markets Solution in Power Point.mp4

01:57

012 Saturating existing markets WaterFall Graph.mp4

03:44

012 Saturation-point-analysis-v4-with-a-waterfall.xlsx

05 - How to use IF function and what you can do instead

001 Why NOT to use IF function.mp4

01:56

002 How to use IF function.mp4

01:43

002 Motivation-system-with-IFs.xlsx

003 How to use VLOOKUP instead of IF function.mp4

03:45

003 Motivation-system-VLOOKUP-instead-of-IF-Empty.xlsx

003 Motivation-system-VLOOKUP-instead-of-IF-Solution.xlsx

004 How to use IFERROR function.mp4

02:11

004 Motivation-system-IFERROR-Empty.xlsx

004 Motivation-system-IFERROR-Solution.xlsx

005 How to use MAX and MIN instead of IF function.mp4

04:08

005 Motivation-system-without-Max-and-Min-Empty.xlsx

005 Motivation-system-without-Max-and-Min-Solution.xlsx

06 - Pivot Tables

001 Pivot tables - introduction.mp4

01:24

002 How to use pivot tables.mp4

05:28

002 Sales-desnsity-analysisv5-Empty.xlsx

002 Sales-desnsity-analysisv5-Solution.xlsx

003 How to use pivot charts.mp4

02:39

003 Sales-desnsity-analysisv5-Pivot-Charts-Empty.xlsx

003 Sales-desnsity-analysisv5-Pivot-Charts-Solution.xlsx

003 Sales-desnsity-analysisv5-Pivot-Charts-Solution-Mac.xlsx

004 How to combine pivot tables with regular calculations.mp4

02:17

004 Sales-desnsity-analysisv5-Add-Calculation-Empty.xlsx

004 Sales-desnsity-analysisv5-Add-Calculation-Solution.xlsx

005 How to use slicers.mp4

02:07

005 Sales-desnsity-analysisv5-Slicer-Empty.xlsx

005 Sales-desnsity-analysisv5-Slicer-Solution.xlsx

006 How to replace pivot tables with SUMIFS and COUNTIFS.mp4

03:17

006 Sales-desnsity-analysisv5-Sumifs-Empty.xlsx

006 Sales-desnsity-analysisv5-Sumifs-Solution.xlsx

007 Going from general to specific with pivots.mp4

01:57

007 Sales-desnsity-analysisv5-General-to-specific-Empty.xlsx

007 Sales-desnsity-analysisv5-General-to-specific-Solution.xlsx

008 Example of analyses using pivot tables- Analysis of sales channels for FMCG.mp4

03:06

008 Sales-channel-FMCGv2.xlsx

008 Sales-channel-FMCGv3-fixed-values.xlsx

external-links.txt

07 - Other useful functions and tools in Excel

001 Other useful functions and tools in Excel.mp4

01:41

002 Conditional Formating.mp4

04:43

002 Conditional-formating-Empty-v2.xlsx

002 Conditional-formating-Solution-v2.xlsx

003 How to use indirect function.mp4

03:12

003 Indirect-Hours-empty-v2.xlsx

003 Indirect-Hours-solutionv4.xlsx

004 Histograms.mp4

02:49

004 Histogram-Empty-v4.xlsx

004 Histogram-Solution-v4.xlsx

005 Regression - introduction.mp4

02:23

006 Regression - example.mp4

06:03

006 Sales-analysis-regression-Empty-v2.xlsx

006 Sales-analysis-regression-Solution-v2.xlsx

007 Data validation.mp4

03:01

007 Data-validation-Empty-v2.xlsx

007 Data-validation-Solution-v2.xlsx

008 How to use SUMPRODUCT function.mp4

05:45

008 Sumproduct-function-Empty.xlsx

008 Sumproduct-function-Solution.xlsx

009 Using solver Planning the flow of finished goods Introduction.mp4

00:33

010 Planning-the-flows-using-Solver-Option-1.xlsx

010 Using solver Planning the flow of finished goods Excel - First Option.mp4

06:13

011 Planning-the-flows-using-Solver-Option-2.xlsx

011 Planning-the-flows-using-Solver-Option-2-Solution.xlsx

011 Using solver Planning the flow of finished goods Excel - Second Option.mp4

03:02

012 When can I retire Introduction.mp4

01:23

013 Retiringv2.xlsx

013 When can I retire No passive income Excel Basic.mp4

05:17

014 When can I retire No passive income Go seek function.mp4

03:05

015 When can I retire With passive income.mp4

02:17

016 What will be the effect of the price increase Introduction.mp4

02:09

017 Coffee-shop-price-increasev2.xlsx

017 What will be the effect of the price increase Solution Impact on coffee only.mp4

04:01

018 What will be the effect of the price increase Solution Full impact.mp4

02:34

external-links.txt

08 - Examples of analyses

001 Examples of analyses in Excel.mp4

01:21

002 Sales channel analysis - case in FMCG.mp4

03:06

002 Sales-channel-FMCGv2.xlsx

003 Efficiency of marketing activities.mp4

08:20

003 Marketing-efficiencyv2.xlsx

004 Model of E-commerce.mp4

09:11

004 e-commerce-EN.xlsx

005 Expansion strategy into other countries Case Introduction.mp4

02:17

006 Expansion strategy into other countries Data for the case study.mp4

03:44

006 Expansion-ranking-of-countriesv4-Empty.xlsx

007 Expansion strategy into other countries Which function to use.mp4

04:53

007 Expansion-ranking-of-countriesv4.xlsx

008 Expansion strategy into other countries Solution in Excel.mp4

03:32

008 Expansion-ranking-of-countriesv4.xlsx

009 Expansion strategy into other countries Solution in Power Point.mp4

02:30

009 Expansion-ranking-of-countriesv4.xlsx

010 How to create a waterfall chart.mp4

03:44

010 Saturation-point-analysis-v4.xlsx

011 How to create scatter plot with Names.mp4

03:12

011 Ranking-of-new-product-for-developmentv2.xlsx

011 Ranking-of-new-product-for-developmentv2-with-scatter-chart.xlsx

012 Data Visualization.html

012 Data-Visualization-v19-upload-version.pdf

external-links.txt

09 - Basic VBA usage - example

001 Distribution-system-simulation-Introduction-and-tasks-v4.docx

001 Logistics system simulation - introduction.mp4

04:28

001 distribution-system-simulation-empty.zip

002 Logistic system simulation - tips how to do it.mp4

08:23

002 distribution-system-simulation-partial-result-v2.zip

003 Logistic system simulation - final solution.mp4

08:33

003 distribution-system-simulation-final-solutionv3.zip

external-links.txt

10 - Essential Excel shortcuts

001 Excel shortcuts - Why it makes sense to learn them.mp4

01:05

002 Essential shortcuts Introduction.mp4

01:52

003 No mouse rule.mp4

01:27

004 How to format cells using shortcuts Text Part 1.mp4

01:45

004 Marketing-efficiencyv2-empty.xlsx

004 Marketing-efficiencyv2-ready.xlsx

006 How to format cells using shortcuts Numbers.mp4

03:11

007 Copying of cells with shortcuts.mp4

02:31

008 Selecting rows and selecting columns.mp4

02:38

009 Adding and removing rows and columns.mp4

02:18

010 Grouping rows and columns.mp4

03:12

011 How to preserve your results or go back to previous ones.mp4

02:39

012 Going fast around the Excel with shortcuts.mp4

02:19

013 Selecting area with shortcuts.mp4

01:22

014 Search and replace data.mp4

01:00

015 Shortcuts test.html

11 - More advanced shortcuts

001 More advanced shortcuts Introduction.mp4

00:51

002 Shortcuts hidden in Functional Keyboards.mp4

02:13

003 Hyperlinks.mp4

02:00

004 Working with data in a table.mp4

01:26

005 How to see formulas.mp4

00:59

006 How to reach the menu with shortcut.mp4

01:42

006 Marketing-efficiencyv2-ready.xlsx

007 Shortcut Test 2.html

external-links.txt

12 - Conclusions

001 Bonus Lecture.html

Description

Master the formulas and tricks that will save you time and make you as efficient as consultants in McKinsey, BCG, Bain

What You'll Learn?

You will master the most crucial functions and features of Excel
Understand the main challenges in analyzing data in Excel
Get ready made analyses in Excel
Analyze complex business problems in Excel
You will master essential Excel shortcuts that will help you drastically speed up your work in Excel
You will learn how to structure Analysis in Excel for your work as Management Consultant or Business Analyst

Who is this for?

Business analysts

Consultants

Researchers

Controllers

Small and medium business owners

Startups founders

What You Need to Know?

Basic Excel

Basic knowledge of economics or finance

More details

Description
What is the aim of this course?Â Â
Excel is the most often used first-choice tool of every business analyst and consultant. Maybe it is not the fanciest or most sophisticated one, Â yet it is universally understood by everybody especially your boss and your customers.Â Â Â
Excel is still a pretty advanced tool with countless features and functions. I have mastered quite a lot of them during my studies and while working. After some time in consulting, I discovered that most of them are not that useful; some of them bring more problems than solutions. On top of that, there are features that we are taught at university that are not flexible and pretty time-consuming. Â While working as a business analyst I developed my own set of tricks to deal with Excel Â I learned how to make my analyses idiot-proven and extremely universal.Â Â Â
I will NOT teach you the entire Excel as it is simply not efficient (and frankly you donâ€™t need it). This course is organized around the 80/20 rule and I want to teach you the most useful (from a business analyst/consultant perspective) formulas as fast as possible. I want you also to acquire thanks to the course good habits in Excel that will save you loads of time.Â Â
If done properly, this course will transform you in 1 day into a pretty good business analyst that knows how to use Excel in a smart way.
This course is based on my 15 years of experience as a consultant in top consulting firms and as a Board Member responsible for strategy, performance improvement, and turn-arounds in the biggest firms from Retail, FMCG, SMG, B2B, and services sectors that I worked for. I have carried out or supervised over 90 different performance improvement projects in different industries that generated in a total of 2 billion in additional EBITDA. On the basis of what you will find in this course, I have trained in person over 100 consultants, business analysts, and managers who now are Partners in PE and VC funds, Investment Directors and Business Analysts in PE and VC, Operational Directors, COO, CRO, CEO, Directors in Consulting Companies, Board Members, etc. On top of that my courses on Udemy were already taken by more than 224 000 students including people working in EY, Walmart, Booz Allen Hamilton, Adidas, Naspers, Alvarez & Marsal, PwC, Dell, Walgreens, Orange, and many others.
I teach step by step on the basis of Excel files that will be attached to the course. To make the best out of the course you should follow my steps and repeat what I do with the data after every lecture. Donâ€™t move to the next lecture if you have not done what I show in the lecture that you have gone through.Â Â
I assume that you know basic Excel so the basic features (i.e. how to write formulas in Excel) are not explained in this course. I concentrate on intermediate and advanced solutions and purposefully get rid of some things that are advanced yet later become very inflexible and useless (i.e. naming the variables). In the end, I will show 4 full-blown analyses in Excel that use the tricks that I show in the lectures.Â Â
To every lecture, you will find attachedÂ (in additional resources) the Excel shown in the Lecture so as a part of this course you will also get a library of ready-made analyses that can, with certain modifications, be applied by you in your work.Â Â
Why have I decided to create this course?Â Â
I have done a number of courses showing you how to analyze data in Excel. Yet, I have noticed that some students lack fluency in operations in Excel. This course is designed to fill in the gap and help you fully appreciate my other courses for business analysts and consultants. It can be used also as a standalone course that will help you to be smart in ExcelÂ Â Â Â
In what way will you benefit from this course?Â Â
The course is a practical, step-by-step guide loaded with tons of analyses, tricks, and hints that will significantly improve the speed with which you do the analyses as well as the quality of the conclusions coming out of available in your company data. There is little theory â€“ mainly examples, a lot of tips from my own experience, and other notable examples worth mentioning. Our intention is that thanks to the course you will know:Â Â
How to use Excel in a smart way to be able to analyze data fast and efficiently
How to draw conclusions from analyses â€“ chosen examples of analyses
How to be efficient in your work as an analyst?
How to build Excels that is understandable for you and your team, even after some time
You can also ask me any question either through the discussion mode or by messaging me directly.Â Â Â
How the course is organized?Â Â
The course is divided currently into the following sections:Â Â
Introduction. We begin with a little intro to the courseÂ Â
How to merge data from different sources. Quite often as an analyst, you will have to combine data from different sources. In this section, I will show you how to do it using the VLOOKUP function and others to make sure that you can easily combine data from different tables into the desired analysis. You will also see how to use HLOOKUP, MATCH, SUMIFS, and INDEX functions.
Cleaning and unifying data. Â 50% of your time will be lost on setting the data right so it can be used for analyses. In this section, you willÂ Â Â learn how to clean and unify data fastÂ Â
How to use the IF function and what you can do instead? Most of the analyses require you to make them dependable on something. For this, you can use the IF function which gets messy with more complicated formulas. I will show you how to avoid the mess and in most cases not use the IF functionÂ Â
Pivot Tables. As a business analyst, you will have to look at data on different levels. I will show you how to do it with Pivot Tables, and Pivot Charts, and what to do instead.Â Â
Other useful functions. In this section, I will show you other useful functions that you should masterÂ Â
Example of analysis. Here we show a full analysis from start to end including drawing conclusions. This will show you want should be the end resultÂ Â
Tools for analyzing data. On top of the Excel functions, you have available other tools in Excel that we will discuss in the section. Most of them save you a lot of trouble in analyzing dataÂ Â
Being faster with Excel. If you spend 6-8 hours a day with Excel you have to be fast and not to waste your valuable time. Here I will show youÂ how to do itÂ Â
Visual Basic â€“ the main things you should know. Sometimes it is much easier to do something in Visual Basic than go via typical Excel formulas. In some cases, Visual Basics create new opportunities. Here I will show you the most useful elements of Visual Basic.Â Â
EssentialÂ Excel shortcuts. In this section, we will discuss the essential shortcuts that will help you do the analyses much faster.
You will be able also to download many additional resourcesÂ Â
Excels with analyses shown in the course
Links to additional presentations and movies
Links to books worth reading
At the end of my course, students will be able toâ€¦Â Â
You will master the most crucial functions and features of Excel
Understand the main challenges in analyzing data in Excel
Perform the analyses in a very effective manner
Who should take this course? Who should not?Â Â
Business analysts
Researchers
Controllers
Consultants
Small and medium business owners
Startups foundersÂ Â Â
What will students need to know or do before starting this course?Â Â
Basic Excel
Basic knowledge of economics or finance
Who this course is for:
Business analysts
Consultants
Researchers
Controllers
Small and medium business owners
Startups founders

User Reviews

Rating

average 0

Total votes0

Focused display

Excel

Asen Gyczew

Instructor's Courses

Expert in performance improvement, scaling businesses, and turning-around companies with significant experience both in management as well as in supervision of medium size companies (EUR 20 – 500 million) and startups. I have trained in person over 100 consultants, business analysts, and managers who now are Investment Directors, CEO, Partners in PE and VC funds, Sales and Marketing Directors, Operational Directors, COO, Directors in Consulting Companies, Supply Chain Directors, Board Members, etc. Functional experience: performance improvement, strategy development, startups, intrapreneurship, post-merger integration and cost reduction, due diligence, turn-arounds, PMO; Industry experience: SMCG (domestic appliances, furniture, ceramic tiles), FMCG (cosmetics, juice, meat), retail (electronics, convenience shops, pharmaceuticals, DIY, vending, fashion, toys), B2B (aluminum system, plywood, alcohol, reagents, loading systems), services (MRO, consulting, outsourcing), foodtech, marketplacesCountries: Poland, Serbia, Russia, Ukraine

Udemy

View courses Udemy

Students take courses primarily to improve job-related skills.Some courses generate credit toward technical certification. Udemy has made a special effort to attract corporate trainers seeking to create coursework for employees of their company.