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!

What you'll learn

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

Requirements

Microsoft Excel 2010, 2013, 2016, 2020, or Microsoft Excel 365

Description

*Updated for January 2023*Do you want to learn how to use Excel in a real working environment?Are you about to graduate from a university and look 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 for financial modeling in Excel?  Join over 180,000+ successful students taking this course.Your instructor possesses the following extensive experience in financial modeling:  Financial advisory unit of a top-tier consulting firmExperience in M&A transactions executed in the UK, Italy, Germany, Switzerland, and PolandEmployed in the in-house Mergers & Acquisitions department of a large multinational corporationA financial advisor in multiple M&A deals ranging from €2 million to €5 billionWorked 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 with this course today.   A comprehensive guide to financial modeling in Excel:  Become an Excel expertBuild sound financial models in ExcelGain an in-depth understanding of the mechanics of company valuationCreate professionally formatted filesDemonstrate superior Excel skills at workPrepare before employment in investment banking, financial advisory, corporate finance, or consultingWhat We Offer:  Well-designed and easy-to-understand materialsDetailed explanations with comprehensible case studies based on real situationsDownloadable course materialsRegular course updatesProfessional chart examples used by major banks and consulting firms.Real-life, step-by-step examplesUpon completing this course, you'll be able to do the following:Work comfortably with Microsoft Excel and many of its advanced featuresBecome a the top Excel users on your teamPerform regular tasks quickerDevelop a P&L statement from a raw data extractionBuild a cash flow statementValue a companyProduce a valuation model from scratchCreate a model with multiple scenariosDesign professional and advanced chartsAcquire Excel proficiency by learning advanced functions, pivot tables, visualizations, and Excel featuresAbout the course:Unconditional 30-day money-back guaranteeNo significant previous experience necessary to understand the course and benefitUnlimited lifetime access to all course materialsEmphasis on learning by doingTake your Microsoft Excel and financial modeling skills to the next levelMake an investment that is rewarded in career prospects, positive feedback, and personal growth.  This course is suitable for graduates aspiring to become investment bankers as it includes a well-structured DCF model with its theoretical concepts. Moreover, it motivates you to be more confident with daily tasks and gives you the edge over other candidates vying for a full-time position.  People with basic knowledge of Excel who go through the course will dramatically increase their Excel skills.  Take advantage of this opportunity to acquire the skills that will advance your career and get an edge over other candidates. Don't risk your future success.

Overview

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

Lecture 1 What does the course cover?

Lecture 2 Bonus! Welcome gift

Lecture 3 Welcome gift number 2

Lecture 4 The best way to take this course

Lecture 5 Download all course materials and frequently asked questions (FAQ)

Section 2: Introduction to Excel

Lecture 6 Introduction to Excel

Lecture 7 Overview of Excel

Lecture 8 The Excel ribbon

Lecture 9 Basic operations with rows and columns

Lecture 10 Data entry in Excel

Lecture 11 Introduction to formatting

Lecture 12 Introduction to Excel formulas

Lecture 13 Introduction to Excel functions

Lecture 14 Cut, copy, & paste

Lecture 15 Paste special

Lecture 16 Format cells

Lecture 17 Basic operations in Excel - 38 pages

Section 3: Assignment 1

Section 4: Useful tips and tools for your work in Excel

Lecture 18 Excel best practices - Welcome lecture

Lecture 19 Initial formatting of Excel sheets for a professional layout

Lecture 20 Fast scrolling through Excel sheets

Lecture 21 Be even quicker: F5 + Enter

Lecture 22 Fixing cell references

Lecture 23 Alt + Enter

Lecture 24 Organize your data with text to columns

Lecture 25 The wrap text button

Lecture 26 Set print area

Lecture 27 Custom sort helps you sort multiple columns in Excel tables

Lecture 28 Create drop-down lists with data validation

Lecture 29 Find and select special types of cells with Select special (F5)

Lecture 30 Assign dynamic names in a financial model

Lecture 31 Define a named range in Excel

Lecture 32 Create a great Index page at the beginning of your models - Hyperlinks

Lecture 33 Introduction to custom formatting in Excel

Lecture 34 Apply custom formatting in a financial model

Lecture 35 Macros are a great timesaver! Here's why!

