How To Become World Class Business Analyst - Cases In Excel

A practical guide on how to do business analysis in Excel especially of sales and operations

What you'll learn
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
Requirements
Basic or intermediate Excel
Description
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 analysts or do a lot of business analyses at their work.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 or supervised over 90 different performance improvement projects in different industries that generated in total 2 billion of 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 124 000 students including people working in EY, Walmart, Booz Allen Hamilton, Adidas, Naspers, Alvarez & Marsal, PwC, Dell, Walgreens, Orange, and many others.I teach through case studies, so you will have a lot of lectures showing examples of analyses, tools that we use. To every lecture, you will find attached (in additional resources) the Excels as well as additional presentations, materials shown in the lectures so as a part of this course you will also get a library of ready-made analyses that can, with certain modification, be applied by you or your team in your work.Why have 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 problems with proper analyzing intermediate and advanced issues – they are usually preoccupied with current activities and have close to no time for business analysis and strategic decision. Moreover, they do not know how to approach any subject from the analytical point of view to make sure that they are making 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 online 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, attached 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 aspects of sales, marketing, operations?How to draw conclusions from those analyses – both for current operations as well as strategic ones?How to be efficient in your work as an analyst? How to build Excels and PowerPoints so that it is understandable for you and your team, even after some time?Chose the right business analysis to identify problems and opportunitiesYou 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 into the following sections:Introduction. We begin with a 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 youTools. In the second section, we briefly go through the main tools and concepts for business analysts. We devote some time to the most popular ones: Excel and PowerPointBusiness modeling - example of analyses. On some occasions, you analyze the whole business to see what is the logic behind it. Here we show how to prepare the model in Excel for some of the business modelsSales & Marketing. Analyzing the market, sales activities, and efficiency, the potential for expansion are some of the most typical tasks set for business analysts. 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 from logistics, production, production planning, and other supply chain issues. Every business analysis here will show you possible savings or opportunities to increase capacity.Management Consulting Presentations. In this section, I will show you the essential things you need to create a consulting presentation during management consulting projects.Cases for training analytical skills. In the last 4 sections, we will show you a lot to short analytical cases studies that will help you improve your analytical skills.You will be able also to download many additional resourcesExcels with analyses shown in the coursePresentation of slides shown in the courseLinks to additional presentations and movies Links to books worth readingEvery business analysis is well described, available as a downloadable Excel file, and can be easiely modified to meet your needs

Overview

Section 1: Introduction

Lecture 1 Introduction

Lecture 2 How this course is organized

Lecture 3 Who is business analyst

Lecture 4 What to do if a blurry image appears

Lecture 5 How to find additional resources

Section 2: Main tools and techniques used by Business Analyst

Lecture 6 Main tools and techniques - introduction

Lecture 7 Excel - basic rules for every Business Analyst

Lecture 8 Excel – Main functions you should master

Lecture 9 VLOOKUP – Introduction

Lecture 10 VLOOKUP with approximate match – Introduction

Lecture 11 What you can use the VLOOKUP for?

Lecture 12 Basic VLOOKUP usage

Lecture 13 Some comments on basic VLOOKUP

Lecture 14 How to assign categories using VLOOKUP?

Lecture 15 How to use HLOOKUP?

Lecture 16 The logic of MATCH function

Lecture 17 How to assign categories based on 2 criteria using VLOOKUP and MATCH function?

Lecture 18 How to assign categories based on 2 criteria using HLOOKUP and VLOOKUP function

Lecture 19 Why NOT to use IF function

Lecture 20 How to use if function?

Lecture 21 How to use VLOOKUP instead of IF function?

Lecture 22 How to use IFERROR function?

Lecture 23 How to use MAX and MIN instead of IF function

Lecture 24 Pivot tables - introduction

Lecture 25 Power Point - how it should be used by business analyst

Lecture 26 Bottom-up approach

Lecture 27 Top-down approach

Lecture 28 Estimating the costs of a wedding – Introduction

Lecture 29 Estimating the costs of a wedding – Partial Solution

Lecture 30 Estimating the costs of a wedding – Excel Part 1

Lecture 31 Estimating the costs of a wedding – Excel Part 2

