Excellence with Excel: Overview
Categories
Accretion-Dilution, Assumptions, Linking Models, Cash-Flow Statement, Accounting, Income Statement, Custom Shortcuts, Balance-Sheet
In this video, we explain the basic philosophy behind the way Excel is taught in this course and go through what we´ll cover and what we won´t cover - and what´s most important for you to know.
Ask a question or leave a comment:
Excellence with Excel
Data Manipulation
- Excellence with Excel: Overview
- Excellence with Excel: Shortcuts You Already Know
- Excellence with Excel: Basic Navigation
- Excellence with Excel: Inserting, Deleting & Editing Rows and Columns
- Excellence with Excel: Editing & Deleting Cells
- Excellence with Excel: Grouping & Hiding Rows and Columns
- Excellence with Excel: Freeze Panes & Zooming
- Excellence with Excel: Manipulating Workbooks & Worksheets
- Excellence with Excel: Hiding, Naming & Coloring Worksheets
- Excellence with Excel: Naming & Jumping to Cells
- Excellence with Excel: Sorting Data
Formatting
- Excellence with Excel: Formatting Overview
- Excellence with Excel: Formatting Numbers
- Excellence with Excel: Formatting Alignment
- Excellence with Excel: Formatting Fonts
- Excellence with Excel: Formatting Borders
- Excellence with Excel: Formatting Fills
- Excellence with Excel: Custom Number Formats
- Excellence with Excel: Cleaning Up Data
- Excellence with Excel: Conditional Formatting
- Excellence with Excel: Printing
Formulas
- Excellence with Excel: Formula Overview
- Excellence with Excel: Built-In Functions
- Excellence with Excel: Iterations & Circularity
- Excellence with Excel: Auditing Formulas
- Excellence with Excel: Leaving Comments
- Excellence with Excel: Modeling Scenarios
- Excellence with Excel: Validating & Uniquifying Lists
- Excellence with Excel: Data Tables
- Excellence with Excel: Lookup Functions
- Excellence with Excel: Pivot Tables
- Excellence with Excel: Recording Macros
- Excellence with Excel: Custom Keyboard Shortcuts
Financial Modeling Fundamentals
Accounting Fundamentals
- Income Statement
- Balance Sheet: Assets
- Balance Sheet: Liabilities & Shareholders' Equity
- Cash Flow Statement
- Linking the Financial Statements
- Overview of Interview Question Model
- Walking Through the 3 Financial Statements
- Depreciation Changes
- Inventory Changes: Cash vs. Debt
- Inventory: LIFO vs. FIFO
- Accrued Expense Accrual & Cash Payout
- Deferred Revenue Accrual & Recognition
- Deferred Income Taxes & Deferred Tax Liabilities
- Declaring & Issuing Dividends
- Issuing & Repurchasing Shares
- Raising & Paying Off Debt
- Equity & Debt Bailouts
- Goodwill Impairment
- Writing Down Owed Debt
- Financing iPhone Factories with Debt (Part 1 - "Moment of Impact")
- Financing iPhone Factories with Debt (Part 2 - After 1 Year)
- Financing iPhone Factories with Debt (Part 3 - PP&E Write-Down and Loan Payback)
- Let's Make Some iPhones (Part 1 - Ordering the Inventory)
- Let's Make Some iPhones (Part 2 - Manufacturing & Selling)
Creating 3-Statement Projections
- Overview
- Income Statement Trends
- Income Statement Projections
- Balance Sheet Trends
- Balance Sheet Projections
- Cash Flow Statement Projections
- Linking the 3 Financial Statements
- Amortization of Intangibles
- Interest Income & Expense Projections
- Deferred Income Taxes & Deferred Tax Liabilities
- Balancing Your Balance Sheet, Part 1
- Balancing Your Balance Sheet, Part 2
- Balancing Your Balance Sheet, Part 3
- Balancing Your Balance Sheet, Part 4
- Interview Questions & Answers
Equity Value, Enterprise Value & Multiples
- Overview and Market Capitalization
- Options Overview
- Treasury Stock Method
- Treasury Stock Method Exercise
- Convertible Bonds
- Equity Value vs. Enterprise Value
- Calculating Enterprise Value
- Enterprise Value Advanced Additions
- Key Valuation Metrics
- Key Valuation Multiples
- Equity Value vs. Enterprise Value in Valuation Multiples
- Interview Questions & Answers
Valuation Overview & Comparables
- Overview
- Selecting Public Company Comparables
- Calculating Enterprise Value for Public Company Comparables
- Calculating Valuation Metrics for Public Company Comparables
- Public Company Comparables - Operating Metrics
- Public Company Comparables - Valuation Multiples
- Selecting Precedent Transactions
- Precedent Transactions - Valuation Multiples
Discounted Cash Flow Analysis
- Overview & Assumptions
- Projecting Cash Flows
- Discounting Cash Flows
- Mid-Year Discount Convention
- Terminal Value - Multiples Method
- Terminal Value - Gordon Growth Method
- Implied Share Price Calculation & Sensitivity Tables
- Walking Through a DCF
- Interview Questions & Answers
- WACC - Overview & Assumptions
- WACC - Calculating Unlevered Beta
- WACC - Calculating Our Own Beta
- WACC - Cost of Equity & WACC Calculation
- WACC - Interview Questions & Answers
Valuation Summary
Merger Model
- Overview
- Transaction Assumptions
- Buyer & Seller Projected Income Statements
- Combining the Income Statements
- Calculating Accretion / Dilution
- Revenue Synergies
- Expense Synergies
- Why We Need Goodwill & Intangible Assets
- Calculating Goodwill, Intangible Assets, Asset Write-Ups & Deferred Tax Liabilities
- Combining & Adjusting the Balance Sheet
- Adjusting the Income Statement
- Sensitivity Tables
- Walking Through a Merger Model
- Interview Questions & Answers
- Bonus: Intel / McAfee Basic Merger Model Webinar
LBO Model
- LBO Overview
- Transaction Assumptions
- Debt Assumptions
- Sources & Uses
- Operating Assumptions & Income Statement
- Cash Flow Statement
- Debt Schedules
- Linking the Statements
- Calculating IRR
- Sensitivity Tables
- Walking Through the Model
- Interview Questions & Answers
- Bonus: Kinetic Concepts LBO Model Webinar
More Advanced LBO Model
- Overview & Changes to Model
- Transaction Assumptions
- Debt Assumptions
- Sources & Uses
- Purchase Price Allocation
- Pro-Forma Balance Sheet Adjustments
- Debt Schedules: Interest
- Debt Schedules: Mandatory Repayments
- Debt Schedules: Optional Repayments
- Modifying the Income Statement & Cash Flow Statement
- Linking the Statements
- IRR Calculation & Sensitivities
- Interview Questions & Answers
- Bonus: J.Crew LBO Model Webinar
Advanced Modeling
Yahoo! Operating Model
- Overview & Excel Setup
- Adjusting the Income Statement for Non-Cash Charges and TAC
- Balance Sheet Overview
- Cash Flow Statement Overview
- How Noncontrolling Interests Flow Through the 3 Statements
- How Investments in Equity Interests Flow Through the 3 Statements
- Revenue Model Overview
- Revenue Model Part 1 - Search Advertising
- Revenue Model Part 2 - Display Advertising
- Revenue Model Part 3 - Other Revenue
- Expense Model Part 1 - Overview, Cost of Revenue, and Employees
- Expense Model Part 2 - Sales & Marketing
- Expense Model Part 3 - Product Development and G&A
- Income Statement Projections
- Balance Sheet Projections
- Cash Flow Statement Projections
- Share Issuances, Repurchases & Dividends
- PP&E Schedule
- Linking the Statements
- Enterprise Value and Operating Model Summary
- Quarterly Projections and Calendarization
- Deferred Income Taxes and Different Methods of Depreciation
- Operating Model Interview Questions & Answers
Yahoo! Valuation
- Overview
- Public Company Comparables Selection
- Google (GOOG) - Public Company Comparable
- eBay (EBAY) - Public Company Comparable
- Amazon (AMZN) - Public Company Comparable
- InterActiveCorp (IACI) - Public Company Comparable
- Yahoo! (YHOO) - Public Company Comparable
- Valuing Yahoo's Equity Interests
- Valuing Yahoo's Net Operating Losses (NOLs)
- Displaying the Public Company Comparables
- Precedent Transactions Selection
- Microsoft / aQuantive - Precedent Transaction
- Google / DoubleClick - Precedent Transaction
- Publicis / Digitas - Precedent Transaction
- Yahoo / RightMedia - Precedent Transaction
- Investor Group / Vertrue - Precedent Transaction
- NBC Universal / iVillage - Precedent Transaction
- WPP Group / 24/7 Real Media - Precedent Transaction
- Displaying the Precedent Transactions
- Selecting M&A Premiums
- Displaying the M&A Premiums
- Discounted Cash Flow - Cash Flow Projections & Mid-Year Discount
- Discounted Cash Flow - Applying the Mid-Year Discount to Stub Periods
- Discounted Cash Flow - WACC Calculation
- Discounted Cash Flow - Share Price Calculation & Sensitivities
- Future Share Price Analysis
- Sum of the Parts Analysis
- Liquidation Valuation
- Valuation Summary & Linking Data
- Making the "Football Field"
- Bonus: ConAgra / Ralcorp Valuation Webinar
Microsoft / Yahoo Merger Model
- Overview
- Buyer & Seller Profiles and Microsoft Income Statement Projections
- Microsoft Balance Sheet Projections
- Microsoft Cash Flow Statement Projections
- Linking Microsoft's 3 Financial Statements
- Transaction Assumptions & Funds Required Calculation
- Transaction Scenarios
- Sources & Uses
- Purchase Price Allocation, Goodwill and Other Intangibles Creation & Asset Write-Ups
- Combining Microsoft and Yahoo!'s Balance Sheets
- Combining Microsoft and Yahoo!'s Income Statements
- Revenue Synergies by Segment Calculation
- Expense Synergies by Segment Calculation
- Book vs. Cash Tax Schedule and Section 382 Net Operating Losses
- Accretion / Dilution & Breakeven Synergies Calculation
- Projecting the Combined Balance Sheets
- Projecting the Combined Cash Flow Statements
- Debt Schedules & Interest Expense Calculation
- Linking the Combined 3 Statements
- Sensitivity Analyses
- Contribution Analysis
- Transaction Summary
- Bonus: Intel / McAfee Basic Merger Model Webinar
- Bonus: Intel / McAfee Advanced Merger Model Webinar
Yahoo! LBO Model
- Overview
- Debt Crash Course
- Changes to Operating Model
- Transaction & Operating Assumptions
- Capital Structure Scenarios
- Debt Assumptions
- Transaction & Financing Fees
- Sources & Uses
- Purchase Price Allocation, Goodwill & Asset Write-Ups
- Pro-Forma Balance Sheet Adjustments
- Debt Schedules - Interest Expense Calculation
- Debt Schedules - Mandatory Repayments
- Debt Schedules - Optional Repayments
- Linking Debt on the Balance Sheet and Cash Flow Statement
- Modifying the Income Statement for LBO Adjustments
- Book vs. Cash Tax and NOL Schedule & Deferred Tax Liabilities
- Summary Credit Statistics & Leverage and Coverage Ratios
- IRR Calculations - Calculating Equity Value Available to Investors
- IRR Calculations - Calculating Returns to Equity & Debt Investors
- Sensitivity Tables
- Bonus: J.Crew LBO Model Webinar
- Bonus: Kinetic Concepts LBO Model Webinar
Deal Commentary & Pitch Book
The Networking Ninja Toolkit
Networking Overview
Lead Generation
Tracking Your Networking
Requesting Informational Interviews
Conducting Informational Interviews
- Informational Interviews: Mindset & Key Strategies
- Sample Informational Interview 1 - Conversation with Alumnus (Casual)
- Sample Informational Interview 2 - Conversation with Referral (Formal)
- Good Questions to Ask in Informational Interviews and "Reasons Why"
- How to "Make the Ask" Following an Informational Interview
Cold Calls & Cold Emails
The Information Session Playbook
Becoming a Weekend Warrior
Networking with Co-Workers
The Networking Ninja Quiz
Bank & Financial Institution Modeling
Overview, Bank Accounting & Regulatory Capital
Bank Operating Model
- Operating Model Overview & Loan Projections
- Loan Charge-Offs & Recoveries
- Gross Loans, Net of Charge-Offs & Key Metrics
- Balance Sheet Projections
- Interest-Earning Assets & Interest-Bearing Liabilities
- Income Statement Projections
- Cash Flow Statement Projections
- Linking the Statements
- Capital Adequacy Calculations
- Dividends Issued & Shares Repurchased
- Operating Model Summary
Bank Valuation
- Valuation Overview
- Public Company Comparables
- Precedent Transactions
- Dividend Discount Model – Overview and Assumptions
- Dividend Discount Model – Cost of Equity
- Dividend Discount Model – Calculating Dividends Issued and Present Value
- Dividend Discount Model – Sensitivity Tables
- Simplified Dividend Discount Model
- Residual Income Model – Overview
- Residual Income Model – Calculating Excess Returns
- Simplified Residual Income Model
- Valuation Summary
Bank Merger Model
- Bank Merger Model - Overview and Key Differences
- Changes to Operating Model
- SunTrust – Balance Sheet Projections
- SunTrust – Interest-Earning Assets & Interest-Bearing Liabilities
- SunTrust – Income Statement Projections
- Linking the Statements & Regulatory Capital
- Transaction Assumptions and Sources & Uses
- Core Deposit Intangibles
- Cost Synergies
- Deposit Divestitures
- Calculating the Restructuring Funding Expense
- Purchase Price Allocation
- Pro-Forma Combined Balance Sheet
- Federal Funds Sold and Federal Funds Purchased Differential
- Pro-Forma Combined Regulatory Capital
- Pro-Forma Combined Dividends
- Combining the Income Statements
- Relative Contribution Analysis
- Calculating the Internal Rate of Return (IRR)
- Sensitivity Tables
- Summary Page
Bank LBO Model
Insurance Overview
- Insurance Financial Modeling: Overview
- Insurance Overview – Premiums and Revenue Recognition
- Insurance Overview – Losses, Expenses, and Key Metrics
- Insurance Overview – Financial Statements
- Insurance Overview – Operating Model Part 1: Premiums and Commissions
- Insurance Overview – Operating Model Part 2: Losses and Expenses
- Insurance Overview – Operating Model Part 3: Income Statement
- Insurance Overview – Operating Model Part 4: Balance Sheet and Cash Flow Statement
- Insurance Overview – Operating Model Part 5: Statutory Accounting and Key Ratios
- Insurance Overview – Valuation and Key Multiples
- Insurance Overview – P&C Insurance Relative and Intrinsic Valuation
- Insurance Overview – Embedded Value for Life Insurance
- Insurance Overview – P&C vs. Life Insurance
PowerPoint Pro
Real Estate & REIT Modeling
Accounting, Overview & Key Metrics
Office Development & Sale
- Office Development Overview & Assumptions
- Hard Costs, Soft Costs, Land Acquisition, FF&E, and TIs
- Sources & Uses of Funds
- Construction Timeline
- Property Income Statement
- Parking Revenue & Expenses
- Property Cash Flow Statement
- Debt Schedules, Part 1 - Setup
- Debt Schedules, Part 2 - Funds Required
- Debt Schedules, Part 3 - Equity and Debt Draws and Optional Repayment
- Linking the Statements
- Property Sale Proceeds
- Allocating Returns and Developer Promotes
- Calculating IRR
- Transaction Summary and Sensitivities
Hotel Acquisition & Renovation
- Hotel Operating and Renovating Assumptions
- Hotel Acquisition Transaction Assumptions
- Sources & Uses of Funds
- Hotel Income Statement - Revenue
- Hotel Income Statement - Expenses
- Acquisition Debt - Interest and Principal Repayment Schedule
- Cash Flow Statement and Debt & Equity Schedules - Part 1
- Cash Flow Statement and Debt & Equity Schedules - Part 2
- IRR Calculation and Sensitivities
- Hotel Acquisition & Renovation Summary
REIT Operating Model
- REIT Operating Model Overview & Setup
- REIT Historical Income Statement
- REIT Historical Balance Sheet
- REIT Historical Cash Flow Statement
- REIT Established and Stabilized Community Projections
- REIT Development/Redevelopment and Acquisition Projections
- REIT Dispositions and Discontinued Operations
- REIT Revenue and NOI Rollup and Change in Gross Real Estate Assets
- REIT Income Statement Projections
- REIT Balance Sheet Projections
- REIT Cash Flow Statement Projections
- REIT Debt Schedules, Part 1 – Setup
- REIT Debt Schedules, Part 2 – Mandatory and Optional Repayments
- REIT Debt Schedules, Part 3 – Linking to the Financial Statements
- REIT FFO, AFFO, and Dividend Projections
- Linking a REIT’s Statements
- REIT Operating Model Summary
REIT Valuation
- REIT Valuation Overview & Public Company Comparables
- Equity Residential [EQR] - Public Company Comparable Analysis
- Summary of REIT Public Company Comparables
- REIT Precedent Transactions
- Net Asset Value Model Overview
- Net Asset Value Model, Part 1: Calculations
- Net Asset Value Model, Part 2: Premiums & Sensitivities
- REIT Discounted Cash Flow Assumptions & Setup
- REIT Discounted Cash Flow Terminal Value & Sensitivities
- REIT Dividend Discount Model
- Replacement Value Methodology
- REIT Valuation Summary
Investment Banking Interview Guide
How to Tell Your Story
How to Discuss Deals in Interviews
Sample Investment Banking Interviews
- Sample Investment Banking Interviews: Finance Background "Fit" Interview
- Sample Investment Banking Interviews: Non-Finance Background "Fit" Interview
- Sample Investment Banking Interviews: Basic Technical Interview
- Sample Investment Banking Interviews: More Advanced Technical Interview
- Sample Investment Banking Interviews: MBA-Level Interview
The Questions & Answers
Oil & Gas Modeling
Overview, Accounting & Key Metrics
Oil & Gas Operating Model
- Overview & Production
- Resource Price Scenarios and Realized Prices
- Price Hedging and Revenue by Segment
- Unit-of-Production Expenses
- Income Statement Projections
- Balance Sheet Projections
- Cash Flow Statement Projections
- Shares Issued & Repurchased and Dividends
- Debt Schedules, Part 1: Assumptions
- Debt Schedules, Part 2: Mandatory and Optional Repayments
- Debt Schedules, Part 3: Interest Expense and Linking the Schedules
- Linking the 3 Financial Statements
- Non-Recurring Charges and Operating Model Summary
- Exxon Mobil – Overview & Production
- Exxon Mobil – Resource Prices & Expenses by Region
- Exxon Mobil – E&P Income Statement
- Exxon Mobil – Full Operating Model
Oil & Gas Valuation
- Overview and Comparable Public Company Selection
- Chesapeake Energy [CHK] – Public Comp Analysis
- Public Comps – Operating Metrics and Valuation Multiples
- Precedent Transactions
- DCF – Assumptions and Setup
- DCF – Terminal Value Calculation and Sensitivities
- NAV – Overview and DCF Translation
- NAV – Revenue Projections
- NAV – Expense Projections
- NAV – Acreage and Other Assets
- NAV – Present Value and Share Price Sensitivities
- Valuation Summary
Oil & Gas Merger Model
- Assumptions & Setup
- Purchase Price Allocation & Balance Sheet Adjustments
- Combining & Adjusting Income Statements
- Oil & Gas Synergies
- Modifying the Debt Schedules
- Combining & Linking the 3 Statements
- Oil & Gas-Specific Accretion / Dilution Metrics
- Contribution Analysis
- Sensitivity Analyses
- Summary & Deal Commentary
Oil & Gas LBO Model
- Overview & Assumptions
- Purchase Price Allocation & Pro-Forma Balance Sheet Adjustments
- Modifying the Debt Schedules to Support an LBO
- Modifying and Linking the 3 Financial Statements
- Credit Statistics and Ratios
- Calculating Returns to Equity and Debt Investors
- Sensitivity Tables
- Why Natural Resource LBOs Are Uncommon