Fundamentals of Financial Modeling

Day One

Excel Skills for Modeling

Excel can be used like a calculator. This session converts participants to using Excel in best practice ways for modeling so that repeatable, consistent, flexible and robust formulae and structures can be created.

• Effective use of keyboard shortcuts
• Dynamic referencing
• Formatting data professionally
• Style basics

Introduction to Modeling

What are the key attributes of a model. Starting the design of a model focused on the required outputs will allow us to work backwards to understand what modules are required to get to this point. However, the structure must allow flex – things change.

• Effective construction of a model
• Objective and structure
• Initial preparation

The Fundamentals of Model Building

Applying best practice approaches we create a model required for analysis, and then add create some forecasts

• Model set up
  • Consistent sheet and print set-up
  • Input vs formulas
  • Inputting base data
• Key Excel functions and tools
  • IF (and other logicals) with flags
  • Setting up date flexibility throughout the model using EDATE or EOMONTH
  • Text strings
  • Conditional formatting
• Adding the financial data
  • Data transfer and links
  • SUMIF and data validation to extract the relevant data
  • Breakdown and grouping of accounting elements
  • Determine drivers of the forecasts
  • Using Excel tools to identify trends
  • Effectively creating assumptions for projections
• Model diagnostics and checks
  • Identifying what could go wrong and flagging up any errors
• Flexible and user-friendly models
  • Quick build scenario manager with switch – base case vs downside vs upside

Auditing and Debugging

Picking up someone else’s model can be challenging. This session guides the participants through an effective approach to reviewing models.

• Using existing models
• How to become comfortable with ‘new’ models
• Auditing models

Formatting and Showcasing

A quick overview of the best approaches to creating an output sheet are explored.

• Creating an output sheet / dashboard
• The camera
• Professional showcase
• Use of graphs