Financial Modeling For Business Analysts And Consultants

Learn Financial Modeling in Excel that will allow you to participate in due diligence, strategy or turn around projects

Last updated 2022-01-10 | 4.3

- You will master financial modeling in Excel at the needed for business analysts and consultants level
- Understand the main challenges in financial modelling
- Perform the analyses of financial models

What you'll learn

You will master financial modeling in Excel at the needed for business analysts and consultants level

Understand the main challenges in financial modelling

Perform the analyses of financial models

You will get access to +30 ready-made models that you can utilize for your purposes

Model your business in Excel

Estimate the value of your business

* Requirements

* Basic Excel
* Basic knowledge of economics or finance

Description

What is the aim of this course?

As a business analyst 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 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.

On top of that, you use financial statements for valuation purposes.

The most typical situation when, as a business analyst or a consultant, you will have to prepare such a model is connected with selling or purchasing a company. You may be doing it on the sell-side or on the buy-side. Financial modeling is done as a part of strategy projects, turnaround projects, due diligence.

I will NOT teach you everything on financial modeling because 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) ways to go as fast as possible from rough description to working model in Excel that you can make more and more complicated.

The aim of this course is that you are able to do a financial model of the business as fast as possible.

If done properly, this course will transform you in 1 day into a pretty good business analyst that knows how to create fast and efficiently financial models.

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 110 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. We will go through a real-life example of a business and create the financial model for it. 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).

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?

This course is a part of my attempt to help business analysts and consultants gain fast useful knowledge that will help them excel at their work. I have done 10 other courses that will help you be great business analysts or consultants. I have covered already: hacks and tricks in Excel, business modeling, market research, management productivity hacks, and others. Now you have the opportunity to learn financial modeling in Excel

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 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 as well as other notable examples worth mentioning. Our intention is that thanks to the course you will know:

What is the difference between business models and financial models?
How to model cost positions and balance sheet positions?
How to create on the basis of this financial statements: profit and loss statement, balance sheet statement, and cash flow statements
How to make sure that there is integrity in the model?
What analyses and conclusions you can draw from the financial model?

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 10 sections and will be adding new sections to address other important issues. Currently, you will find the following sections:

Introduction. We begin with a little intro to the course
Modeling of Profit & Loss account. In this section, we start by showing how to model cost positions (except for Depreciation)and how to create on the basis of this a profit & loss statement
Capex and Depreciation. Capex and Depreciation are some of the most difficult elements that you will model in Excel. Therefore, we have created a separate section devoted just to this subject. In this section you will learn how to follow specific groups of assets, track their gross and net value and calculate depreciation.
Working Capital. Working capital is crucial to the business as it defines how much money you have to have engaged in the business. I will show you how to model specific positions of Working Capital
Debt. You may need external sources of financing for the company. I will show you how to model loans and get the necessary info to create a balance sheet later.
Equity. In this section, I will show you how to model the equity and all relations with shareholders
Balance sheet modeling. Here we get everything together to model the balance sheet statement.
Cash flow modeling. Here we get everything together to model the cash flow statement. Here we will check the integrity of the model as well.
Analyses of the Financial Model. In the end, on the basis of a ready model, I will try to draw the conclusions that will show us whether the business we have been analyzing is interesting or not
Valuation. In this section, I will show you the main valuation methods. We will look at DCF and Multiplier method
Business modeling. In many cases, you will have no time to do a full financial model. In such situations, you may resort to creating a business model. A business model will usually have only a P&L statement. In some cases also simplified the Cash Flow statement. In this section, we will see how you can create such models. You can also get access to more than 30 models. You will see how to model e-commerce, marketplace, real estate, subscription businesses, and many more.

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 financial modeling in Excel at the needed for a business analyst and consultant level
Understand the main challenges in financial modeling
Perform the analyses of the financial model
You will get a ready model that you can utilize for your purposes

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
Researchers
Controllers
Consultants
Small and medium business owners

Course content

12 sections • 73 lectures

Introduction 13 lectures

What the course will be about Preview 02:34

Here I will explain you what the course will be about and how you can make the best out of it.

About me Preview 01:47

Here I will talk a little bit about me and my company

What to do if a blurry image appears Preview 02:04