Lecture 36 How to save macros and use them across several workbooks

Lecture 37 Fix the top row of your table with freeze panes

Lecture 38 How to search functionalities in Excel

Lecture 39 Filter by color - an excellent tool

Lecture 40 Working with conditional formatting

Lecture 41 A neat trick - Multiply by 1

Lecture 42 Find and replace - references

Lecture 43 FAQ: Why do we replace external references and how does this help us?

Lecture 44 Find and replace - formatting

Lecture 45 Removing (automatic) green arrows

Lecture 46 Beauty saving - The professional way of saving files

Lecture 47 Formula auditing with F2

Section 5: Assignment 2

Section 6: Keyboard shortcuts in Excel

Lecture 48 Keyboard shortcuts save LOTS of time in Excel

Lecture 49 Keyboard shortcuts in Excel

Section 7: Excel's key functions and functionalities made easy

Lecture 50 Excel's key functions - Welcome lecture

Lecture 51 A helpful consideration

Lecture 52 Key functions in Excel: IF

Lecture 53 Key Excel functions: SUM, SUMIF and SUMIFS

Lecture 54 = and + are interchangeable when you start typing a formula

Lecture 55 Key Excel functions: COUNT, COUNTA, COUNTIF, COUNTIFS

Lecture 56 Key Excel functions: AVERAGE and AVERAGEIF

Lecture 57 Work with text efficiently: LEFT, RIGHT, MID, UPPER, LOWER, PROPER, CONCATENATE

Lecture 58 Working with text (continued)

Lecture 59 Find the minimum or maximum value in a range of cells in Excel

Lecture 60 Include ROUND in your financial models

Lecture 61 Key Excel functions: VLOOKUP and HLOOKUP

Lecture 62 How to enlarge the formula bar

Lecture 63 INDEX, MATCH and their combination - the perfect substitute for VLOOKUP

Lecture 64 A great Excel technique: INDEX, MATCH, MATCH

Lecture 65 XLOOKUP: a solid substitute for VLOOKUP and INDEX&MATCH

Lecture 66 Using Excel's IFERROR function to trap spreadsheet errors

Lecture 67 RANK is a valuable tool when using Excel for financial and business analysis

Lecture 68 CHOOSE - Learn how to render your models flexible

Lecture 69 Use Goal Seek to find the result that you are looking for

Lecture 70 Include sensitivity analysis in your models through Data Tables

Lecture 71 Excel's dynamic and interactive tables: Pivot tables

Section 8: Update! SUMIFS - Exercise

Lecture 72 Exercise - Excel's SUMIFS function - unsolved

Lecture 73 Exercise - Excel's SUMIFS function - solved & explained

Section 9: Assignment 3

Section 10: Financial functions in Excel

Lecture 74 Future and present values in Excel

Lecture 75 Calculating the rate of return of an investment with the IRR function

Lecture 76 Calculating a complete loan schedule in Excel

Lecture 77 Date functions in Excel

Section 11: Microsoft Excel's Pivot Tables in depth

Lecture 78 Introduction to Pivot Tables and the way they are applied

Lecture 79 Creating Pivot Tables easily!

Lecture 80 Give your Excel Pivot Tables a makeover

Lecture 81 Modifying and pivoting fields to obtain the Pivot Table you need

Lecture 82 Learn more about GETPIVOTDATA - A very important Excel function

Lecture 83 An introduction to slicers - The modern-day Pivot Table filters

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

Lecture 84 Case study - Build a P&L from scratch - Welcome lecture

Lecture 85 Introduction to the case study

Lecture 86 Understand your data source before you start working on it

Lecture 87 Order the source worksheets

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

Lecture 89 Learn how to create a database

Lecture 90 Use VLOOKUP to fill the database sheet

Lecture 91 Use SUMIF to complete the database sheet

Lecture 92 FAQ: Sum of FY2018 doens't go down to zero

Lecture 93 Use INDEX & MATCH as a substitute for VLOOKUP

Lecture 94 Substituting VLOOKUP with XLOOKUP (Office 365 Only)

Lecture 95 Mapping the rows in the database sheet

Lecture 96 In case you have any doubts about the Mapping exercise

Lecture 97 Building the structure of the P&L sheet

