How to become world class Business Analyst - Cases in Excel
- 8.5 hours on-demand video
- 3 articles
- 94 downloadable resources
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to 4,000+ top Udemy courses anytime, anywhere.
Try Udemy for Business- Kill any discussion with solid data analyses
- Do the analyses 10x faster
- Structure complex problems and solve them in Excel
- Perform the analyses in a very effective manner
- Understand the main challenges in analyzing different aspects in sales, marketing and operations
- Build business models for e-commerce, service companies and B2C services
- You will get ready made examples of complex analyses from top-notch consulting companies
- You will learn what kind of analyses can be performed during a management consulting project
- Basic or intermediate Excel
What is the aim of this course?
This course will help you drastically improve your knowledge and skills in business analysis through a series of practical cases. It is designed for people who want to become business analyst or do a lot of business analyses at their work. It is based on my 14 years of experience as a consultant in top consulting companies and as a Board Member responsible for strategy, improvement and turn-arounds in biggest companies from FMCG, SMG, B2B sector that I worked for. On the basis of what you will find in this course I have trained over 100 business analysts who now are Investment Directors, Senior Analyst, Directors in Consulting Companies, Board Members etc. On top of that my courses on Udemy were already taken by more than 51 000 students including people working in EY, Walmart, Booz Allen Hamilton, Alvarez & Marsal, PwC, Dell, Walgreens and many others.
I teach through cases so most of the lectures will be a case study showing some business problem solved in Excel. To every lecture you will find attached (in additional resources) the Excel shown the specific business analysis shown in the Lecture. Thanks to this as a part of this course you will also get a library of ready-made business analyses that can, with certain modification, be applied by you in your work. Every business analysis show in this lecture is easy to edit and adjust to your need. The analysis are taken from real life management consulting projects
Why I decided to create this course?
The companies I have worked with so far as a Board Member (I am usually, Chief Restructuring Officer VP responsible for Strategy and Development) still have huge problem with proper analyzing intermediate and advance issues – they are usually preoccupied by current activities and have close to no time for business analysis and strategic decision. Moreover, they do not know how to approach many subject from the analytical point of view to make sure that they are taking the right decision. Therefore, quite often they hire consultants who in turn lack the practical knowledge and experience. This course is my small contribution to improving both consulting as well operational and strategic analyses in FMC, SMCG, B2B companies including also on-line models like e-commerce, SaaS, marketplaces.
In what way will you benefit from this course?
The course is a practical, step by step guide loaded with tones of analyses, tricks, hints that will significantly improve the speed with which you do the business analyses as well as the quality of the conclusions coming out of available in your company data. Every business analysis is well described and presented in a form of Excel, attachted to the lecture. There is little theory – mainly examples, a lot of tips from my own experience as well as other notable examples worth mentioning. Our intention is that, thanks to the course, you will know:
How to analyze different aspect of sales, marketing, operations?
How to draw conclusions from those analyses – both for current operations as well ase strategic ones?
How to be efficient in your work as analyst?
How to build Excels and Power Points so that it is understandable for you and your team, even after some time?
Chose the right business analysis to identify problems and opportunities
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 in 5 sections and will be adding new section to address other important issues. Currently you will find the following sections:
Introduction. We begin with little intro into the course as well as some general info on the role of business analyst – what skills, education, knowledge is required from you and what are the available career paths for you
Tools. In the second section we briefly go through main tools and concepts for business analysts. We devote some time to the most popular ones: Excel an Power Point
Business modelling - example of analyses. On some occasion you analyze the whole business to see what is the logic behind. Here we show how to prepare the model in Excel for some of the business models
Sales & Marketing. Analyzing the market, sales activities and efficiency, potential for expansion are one of the most typical tasks set for business analyst. Here I provide you with plenty of practical real-life examples of problems and solutions. Every business analysis also shows you the potential for increased sales or margin.
Operations. Quite often the business performance depends on Operational Excellence. In this section we will show you examples of analysis form logistics, production, production planning, and other supply chain issues. Every business analysis here will show you possible savings or opportunity to increase capacity.
You will be able also to download many additional resources
Excels with analyses shown in the course
Presentation of slides show in the course
Links to additional presentations and movies
Links to books worth reading
Every business analysis is well described, available as a dowloadable Excel file and can be easy modify to meet your needs
- Business analysts
- Researchers
- Controllers
- Consultants
- Small and medium business owners
- Startup founders
- Students considering entering management consulting
In this lecture we will explain why it makes sense to take the course, what you will get out of it and what you should look forward to. We will also explain the logic we used to build the course
I will show you how the course is organized
Here we will explain who is business analyst, what are the available career pahts to them and what kind of skills are required to become one. We will also briefly talk about what you should gradeatue to have a bigger chances of becoming business analyst
Here I will show you what you will learn in this section
Here I will show you the main rules for Excel files that I recommend using when you are doing your analysis in Excel. This will help you create things understood by others that you can reuse for other projects
In the next few lectures we will repeat the Excel functions that will be useful later when we will be solving case studies. We will discuss the following sections:
VLOOKUP
HLOOKUP
MATCH
IF
IFERRORPivot Tables
If you are familiar with that you can skip these lectures and move on to management consulting tools and techqniues
Enjoy the content and if you have any questions please let me know!
Power Pointe presentation is the second most important tools to be used by Business Analyst in his work. After all it is not only important to analyse data and draw conclusions but also to show them in a nice manner to decision makers. Here you will learn basic rules on how to build your Power Point deck in fast and easy manner. You will see also some examples of good and bad slides. There are many advanced tools for presentation (i.e.) Prezi. Still Power Point is the most often used and widely accepted
In this lecture you will get to know our favorite method for first rough estimation – the bottom-up method. We give you also 2 examples with Excel calculation how to use it.
Let’s try to put the backward reasoning to practice. In this case study we will use it to decide what effort has to be done to 10x the current business. A few information about the retailer
He has currently 100 stores. Each store sales are equal to EUR 2 M
He wants to 10x the business up to Year 5
Estimate what LFL growth he must get and how many stores he must open
Assuming EUR 1 M per store estimate the Capex
Estimate how many people he has to recruit assuming 6 people per store
Backward logic can be used in many situations. Below some of them
You have a clear goal in the future but not a clear path towards it
You want to estimate the pace needed to reach the goal
You want to check the implications for you or your department
You want to see what are the limitations preventing you from reaching the goal
You want to see whether the goal is not too ambitious
You want to see whether the goal does not require too big resources
In many cases you can get high end result from compounding effect in time or by getting small improvements in many areas. In this lecture I will explain the concept
Issue tree is a simple yet powerful concept used in consulting for structuring discussion and picking the right topics to concentrate on. In this lecture I will show you the principles of using it as well as a practical example
In every company you have bottlenecks that limit your ability to go forward and deliver more goods and services. Here I will show you how to estimate what is the impact for the whole system and how you can remove improve them. I will use simple production example to show you the effect on the whole system. You will see how the bottleneck can shift from 1 shift to another. You will also learn how to prioritize where it make sense to invest to get the best results.
Before doing this lecture I strongly encourage you first to take the OEE lecture – we use the OEE concept here so if you are not familiar with it please start with OEE lecture as a warm-up.
As the course is design in such a way to simulate real project in this part we will talk about how you should prepare yourself once you know that you will participate in a project. This lecture will also introduce you to some consulting concepts which will make your life during the project much easier. Attached please find an example of issue tree
I will show you what you will master in this section
In this lecture you will get insight into the business model of different e-commerce companies. E-commerce businesses group is very diversified so we will try to show the similarities and the differences between concepts.. You will learn here the definition of this business model, see some examples of companies using this business models. You will also understand what really matters in this sort of business, what KPIs you should look at and what values you can expect.
In this Lecture I will go through a business model of e-commerce and show you how you can model the behavior of the customer, acquisition methods and margins in Excel. Attached to this lecture you will find the file that I show in the video so download it and go through it. You can use it to model the e-commerce business you are analyzing
Excel gives you great opportunities to calculate whether your idea makes sense or not. For this you will use the knowledge you have gathered in the previous lectures on your concept, location and your customers. Excel is obviously less emotion than you and the model you will create here can be used as a cornerstone of your business plans. It shows also to banks and potential investors what is your cash flow. We start with a simple model so you can understand it and learn how to use it.
In this lecture we make more advanced model. We introduce the traffic parameter and show how it is converted first into people that are engaged (stop and enter your restaurant) and later into customers (they buy something from you). Here we will be operating on 2 customer groups: first one eats at the restaurant; second one comes for take-aways. For both customer groups we calculate separate conversion rates and Average ticket (ATV). We still here show you a business model for a 1 restaurant
Before I show you the model in Excel I want to present you in this lecture the overall logic
I show you in the lecture the model of the consulting firm in Excel. After modifying it you can use it for your purposes (we attach the Excel files to the lecture). This model will let you check whether your business idea makes sense.
I show you in the lecture the model of the consulting firm in Excel. After modifying it you can use it for your purposes (we attach the Excel files to the lecture). This model will let you check whether your business idea makes sense.
As a business analysts or a consultant you will have to from time to time create financial models. There are a bit different than business models. In business models you go into details of operations and you focus on getting the links on the KPIs. Those models are usually done for internal purposes, to manage in the right direction the business. Financial models on the other hand are more for external users. You concentrate in them on creating the picture of the business in a standardized, understood by everybody way. You also want to create the financial statements: profit & loss statement, balance sheet statement, cash flow statement
If you want to learn more when it comes to modeling, I suggest checking the following resources
Offline Business Models
Online Business Models
Essential Finance & Accounting
Financial Modeling for Management Consultants
Financial Modeling for Startups
Financial Analysis
I will attach resources to this lecture
I will show you what you will master in this section
In this lecture we show how you can measure and analyze what is the maximal reach of your B2B bussiness having in mind transportation costs and markups. We will provide you with fast way to identify potential customers and measure your targeted price on a specific foreign market
Here I will show you how to see which marketing activities you should bet on
Most producers / brand owners operate using many sales channels: wholesalers, own shops, e-commerce, marketplace etc. It makes sense to check how profitability looks across channels and what can e done to improve overall situation. I will show you how it cane be done with a simple Excel analysis and a pivot table. I will go also try to demonstrate what kind of conclusions can you draw and what should be your next steps, on the basis of the results you are getting form the analyses
Now using data in the sheet "Concepts" on the number of store per City per concept try to estimate how many stores by concepts (Standard & Luxury Concept) we will have in each and every city. Try also to estimat the space by city by concept. For that use the sheet "Space" where we have already created the suggested structure for the data. Please use attached file Saturation point analysis v4 - Partial Solution.xlsx
So, pause the lecture, solve this part on your own and once you are down please move to the next lecture
In Retail you have space that you divide among different product groups. For every group you should calculate the total margin using sales density, % Gross Margin and space allocated to specific group. You have to analyze their performance and decide how to split the space. In this lecture I will show you the general concept
If you want to learn more when it comes to sales & marketing, I suggest checking the following resources
Sales & Marketing Analysis
Market Research
Strategy for Management Consultants
Sales Forecasting for Management Consulting
Funnel Analysis
Pricing for Management Consultants
I will show you what you will master in this section
In every company you have bottlenecks that limit your ability to go forward and deliver more goods and services. Here I will show you how to estimate what is the impact for the whole system and how you can remove improve them. I will use simple production example to show you the effect on the whole system. You will see how the bottleneck can shift from 1 shift to another. You will also learn how to prioritize where it make sense to invest to get the best results.
Before doing this lecture I strongly encourage you first to take the OEE lecture – we use the OEE concept here so if you are not familiar with it please start with OEE lecture as a warm-up.
In this lecture you will learn how to calculate optimal batches and how to estimate savings when comparing to actuals batches. We will use his an example of juice producer
2-stage production planning (planning for assembling and bundles). In many cases you have to first prepare the components and then on the basis of this produce the end product. This requires certain amount of additional planning, especially taking into account that the same component can be in different end-products. I will show you here on the basis of example of cosmetics producer how to create such a production plan, assuming optimal production batches for components and taking into account the inventories.
If you want to learn more when it comes to operations, I suggest checking the following resources
Supply Chain for Management Consultants
Essential Lean Manufacturing for Management Consultants
KPIs for Management Consultants & Managers
Performance Improvement Projects for Management Consultants
Production Planning for Management Consultants
In management consulting you are delivering your advices, observation and analyses using presentations. On some projects you may be producing as much as 200-300 slides. Slide preparation is very time-consuming and you have to make sure that you structure you work properly to deliver according to promised deadlines. That's why in the next few lectures I will give you some hints how to create efficiently such a presentation.
There 10 phases of producing the final presentation. You start with creating the structure of the presentation then you move to making sketches, template and slides. You end up with checking for errors and omissions. I will discuss briefly here each and every stage
There are 2 types of presentations: presentation that will be read by the customer on his own and presentation that will be delivered to the customer during a meeting. Those presentations different in size, delivery time, animations present in the presentation and last but not least font size. I will be discussing those difference in more detail in this lectur
Power Pointe presentation is the second most important tools to be used by Business Analyst in his work. After all it is not only important to analyse data and draw conclusions but also to show them in a nice manner to decision makers. Here you will learn basic rules on how to build your Power Point deck in fast and easy manner. You will see also some examples of good and bad slides. There are many advanced tools for presentation (i.e.) Prezi. Still Power Point is the most often used and widely accepted
When you are delivering the presentation people want to listen to YOU and not read the presentation. It should just support what you are saying. You should not be a slave of the presentation. I will show you some tips how to achieve a positive effect during the delivery
In this lecture I will show what cases we will solve in this section
In this lecture you have to estimate what is the market for TVs in a specific market, what are the cost drivers. Imagine that you were asked to estimate the market for TV sets. Ask yourself 3 questions:
- What is meant here by “the market”? Annual sales or total ownership?
- Do you want the market estimation to be in volumes or in values?
- What is included in the TV sets (i.e. monitors are in or not)?
Imagine that you were asked to estimate the market for ceramic tiles. You have to ask yourself the following questions:
- What is meant here by “the market”? Annual sales or total ownership?
- Do you want the market estimation to be in volumes or in values?
- What is included in market? Should we include B2C or also B2B?
- Are we talking about just ceramic tiles or other connected products
In this section I will show you want kind of cases relating to marketing and sales you may get during the interview. There will be examples from FMCG, retailers and service companies.
If you work for a retailer one of the most useful analysis is to check whether there is still potential to growth and how big it is. In this lecture I will show you 2 ways in which you can estimate how many shops you can open in a specific location, city, province. One approach will be concentrated solely on the number of shops whereas the second one will also take into account the size of shops