Here I will explain what to do if the image becomes blurry

How to find additional resources Preview 01:05

Here I will explain how to find additional resources

Difference between financial model and business model Preview 02:38

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

Essential Finance & Accounting Preview 00:52

I assume that you have sufficient knowledge of finance and accounting but just in case I also attach a presentation that will cover this topic extensivelly

Our approach to modelling Preview 00:52

In the whole course to model most of the cost position we will extensively use bottom-up approach. Understanding the approach will help you be great at financial modeling

We will have a few lectures on bottom-up approach. You can skip the if you want and move on to the building the financial model

What is a bottom-up approach
Estimate wedding cost using bottom-up approach

Bottom-up approach Preview 07:53

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.

Estimating the costs of a wedding – Introduction Preview 01:10

Imagine that you have to estimate the costs of the wedding to help Tomasz plan his wedding. Use for that the bottom-up approach. We will use this case study to see how bottom-up approach works in practice

Estimating the costs of a wedding – Partial Solution Preview 05:09

In this lecture I will show you the solution to previously introduced case study

Estimating the costs of a wedding – Excel Part 1 Preview 02:38

In this lecture I will show you the solution to previously introduced case study

Estimating the costs of a wedding – Excel Part 2 Preview 02:58

In this lecture I will show you the solution to previously introduced case study

Case Introduction Preview 01:21

In the first few sections we will cover an interesting model of a producer of hand-made clay products. Here I will briefly introduce this case study

Modeling of Profit & Loss Account 8 lectures

How to make the most of the course Preview 02:08

Here I will show you how you can benefit to the fullest extent from this course

Profit and loss intro Preview 00:30

We start by showing how we will get from cost position to profit & loss statemen

Sales Preview 04:24

Sales are driving the whole financial model. Therefore, we start from sales and show you how to model it.

Materials and Energy Preview 02:36

In terms of cost quite often materials and energy are the main cost position. We will show you how to model it.

External services Preview 02:09

You will be buying a lot of services from third parties. Some companies have a lot of contractors instead of workers. We will show you how to model it in this lecture

Taxes & Payments Preview 00:56

I will show you in this lecture how to model Taxes & Payments

Payroll Preview 04:28

Salaries and wages are very important in any business. I will show you how to model it taking into account base salaries, FTE (full time equivalents) and assumed growth in salaries and wages

Other Costs Preview 01:59

I will show you in this lecture how to model Other Costs

Capex and Depreciation 4 lectures

Modeling Capex and depreciation - introduction Preview 03:29

Capex and Depreciation are one of the most difficult elements that you will model in Excel. Therefore, we have created a separate section devoted just to this subject . In this section you will learn how to follow specific group of assets, truck their gross and net value and calculate depreciation.

Intangibles - modeling of gross value, net value and depreciation Preview 04:47

I will show you in this lecture how to model Intangibles especially I will show you in this lecture how to

Machinery - modeling of gross value, net value and depreciation Preview 01:51

I will show you in this lecture how to model Machinery especially I will show you in this lecture how to

Adding depreciation to the P&L Preview 01:20

At the end we add the depreciation to P&L

Working Capital 5 lectures

Introduction to Working Capital Preview 03:03

Working capital is crucial to the business as it defines how much money you have to have engaged in the business. I will show you how to model specific positions of Working Capital

Inventory of materials, WIP and finished goods Preview 03:13

I will show you in this lecture how to model inventory of materials, WIP and finished goods

Receivables Preview 03:22

I will show you in this lecture how to model receivables

Liabilities Preview 04:03

I will show you in this lecture how to model liabilities

Corporate tax and final working capital estimation Preview 01:13

I will show you in this lecture how to model Corporate tax and final working capital estimation

Debt 4 lectures

Introduction to debt modeling Preview 01:00

You may need external sources of financing for the company. I will show you how to model loans and get the necessary info to create balance sheet later.

Old loans Preview 03:04

I will show you in this lecture how to model old loans

New loan Preview 03:55

I will show you in this lecture how to model new loan

Interest paid and interest earned Preview 03:21

I will show you in this lecture how to model interest paid and interest earned

Equity 2 lectures

Introduction to Equity Preview 01:07