Lecture 98 Formatting sets You apart from the competition in Excel - A Practical Example

Lecture 99 Populate the P&L sheet with SUMIF

Lecture 100 FAQ: Why the sum of the check should be 0?

Lecture 101 Learn how to find mistakes with COUNTIF

Lecture 102 Calculating year-on-year percentage variations the proper way

Lecture 103 FAQ: Why do we subtract -1 when calculating year-on-year growth?

Section 13: Introduction to Excel charts

Lecture 104 How to insert a chart in Excel

Lecture 105 Editing Excel charts

Lecture 106 Excel chart formatting

Lecture 107 How to create a bridge chart in Excel

Lecture 108 New ways to visualize your data - Treemap charts

Lecture 109 Sparklines

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

Lecture 110 Create professional and good-looking charts - Introduction

Lecture 111 Build a column stacked chart with a secondary line axis in Excel

Lecture 112 Learn how to build effective doughnut charts in Excel

Lecture 113 Learn how to build an area chart in Excel

Lecture 114 Learn how to create bridge charts

Lecture 115 Learn how to create bridge charts in Excel 2007, 2010, and 2013

Lecture 116 Course Challenge - Apply your skills in practice!

Section 15: Business analysis techniques applied in Excel

Lecture 117 Trend Analysis in Excel

Lecture 118 Comparative Analysis in Excel

Lecture 119 Value-based analysis in Excel

Lecture 120 Correlation analysis in Excel

Lecture 121 Time series analysis in Excel

Lecture 122 Regression analysis in Excel

Section 16: Case Study - Building an FMCG Model from Scratch

Lecture 123 Introduction to the Case Study

Lecture 124 Preliminary mapping of the data extraction

Lecture 125 Working with an SAP data extraction

Lecture 126 Creating an output structure of the FMCG model

Lecture 127 Improving the layout and appearance of the FMCG report

Lecture 128 Inserting formulas and automating calculations

Lecture 129 Creating a Master Pivot Table: The main data source for the FMCG report

Lecture 130 GETPIVOTDATA is great! Extracting data from the Master Pivot Table

Lecture 131 A potential error with GETPIVOTDATA you might encounter and its fix

Lecture 132 FAQ: My GETPIVOTDATA function doesn't work

Lecture 133 Combining Slicers and GETPIVOTDATA: The key to our success

Lecture 134 Getting fancy with Excel slicers - Good-looking Excel slicers

Lecture 135 This is how the report can be used in practice by high-level executives

Section 17: Financial modeling fundamentals

Lecture 136 Financial modeling basics - Welcome lecture

Lecture 137 What is a financial model?

Lecture 138 Why use financial models?

Lecture 139 Financial modeling - worst practices - things you should avoid

Lecture 140 Financial modeling - best practices

Lecture 141 Financial modeling - The types of models that are built in practice

Lecture 142 Financial modeling: The right level of detail in a model

Lecture 143 Financial modeling: Forecasting guidelines

Lecture 144 Building a complete model - Important considerations

Lecture 145 Modeling the Income statement

Lecture 146 Modeling the Balance sheet - Part 1

Lecture 147 Modeling the Balance sheet - Part 2

Section 18: Introduction to Company Valuation

Lecture 148 The core theory behind Company Valuation

Section 19: Introduction to Mergers & Acquisitions

Lecture 149 Introduction to Mergers & Acquisitions

Lecture 150 Why acquire another firm

Lecture 151 Company valuation

Section 20: Learn how to build a Discounted Cash Flow model in Excel

Lecture 152 Valuation Case study - Welcome lecture

Lecture 153 Introduction to the DCF exercise

Lecture 154 The stages of a complete DCF Valuation

Lecture 155 Description of the structure of the DCF model

Lecture 156 A glimpse at the company we are valuing - Cheeseco

Lecture 157 Modeling the top line

Lecture 158 Introducing scenarios to the model with Choose

Lecture 159 Modeling other items: Other revenues and Cogs

Lecture 160 Modeling other items: Operating expenses and D&A

Lecture 161 Modeling other items: Interest expenses, Extraordinary items and Taxes

Lecture 162 Forecasting Balance sheet items

Lecture 163 An introduction to the "Days" methodology

