Tutorials Training Blog Contact
Login

X

Excel Core Skills

We’ve all seen bad spreadsheets. You’re ready for something different. You know that polished financial models are built on the back of solid skills… Learn the DNA of Excel and get a data backed program for becoming a bulletproof financial modeler.  

Break down the skillset of financial modeling and piece it together step by step, with financial modeling best practices layered throughout. Learn how formula work and when they break down, the tools like named ranges and data validation to use, and the top 50 shortcuts to make you a speed demon.  Pull all of these together and get exercises to drill what you’ve learned - and just like our other students, you’ll get up to 3x quicker at developing robust, transparent financial models

MODULE 1: Course Introduction and Context

  • 1A. INTRO Welcome to the Course
2mins
  • 1B. INTRO Section Overview
1min
  • 1C. INTRO Course Structure & Why the MRE's
    are a Powerful Component of this Course
2mins
  • 2. INTRO Putting the Course Content in Context
4mins

MODULE 2: The Fundamentals

  • 3. GETTING STATED End Game
16mins
  • 4. GETTING STATED Best Practice
    Financial Modeling
13mins
  • 5. GETTING STATED
    Navigation Anchoring
15mins

MODULE 3: The 80/20 rule of Formulas used in Financial Models

  • 6. FORMULAS Top Ten Functions
4mins
  • 7. FORMULAS Call ups - Position
    Calculated 
4mins
  • 8. FORMULAS Call ups - Position Input
5mins
  • 9. FORMULAS C1-LOOKUP
6mins
  • 10. FORMULAS C1-HLOOKUP
2mins
  • 11. FORMULAS C1-LOOKUP challenges
4mins
  • 12. FORMULAS C2- Index & OFFSET
4mins
  • 13. FORMULAS C3-MATCH
3mins
  • 14. FORMULAS C4-INDEX MATCH 
4mins
  • 15. FORMULAS C5-MATCH, INDEX
    CHOOSE
5mins
  • 16. FORMULAS C5-OFFSET,
    SUMPRODUCT
4mins
  • 17. FORMULAS C5-SUMIF, LOOKUP 
3mins
  • 18. FORMULAS C6 - OFFSET for Height
    & Width
5mins
  • 19. Call -ups summary
1min
  • 20. FORMULAS Create Flags To Massively
    Simplify Timing Formula IF & AND
7mins
  • 21. FORMULAS Create the Timing Strip -
    EOMONTH & YEAR
5mins

MODULE 4: The Excel Tools to Use Consistently and Why

  • 22. TOOLS Introduction to Tools: Expand
    your Excel toolkit with these 5 in built Excel
    Tools (.xlsx download)
4mins
  • 23. TOOLS Tools Which Enhance
    Robustness - Named Ranges & Data Validation
6mins
  • 24. TOOLS Tools Which Enhance
    Presentation - Cell Styles, Custom Formats &
    Conditional Formatting
16mins

MODULE 5: Building Speed - 3x Your Speed with
Shortcuts

  • 25. SHORTCUTS Introduction to Shortcuts 
8mins
  • 26. SHORTCUTS 5 Shortcuts to
    NAVIGATE the Model
2mins
  • 27. SHURTCUTS 17 Shortcuts for BUILDING
    the Model
8mins
  • 28. SHORTCUTS 12  Shortcuts for
    FORMATTING the Model 
9mins
  • 29. SHORTCUTS 7  Shortcuts for
    CHECKING the Model 
7mins
  • 30. SHORTCUTS 4  Shortcuts for
    AMENDINGING the Model 
3mins
  • 31. SHORTCUTS THE ONE  Shortcuts for
    AUDITING  the Model 
2mins
  • 32. SHORTCUTS 4  Shortcuts for MS Office
1mins
  • 33. SHORTCUTS 1Recap:  THE TOP 30
    SHORTCUTS (+.pdf download)
1mins
  • 34. SHORTCUTS Exercises - Honing your
    Skills with Shortcuts
2mins
  • 35. SHORTCUTS Closing
1mins

MODULE 6: Putting it All Together With Memory Rentention Exercise- Prepare To Get Way Faster!

  • 36. MEMORY RENTENTION Introduction to
    the Memory Retention Exercises 
6mins
  • 37. MEMORY RENTENTION Memory
    Rentention Exercise 1
6mins
  • 38. MEMORY RENTENTION Memory
    Rentention Exercise 2
6mins
  • 39. MEMORY RENTENTION MRE1 Speed run
2mins
  • 40. MEMORY RENTENTION MRE1 Speed run
1min

X

Blueprint of Project Finance Modeling

Get the essential PF theory, the landscape of private infra and PPP deals and learn how the financial model fits in the process. Using a modular approach, get the knowledge to piece together sophisticated models, and adapt them for any future deal.

