Beginner to Pro in Excel: Financial Modeling and Valuation - Welcome!

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)

Introduction to Excel

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

Useful tips and tools for your work in Excel

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

Keyboard shortcuts in Excel

Keyboard shortcuts save LOTS of time in Excel
Keyboard shortcuts in Excel

Excel's key functions and functionalities made easy

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

Update! SUMIFS - Exercise

Exercise - Excel's SUMIFS function - unsolved
Exercise - Excel's SUMIFS function - solved & explained

Financial functions in Excel

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

Microsoft Excel's Pivot Tables in depth

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

Case study - Building a complete P&L from scratch in Excel

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?

Introduction to Excel charts

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

P&L Case Study continued - Let's create some great-looking professional charts

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!

Business analysis techniques applied in Excel

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

Case Study - Building an FMCG Model from Scratch

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

Financial modeling fundamentals

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

Introduction to Company Valuation

The core theory behind Company Valuation

Introduction to Mergers & Acquisitions

Introduction to Mergers & Acquisitions
Why acquire another firm
Company valuation

Learn how to build a Discounted Cash Flow model in Excel

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

Tesla valuation - Complete practical exercise

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!

Capital budgeting - The theory

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

Capital Budgeting - A complete Case study

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!!

Next steps

Next steps

APPENDIX - Microsoft Excel 2010 - Introduction to Excel

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

APPENDIX - Microsoft Excel 2010 - Useful tips and tricks

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