Manchester

Download Calendar

Course details

Day 1

Financial Modeling - Fundamentals Part 1

 

This session teaches participants how to build a three statement integrated financial model, comprising income statement, balance sheet and cash flow statement. Best-practice modelling techniques are demonstrated, to ensure maximum accuracy and efficiency. Participants also learn how to stress-test the assumptions used, to check the model for mistakes and to document it. This session uses simplified teaching models as well as a real company forecast model.

 

Learning outcomes

  • Setting Excel up for maximum efficiency
  • Best-practice keyboard shortcuts for modelling
  • Key modeling formulas
  • Alternative model layouts
  • Structure of an integrated three statement forecast model
  • Constructing the income statement, balance sheet and cash flow statement
  • Balancing the forecast balance sheet using cash and revolver
  • Incorporating interest income and expense
  • Introduction to checking methodologies
  • Performing audit trails

 

Financial Modeling - Fundamentals Part 2

 

Using a real company forecast model, we start the session by reviewing the structure and key components of an integrated three statement forecast model. We then focus on modeling operating cash (required cash) and separating it from excess cash. We then teach participants how to build a forecast cash flow statement from scratch, using income statement and balance sheet inputs. The last part of this session focuses on interest calculations and circularities, where we teach participants the difference between using beginning, ending or average debt/cash balances and how to work safely and effectively with or without circular formulae in a model.

 

 Learning outcomes

  • Review of the structure and key components of a forecast model
  • Review of the modelling steps to build a three statement integrated model
  • Modeling operating cash (require cash) and excess cash
  • Building a forecast cash flow statement from scratch
  • Calculating interest on cash and debt balances
  • What is a circular formula?
  • Working with intentional circular references
  • Avoiding non-intentional circular references

Day 2

Financial Modeling - Cash Sweep

 

In this session we build a three statement operating model for a real company, which incorporates a detailed revenue forecast based on price and volume drivers. We set up a hierarchy of a series of debt items and we learn how to model the sequential debt paydown using a ‘cash sweep’ approach. Best practice modeling techniques are emphasized throughout.

 

Learning outcomes

  • Building a detailed debt schedule
  • The waterfall of debt repayments
  • Building a simple cash sweep
  • Modeling revolver
  • Modeling a price/volume revenue forecast

 

Financial Modeling - Troubleshooting

 

This session covers a range of error-checking techniques to find and correct the most common errors found in financial models. We use a series of financial models to allow participants to practice error-checking.

 

Learning outcomes

  • Stabilizing a model
  • Making a model map
  • Sanity checking a model
  • Fixing a non-balancing balance sheet
  • Troubleshooting techniques for cash flow statements
  • Checking the model’s matrix
  • Finding unidentified hard numbers
  • Using Excel’s ‘Jump tool’ to trace through formulas with ease
  • Using Excel to show the formulas underlying output
  • Using Excel’s auditing tool to trace formulas

Day 3

Excel Power Modeling

 

This program covers a range of tools and techniques in Excel designed to improve effectiveness and efficiency. The program covers building scenarios into models using a variety of functions, together with adding in on sheet controls, sensitivity analyses and flexible output tables. The topics in this menu can be chosen according to delegates’ needs.

 

Learning outcomes

  • Modeling with speed and style
    • Setting up toolbars so that Excel can be used as efficiently as possible
    • Setting up formatting styles
    • Custom number formats
  • Keeping circularities under control
    • Working with intentional circular references
    • Debugging a model with non-intentional circular references
    • Avoiding intentional circular references with a circular reference macro
  • Flexible output tables
    • Use functions to extract information from a model
    • VLOOKUP
    • HLOOKUP
    • OFFSET
    • INDEX
    • MATCH
    • Data validation
    • INDIRECT
  •  Scenarios
    • Building scenarios into the model
    • CHOOSE
    • OFFSET
    • INDEX
    • MATCH
    • On sheet controls
    • Conditional Formatting
  • Working with text and dates
    • Creating dynamic headings and footnotes in the model
    • Working with dates
    • Concatenation
    • TEXT function
    • UPPER, LOWER, PROPER functions
    • TRIM and CLEAN functions
    • RIGHT, LEFT & MID functions
    • DATE, DAY, MONTH, YEAR functions
  • Sensitivity analysis
    • Run sensitivities on model outputs
    • One-input data tables
    • Two-input data tables
    • Three-input data tables
    • Data table secrets
  •  Lists
    • Creating lists
    • Sorting
    • Basic and advanced filtering
    • SUMIF and COUNTIF
    • Calculate with advanced criteria with database functions
    • Pivot tables
  • Other functionality
    • Printing with views
    • Print macro
    • Print macro with a form for easy print selection
    • User defined functions
    • Beauty save macro
    • Protecting your work
    • Charts
    • Arrays
    • Range names
    • Shortcuts

"It's great to have access to the AMT online platform. I found the videos and quizzes really useful to refer back to after my public course. The videos in particular were simple and easy to understand." ~ analyst, private equity firm

NB.

This course is non-residential. The venue will provide light refreshments. AMT reserve the right to cancel or postpone sessions or change content if registrations are insufficient to continue 2 weeks prior to scheduled commencement date. Registrants will be given at least 5 business days’ notice of such changes.