With a wind-farm case study, this course guides you in building - from scratch - your very own infrastructure model. Model a greenfield asset, calculate revenue, fixed & variable expenses. Review the relevant parts of a term sheet and model paying down debt with annuity & sculpted repayment mechanisms, and analyse lender covenants. Bring the model together into a Cashflow Waterfall. Using equity & project returns, DSCR and other outputs, trace the drivers of performance with a scenario manager, and run a debt and equity type process as a window to how stakeholders might view the deal. 

MODULE 1: Introduction, Course Aims & Structure

  • Intro
1min
  • 1b Introduction, Course Aims & Structure
3mins

MODULE 2:  Principles of Project Finance

  • 2a Project Finance Mechanics: Discuss the
    Characteristics, Complexity and Players 
10mins
  • 2b Types of Project Finance Deals -
    from Public to Private Infrastructure
7mins
  • 2c Timeline of a Project Finance Deal -
    Beginning to End
14mins

MODULE 3: The Case Study and The End Game

  • 3a Introduction to Case Study & the
    Purpose of a Financial Model
4mins
  • 3b The End Game of Project Finance:
    Using the Scenario Manager to Analyse the Deal
9mins

MODULE 4: The Start Model

  • 4a Introduction to the Start Model
    + (.xlsx download)
11mins

MODULE 5: The Timing Sheet: Transform a
Spreadsheet into a Financial Model

  • 5a Introduction to
    Timing & Timing Sheet Setup
11mins
  • 5b Creating the Timing Strip
    with Dates & Flags
16mins
  • 5c Counters: The Connective
    Tissue of a Model
10mins
  • 5d Rounding out the Timing Sheet with
    Escalation & Base Rate Interest
11mins
  • 5e Finishing the Timing Sheet & Copy Sheet
7mins

MODULE 6: Constructing a Greenfield Infrastructure
Project and Calculating Sources Of Funds

  • 6a Introduction & Calculating Construction
    Spend Profile
10mins
  • 6b Creating A Robust Structure for Sources &
    Uses of Construction Funds
16mins
  • 6c The Cost of Debt: Calculating Interest
    During Construction and Financing Fees
22mins

MODULE 7: Operations Over the Lifetime of the Asset -
Revenue and Costs

  • 7a Introduction and Review the Inputs
5mins
  • 7b Calculating Revenue of a
    Windfarm with Merchant Revenues
14mins
  • 7c Split Opex into Fixed and Variable,
    Escalating Fixed Opex
12mins
  • 7d Grouping, Recap & Explanations
6mins

MODULE 8: Explore the Cashflow Waterfall; Theory and Bring In Cash Items to One Central Location

  • 8a Theory, Criticial Line Items, Hierarchy
    of CFW and Difference Between CFW and CFS 
8mins
  • 8b Modeling the CFW in Excel
8mins

MODULE 9: Learn about Project Finance Debt Modeling through Annuity, Sculpting & Debt Metrics

  • 9a Intro & Theory: Components of a
    Term Sheet, Evaluating
    Debt Tenor & Four Repayment
8mins
  • 9b Setup & Modeling the Debt Account
9mins
  • 9c Calculating Interest Expense
6mins
  • 9d Modeling the Annuity Calculation
    from First Principles
9mins
  • 9e Modeling Debt Sculpting through
    the Target Ratio
12mins
  • 9f Debt Ratios: Understanding and
    Calculating the Debt Service
    Coverage Ratio; Pulling
18mins
  • 9g Debt Ratios: Understanding and
    Calculating the Loan Life Coverage Ratio
14mins

MODULE 10: Equity Account, Distributions and Returns to Project and Equity Holders

  • 10a Introduction and Calculating
    Distributions to Equity Holders
9mins
  • 10b Setting up Equity Returns Cashflows
3mins
  • 10c Theory and Calculation of Net Present
    Value from First Principles and XNPV Function
11mins
  • 10d Theory of IRR and Calculation using XIRR
4mins
  • 10e Calculating Project Returns
7mins

MODULE 11: Using The Scenario Manager
To Turbocharge Your Analysis

  • 11a Introduction to the Scenario Manager;
    What It Does & How it Works
5mins
  • 11b Setting up the Scenario Manager in
    Excel to Sensitise Key Variables
    Throughout 
11mins
  • 11c Creating a Data Table to Simultaneously
    Output Results for All Cases
15mins

X

Depreciation, Tax & Financial Statements

This section advances the accounting rigour around a project finance model. Like the other sections, each module begins with the “Why”, giving you the theoretical background, before diving into the “How” - the implementation in the model..

Continuing from the Blueprint model, pull together the elements to deliver a bankable financial model. Calculate working capital using multiple methodologies, discuss and calculate corporate tax, calculate depreciation using straight line, reducing balance or MACRS methodology, bring together a P&L and Balance sheet. Improve the model robustness by adding checks.

