Course Content
Section 29: BONUS — Data Analysis with ChatGPT — Deal with Inconsistent Data
topic_summary
0/7
Identifying and Handling Erroneous Data
Verifying Data for Errors
Eliminating Duplicate Rows
Addressing Unit Discrepancies in Data
Dividing Cell Content into Multiple Cells
Section 29: BONUS — Data Analysis with ChatGPT — Deal with Inconsistent Data quiz
Section 29: BONUS — Data Analysis with ChatGPT — Deal with Inconsistent Data project
Section 22: How to Adjust EBIT — Theoretical Framework
topic_summary
0/3
How to adjust EV — theoretical framework
Section 22: How to adjust EBIT — theoretical framework quiz
Section 22: How to adjust EBIT — theoretical framework project
Section 22: How to Adjust EBIT — Practical Example — Volkswagen
topic_summary
0/3
How to adjust EBIT — practical example — Volkswagen
Section 22: How to adjust EBIT — practical example — Volkswagen quiz
Section 22: How to adjust EBIT — practical example — Volkswagen project
Section 22: How to adjust EV — practical example — Volkswagen
topic_summary
0/3
How to adjust EV — practical example — Volkswagen
Section 22: How to adjust EV — practical example — Volkswagen quiz
Section 22: How to adjust EV — practical example — Volkswagen project
Section 22: Conclusion of the practical exercise
topic_summary
0/3
Conclusion of the practical exercise
Section 22: Conclusion of the practical exercise quiz
Section 22: Conclusion of the practical exercise project
Section 23: Comprehensive LBO Valuation
topic_summary
0/31
Filling in the Balance Sheet at Transaction Sheet
Section 23: Comprehensive LBO Valuation Quiz
Exit valuation and IRR comparison
Balancing the Balance sheet
Modeling the Revolver facility (second part)
Completing the Profit and Loss statement
Modeling the Revolver facility (first part)
Finalizing the Financing Cash Flow
Building an Equity schedule
Calculating Cash Flow
Designing the Financing sheet
Structuring Cash Flow
Creating a Fixed asset amortization schedule
Developing a Debt schedule
Projecting Fixed Asset Roll Forward
Add financials to the Balance sheet
Case description
Modeling Working Capital
Completar el estado de pérdidas y ganancias (hasta EBITDA)
Forecasting financials using the Drivers sheet
Building a Fixed asset roll forward schedule
Integrating assumptions into the Drivers sheet
Goodwill calculation
Shaping the Balance sheet at transaction
Sources and uses of funds
Estimating transaction fees
Valuing the Target company
Analyzing historical Profit and Loss figures
Constructing the Profit and Loss header
Key drivers in the LBO model
Introduction to LBO Modeling exercise
Section 24: Capital Budgeting — The Theory
topic_summary
0/8
Introduction to Capital budgeting
Why do 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)
Section 24: Capital budgeting — The theory quiz
Section 25: Capital Budgeting — A Complete Case Study
topic_summary
0/15
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
Estimando el costo promedio ponderado de capital (WACC)
Finding cost of equity
Performing sensitivity analysis and completing the exercise
FAQ: Data does not change in the DCF sheet, it changes from the Drivers sheet
Assignment 5: Build a complete capital budgeting model from scratch
Congratulations!!
Section 25: Capital Budgeting — A complete Case study quiz
Section 26: Next Steps
Topic Summary
0/3
Next steps
Section 26: Next steps quiz
Section 26: Next steps project
Section 27: BONUS — Data Analysis with ChatGPT — Intro
topic_summary
0/4
How ChatGPT Advanced Data Analysis Will Boost Your Productivity
The Limitations of ChatGPT Advanced Data Analysis
Section 27: BONUS — Data Analysis with ChatGPT — Intro quiz
Section 27: BONUS — Data Analysis with ChatGPT — Intro project
Section 28: BONUS — Data Analysis with ChatGPT — Exploratory Data
topic_summary
0/7
Exploratory Data Analysis — Intro
Extracting Descriptive Statistics with ChatGPT
Identifying Outliers
Correlation Analysis: Hypothesis Formulation and Feature Ideas
Crafting a Comprehensive EDA Prompt
Section 28: BONUS — Data Analysis with ChatGPT — Exploratory Data quiz
Section 28: BONUS — Data Analysis with ChatGPT — Exploratory Data project
Section 22: How to Value a Company with Multiples
topic_summary
0/11
What are valuation multiples and why do 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
Section 22: How to value a company with multiples quiz
Section 22: How to Value a Company with Multiples Project
Section 30: BONUS — Data Analysis with ChatGPT — Organize Messy Data
topic_summary
0/6
Organize Messy Data
Data Mapping
Effective Data Classification
Order Data with ChatGPT
Section 30: BONUS — Data Analysis with ChatGPT — Organize Messy Data quiz
Section 30: BONUS — Data Analysis with ChatGPT — Organize Messy Data project
Section 31: BONUS — Data Analysis with ChatGPT — Working with Functions
topic_summary
0/7
COUNTIFS, SUMIFS
Data Lookup
Data Ranking
Fill Until Non-blank Values
Translating Logic into Excel Formulas with ChatGPT
Section 31: BONUS — Data Analysis with ChatGPT — Working with Functions quiz
Section 31: BONUS — Data Analysis with ChatGPT — Working with Functions project
Section 32: BONUS — Data Analysis with ChatGPT — Clean Text Data
Topic Summary
0/9
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
Section 32: BONUS — Data Analysis with ChatGPT — Clean Text Data quiz
Section 32: BONUS — Data Analysis with ChatGPT — Clean Text Data project
Section 33: BONUS — Data Analysis with ChatGPT — Dates and Time Data
topic_summary
0/6
Obtain Day and Month From Date
Converting Hours to Decimals
Obtaining Age in Years, Months, Days
Order Dates Chronologically
Section 33: BONUS — Data Analysis with ChatGPT — Dates and Time Data quiz
Section 33: BONUS — Data Analysis with ChatGPT — Dates and Time Data project
Section 34: BONUS — Data Analysis with ChatGPT — Complete Practical Exercise
topic_summary
0/7
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)
Analyzing the ‘Free plan users’ Data
Concluindo o Exercício Prático
Section 34: BONUS — Data Analysis with ChatGPT — Complete Practical Exercise quiz
Section 34: BONUS — Data Analysis with ChatGPT — Complete Practical Exercise project
Section 35: APPENDIX — Microsoft Excel 2010 — Introduction to Excel
topic_summary
0/11
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
Section 35: APPENDIX — Microsoft Excel 2010 — Introduction to Excel quiz
Section 35: APPENDIX — Microsoft Excel 2010 — Introduction to Excel project
Section 36: APPENDIX — Microsoft Excel 2010 — Useful tips and tricks
topic_summary
0/18
Find and select special types of cells with Select Special (F5)
Section 36: APPENDIX — Microsoft Excel 2010 — Useful tips and tricks project
Section 36: APPENDIX — Microsoft Excel 2010 — Useful tips and tricks quiz
Using the ‘Alt + Enter’ combination
Create easily printable documents in Excel by using Set Print Area
Learn how to assign dynamic names within a financial model
Learn how to organize your data with Text to Columns
Define a named range in Excel
Assign custom formats to specific cells within a financial model (e.g. Multiples)
Initial formatting of an Excel sheet to render it professional
Macros are a great timesaver! Here’s why!
Fix the top row of your table with Freeze Panes
Create a great Index page at the beginning of your models — Hyperlinks
Using Custom-sort in order to sort multiple columns within a table
Create a drop-down list by using Data Validation
Learn how to use the Wrap Text button
Introduction to fixing of cell references
Fast scrolling through Excel sheets
Section 37: APPENDIX — Microsoft Excel 2010 — Keyboard shortcuts
topic_summary
0/3
Keyboard shortcuts Save LOTS of time in Excel 2010
Section 37: APPENDIX — Microsoft Excel 2010 — Keyboard shortcuts quiz
Section 37: APPENDIX — Microsoft Excel 2010 — Keyboard shortcuts project
Section 38: APPENDIX — Microsoft Excel 2010 — Excel Functions
topic_summary
0/15
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
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
Section 38: APPENDIX — Microsoft Excel 2010 — Excel functions quiz
Section 38: APPENDIX — Microsoft Excel 2010 — Excel functions project
Section 39: APPENDIX — Company Valuation
topic_summary
0/13
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
Section 39: APPENDIX — Company Valuation quiz
Section 39: APPENDIX — Company Valuation project
Section 13: Introduction to Excel Charts
topic_summary
0/8
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
Section 13: Introduction to Excel charts quiz
Section 13: Introduction to Excel charts project
Section 3: Assignment 1
topic_summary
0/3
Basic Excel — Practice activity
Section 3: Assignment 1 quiz
Section 3: Assignment 1 project
Section 4: Useful Tips and Tools for Your Work in Excel
topic_summary
0/38
A neat trick — Multiply by 1
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
Quiz: Excel macros — quiz
Fix the top row of your table with freeze panes
00:00
How to search functionalities in Excel
Filter by color — an excellent tool
Working with conditional formatting
Quiz: Useful tips and tools for your work in Excel — quiz
Introduction to custom formatting in Excel
Find and replace — references
00:00
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
Section 4: Useful tips and tools for your work in Excel quiz
Section 4: Useful tips and tools for your work in Excel project
Set print area
Initial formatting of Excel sheets for a professional layout
00:00
Fast scrolling through Excel sheets
00:00
Be even quicker: F5 + Enter
Fixing cell references
Quiz: Introduction to fixing of cell references
00:00
Alt + Enter
Organize your data with text to columns
Quiz: Learn how to organize your data with text to columns
The wrap text button
Excel best practices — Welcome lecture
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)
00:00
Quiz: Select Special
Assign dynamic names in a financial model
Quiz: Assigning dynamic names
00:00
Define a named range in Excel
Create a great Index page at the beginning of your models — Hyperlinks
Section 5: Assignment 2
Topic Summary
0/3
Excel tips & tools — Practice activity
Section 5: Assignment 2 quiz
Section 5: Assignment 2 project
Section 6: Keyboard Shortcuts in Excel
topic_summary
0/4
Keyboard shortcuts save LOTS of time in Excel
Keyboard shortcuts in Excel
Section 6: Keyboard shortcuts in Excel quiz
Section 6: Keyboard shortcuts in Excel project
Section 7: Excel’s key functions and functionalities made easy
topic_summary
0/29
How to enlarge the formula bar
Section 7: Excel’s key functions and functionalities made easy project
Section 7: Excel’s key functions and functionalities made easy quiz
Excel’s dynamic and interactive tables: Pivot tables
Quiz: Include sensitivity analysis in your models through Data Tables
Include sensitivity analysis in your models through Data Tables.
Quiz: Use Goal Seek in order to find the result that you are looking for
Use Goal Seek to find the result that you are looking for
CHOOSE — Learn how to make your models flexible.
RANK is a valuable tool when using Excel for financial and business analysis.
Using Excel’s IFERROR function to trap spreadsheet errors
XLOOKUP: a solid substitute for VLOOKUP and INDEX&MATCH
Quiz: 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
Excel’s key functions — Welcome lecture
Key Excel functions: VLOOKUP and HLOOKUP
Include ROUND in your financial models
Find the minimum or maximum value in a range of cells in Excel
Working with text (continued)
Quiz: Work with text efficiently: LEFT, RIGHT, MID, UPPER, LOWER, PROPER, …
Work with text efficiently: LEFT, RIGHT, MID, UPPER, LOWER, PROPER, CONCATENATE
Quiz: Key Excel functions: AVERAGE and AVERAGEIF
Key Excel functions: AVERAGE and AVERAGEIF
Key Excel functions: COUNT, COUNTA, COUNTIF, COUNTIFS
«=» and + are interchangeable when you start typing a formula
00:00
Key Excel functions: SUM, SUMIF, and SUMIFS
00:00
Key functions in Excel: IF
A helpful consideration
Section 8: Update! SUMIFS — Exercise
topic_summary
0/4
Exercise — Excel’s SUMIFS function — unsolved
Exercise — Excel’s SUMIFS function — solved & explained
Section 8: Update! SUMIFS — Exercise quiz
Section 8: Update! SUMIFS — Exercise project
Section 9: Assignment 3
topic_summary
0/3
Excel formulas — Practice activity
Section 9: Assignment 3 quiz
Section 9: Assignment 3 project
Section 10: Financial Functions in Excel
topic_summary
0/6
Future and present values in Excel
Calculando la tasa de retorno de una inversión con la función TIR
Calculating a complete loan schedule in Excel
Date functions in Excel
Section 10: Financial functions in Excel quiz
Section 10: Financial functions in Excel project
Section 11: Microsoft Excel’s Pivot Tables in Depth
topic_summary
0/8
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
Section 11: Microsoft Excel’s Pivot Tables in depth quiz
Section 11: Microsoft Excel’s Pivot Tables in Depth Project
Section 12: Case Study — Building a Complete P&L from Scratch in Excel
topic_summary
0/22
Mapping the rows in the database sheet
Section 12: Case study — Building a complete P&L from scratch in Excel project
Section 12: Case study — Building a complete P&L from scratch in Excel quiz
FAQ: Why do we subtract -1 when calculating year-on-year growth?
Calculando variações percentuais ano a ano da maneira correta
Learn how to find mistakes with COUNTIF
FAQ: Why should the sum of the check be 0?
Populate the P&L sheet with SUMIF
Formatting sets You apart from the competition in Excel — A Practical Example
Building the structure of the P&L sheet
In case you have any doubts about the Mapping exercise
Case study — Build a P&L from scratch — Welcome lecture
Substituting VLOOKUP with XLOOKUP
Use INDEX & MATCH as a substitute for VLOOKUP
FAQ: The sum of FY2018 does not decrease to zero.
Use SUMIF to complete the database sheet
Use VLOOKUP to fill the database sheet
Learn how to create a database
Create a code: the best way to organize your data and work efficiently with it
Order the source worksheets
Understand your data source before you start working on it
Introduction to the case study
Section 2: Introduction to Excel
topic_summary
0/17
Quiz: Introduction to Excel formulas
Section 2: Introduction to Excel project
Section 2: Introduction to Excel quiz
Basic operations in Excel — 38 pages
Format cells
Paste special
Quiz: Cut, copy, & paste
Cut, copy, & paste
Introduction to Excel functions
Introduction to Excel
Introduction to Excel formulas
Quiz: Rows and columns
Introduction to formatting
Data entry in Excel
00:00
Basic operations with rows and columns
The Excel ribbon
Overview of Excel
Section 14: P&L Case Study Continued
topic_summary
0/9
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
Course Challenge — Apply your skills in practice!
Section 14: P&L Case Study continued quiz
Section 14: P&L Case Study continued project
Section 15: Business Analysis Techniques Applied in Excel
topic_summary
0/8
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
Section 15: Business analysis techniques applied in Excel quiz
Section 15: Techniques d’analyse commerciale appliquées dans le projet Excel
Section 16: Case Study — Building an FMCG Model from Scratch
topic_summary
0/15
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
Getting fancy with Excel slicers — Good-looking Excel slicers
This is how the report can be used in practice by high-level executives.
Assignment 4: Dynamic reporting with GETPIVOTDATA and slicers
Section 16: Case Study — Building an FMCG Model from Scratch quiz
Section 16: Case Study — Building an FMCG Model from Scratch project
Section 17: Financial Modeling Fundamentals
topic_summary
0/16
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: The right level of detail in a model
Financial modeling: Forecasting guidelines
Quiz: Forecasting financials
Building a complete model — Important considerations
Modeling the Income Statement
Modeling the Balance Sheet — Part 1
Modeling the Balance Sheet — Part 2
Quiz: Building a financial model
Section 17: Financial modeling fundamentals quiz
Section 17: Financial Modeling Fundamentals Project
Section 18: Introduction to Company Valuation
topic_summary
0/13
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
Section 18: Introduction to Company Valuation quiz
Section 18: Introduction to Company Valuation project
Section 19: Introduction to Mergers & Acquisitions
Topic Summary
0/3
Introduction to Mergers & Acquisitions
Section 19: Introduction to Mergers & Acquisitions quiz
Section 19: Introduction to Mergers & Acquisitions Project
Section 20: Learn how to build a Discounted Cash Flow model in Excel
topic_summary
0/30
Creating an output P&L sheet
Section 20: Learn how to build a Discounted Cash Flow model in an Excel project
Section 20: Learn how to build a Discounted Cash Flow model in Excel quiz
Calculating Equity value
Calculando el valor continuado y el valor empresarial del negocio
Discounting Unlevered free cash flows to obtain their Present value
Introducing Weighted Average Cost of Capital and Perpetuity Growth
Obtaining the rest of the cash flows through Find and Replace
Calculando el flujo de caja neto
Calculating Unlevered free cash flow
Bridging Unlevered free cash flow to Net cash flow
Creating a structure for the calculation of Unlevered free cash flows
Completing the output BS sheet for the historical period
Populating the output BS sheet
Populating the output P&L sheet
Valuation Case Study — Welcome Lecture
Forecasting Property, Plant & Equipment, Other Assets, and Other Liabilities
Forecasting DSO, DPO, and DOI
Calculation of DSO, DPO, and DOI for the historical period
An introduction to the «Days» methodology
Forecasting Balance Sheet Items
Modeling other items: Interest expenses, Extraordinary items, and Taxes
Modeling other items: Operating expenses and D&A
Modeling other items: Other revenues and COGS
Introducing scenarios to the model with Choose
Modeling the top line
A glimpse at the company we are valuing — CheeseCo
Description of the structure of the DCF model
The stages of a complete DCF Valuation
Introduction to the DCF exercise
Section 20: Course Content
topic_summary
0/5
Sensitivity analysis for WACC and perpetuity growth
A possible application of Goal seek
Using charts to summarize the results of the DCF model
Section 20: Course content quiz
Section 20: Course content project
Section 21: Tesla Valuation — Complete Practical Exercise
topic_summary
0/32
Calculating investments in working capital
Section 21: Tesla valuation — Complete practical exercise project
Section 21: Tesla valuation — Complete practical exercise quiz
You made it!
Final comments
Calculando el valor empresarial, el valor de capital y el precio por acción
Performing discounted cash flow valuation (DCF)
Estimación del costo promedio ponderado de capital (WACC)
Balancing the Balance sheet
Bridging Unlevered free cash flow to Net cash flow
Calculating Net Income
Modelando las necesidades de financiamiento de Tesla en el período de pronóstico
Completing Unlevered free cash flow
Forecasting other liabilities
Forecasting other assets
Forecasting Unlevered Free Cash Flow
Organizing external inputs in a ‘Drivers’ sheet
Producing a clean P&L output sheet
Calculating DSO, DIO, DPO
Building a fixed asset roll forward: D&A schedule
Building a fixed asset roll forward: estimating Capex
Building a fixed asset roll forward: PP&E
Forecasting operating expenses
Calculating ‘energy’ and ‘services’ gross profit and cost of sales
Forecasting ‘energy’ and ‘services’ revenue
Calculating automotive cost of sales
Calculating automotive gross profit
Peer comparison: Gross profit %
Calculating automotive revenue
Estimating an average selling price of Tesla vehicles
Comparar las cifras de entrega con las de los colegas de la industria
Forecasting Tesla’s expected deliveries
Beginner to Pro in Excel: Financial Modeling and Valuation
    Join the conversation
    Previous
    Next
    0% Complete
    This site is registered on wpml.org as a development site. Switch to a production site key to remove this banner.