IIRF Online > Finance & Accounting > Financial Modeling & Analysis > Financial Modeling > Beginner to Pro in Excel: Financial Modeling and Valuation
Beginner to Pro in Excel: Financial Modeling and Valuation by Udemy
Financial Modeling in Excel that would allow you to walk into a job and be a rockstar from day one!
Course Highlights
- Master Microsoft Excel and its advanced features
- Become a top Excel users on your team
- Execute regular tasks quicker
- Build P&L statements from a raw data extraction
- Acquire financial modeling skills
- Learn how to value a company
- Build valuation models from scratch
- Create models with multiple scenarios
- Design professional advanced charts
- Become a proficient user with Excel functions, pivot tables, visualizations, and advanced features
Skills you will learn!
Curriculum
5 Topics
What does the course cover?
Bonus! Welcome gift
Welcome gift number 2
The best way to take this course
Download all course materials and frequently asked questions (FAQ)
16 Topics
Introduction to Excel
Overview of Excel
Overview of Excel
The Excel ribbon
Basic operations with rows and columns
Data entry in Excel
Introduction to formatting
Rows and columns
Introduction to Excel formulas
Introduction to Excel formulas
Introduction to Excel functions
Cut copy & paste
Cut copy & paste
Paste special
Format cells
Basic operations in Excel - 38 pages
1 Topic
Basic Excel - Practice activity
37 Topics
Excel best practices - Welcome lecture
Initial formatting of Excel sheets for a professional layout
Fast scrolling through Excel sheets
Fast scrolling through Excel sheets
Be even quicker: F5 + Enter
Fixing cell references
Introduction to fixing of cell references
Alt + Enter
Organize your data with text to columns
Learn how to organize your data with text to columns
The wrap text button
Set print area
Custom sort helps you sort multiple columns in Excel tables
Create drop-down lists with data validation
Find and select special types of cells with Select special (F5)
Select Special
Assign dynamic names in a financial model
Assigning dynamic names
Define a named range in Excel
Create a great Index page at the beginning of your models - Hyperlinks
Introduction to custom formatting in Excel
Apply custom formatting in a financial model
Macros are a great timesaver! Here's why!
How to save macros and use them across several workbooks
Excel macros - quiz
Fix the top row of your table with freeze panes
How to search functionalities in Excel
Filter by color - an excellent tool
Working with conditional formatting
Useful tips and tools for your work in Excel - quiz
A neat trick - Multiply by 1
Find and replace - references
FAQ: Why do we replace external references and how does this help us?
Find and replace - formatting
Removing (automatic) green arrows
Beauty saving - The professional way of saving files
Formula auditing with F2
1 Topic
Excel tips & tools - Practice activity
2 Topics
Keyboard shortcuts save LOTS of time in Excel
Keyboard shortcuts in Excel
28 Topics
Excel's key functions - Welcome lecture
A helpful consideration
Key functions in Excel: IF
Key Excel functions: SUM SUMIF and SUMIFS
= and + are interchangeable when you start typing a formula
Key Excel functions: COUNT COUNTA COUNTIF COUNTIFS
Key Excel functions: AVERAGE and AVERAGEIF
Key Excel functions: AVERAGE and AVERAGEIF
Work with text efficiently: LEFT RIGHT MID UPPER LOWER PROPER CONCATENATE
Work with text efficiently: LEFT RIGHT MID UPPER LOWER PROPER CONCATENATE
Working with text (continued)
Find the minimum or maximum value in a range of cells in Excel
Include ROUND in your financial models
Key Excel functions: VLOOKUP and HLOOKUP
How to enlarge the formula bar
INDEX MATCH and their combination - the perfect substitute for VLOOKUP
A great Excel technique: INDEX MATCH MATCH
INDEX MATCH and their combination - the perfect substitute for VLOOKUP
XLOOKUP: a solid substitute for VLOOKUP and INDEX&MATCH
Using Excel's IFERROR function to trap spreadsheet errors
RANK is a valuable tool when using Excel for financial and business analysis
CHOOSE - Learn how to render your models flexible
Use Goal Seek to find the result that you are looking for
Use Goal Seek in order to find the result that you are looking for
Include sensitivity analysis in your models through Data Tables
Include sensitivity analysis in your models through Data Tables
Excel's dynamic and interactive tables: Pivot tables
Excel's key functions and functionalities made easy
2 Topics
Exercise - Excel's SUMIFS function - unsolved
Exercise - Excel's SUMIFS function - solved & explained
1 Topic
Excel formulas - Practice activity
4 Topics
Future and present values in Excel
Calculating the rate of return of an investment with the IRR function
Calculating a complete loan schedule in Excel
Date functions in Excel
6 Topics
Introduction to Pivot Tables and the way they are applied
Creating Pivot Tables easily!
Give your Excel Pivot Tables a makeover
Modifying and pivoting fields to obtain the Pivot Table you need
Learn more about GETPIVOTDATA - A very important Excel function
An introduction to slicers - The modern-day Pivot Table filters
20 Topics
Case study - Build a P&L from scratch - Welcome lecture
Introduction to the case study
Understand your data source before you start working on it
Order the source worksheets
Create a code: the best way to organize your data and work efficiently with it
Learn how to create a database
Use VLOOKUP to fill the database sheet
Use SUMIF to complete the database sheet
FAQ: Sum of FY2018 doens't go down to zero
Use INDEX & MATCH as a substitute for VLOOKUP
Substituting VLOOKUP with XLOOKUP (Office 365 Only)
Mapping the rows in the database sheet
In case you have any doubts about the Mapping exercise
Building the structure of the P&L sheet
Formatting sets You apart from the competition in Excel - A Practical Example
Populate the P&L sheet with SUMIF
FAQ: Why the sum of the check should be 0?
Learn how to find mistakes with COUNTIF
Calculating year-on-year percentage variations the proper way
FAQ: Why do we subtract -1 when calculating year-on-year growth?
6 Topics
How to insert a chart in Excel
Editing Excel charts
Excel chart formatting
How to create a bridge chart in Excel
New ways to visualize your data - Treemap charts
Sparklines
8 Topics
Create professional and good-looking charts - Introduction
Build a column stacked chart with a secondary line axis in Excel
Learn how to build effective doughnut charts in Excel
Learn how to build an area chart in Excel
Learn how to create bridge charts
Learn how to create bridge charts in Excel 2007 2010 and 2013
Case Study - Building a Complete P&L from scratch in Excel
Course Challenge - Apply your skills in practice!
6 Topics
Trend Analysis in Excel
Comparative Analysis in Excel
Value-based analysis in Excel
Correlation analysis in Excel
Time series analysis in Excel
Regression analysis in Excel
14 Topics
Introduction to the Case Study
Preliminary mapping of the data extraction
Working with an SAP data extraction
Creating an output structure of the FMCG model
Improving the layout and appearance of the FMCG report
Inserting formulas and automating calculations
Creating a Master Pivot Table: The main data source for the FMCG report
GETPIVOTDATA is great! Extracting data from the Master Pivot Table
A potential error with GETPIVOTDATA you might encounter and its fix
FAQ: My GETPIVOTDATA function doesn't work
Combining Slicers and GETPIVOTDATA: The key to our success
Getting fancy with Excel slicers - Good-looking Excel slicers
This is how the report can be used in practice by high-level executives
Dynamic reporting with GETPIVOTDATA and slicers
15 Topics
Financial modeling basics - Welcome lecture
What is a financial model?
Why use financial models?
Financial modeling - worst practices - things you should avoid
Financial modeling - best practices
Financial modeling - The types of models that are built in practice
Financial modeling - Quiz
Financial modeling: The right level of detail in a model
Financial modeling: Forecasting guidelines
Forecasting financials - Quiz
Building a complete model - Important considerations
Modeling the Income statement
Modeling the Balance sheet - Part 1
Modeling the Balance sheet - Part 2
Building a financial model - Quiz
11 Topics
Why value a company
An investor's perspective
The main value drivers when valuing a business
How to calculate Unlevered Free Cash Flow
How to calculate Weighted Average Cost of Capital (WACC)
How to calculate cost of debt
How to calculate cost of equity
Forecasting a firm's financials
How to obtain Terminal Value
Discounting cash flows
How to obtain Enterprise and Equity Value
1 Topic
Introduction to Mergers & Acquisitions
31 Topics
Valuation Case study - Welcome lecture
Introduction to the DCF exercise
The stages of a complete DCF Valuation
Description of the structure of the DCF model
A glimpse at the company we are valuing - Cheeseco
Modeling the top line
Introducing scenarios to the model with Choose
Modeling other items: Other revenues and Cogs
Modeling other items: Operating expenses and D&A
Modeling other items: Interest expenses Extraordinary items and Taxes
Forecasting Balance sheet items
An introduction to the "Days" methodology
Calculation of DSO DPO and DOI for the historical period
Forecasting DSO DPO and DOI
Forecasting Property Plant & Equipment Other assets and Other liabilities
Creating an output P&L sheet
Populating the output P&L sheet
Populating the output BS sheet
Completing the output BS sheet for the historical period
Creating a structure for the calculation of Unlevered free cash flows
Bridging Unlevered free cash flow to Net cash flow
Calculating Unlevered free cash flow
Calculating Net cash flow
Obtaining the rest of the cash flows through Find and Replace
Introducing Weighted average cost of capital and Perpetuity growth
Discounting Unlevered free cash flows to obtain their Present value
Calculating Continuing value and Enterprise value of the business
Calculating Equity value
Sensitivity analysis for WACC and perpetuity growth
A possible application of Goal seek
Using charts to summarize the results of the DCF model
30 Topics
Organizing external inputs in a 'Drivers' sheet
Forecasting Tesla's expected deliveries
Comparing delivery figures with the ones of industry peers
Estimating an average selling price of Tesla vehicles
Calculating automotive revenue
Peer comparison: Gross profit %
Calculating automotive gross profit
Calculating automotive cost of sales
Forecasting 'energy' and 'services' revenue
Calculating 'energy' and 'services' gross profit and cost of sales
Forecasting operating expenses
Building a fixed asset roll forward: PP&E
Building a fixed asset roll forward: estimating Capex
Building a fixed asset roll forward: D&A schedule
Calculating DSO DIO DPO
Producing a clean P&L output sheet
Calculating investments in working capital
Forecasting Unlevered free cash flow
Forecasting other assets
Forecasting other liabilities
Completing Unlevered free cash flow
Modeling Tesla's financing needs in the forecast period
Calculating Net income
Bridging Unlevered free cash flow to Net cash flow
Balancing the Balance sheet
Estimating Weighted average cost of capital (WACC)
Performing discounted cash flow valuation (DCF)
Calculating enterprise value equity value and price per share
Final comments
You made it!
13 Topics
What are valuation multiples and why we use them?
What types of valuation multiples are there?
Trading vs transaction valuation multiples
Main principles of multiples valuation
Comparison of earnings multiples (P/E vs EV/EBITDA)
Introduction to the exercise
High-level assessment of peer companies
Assessment of P&L data - comparable companies
How to adjust EBIT - theoretical framework
How to adjust EV - theoretical framework
How to adjust EBIT - practical example - Volkswagen
How to adjust EV - practical example - Volkswagen
Conclusion of the practical exercise
30 Topics
Case description
Introduction to LBO Modeling exercise
Key drivers in the LBO model
Constructing the Profit and Loss header
Analyzing historical Profit and Loss figures
Valuing the Target company
Estimating transaction fees
Sources and uses of funds
Shaping the Balance sheet at transaction
Goodwill calculation
Integrating assumptions into the Drivers sheet
Building a Fixed asset roll forward schedule
Forecasting financials using the Drivers sheet
Completing the Profit and Loss statement (up to EBITDA)
Modeling Working capital
Filling in the Balance sheet at transaction sheet
Add financials to the Balance sheet
Projecting Fixed asset roll forward
Developing a Debt schedule
Creating a Fixed asset amortization schedule
Structuring Cash Flow
Designing the Financing sheet
Calculating Cash Flow
Building an Equity schedule
Finalizing the Financing Cash Flow
Modeling the Revolver facility (first part)
Completing the Profit and Loss statement
Modeling the Revolver facility (second part)
Balancing the Balance sheet
Exit valuation and IRR comparison
9 Topics
Introduction to Capital budgeting
Why we need Capital budgeting?
The time value of money
Calculating future and present value
Calculating cost of equity
Coming up with project-specific beta
Weighted average cost of capital (WACC)
The type of cash flows we will have in a project
Estimating the project's cash flows
15 Topics
Introduction to the Capital budgeting exercise
Organizing an "Inputs" sheet
Forecasting savings: Building a plant in Vietnam vs. producing cars in Italy
Fixed asset rollforward
The impact of working capital
Modeling debt financing
Adding a P&L sheet
Calculating project cash flows
Estimating the weighted average cost of capital (WACC)
Finding cost of equity
Discounting the project's cash flows and residual value
Performing sensitivity analysis and completing the exercise
FAQ: Data does not change in the DCF sheet it changes from the Drivers sheet
Build a complete capital budgeting model from scratch
Congratulations!!
1 Topic
Next steps
2 Topics
How ChatGPT Advanced Data Analysis Will Boost Your Productivity
The Limitations of ChatGPT Advanced Data Analysis
4 Topics
Exploratory Data Analysis - Intro
Extracting Descriptive Statistics with ChatGPT
Identifying Outliers
Crafting a Comprehensive EDA Prompt
5 Topics
Identifying and Handling Erroneous Data
Verifying Data for Errors
Eliminating Duplicate Rows
Addressing Unit Discrepancies in Data
Dividing Cell Content into Multiple Cells
4 Topics
Organize Messy Data
Data Mapping
Effective Data Classification
Order Data with ChatGPT
5 Topics
COUNTIFS SUMIFS
Data Lookup
Data Ranking
Fill Until Non-blank Values
Translating Logic into Excel Formulas with ChatGPT
7 Topics
Correcting Inverted Names
Dealing with Trailing and Leading Spaces in Text Data
Standardizing Letter Capitalization
Handling Multi-lingual Text Data
Multiple "Find and Replace" at the Same Time with ChatGPT
Criteria-based Duplicate Removal
Removing Duplicates Across Multiple Columns
4 Topics
Obtain Day and Month From Date
Converting Hours to Decimals
Obtaining Age in Years Months Days
Order Dates Chronologically
5 Topics
Intro to the Exercise and Preprocessing Ideas from ChatGPT
Preprocessing the 'Free plan users' Sheet (1/2)
Preprocessing the 'Free plan users' Sheet (2/2)
Working on the 'Transactions' Sheet
Dealing with the Country Mapping Issue
9 Topics
Overview of Excel
Basic manipulations with rows and columns
The Excel ribbon
Data entry in Excel
Introduction to formatting
Introduction to Excel formulas
Introduction to Excel functions
Cut copy & paste
Paste special
16 Topics
Initial formatting of an Excel sheet to render it professional
Fast scrolling through Excel sheets
Introduction to fixing of cell references
Learn how to use the Wrap Text button
Create a drop-down list by using Data Validation
Using Custom-sort in order to sort multiple columns within a table
Create a great Index page at the beginning of your models - Hyperlinks
Fix the top row of your table with Freeze Panes
Macros are a great timesaver! Here's why!
Find and select special types of cells with Select Special (F5)
Assign custom formats to specific cells within a financial model (e.g Multiples)
Define a named range in Excel
Learn how to organize your data with Text to Columns
Learn how to assign dynamic names within a financial model
Create easily printable documents in Excel by using Set Print Area
Using the 'Alt + Enter' combination
1 Topic
Keyboard shortcuts Save LOTS of time in Excel 2010
13 Topics
Key Excel functions: SUM SUMIF and SUMIFS
Key Excel functions: COUNT COUNTA COUNTIF COUNTIFS
Key Excel functions: AVERAGE and AVERAGEIF
Elaborate text efficiently: LEFT RIGHT MID UPPER LOWER PROPER CONCATENATE
Find the minimum or maximum value in a range of cells in Excel
Include ROUND in your financial models
Key Excel functions: VLOOKUP and HLOOKUP
INDEX MATCH and their combination - the perfect substitute for VLOOKUP
Using Excel's IFERROR function to trap spreadsheet errors
Learn how to render your models flexible with CHOOSE
Use Goal Seek to find the result that you are looking for
Include sensitivity analysis in your models through Data Tables
Excel's dynamic and interactive tables: Pivot tables
11 Topics
Why value a company
An investor's perspective when valuing a company
Which are the drivers of company value?
How to calculate UFCF
What is WACC
How to find cost of debt
How to find cost of equity
Forecasting financials
Calculating terminal value
Discounting cash flows
Calculating enterprise and equity value
Beginner to Pro in Excel: Financial Modeling and Valuation
Related Financial Modeling Courses
Thank You!
Your review has been submitted successfully.