Lecture 164 Calculation of DSO, DPO and DOI for the historical period

Lecture 165 Forecasting DSO, DPO and DOI

Lecture 166 Forecasting Property, Plant & Equipment, Other assets and Other liabilities

Lecture 167 Creating an output P&L sheet

Lecture 168 Populating the output P&L sheet

Lecture 169 Populating the output BS sheet

Lecture 170 Completing the output BS sheet for the historical period

Lecture 171 Creating a structure for the calculation of Unlevered free cash flows

Lecture 172 Bridging Unlevered free cash flow to Net cash flow

Lecture 173 Calculating Unlevered free cash flow

Lecture 174 Calculating Net cash flow

Lecture 175 Obtaining the rest of the cash flows through Find and Replace

Lecture 176 Introducing Weighted average cost of capital and Perpetuity growth

Lecture 177 Discounting Unlevered free cash flows to obtain their Present value

Lecture 178 Calculating Continuing value and Enterprise value of the business

Lecture 179 Calculating Equity value

Lecture 180 Sensitivity analysis for WACC and perpetuity growth

Lecture 181 A possible application of Goal seek

Lecture 182 Using charts to summarize the results of the DCF model

Section 21: Tesla valuation - Complete practical exercise

Lecture 183 Organizing external inputs in a 'Drivers' sheet

Lecture 184 Forecasting Tesla's expected deliveries

Lecture 185 Comparing delivery figures with the ones of industry peers

Lecture 186 Estimating an average selling price of Tesla vehicles

Lecture 187 Calculating automotive revenue

Lecture 188 Peer comparison: Gross profit %

Lecture 189 Calculating automotive gross profit

Lecture 190 Calculating automotive cost of sales

Lecture 191 Forecasting 'energy' and 'services' revenue

Lecture 192 Calculating 'energy' and 'services' gross profit and cost of sales

Lecture 193 Forecasting operating expenses

Lecture 194 Building a fixed asset roll forward: PP&E

Lecture 195 Building a fixed asset roll forward: estimating Capex

Lecture 196 Building a fixed asset roll forward: D&A schedule

Lecture 197 Calculating DSO, DIO, DPO

Lecture 198 Producing a clean P&L output sheet

Lecture 199 Calculating investments in working capital

Lecture 200 Forecasting Unlevered free cash flow

Lecture 201 Forecasting other assets

Lecture 202 Forecasting other liabilities

Lecture 203 Completing Unlevered free cash flow

Lecture 204 Modeling Tesla's financing needs in the forecast period

Lecture 205 Calculating Net income

Lecture 206 Bridging Unlevered free cash flow to Net cash flow

Lecture 207 Balancing the Balance sheet

Lecture 208 Estimating Weighted average cost of capital (WACC)

Lecture 209 Performing discounted cash flow valuation (DCF)

Lecture 210 Calculating enterprise value, equity value, and price per share

Lecture 211 Final comments

Lecture 212 You made it!

Section 22: How to value a company with multiples

Lecture 213 What are valuation multiples and why we use them?

Lecture 214 What types of valuation multiples are there?

Lecture 215 Trading vs transaction valuation multiples

Lecture 216 Main principles of multiples valuation

Lecture 217 Comparison of earnings multiples (P/E vs EV/EBITDA)

Lecture 218 Introduction to the exercise

Lecture 219 High-level assessment of peer companies

Lecture 220 Assessment of P&L data - comparable companies

Lecture 221 How to adjust EBIT - theoretical framework

Lecture 222 How to adjust EV - theoretical framework

Lecture 223 How to adjust EBIT - practical example - Volkswagen

Lecture 224 How to adjust EV - practical example - Volkswagen

Lecture 225 Conclusion of the practical exercise

Section 23: Capital budgeting - The theory

Lecture 226 Introduction to Capital budgeting

Lecture 227 Why we need Capital budgeting?

Lecture 228 The time value of money

Lecture 229 Calculating future and present value

Lecture 230 Calculating cost of equity

Lecture 231 Coming up with project-specific beta

Lecture 232 Weighted average cost of capital (WACC)

Lecture 233 The type of cash flows we will have in a project

Lecture 234 Estimating the project's cash flows

Section 24: Capital Budgeting - A complete Case study