In this section I will show you how to model the equity and all relations with shareholders

Equity modeling Preview 03:11

I will show you in this lecture how to model Equity

Balance sheet modeling 6 lectures

Introduction to Balance sheet modeling Preview 01:53

I will show you in this lecture how to model balance sheet

Fixed assets Preview 02:32

I will show you in this lecture how to model fixed assets

Current Assets Preview 02:10

I will show you in this lecture how to model current assets

Own capital Preview 01:09

I will show you in this lecture how to model own capital

Current liabilities Preview 02:27

I will show you in this lecture how to model current liabilities

Balance sheet - summary Preview 01:26

In this lecture we summarize the balance sheet and check the integrity of the model

Cash flow modeling 6 lectures

Cash Flow introduction Preview 03:32

Here we get everything together to model the cash flow statement. Here we will check integrity of the model as well

Operational cash flows Preview 02:30

I will show you in this lecture how to model operational cash flows

Investment cash flows Preview 00:55

I will show you in this lecture how to model investment cash flows

Cash flow from financial activities Preview 01:46

I will show you in this lecture how to model cash flow from financial activities

Shareholders influence on Cash Flow Preview 01:02

I will show you in this lecture how to model shareholders influence on Cash Flow

Final Cash Flow Preview 02:58

I will show you in this lecture how to model Final Cash Flow

Analyses of the Financial Model 6 lectures

Introduction to Analyses Preview 01:59

At the end, on the basis of ready model I will try to draw the conclusions that will show us whether the business we have been analyzing is interesting or not

Profit & Loss analyses Preview 03:15

I will show you in this lecture how to model Profit & Loss analyses

Cash Flow analyses Preview 03:11

I will show you in this lecture how to model Cash Flow analyses

Possible improvements to be further analysed Preview 01:47

In this section I will show you where you can look for improvements on the basis of financial model

Alternative wasy to model Sales Preview 01:39

In the first part of the course we have estimated sales using: sales from previous year, assumed change in price and assumed change in quantity. There is an alternative way to achieve this effect and we will discuss it in this lecture

Alternative wasy to model Variable Costs Preview 01:25

In the first part of the course we have estimated variable costs using: sales from previous year, assumed change in cost and other additional one-offs. There is an alternative way to achieve this effect and we will discuss it in this lecture

Valuation 8 lectures

Introduction to Valuation Preview 02:16

In this section, I will show you how to do the valuation of the company that we have created the financial model for. We will use the 2 set of methods DCF and multipliers.

Introduction to DCF method Preview 02:25

In this lecture, I will show you the basic assumptions behind the DCF methods

Difference between FCFF and FCFE Preview 02:13

There are 2 main DCF methods: FCFF and FCFE. Here I will show you the difference between them before we go into details.

DCF method of valuation applied to FCFF Preview 03:42

In this lecture, we will apply the DCF using the FCFF model to the financial model we have created.

DCF method of valuation applied to FCFE Preview 01:59

In this lecture, we will apply the DCF using the FCFE model to the financial model we have created.

Introduction to using multipliers for valuation Preview 02:56

Now, we move on to the multiplier methods of valuation.

Enterprise to EBIT or EBITDA Preview 04:08

We will start with the EBIT and EBITDA multipliers.

Price per Earning multiplier Preview 02:09

In this lecture, I will use the P/E to do the valuation.

Business Modeling - examples 10 lectures

Introduction to business modeling Preview 01:35

Here I will show you examples of business model that differ from financial models

Difference between financial model and business model Preview 02:38

We will start with reminder about the difference between financial and business models

Mechanics of business model for a consulting firm Preview 02:26

Before I show you the model in Excel I want to present you in this lecture the overall logic

The business model of a consulting firm in Excel - Capacity and revenues Preview 02:54

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.

The business model of a consulting firm in Excel - costs and profit & loss Preview 05:05

E-commerce Introduction Preview 20:57

In this lecture we will explain you how e-commerce business model works and what you should concentrate on

E-commerce business model Preview 09:10

In this lecture I will show you how you can build the model in Excel and what conclusions can you draw from it.

Business model of a restaurant in Excel - basic model Preview 08:32

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.

Business model of a restaurant in Excel - advanced model Preview 08:58

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