Financial Modeling in Excel (3 days)

Day 1 - Modeling Standards and Building an Integrated Financial Model

Participants begin with basic keyboard drills and shortcuts, and then rapidly progress to efficient formula construction and basic modeling and checking techniques. Using simple practice models, participants complete basic projections and start learning how to integrate the financial statements in a spreadsheet. The problem of circularities is addressed in detail, and participants learn how to use the iteration tool to solve circularities. Participants also learn how to stress-test the assumptions used, to efficiently check their work and to document it. Although simple, the sample companies contain the basic parts common to all projection models.

Key Topics:

• Basic keyboard shortcuts
• Excel settings for efficiency
• Key modeling formulas and structures
• Simple 3-statement projections
• Standards for good models
• Income statement, balance sheet and cash flow statement construction
• Introduction to checking methodologies
• Audit trails
• Incorporation of interest income and expense
• Circularity and iteration
• Ratios and summary
• Complete additional fully integrated models

Day 2 - Projecting a Real Company

Working with a real company's financials exposes the participants to some of the complexities that they will encounter, while reinforcing the basic methodologies common to the construction of all models. This session covers a more complex cash flow statement and a detailed debt schedule. The session includes plenty of practice time, to allow participants to familiarize themselves with the modeling techniques, increasing their speed and accuracy.

Key Topics:

• Multiple years historical information
• Complex assumptions for income statement and balance sheet accounts
• Building a complete debt schedule
• Consolidating and re-applying knowledge of circularity and iteration
• Building cash flow statements from scratch
• Troubleshooting techniques for cash flow statements
• Error finding and integrity checking

Day 3 - Power Modeling in Excel

Participants are introduced to a variety of techniques and functions in Excel that enable quick and easy scenario and sensitivity analysis in financial models. They will be shown how to overcome some of the more frustrating issues that arise from data downloads such as number formats not being recognized and taught how to automate the extraction of useful information from strings of text and numbers. Further topics include creating charts, printing, and an introduction to macros. Finally, participants apply the techniques to an existing fully integrated model.

Key topics:

• Creating flexible output tables
  • VLOOKUP
  • HLOOKUP
  • INDEX
  • OFFSET
  • MATCH
  • INDIRECT
• Building flexible scenarios
• CHOOSE
• Conditional formatting
• Data validation
• Buttons and Switches
  • Using text functions for data extraction and dynamic headers
  • LEFT
  • RIGHT
  • FIND
  • LEN
  • MID
  • VALUE
  • TEXT
  • Concatenation
• Sensitivity analysis
  • 1, 2 and 3 input datatables
• Building basic Macros