Lecture 32 Backward logic

Lecture 33 Backward logic based decisions – Case Introduction

Lecture 34 Backward logic based decisions – Solution

Lecture 35 Backward logic based decisions – When it makes sense to use it

Lecture 36 Compounded effect

Lecture 37 Issue tree

Lecture 38 Issue tree – Examples Introduction

Lecture 39 Issue tree – Logistics

Lecture 40 Issue tree – Retail

Lecture 41 Issue tree – FMCG

Lecture 42 Opportunity Tree

Lecture 43 OEE and OLE

Lecture 44 Cost drivers

Lecture 45 Theory of constraints and bottlenecks

Section 3: Business modeling - example of analyses

Lecture 46 Business Modeling - introduction

Lecture 47 E-commerce business model - intro

Lecture 48 E-commerce business model - Excel example

Lecture 49 Business model of a restaurant in Excel - basic model

Lecture 50 Business model of a restaurant in Excel - advanced model

Lecture 51 Restaurant chain - how to model it in Excel?

Lecture 52 Mechanics of business model for a consulting firm

Lecture 53 The business model of a consulting firm in Excel - Capacity and revenues

Lecture 54 The business model of a consulting firm in Excel - costs and profit & loss

Lecture 55 Financial models vs Business Models

Lecture 56 Other busines models

Lecture 57 What to study if you want to learn more

Section 4: Sales and marketing analyses - examples

Lecture 58 Sales and marketing analyses - introduction

Lecture 59 Catchment area / reach analysis - B2B

Lecture 60 Efficiency of marketing activities

Lecture 61 Sales and margin efficiency in retail

Lecture 62 Sales channel analysis - case in FMCG

Lecture 63 Saturating existing markets – Introduction

Lecture 64 Saturating existing markets – Data for the case study

Lecture 65 Saturating existing markets – Number of Stores

Lecture 66 Saturating existing markets – Data by Concepts

Lecture 67 Saturating existing markets – Stores by concepts

Lecture 68 Saturating existing markets – Concept & Space

Lecture 69 Saturating existing markets – Solution in Power Point

Lecture 70 Saturating existing markets – WaterFall Chart

Lecture 71 Product range analysis - Introduction

Lecture 72 Product range analysis - Example

Lecture 73 How to expand the brand – Introduction

Lecture 74 How to expand the brand – Solution – Part 1

Lecture 75 How to expand the brand – Solution – Part 2

Lecture 76 How to find the white spaces untouched by your brand – Introduction

Lecture 77 How to find the white spaces untouched by your brand – Solution – Per capita

Lecture 78 How to find the white spaces untouched by your brand – Solution – Local shares

Lecture 79 How to find the white spaces untouched by your brand – Solution – Presentation

Lecture 80 Sales force efficiency analysis – Introduction

Lecture 81 Sales force efficiency analysis – Solution – Unit Cost

Lecture 82 Sales force efficiency analysis – Solution – ABC analysis of Current Solution

Lecture 83 Sales force efficiency analysis – Solution – more on Current Solution

Lecture 84 Sales force efficiency analysis – Solution – Margin Maximazation

Lecture 85 Sales force efficiency analysis – Solution – Specialization

Lecture 86 Sales force efficiency analysis – Solution – Summary

Lecture 87 Sales force efficiency analysis – Practical Tips

Lecture 88 Overview of segmentation methods

Lecture 89 What to study if you want to learn more

Section 5: Operations - Production, Supply Chain - examples of analyses

Lecture 90 Operation analyses - introduction

Lecture 91 OEE analysis

Lecture 92 Removing bottlenecks

Lecture 93 Optimal production batch analysis - FMCG

Lecture 94 2-stage production planning - example of cosmetics producers preparing bundles

Lecture 95 Finding the best spot for your factory – Introduction

Lecture 96 Finding the best spot for your factory – Cost comparison - Excel

Lecture 97 Finding the best spot for your factory – NPV for every project - Excel

Lecture 98 Top-dow approach for a FMCG - introduction

Lecture 99 Top down approach for a FMCG - tips how to do it

Lecture 100 Top down approach for a FMCG - solution

Lecture 101 Car Industry – Planning the flow of finished goods – Introduction