Lecture 235 Introduction to the Capital budgeting exercise

Lecture 236 Organizing an "Inputs" sheet

Lecture 237 Forecasting savings: Building a plant in Vietnam vs. producing cars in Italy

Lecture 238 Fixed asset rollforward

Lecture 239 The impact of working capital

Lecture 240 Modeling debt financing

Lecture 241 Adding a P&L sheet

Lecture 242 Calculating project cash flows

Lecture 243 Estimating the weighted average cost of capital (WACC)

Lecture 244 Finding cost of equity

Lecture 245 Discounting the project's cash flows and residual value

Lecture 246 Performing sensitivity analysis and completing the exercise

Lecture 247 FAQ: Data does not change in the DCF sheet, it changes from the Drivers sheet

Lecture 248 Congratulations!!

Section 25: Next steps

Lecture 249 Next steps

Section 26: APPENDIX - Microsoft Excel 2010 - Introduction to Excel

Lecture 250 Overview of Excel

Lecture 251 Basic manipulations with rows and columns

Lecture 252 The Excel ribbon

Lecture 253 Data entry in Excel

Lecture 254 Introduction to formatting

Lecture 255 Introduction to Excel formulas

Lecture 256 Introduction to Excel functions

Lecture 257 Cut, copy, & paste

Lecture 258 Paste special

Section 27: APPENDIX - Microsoft Excel 2010 - Useful tips and tricks

Lecture 259 Initial formatting of an Excel sheet to render it professional

Lecture 260 Fast scrolling through Excel sheets

Lecture 261 Introduction to fixing of cell references

Lecture 262 Learn how to use the Wrap Text button

Lecture 263 Create a drop-down list by using Data Validation

Lecture 264 Using Custom-sort in order to sort multiple columns within a table

Lecture 265 Create a great Index page at the beginning of your models - Hyperlinks

Lecture 266 Fix the top row of your table with Freeze Panes

Lecture 267 Macros are a great timesaver! Here's why!

Lecture 268 Find and select special types of cells with Select Special (F5)

Lecture 269 Assign custom formats to specific cells within a financial model (e.g Multiples)

Lecture 270 Define a named range in Excel

Lecture 271 Learn how to organize your data with Text to Columns

Lecture 272 Learn how to assign dynamic names within a financial model

Lecture 273 Create easily printable documents in Excel by using Set Print Area

Lecture 274 Using the 'Alt + Enter' combination

Section 28: APPENDIX - Microsoft Excel 2010 - Keyboard shortcuts

Lecture 275 Keyboard shortcuts Save LOTS of time in Excel 2010

Section 29: APPENDIX - Microsoft Excel 2010 - Excel functions

Lecture 276 Key Excel functions: SUM, SUMIF and SUMIFS

Lecture 277 Key Excel functions: COUNT, COUNTA, COUNTIF, COUNTIFS

Lecture 278 Key Excel functions: AVERAGE and AVERAGEIF

Lecture 279 Elaborate text efficiently: LEFT, RIGHT, MID, UPPER, LOWER, PROPER, CONCATENATE

Lecture 280 Find the minimum or maximum value in a range of cells in Excel

Lecture 281 Include ROUND in your financial models

Lecture 282 Key Excel functions: VLOOKUP and HLOOKUP

Lecture 283 INDEX, MATCH and their combination - the perfect substitute for VLOOKUP

Lecture 284 Using Excel's IFERROR function to trap spreadsheet errors

Lecture 285 Learn how to render your models flexible with CHOOSE

Lecture 286 Use Goal Seek to find the result that you are looking for

Lecture 287 Include sensitivity analysis in your models through Data Tables

Lecture 288 Excel's dynamic and interactive tables: Pivot tables

Section 30: APPENDIX - Company Valuation

Lecture 289 Why value a company

Lecture 290 An investor's perspective when valuing a company

Lecture 291 Which are the drivers of company value?

Lecture 292 How to calculate UFCF

Lecture 293 What is WACC

Lecture 294 How to find cost of debt

Lecture 295 How to find cost of equity

Lecture 296 Forecasting financials

Lecture 297 Calculating terminal value

Lecture 298 Discounting cash flows

Lecture 299 Calculating enterprise and equity value

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