MODULE 1: Couse Overview

  • 1a Welcome to this Section
2mins
  • 1b Course Overview
3mins

MODULE 2: Model working Capital to Adjust
Revenue & Expenses from Accrual to Cash Basis

  • 2a Working capital: Corporate Finance vs
    Project Finance
1min
  • 2b Introduction to Working Capital
7mins
  • 2c Debtors & Working Capital adjustments
6mins
  • 2d  Creditors & Working Capital adjustments
5mins

MODULE 2: The Profit & Loss Statement

  • 3a Introduction to the P&L 
5mins
  • 3b Modeling the P&L
7mins

MODULE 4: Modeling Accounting & Tax
Depreciation Flexibly Using the OFFSET Method

  • 4a Introduction & Tax vs Accounting
    Depreciation
3mins
  • 4b Type of Depreciation
3mins
  • 4c Challenges of Depreciation & Introduction
    to the Old School Depreciation Table
6mins
  • 4d The Depreciation Setup in The Model
1mins
  • 4e Calculating Maintenance Capex
4mins
  • 4f Depreciation Inputs: Categories &
    Useful Life
2mins
  • 4g How to Summarise Capex into
    Additions Categories
12mins
  • 4h Creating a Template Account &
    Depreciation Base
18mins
  • 4i Linking Additions & Depreciation
    and Reviewing Depreciation
6mins
  • 4j Replicating the Templates,
    Updating for Tax Dpreciation & Summarising
11mins

MODULE 5: Geared and Ungeared Tax Calculations

  • 5a Introduction to Tax
8mins
  • 5b Calculating Taxable Income & Modeling
    the Tax Loss Account
8mins
  • 5c Calculating Tax Payable & Tax Paid
4mins
  • 5d Ungeared Tax Calculations for
    stripping Out the capital Structure
2mins
  • 5e Recap of Tax
1min

MODULE 6: The Balance Sheet - Fitting
Everything Together Like a Jigsaw Puzzle

  • 6a Introduction to the Balance Sheet
7mins
  • 6b Establishing the Balance sheet
    Framework & Linking in Balances
11mins
  • 6c Balance Sheet Checks and Correcting
    Errors Using Timing & Magnitude
9mins
  • 6d Using the Scalpel Method
    to Isolated Errors
6mins
  • 6e Correction Errors to Balance
    the  Balance Sheet
4mins

MODULE 7: Warnings & Alerts Checks to Improve Model Robustness

  • 7a Setting up & Creating Alerts
6mins
  • 7b Creating Warnings, and Using
    Rounding & Formatting
5mins
  • 7c Setting up the Ribbon for Best
    Practice Transparency
6mins

MODULE 8: Extensions

  • 8a  Tax Depreciation - Introduction
    to MACRS Depreciation 
5mins
  • 8b Incorporating MACRS Depreciation
    into the End Model
21mins
  • 8c Closing
1min

X

Boot Module: Project Finance Airport Model

Learn project finance through the lens of an airport. Review the theory of PF, discuss the business case of Heathrow Airport expansion, then dive into the particulars of the project finance model. Go through the case study step by step. Calculate CFADS, tax, debt ratios like DSCR & LLCR, and equity and project returns. Get hands on in the model. Run different cases to optimize the project and meet stakeholders criteria.

MODULE 1: Section Introduction and Context

  • 1A Section Overview
1min
  • 1B Project Finance Primer
10mins

MODULE 2: Project Finance Airport Case Study

  • 2A Case Study Overview
6mins
  • 2B Case Study Timeline and Process
5mins
  • 2C Construction and Operations Calculations
5mins
  • 2D Operations - Cashflow & Metrics
6mins
  • 2E Negotiations & Optimizations
8mins

X

Advanced Debt & Equity for Project Finance Modeling

This section will challenge you to get the most out of a project finance deal. Using advanced material, it expands on our work in Blueprint and asks questions such as:

  • How does DSCR target or gearing driven debt sizing work? 
  • How do I reprofile debt to optimize equity returns?
  • How does a model integrate advanced funding features like a DSRA? 

Go further by adding in sophisticated funding structures, like the option to refinance debt and shareholder loans. Automate debt sizing and the scenario manager to solve the debt case, or run a debt process by evaluating multiple lender terms.

MODULE 1: Overview of the Course

  • 1A Welcome to the Course
1min
  • 1B Course Content
3mins
  • 1C Intro to model
5mins

MODULE 2: The Relationship Between
Debt Sculpting & Debt Sizing to a Term Sheet

  • 2A Introduction to Debt
    Scupting & Debt Sizing