Lecture 102 Car Industry – Planning the flow of finished goods – Excel - First Option

Lecture 103 Car Industry – Planning the flow of finished goods – Excel - Second Option

Lecture 104 Measuring the capacity of Central Warehouse – Introduction

Lecture 105 Measuring the capacity of Central Warehouse – Tip

Lecture 106 Measuring the capacity of Central Warehouse – Capacity in Excel

Lecture 107 Measuring the capacity of Central Warehouse – Cost in Excel

Lecture 108 Standarization

Lecture 109 Standarization - calculation in Excel

Lecture 110 5S - Introduction

Lecture 111 5S - calculation in Excel

Lecture 112 1 worker 2 machines

Lecture 113 1 worker 2 machines - Excel

Lecture 114 What to study if you want to learn more

Section 6: Management Consulting Presentation

Lecture 115 Management Consulting Presentation – Introduction

Lecture 116 Why presentations are important

Lecture 117 Phases of creating a management consulting presentation

Lecture 118 Presentation for reading vs audience

Lecture 119 Occasions for which you will create presentations in Management Consulting

Lecture 120 Structure of the presentation

Lecture 121 Create the messages

Lecture 122 Sketching of the presentation

Lecture 123 Creating the template

Lecture 124 Basic rules for a management consulting presentations

Section 7: Management Consulting Presentation: Filling in the slides & Delivery

Lecture 125 Template vs real slides

Lecture 126 The flow of the presentation

Lecture 127 Rules for delivering successful presentation

Lecture 128 Comparison of the written and the delivered version

Lecture 129 What to study if you want to learn more

Lecture 130 Data Visualization

Section 8: Cases for training analytical skills - Market Estimation

Lecture 131 Business analyst cases - introduction

Lecture 132 Market estimation introduction

Lecture 133 Estimation of TV market - case to be solved

Lecture 134 Estimation of TV market - solution

Lecture 135 Estimation of tile market - case to be solved

Lecture 136 Estimation of tile market - solution

Lecture 137 Estimation of kids wear market - case to be solved

Lecture 138 Estimation of kids wear market - solution

Section 9: Cases for training analytical skills - Sales and Marketing

Lecture 139 Sales and marketing cases - introduction

Lecture 140 Sales channels comparison - Problem

Lecture 141 Sales channels comparison - Solution

Lecture 142 Potential for growth - retailer - Problem

Lecture 143 Potential for growth - retailer - Solution

Lecture 144 Efficiency of sales and marketing activities in consulting firm - Problem

Lecture 145 Efficiency of sales and marketing activities in consulting firm - Solution

Lecture 146 Pricing in multichannel surrounding - Problem

Lecture 147 Pricing in multichannel surrounding - Solution

Lecture 148 How to expand strong brand in Retail - Problem?

Lecture 149 How to expand strong brand in Retail - Solution

Section 10: Cases for training analytical skills - Strategy

Lecture 150 KPIs for cinema - Problem

Lecture 151 KPIs for cinema - Solution

Lecture 152 KPIs for marketplace - Problem

Lecture 153 KPIs for marketplace - Solution

Lecture 154 What is the business model of IKEA - Problem

Lecture 155 What is the business model of IKEA - Solution

Lecture 156 What could be the business model for Google Car - Problem

Lecture 157 What could be the business model for Google Car - Solution

Lecture 158 Why a niche strategy makes sense - Problem?

Lecture 159 Why a niche strategy makes sense - Solution

Lecture 160 How to scale up a business – best practices from top firms

Section 11: Cases for training analytical skills - Operations

Lecture 161 Content marketing agency - how to increase it's output? - Problem

Lecture 162 Content marketing agency - how to increase it's output? - Solution

Lecture 163 How to improve line for cutting frozen fish? - Problem

Lecture 164 How to improve line for cutting frozen fish? - Solution

Lecture 165 How long will wait in the queue? – Problem

Lecture 166 How long will wait in the queue? - Solution

Section 12: Section - conclusions

Lecture 167 Bonus Lecture

Business analysts,Researchers,Controllers,Consultants,Small and medium business owners,Startup founders,Students considering entering management consulting