Beginner to Pro in Excel: Financial Modeling and Valuation
-
Beginner to Pro in Excel Financial Modeling and Valuation – Welcome!
-
Introduction to ExcelIntroduction to Excel0sOverview of Excel0sOverview of ExcelThe Excel ribbon0sBasic operations with rows and columns0sData entry in Excel0sIntroduction to formatting0sRows and columnsIntroduction to Excel formulas0sIntroduction to Excel formulasIntroduction to Excel functions0sCut, copy, & paste0sCut, copy, & pastePaste special0sFormat cells0sBasic operations in Excel – 38 pages
-
Useful tips and tools for your work in ExcelExcel best practices – Welcome lecture0sInitial formatting of Excel sheets for a professional layout0sFast scrolling through Excel sheets0sFast scrolling through Excel sheetsBe even quicker: F5 + Enter0sFixing cell references0sIntroduction to fixing of cell referencesAlt + Enter0sOrganize your data with text to columns0sLearn how to organize your data with text to columnsThe wrap text button0sSet print area0sCustom sort helps you sort multiple columns in Excel tables0sCreate drop-down lists with data validation0sFind and select special types of cells with Select special (F5)0sSelect SpecialAssign dynamic names in a financial model0sAssigning dynamic namesDefine a named range in Excel0sCreate a great Index page at the beginning of your models – Hyperlinks0sIntroduction to custom formatting in Excel0sApply custom formatting in a financial model0sMacros are a great timesaver! Here’s why!0sHow to save macros and use them across several workbooks0sExcel macros – quizFix the top row of your table with freeze panes0sHow to search functionalities in Excel0sFilter by color – an excellent tool0sWorking with conditional formatting0sUseful tips and tools for your work in Excel – quizA neat trick – Multiply by 10sFind and replace – references0sFAQ: Why do we replace external references and how does this help us?Find and replace – formatting0sRemoving (automatic) green arrows0sBeauty saving – The professional way of saving files0sFormula auditing with F20s
-
Keyboard shortcuts in Excel
-
Excel’s key functions and functionalities made easyExcel’s key functions – Welcome lecture0sA helpful considerationKey functions in Excel: IF0sKey Excel functions: SUM, SUMIF and SUMIFS0s= and + are interchangeable when you start typing a formula0sKey Excel functions: COUNT, COUNTA, COUNTIF, COUNTIFS0sKey Excel functions: AVERAGE and AVERAGEIF0sKey Excel functions: AVERAGE and AVERAGEIFWork with text efficiently: LEFT, RIGHT, MID, UPPER, LOWER, PROPER, CONCATENATE0sWork with text efficiently: LEFT, RIGHT, MID, UPPER, LOWER, PROPER, CONCATENATEWorking with text (continued)0sFind the minimum or maximum value in a range of cells in Excel0sInclude ROUND in your financial models0sKey Excel functions: VLOOKUP and HLOOKUP0sHow to enlarge the formula bar0sINDEX, MATCH and their combination – the perfect substitute for VLOOKUP0sA great Excel technique: INDEX, MATCH, MATCH0sINDEX, MATCH and their combination – the perfect substitute for VLOOKUPXLOOKUP: a solid substitute for VLOOKUP and INDEX&MATCH0sUsing Excel’s IFERROR function to trap spreadsheet errors0sRANK is a valuable tool when using Excel for financial and business analysis0sCHOOSE – Learn how to render your models flexible0sUse Goal Seek to find the result that you are looking for0sUse Goal Seek in order to find the result that you are looking forInclude sensitivity analysis in your models through Data Tables0sInclude sensitivity analysis in your models through Data TablesExcel’s dynamic and interactive tables: Pivot tables0sExcel’s key functions and functionalities made easy
-
Update! SUMIFS – Exercise
-
Financial functions in Excel
-
Microsoft Excel’s Pivot Tables in depthIntroduction to Pivot Tables and the way they are applied0sCreating Pivot Tables easily!0sGive your Excel Pivot Tables a makeover0sModifying and pivoting fields to obtain the Pivot Table you need0sLearn more about GETPIVOTDATA – A very important Excel function0sAn introduction to slicers – The modern-day Pivot Table filters0s
-
Case study – Building a complete P&L from scratch in ExcelCase study – Build a P&L from scratch – Welcome lecture0sIntroduction to the case study0sUnderstand your data source before you start working on it0sOrder the source worksheets0sCreate a code: the best way to organize your data and work efficiently with it0sLearn how to create a database0sUse VLOOKUP to fill the database sheet0sUse SUMIF to complete the database sheet0sFAQ: Sum of FY2018 doens’t go down to zeroUse INDEX & MATCH as a substitute for VLOOKUP0sSubstituting VLOOKUP with XLOOKUP (Office 365 Only)0sMapping the rows in the database sheet0sIn case you have any doubts about the Mapping exercise0sBuilding the structure of the P&L sheet0sFormatting sets You apart from the competition in Excel – A Practical Example0sPopulate the P&L sheet with SUMIF0sFAQ: Why the sum of the check should be 0?Learn how to find mistakes with COUNTIF0sCalculating year-on-year percentage variations the proper way0sFAQ: Why do we subtract -1 when calculating year-on-year growth?
-
Introduction to Excel charts
-
P&L Case Study continued – Let’s create some great-looking professional chartsCreate professional and good-looking charts – Introduction0sBuild a column stacked chart with a secondary line axis in Excel0sLearn how to build effective doughnut charts in Excel0sLearn how to build an area chart in Excel0sLearn how to create bridge charts0sLearn how to create bridge charts in Excel 2007, 2010, and 20130sCase Study – Building a Complete P&L from scratch in ExcelCourse Challenge – Apply your skills in practice!0s
-
Business analysis techniques applied in Excel
-
Case Study – Building an FMCG Model from ScratchIntroduction to the Case Study0sPreliminary mapping of the data extraction0sWorking with an SAP data extraction0sCreating an output structure of the FMCG model0sImproving the layout and appearance of the FMCG report0sInserting formulas and automating calculations0sCreating a Master Pivot Table: The main data source for the FMCG report0sGETPIVOTDATA is great! Extracting data from the Master Pivot Table0sA potential error with GETPIVOTDATA you might encounter and its fix0sFAQ: My GETPIVOTDATA function doesn’t workCombining Slicers and GETPIVOTDATA: The key to our success0sGetting fancy with Excel slicers – Good-looking Excel slicers0sThis is how the report can be used in practice by high-level executives0s
-
Financial modeling fundamentalsFinancial modeling basics – Welcome lecture0sWhat is a financial model?0sWhy use financial models?0sFinancial modeling – worst practices – things you should avoid0sFinancial modeling – best practices0sFinancial modeling – The types of models that are built in practice0sFinancial modeling – QuizFinancial modeling: The right level of detail in a model0sFinancial modeling: Forecasting guidelines0sForecasting financials – QuizBuilding a complete model – Important considerations0sModeling the Income statement0sModeling the Balance sheet – Part 10sModeling the Balance sheet – Part 20sBuilding a financial model – Quiz
-
Introduction to Company ValuationWhy value a company0sAn investor’s perspective0sThe main value drivers when valuing a business0sHow to calculate Unlevered Free Cash Flow0sHow to calculate Weighted Average Cost of Capital (WACC)0sHow to calculate cost of debt0sHow to calculate cost of equity0sForecasting a firm’s financials0sHow to obtain Terminal Value0sDiscounting cash flows0sHow to obtain Enterprise and Equity Value0s
-
Introduction to Mergers & Acquisitions
-
Learn how to build a Discounted Cash Flow model in ExcelValuation Case study – Welcome lecture0sIntroduction to the DCF exercise0sThe stages of a complete DCF Valuation0sDescription of the structure of the DCF model0sA glimpse at the company we are valuing – Cheeseco0sModeling the top line0sIntroducing scenarios to the model with Choose0sModeling other items: Other revenues and Cogs0sModeling other items: Operating expenses and D&A0sModeling other items: Interest expenses, Extraordinary items and Taxes0sForecasting Balance sheet items0sAn introduction to the “Days” methodology0sCalculation of DSO, DPO and DOI for the historical period0sForecasting DSO, DPO and DOI0sForecasting Property, Plant & Equipment, Other assets and Other liabilities0sCreating an output P&L sheet0sPopulating the output P&L sheet0sPopulating the output BS sheet0sCompleting the output BS sheet for the historical period0sCreating a structure for the calculation of Unlevered free cash flows0sBridging Unlevered free cash flow to Net cash flow0sCalculating Unlevered free cash flow0sCalculating Net cash flow0sObtaining the rest of the cash flows through Find and Replace0sIntroducing Weighted average cost of capital and Perpetuity growth0sDiscounting Unlevered free cash flows to obtain their Present value0sCalculating Continuing value and Enterprise value of the business0sCalculating Equity value0sSensitivity analysis for WACC and perpetuity growth0sA possible application of Goal seek0sUsing charts to summarize the results of the DCF model0s
-
Tesla valuation – Complete practical exerciseOrganizing external inputs in a ‘Drivers’ sheet0sForecasting Tesla’s expected deliveries0sComparing delivery figures with the ones of industry peers0sEstimating an average selling price of Tesla vehicles0sCalculating automotive revenue0sPeer comparison: Gross profit %0sCalculating automotive gross profit0sCalculating automotive cost of sales0sForecasting ‘energy’ and ‘services’ revenue0sCalculating ‘energy’ and ‘services’ gross profit and cost of sales0sForecasting operating expenses0sBuilding a fixed asset roll forward: PP&E0sBuilding a fixed asset roll forward: estimating Capex0sBuilding a fixed asset roll forward: D&A schedule0sCalculating DSO, DIO, DPO0sProducing a clean P&L output sheet0sCalculating investments in working capital0sForecasting Unlevered free cash flow0sForecasting other assets0sForecasting other liabilities0sCompleting Unlevered free cash flow0sModeling Tesla’s financing needs in the forecast period0sCalculating Net income0sBridging Unlevered free cash flow to Net cash flow0sBalancing the Balance sheet0sEstimating Weighted average cost of capital (WACC)0sPerforming discounted cash flow valuation (DCF)0sCalculating enterprise value, equity value, and price per share0sFinal comments0sYou made it!0s
-
How to value a company with multiplesWhat are valuation multiples and why we use them?0sWhat types of valuation multiples are there?0sTrading vs transaction valuation multiples0sMain principles of multiples valuation0sComparison of earnings multiples (P/E vs EV/EBITDA)0sIntroduction to the exercise0sHigh-level assessment of peer companies0sAssessment of P&L data – comparable companies0sHow to adjust EBIT – theoretical framework0sHow to adjust EV – theoretical framework0sHow to adjust EBIT – practical example – Volkswagen0sHow to adjust EV – practical example – Volkswagen0sConclusion of the practical exercise0s
-
Comprehensive LBO valuationCase descriptionIntroduction to LBO Modeling exercise0sKey drivers in the LBO model0sConstructing the Profit and Loss header0sAnalyzing historical Profit and Loss figures0sValuing the Target company0sEstimating transaction fees0sSources and uses of funds0sShaping the Balance sheet at transaction0sGoodwill calculation0sIntegrating assumptions into the Drivers sheet0sBuilding a Fixed asset roll forward schedule0sForecasting financials using the Drivers sheet0sCompleting the Profit and Loss statement (up to EBITDA)0sModeling Working capital0sFilling in the Balance sheet at transaction sheet0sAdd financials to the Balance sheet0sProjecting Fixed asset roll forward0sDeveloping a Debt schedule0sCreating a Fixed asset amortization schedule0sStructuring Cash Flow0sDesigning the Financing sheet0sCalculating Cash Flow0sBuilding an Equity schedule0sFinalizing the Financing Cash Flow0sModeling the Revolver facility (first part)0sCompleting the Profit and Loss statement0sModeling the Revolver facility (second part)0sBalancing the Balance sheet0sExit valuation and IRR comparison0s
-
Capital budgeting – The theoryIntroduction to Capital budgeting0sWhy we need Capital budgeting?0sThe time value of money0sCalculating future and present value0sCalculating cost of equity0sComing up with project-specific beta0sWeighted average cost of capital (WACC)0sThe type of cash flows we will have in a project0sEstimating the project’s cash flows0s
-
Capital Budgeting – A complete Case studyIntroduction to the Capital budgeting exercise0sOrganizing an “Inputs” sheet0sForecasting savings: Building a plant in Vietnam vs. producing cars in Italy0sFixed asset rollforward0sThe impact of working capital0sModeling debt financing0sAdding a P&L sheet0sCalculating project cash flows0sEstimating the weighted average cost of capital (WACC)0sFinding cost of equity0sDiscounting the project’s cash flows and residual value0sPerforming sensitivity analysis and completing the exercise0sFAQ: Data does not change in the DCF sheet, it changes from the Drivers sheetCongratulations!!0s
-
Next steps
-
BONUS – Data Analysis with ChatGPT – Intro
-
BONUS – Data Analysis with ChatGPT – Exploratory Data Analysis (EDA)
-
BONUS – Data Analysis with ChatGPT – Deal with Inconsistent Data
-
BONUS – Data Analysis with ChatGPT – Organize Messy Data
-
BONUS – Data Analysis with ChatGPT – Working with Functions
-
BONUS – Data Analysis with ChatGPT – Clean Text DataCorrecting Inverted Names0sDealing with Trailing and Leading Spaces in Text Data0sStandardizing Letter Capitalization0sHandling Multi-lingual Text Data0sMultiple “Find and Replace” at the Same Time with ChatGPT0sCriteria-based Duplicate Removal0sRemoving Duplicates Across Multiple Columns0s
-
BONUS – Data Analysis with ChatGPT – Dates and Time Data
-
BONUS – Data Analysis with ChatGPT – Complete Practical Example
-
APPENDIX – Microsoft Excel 2010 – Introduction to Excel
-
APPENDIX – Microsoft Excel 2010 – Useful tips and tricksInitial formatting of an Excel sheet to render it professional0sFast scrolling through Excel sheets0sIntroduction to fixing of cell references0sLearn how to use the Wrap Text button0sCreate a drop-down list by using Data Validation0sUsing Custom-sort in order to sort multiple columns within a table0sCreate a great Index page at the beginning of your models – Hyperlinks0sFix the top row of your table with Freeze Panes0sMacros are a great timesaver! Here’s why!0sFind and select special types of cells with Select Special (F5)0sAssign custom formats to specific cells within a financial model (e.g Multiples)0sDefine a named range in Excel0sLearn how to organize your data with Text to Columns0sLearn how to assign dynamic names within a financial model0sCreate easily printable documents in Excel by using Set Print Area0sUsing the ‘Alt + Enter’ combination0s
-
APPENDIX – Microsoft Excel 2010 – Keyboard shortcuts
-
APPENDIX – Microsoft Excel 2010 – Excel functionsKey Excel functions: SUM, SUMIF and SUMIFS0sKey Excel functions: COUNT, COUNTA, COUNTIF, COUNTIFS0sKey Excel functions: AVERAGE and AVERAGEIF0sElaborate text efficiently: LEFT, RIGHT, MID, UPPER, LOWER, PROPER, CONCATENATE0sFind the minimum or maximum value in a range of cells in Excel0sInclude ROUND in your financial models0sKey Excel functions: VLOOKUP and HLOOKUP0sINDEX, MATCH and their combination – the perfect substitute for VLOOKUP0sUsing Excel’s IFERROR function to trap spreadsheet errors0sLearn how to render your models flexible with CHOOSE0sUse Goal Seek to find the result that you are looking for0sInclude sensitivity analysis in your models through Data Tables0sExcel’s dynamic and interactive tables: Pivot tables0s
-
APPENDIX – Company ValuationWhy value a company0sAn investor’s perspective when valuing a company0sWhich are the drivers of company value?0sHow to calculate UFCF0sWhat is WACC0sHow to find cost of debt0sHow to find cost of equity0sForecasting financials0sCalculating terminal value0sDiscounting cash flows0sCalculating enterprise and equity value0s
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?
Would you like to become your team’s go-to person for financial modeling in Excel?
Join over 240,000+ successful students taking this course.
Your instructor possesses the following extensive experience in financial modeling:
Financial advisory unit of a top-tier consulting firm
Experience in M&A transactions executed in the UK, Italy, Germany, Switzerland, and Poland
Employed in the in-house Mergers & Acquisitions department of a large multinational corporation
A financial advisor in multiple M&A deals 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 with this course today.
A comprehensive guide to financial modeling in Excel:
Become an Excel expert
Build sound financial models in Excel
Gain an in-depth understanding of the mechanics of company valuation
Create professionally formatted files
Demonstrate superior Excel skills at work
Prepare before employment in investment banking, financial advisory, corporate finance, or consulting
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
Professional chart examples used by major banks and consulting firms.
Real-life, step-by-step examples
Upon completing this course, you’ll be able to do the following:
Work comfortably with Microsoft Excel and many of its advanced features
Become a the top Excel users on your team
Perform regular tasks quicker
Develop a P&L statement from a raw data extraction
Build a cash flow statement
Value a company
Produce a valuation model from scratch
Create a model with multiple scenarios
Design professional and advanced charts
Acquire Excel proficiency by learning advanced functions, pivot tables, visualizations, and Excel features
About the course:
Unconditional 30-day money-back guarantee
No significant previous experience necessary to understand the course and benefit
Unlimited lifetime access to all course materials
Emphasis on learning by doing
Take your Microsoft Excel and financial modeling skills to the next level
Make 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.
What's included
- 19.5 hours on-demand video
- 10 articles
- 727 downloadable resources
- Access on mobile and TV
- Certificate of completion