5mins
  • 2B Seeing the Relationship Between Debt
    Sculpting & Sizing with the Mini-model:
    Setup, Sculpting & Tenor vs Sizing
3mins
  • 2C Seeing the Relationship between
    Debt Sculpting & Sizing with the Mini-
    model: Debt Sizing
3mins
  • 2D Seeing the Relatinship Between
    Debt Sculpting & Sizing with the Mini-
    model: Resolving Circularities
2mins
  • 2E Seeing the Relationship between Debt
    Sculpting & Sizing with the Mini-model:
    Manual sizing, Bisection Method, Recap
4mins
  • 2F Capital Structure - Macro Sheet & Max
    Permissible Debt Size
5mins
  • 2G Capital Structure - The Calculated/Applied
    Structure and Linking
5mins

MODULE 3:Writing Elegant & Robust Macros to size Debt Quickly 

  • 3A Introduction to Macros
3mins
  • 3B Recording & Triggering  a
    Macro to Get Started
6mins
  • 3C Getting into VBA & Refining Code 
5mins
  • 3D Using the For Loop to Iterate
    the Macro
5mins
  • 3E Improving Robustness &
    Using Named Ranges
2mins
  • 3F Using the Do While Loop to
    Iterate the Macro
2mins
  • 3G BAsic Improvements to
    Speed up the Macro 
3mins
  • 3H Recap to Understand the Role of Loops,
    Using shortcuts and Speed Improvements
6mins

MODULE 4: Incorporating a Debt Service Reserve Account to Reduce the Probability of Default

  • 4A Introduction to the DSRA
4mins
  • 4B Overview of the DSRA - The Logic Behind
    Addition & Releases of Cash
5mins
  • 4C Setting up the DSRA &
    Establishing the Target Balance 
7mins
  • 4D Funding the DSRA from
    Sources of Cash
5mins
  • 4E How to Break the Circularity &
    Creating a DSRA Macro
5mins
  • 4F Funding from Cashflows to
    Top-Up the DSRA
11mins
  • 4G Release from DSRA in the
    Event of CFADS Shortfall
4mins
  • 4H Release from DSRA for DSRA in
    Excess of Target Balance
3mins
  • 4I Review of DSRA, Linking to
    Financial Statements and
    Distribution Lock-Up Flag
7mins

MODULE 5: Introduction -  Motivations for Refinancing Debt & Types of Refinancing

  • 5A Introduction - Motivations for
    Refinancing Debt & Types of Refinancing
2mins
  • 5B Inputs for Refinancing Debt
3mins
  • 5C Timing of Refinancing Debt &
    Modifying the Senior Debt Structure
2mins
  • 5D Calculating the Refinancing Fee
4mins
  • 5E Calculating the Refinancing Margin  &
    Incorporatng it into the Debt Structure 
5mins

MODULE 6: Sophisticated Equity Structures with Shareholder Loans 

  • 6A SHL - Introduction to Shareholder Loan,
    Purpose and Order of Repayment
3mins
  • 6B Creating Inputs for the Shareholder
    Loan and Building an Account
4mins
  • 6C SHL Drawdowns-Integrating the
    SHL into Funding Sources & Updating
    the Capital Structure
7mins
  • 6D Calculating SHL Interest Capitalised
    and Interest Paid
  • 6E Calculating SHL  Principal
    Repayment and Updating Distrbutions
4mins
  • 6F Updating the CFW, Testing and
    Recap
3mins

MODULE 7: Analysis & Advanced Macros

  • 7A Introduction to Automating Debt
    Sizing & Equity Calculations with Macro's
5mins
  • 7B Introducing the Debt Sizing
    Switch & Scheduling Repayment
5mins
  • 7C Creating  a Master Macro
    to Run All Macro's Once
9mins
  • 7d Testing & Updating the Scenario Manager
5mins
  • 7E Setting up the Data Table for a Macro
4mins
  • 7F Writing the Data Table Macro to Run All
    Cases AND Run Debt Sizing
    Macro's where Required
8mins
  • 7G Performing Debt and Equity Analysis
4mins
  • 7H Reprofiling Debt in the Case of
    Gearing Governed Debt Size
    to Improve Equity Returns
5mins

CLOSING

  • 8 Conclusion
1min

X

Bringing Everything Together & Extensions

This section brings together everything we’ve learned so far and delivers a complete project finance model. We start to look into ways of extending the model with material such as how to change the construction timeframe from quarterly to monthly periodicity in a best practice manner, while keeping operations quarterly.

MODULE 1: Bringing Everything Together

  • 1A. Overview & Specific Changes
5mins
  • 1B. Investigating the DSRA Circularity
5mins

MODULE 2: Extending the Model

  • 2A. Extensions: Adjusting Construction Periodicity
    From Quarterly To Monthly
11mins
Close

50% Complete

Two Step

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.