Beginner to Pro in Excel: Financial Modeling and Valuation
4.5 (27,567 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
124,315 students enrolled

Beginner to Pro in Excel: Financial Modeling and Valuation

Financial Modeling in Excel that would allow you to walk into a job and be a rockstar from day one!
4.5 (27,567 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
124,315 students enrolled
Created by 365 Careers
Last updated 7/2020
English
English [Auto], French [Auto], 5 more
  • German [Auto]
  • Indonesian [Auto]
  • Italian [Auto]
  • Portuguese [Auto]
  • Spanish [Auto]
This course includes
  • 12.5 hours on-demand video
  • 10 articles
  • 548 downloadable resources
  • Access on mobile
  • Assignments
  • Certificate of Completion
What you'll learn
  • Master Microsoft Excel and many of its advanced features
  • Become one of the top Excel users in your team
  • Carry out regular tasks faster than ever before
  • Build P&L statements from a raw data extraction
  • Build Cash Flow statements
  • Discover how to value a company
  • Build Valuation models from scratch
  • Create models with multiple scenarios
  • Design professional and good-looking advanced charts
Requirements
  • Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016, or Microsoft Excel 2020
Description

**Updated for June 2020!**

Do you want to learn how to use Excel in a real working environment?

Are you about to graduate from university and start looking for your first job? 

Are you a young professional looking to establish yourself in your new position? 

Would you like to become your team's go-to person when it comes to Financial Modeling in Excel? 

If you answered yes to any of these, then this is the right course for you!

Join over 119,002 successful students taking this course!

The instructor of this course has extensive experience in Financial Modeling: 

  • Worked in the Financial advisory unit of a top-tier consulting firm

  • Experience in transactions carried out in Italy, Germany, Switzerland and Poland

  • Worked in the in-house Mergers & Acquisitions department of a large multinational corporation

  • Been a financial advisor in multiple M&A deals with sizes ranging from €2 million to €5 billion

  • Worked on company valuations, due diligence analysis, impairment tests, bankruptcy proceedings, cash flow analysis, and more.

Learn the subtleties of Financial Modeling from someone who has walked the same path. Beat the learning curve and stand out from your colleagues. 

 A comprehensive guide to Financial Modeling in Excel: 

  • Become an Excel expert

  • Learn how to build sound Financial Models and stand out among your colleagues

  • Gain an in-depth understanding of the mechanics of Company Valuation

  • Build your files professionally

  • Demonstrate superior Excel skills at work

  • Be prepared from day one for your Investment Banking, Financial Advisory or Consulting career

What we offer: 

  • Well-designed and easy-to-understand materials

  • Detailed explanations with comprehensible case studies based on real situations

  • Downloadable course materials

  • Regular course updates

  • NEW! Includes professional chart examples that are 1:1 with those used by major banks and consulting firms.

By completing this course, you will:

  • Be able to work comfortably with Microsoft Excel and many of its advanced features

  • Become one of the top Excel users in your team

  • Be much quicker at carrying out regular tasks

  • Be able to build a P&L statement from a raw data extraction

  • Be able to build a cash flow statement

  • Know how to value a company

  • Be able to build a valuation model from scratch

  • Know how to create a model with multiple scenarios

  • Know how to create professional and good-looking advanced charts

About the course:

  • An unconditional Udemy 30-day money-back guarantee – because we believe in the quality of our content

  • No significant previous experience is needed to understand the course properly and benefit from its content

  • Unlimited access to all course materials

  • Emphasis on learning by doing

  • You can always contact us for any clarification free of charge

  • Our goal is to take your Microsoft Excel and Financial Modeling skills to the next level

Make an investment that will be highly rewarded in career prospects, positive feedback, and personal growth. 

This course is suitable for graduates who aspire to become investment bankers as it includes a well-structured DCF model and goes through the theoretical concepts behind it. Moreover, it will encourage you to be more confident when coping with daily tasks and will give you the edge when the firm must decide whether to take you on for a full-time position. 

People with basic knowledge of Excel who go through the course will dramatically increase their Excel skills. 

Go ahead and subscribe to this course! If you don't acquire these skills now, you will miss an opportunity to separate yourself from others. Don't risk your future success! Let's start learning together now! 

Who this course is for:
  • Graduate students who aspire to become investment bankers, financial advisory professionals, consultants etc.
  • Business and finance practitioners who are eager to improve their Excel and Financial Modeling skills
Course content
Expand all 262 lectures 13:53:45
+ Beginner to Pro in Excel: Financial Modeling and Valuation - Welcome!
5 lectures 07:44
Bonus! Welcome gift
1 page
Welcome gift number 2
1 page
Download all course materials and frequently asked questions (FAQ)
02:17
+ Introduction to Excel
12 lectures 33:39
Introduction to Excel
00:37
Overview of Excel
03:33
Overview of Excel
1 question
The Excel ribbon
02:38
Basic operations with rows and columns
01:44
Data entry in Excel
03:00
Introduction to formatting
05:39
Rows and columns
1 question
Introduction to Excel formulas
03:57
Introduction to Excel formulas
1 question
Introduction to Excel functions
03:41
Cut, copy, & paste
02:10
Cut, copy, & paste
1 question
Paste special
01:16
Format cells
05:24

This opening section will take you through the basics of Excel. An easy-to-understand introduction that will bring you up to speed in no time.

Some of the topics and concepts discussed in this lesson:

    ·Row and Column references

    ·Name box

    ·Formula bar

    ·The Ribbon

    ·Format cells

    ·Basic operations in Excel: Copy, Paste, Paste Special, Cut, Undo, Redo

Basic operations in Excel - 38 pages
37 pages
+ Useful tips and tools for your work in Excel
30 lectures 55:53
Excel best practices - Welcome lecture
00:41
Initial formatting of Excel sheets for a professional layout
01:57
Fast scrolling through Excel sheets
01:34
Fast scrolling through Excel sheets
1 question
Be even quicker: F5 + Enter
01:51
Fixing cell references
02:12
Introduction to fixing of cell references
1 question
Alt + Enter
00:48
Learn how to organize your data with text to columns
1 question
The wrap text button
00:43
Set print area
01:10
Custom sort helps you sort multiple columns in Excel tables
01:21
Create drop-down lists with data validation
01:48
Select Special
1 question
Assign dynamic names in a financial model
01:20
Assigning dynamic names
1 question
Define a named range in Excel
01:34
Create a great Index page at the beginning of your models - Hyperlinks
01:16
Introduction to custom formatting in Excel
06:14
Apply custom formatting in a financial model
01:33
How to save macros and use them across several workbooks
01:34
Excel macros - quiz
1 question
Fix the top row of your table with freeze panes
01:11
How to search functionalities in Excel
00:40
Filter by color - an excellent tool
01:43
Working with conditional formatting
05:40
Useful tips and tools for your work in Excel - quiz
5 questions
A neat trick - Multiply by 1
01:34
Find and replace - references
03:03
FAQ: Why do we replace external references and how does this help us?
00:24
Find and replace - formatting
01:53
Removing (automatic) green arrows
01:31
Formula auditing with F2
01:50
+ Keyboard shortcuts in Excel
2 lectures 04:57
Keyboard shortcuts save LOTS of time in Excel
04:57

Learning how to use keyboard shortcuts is an essential part of your Microsoft Excel training. The slides in this lesson will enable you to pick up a number of useful keyboard combinations that will dramatically increase the efficiency of your work

Keyboard shortcuts in Excel
11 pages
+ Excel's key functions and functionalities made easy
21 lectures 01:00:01
Excel's key functions - Welcome lecture
00:45
A helpful consideration
00:45

The IF function tests whether a certain condition is true or false. It works in the following way:

1.Excel tests whether the logical test, which is the first argument of the function, is satisfied

2.If the logical test is satisfied, Excel delivers value A (the second argument of the function)

3.If the logical test is not satisfied, Excel delivers value B (the third argument of the function)

Key functions in Excel: IF
02:35

    SUM functions are widely used in Excel. They allow us to add the numbers within a given cell range

    1.SUM has only one argument – the range of cells to be summed

    2.SUMIF allows us to add the cells within a range, which satisfy a given criterion

    3.SUMIFS is able to add up values in a range of cells that satisfy multiple conditions

Key Excel functions: SUM, SUMIF and SUMIFS
04:20
= and + are interchangeable when you start typing a formula
00:44

    This lesson is dedicated to Excel’s COUNT functions. They are applied when we need to count the cells within a range that contain numbers, text, or satisfy specific conditions. In particular we will be examining:

    1.COUNT - counts the number of cells that contain numbers

    2.COUNTA - counts the number of cells that contain text

    3.COUNTIF - returns the number of cells that satisfy a specific condition

    4.COUNTIFS - counts the number of cells within a given cell range, if multiple conditions are satisfied

Key Excel functions: COUNT, COUNTA, COUNTIF, COUNTIFS
04:00

    This lesson describes the formula syntax and application of the AVERAGE and AVERAGEIF functions in Excel.

    1.AVERAGE finds the mean of a specified range of cells

    2.AVERAGEIF finds the mean of a specified range of cells, if a given condition is satisfied

Key Excel functions: AVERAGE and AVERAGEIF
01:53
Key Excel functions: AVERAGE and AVERAGEIF
1 question

    Text functions are an indispensable tool for any Excel user. In this lesson, we will focus on three types of text functions:

    1.Text functions extracting a part of a cell - LEFT, RIGHT and MID

    2.Functions changing the case of a text - UPPER, LOWER and PROPER

    3.Combining cell values of multiple cells - & and CONCATENATE

Elaborate text efficiently: LEFT, RIGHT, MID, UPPER, LOWER, PROPER, CONCATENATE
04:24
Elaborate text efficiently: LEFT, RIGHT, MID, UPPER, LOWER, PROPER, CONCATENATE
1 question
Working with text in Excel
02:40

    In this video tutorial, we will continue to present to you popular functions in Microsoft Excel;

    1.MAX finds the highest value in a range of cells

    2.MIN allows us to obtain the lowest value in a range of cells

Find the minimum or maximum value in a range of cells in Excel
00:51

ROUND would allow you to round numbers in Excel

Include ROUND in your financial models
01:01

    Transferring data efficiently is at the heart of your success in Microsoft Excel. This lesson provides an easy to understand introduction to two of the venerable functions in Excel:

    1.Vlookup – finds a lookup value in the leftmost column of a table and returns a corresponding value

    2.Hlookup – finds a lookup value in the top row of a table and returns a corresponding value

Learn how to transfer data efficiently in Excel: VLOOKUP and HLOOKUP
07:31
How to enlarge the formula bar
01:03

    The combination of Index and Match is our favorite tool for looking up data. It is a flexible instrument that overcomes some of the shortages of Vlookup and Hlookup;

    1.Index extracts the value lying at the intersection of a particular row and column

    2.Match returns the relative position of an item

    3.The combination of Index & Match - Index needs as an input relative positions within a range, Match delivers those relative positions by finding the lookup value

INDEX, MATCH and their combination - the perfect substitute for VLOOKUP
07:18
A great Excel technique: INDEX, MATCH, MATCH
04:59

INDEX, MATCH and their combination - the perfect substitute for VLOOKUP
1 question

    Error messages are undesired in Excel spreadsheets for several reasons:

    1.If a range contains a cell with an error, this impedes us from carrying out operations with the entire range

    2.They disturb the eye of Excel Users

    3.A file containing error messages cannot be shown to external users

    IFERROR resolves these issues. The function tests whether a given cell contains an error, and if it does it replaces the error message with a value specified by the Excel user

Using Excel's IFERROR function to trap spreadsheet errors
01:42
RANK is a valuable tool when using Excel for financial and business analysis
02:36

CHOOSE is a great tool for Financial Modeling. It allows Excel users to build various scenarios, which can be critical when dealing with an uncertain environment and building a dynamic model.

CHOOSE has n arguments, which can be divided into two groups – its index number (the first argument) and the rest of the arguments.

    ·The Index Number determines, which argument will be selected

    ·The rest of the arguments are the values from, which CHOOSE selects

Learn how to render your models flexible with CHOOSE
02:18

    Goal Seek is part of the “What-if” analysis tool within Excel. It is a nice instrument that allows users to find a desired result for a given parameter by changing other parameters related to it.

    Three inputs are required in order to use the Goal Seek functionality:

    1.A cell to set

    2.The value to which we would like to set the cell

    3.A cell to modify

Use Goal Seek to find the result that you are looking for
02:13
Use Goal Seek in order to find the result that you are looking for
1 question

This lecture introduces you to Data Tables. A powerful tool enabling you to provide sensitivity analysis for a number of parameters. Data Tables illustrate perfectly the influence that a given parameter has on the final output

Include sensitivity analysis in your models through Data Tables
04:06
Include sensitivity analysis in your models through Data Tables
1 question

    Pivot Tables are one of Excel’s most powerful features. They allow us to synthesize and elaborate, with ease, large amounts of data.

    In this lesson we will learn how to:

    1.Create a Pivot Table

    2.Introduce different variables to the Pivot Table Report

    3.Choose the type of calculation that you want to use to summarize data

Excel's dynamic and interactive tables: Pivot tables
02:17
Excel's key functions and functionalities made easy
5 questions
+ Update! SUMIFS - Exercise
2 lectures 08:36

An exercise focusing on the practical application of “Sumifs” - a very interesting Excel function that allows you to create dynamic tables in an efficient way.

Exercise - Excel's SUMIFS function - unsolved
1 page
+ Financial functions in Excel
4 lectures 22:26
Future and present values in Excel
07:02
Calculating the rate of return of an investment with the IRR function
03:30
Calculating a complete loan schedule in Excel
06:16
Date functions in Excel
05:38
+ Microsoft Excel's Pivot Tables in depth
6 lectures 20:14
Introduction to Pivot Tables and the way they are applied
01:35
Creating Pivot Tables easily!
03:47
Give your Excel Pivot Tables a makeover
02:14
Modifying and pivoting fields to obtain the Pivot Table you need
04:33
Learn more about GETPIVOTDATA - A very important Excel function
03:45
+ Case study - Building a complete P&L from scratch in Excel
20 lectures 49:44
Case study - Build a P&L from scratch - Welcome lecture
01:02

    The next part of the course is structured as a Case Study and would allow you to:

    1.Apply in practice what we have seen so far

    2.Work on a real-world task

    3.Enhance your spreadsheet formatting skills

    4.Learn how to create advanced charts

Introduction to the case study
01:06

    Before managing big data, you must understand it first. That is why in this lesson we will focus on understanding our data source. The data in the source sheets is structured in the following way:

    1.Name of P&L account

    2.Partner company number (indicates whether a transaction was registered with a related or with an external counterparty)

    3.Name of Partner company

    4.Registered amount

    5.Accounting code

Understand your data source before you start working on it
02:42

    The next video of our Case Study we will show you how to order a source worksheet. The operations to be carried out include:

    1.Writing titles on the top of the source tables

    2. Placing filters

    3.Align the data in a coherent way

    4.Eliminate unnecessary rows

Order the source worksheets
02:16

When we have a large data extraction, we have to be sure that we will be able to manage it efficiently. A good way to do that is by creating a code, which summarizes the important information about each line item

Create a code: the best way to organize your data and work efficiently with it
01:59

Gathering data from different years (and different sheets) on a single sheet is crucial. It allows the user to classify all the data in a coherent way when attributing a P&L category

Learn how to create a database
01:58

In the previous video we were able to organize all of the line item codes in the same sheet. In this lesson we will apply VLOOKUP in order to fill in some of the fields of our Database

Use VLOOKUP to fill the database sheet
05:29
50% completed!
01:03

    The Database sheet unites all three years of data together. The only fields that are missing are the actual amounts that were observed for each line item during the three years.

    In this lesson, we will fill in the amounts observed for each code with SUMIF. In addition to that, we will use a method that would help us understand whether we have worked correctly or not.

Use SUMIF to complete the database sheet
04:06
FAQ: Sum of FY2018 doens't go down to zero
00:48

    In one of our previous lessons we used VLOOKUP in order to fill some of the fields in the Database sheet. In this video we will show you two things:

    1.INDEX & MATCH is a suitable replacement of VLOOKUP in Microsoft Excel

    2.In certain situations INDEX & MATCH is more powerful than VLOOKUP

Use INDEX & MATCH as a substitute for VLOOKUP
03:51

Given that the Database sheet contains a large amount of rows, we have to group such information into categories. This so-called “Mapping” exercise is necessary as it would allow us to produce a concise (and meaningful) P&L statement

Mapping the rows in the database sheet
02:16
In case you have any doubts about the Mapping exercise
06:15

    In this lesson, we will organize all categories obtained in the “Mapping” exercise into a P&L statement. In order to do that we will have to:

    1.Copy all categories into a new sheet

    2.Remove duplicate values

    3.Order the categories in order to form a meaningful P&L format

Building the structure of the P&L sheet
02:21

Correct formatting will result in a greater initial and lasting impression of your work. In this lesson we will add a few important formatting details to our existing P&L structure – place units of measurement and financial period on top, add subtotal and total lines etc.

Formatting sets You apart from the competition in Excel - A Practical Example
03:17

In this video we will populate the ready P&L structure with the numbers from the Database sheet by using SUMIF

Populate the P&L sheet with SUMIF
02:47
FAQ: Why the sum of the check should be 0?
00:18

    We already said that it is very, very important to use checks in order to verify if the result that was obtained is correct. What if it is not?

    In this lesson we will learn how to find mistakes by using COUNTIF.

Learn how to find mistakes with COUNTIF
02:14

Percentage variations and incidence on total are useful tools, which highlight the dynamics of a given business. In this lesson we will learn how to add them to our P&L statement.

Calculating year-on-year percentage variations the proper way
03:08
FAQ: Why do we subtract -1 when calculating year-on-year growth?
00:47
+ Introduction to Excel charts
6 lectures 14:53
How to insert a chart in Excel
02:10
Editing Excel charts
02:29
Excel chart formatting
03:09
How to create a bridge chart in Excel
01:42
New ways to visualize your data - Treemap charts
01:31
Sparklines
03:52