2 Days

Course Delivery: Live Online

Course Objectives 

CPD Certified

Excel is undoubtedly a fantastic business tool. The trainer-led, intensive course is necessarily hands-on at all points with the participants harnessing Excel to create solutions to a wide variety of financial and organizational tasks. We move away from using Excel as merely a calculator to dynamically using it as a tool which has enormous functionality, is easily repeatable, efficiently and clearly formatted and easily understood.

Key Learning Outcomes:

  • Develop a “toolkit” of Excel tips and techniques
  • Sensitize and stress test Excel outputs from spreadsheet and models
  • Problem solve within an Excel environment
  • Analyze large volumes of data
  • Rank data and statistically analyze large data volumes
  • Efficiently data mine and analyze large data volumes
  • Confidently use Excel’s keyboard shortcuts to improve their speed and efficiency
  • Confidently develop Excel formulae in order to build robust solutions
  • Quickly and consistently format presentation-quality spreadsheets

Who Should Attend

This two day course is suitable for anybody needing to rapidly improve their Excel skills, and would be useful for professionals needing to data mine, analyze and efficiently work with large data volumes, as well as for those needing to build spreadsheet to stress and simulate data outputs. The skills addressed in this program will be used and applied in our financial modelling programs. The financial modelling programs focus on forecasting and valuation within an Excel environment.

Related Courses 

Day One
Get Quicker, Faster

You’ve maybe seen those in the office who’s fingers quickly skim across the keyboard to create magic on their screens? We start the course by setting up Excel to have its most efficient settings before looking at how to quickly use the keyboard to more accurately navigate and manipulate the data on a spreadsheet.

  • Setting up your profile
    • Building your own quick access toolbar
    • Calculation options
  • Keyboard shortcuts (to make you 8 times faster)
    • Navigation
    • Highlight and moving data
    • Spreadsheet manipulation
  • Print set up for perfect printing
  • Shortcut exercise: Attendees are given a spreadsheet which has a number of errors. The task is to correct the spreadsheet without using the mouse. Attendees will be provided with the Fitch Learning Excel Shortcut card.
Efficient Presentation Protocols

If we do this right, you’ll never have to do any formatting again in your life. It’s all there in Excel, but most people don’t know where to look.

  • Inputs vs. formulae
  • pdf tricks for efficient data extraction into Excel
  • Customizing numbers (percentages, multiples, dates, thousands, millions, etc.)
  • Styles – the quick way to format and create formats that can be used again and again and again
    • Building and developing a style template
    • Merger styles between models
  • Format exercise: Data within the model must be quickly formatted appropriately
Building Flexible, Auditable Formulas in Excel

Copy and pasting is easy. If I get my referencing correct I can quickly move away from the “Excel as a calculator” mode to “Excel as an efficient business tool” (mostly using copy and paste). To add the context of efficient Excel practices we also explore how any formula can be easily audited and spreadsheets debugged.

  • Relative vs. absolute referencing
  • Using names to increase formula efficiency
    • Creating cell and range names
    • Applying and redefining names
    • Naming conventions
  • Spreadsheet debugging skills
    • Building diagnostics into models
    • Auditing skills
      • F5 functionality
      • Watch windows
    • Using the camera
    • Link elimination
    • Circularity issues
    • Unnecessary macro creation
  • Dollarizing and naming exercise: Attendees will be asked to code a revenue matrix using relative and absolute formulae
Using the Must-know Functions I

There are nearly 500 functions in Excel. However, which are the ones we need most? This session introduces and uses these key functions.

  • Logical statements
    • IF, AND, OR functions
    • Advanced logicals with flags
    • Formula layout to simplify
    • Snapping formulae (Alt-Enter)
  • Data management and analysis within Excel
    • Data sorting and filtering
    • “Wild-card” SUMIF, COUNTIF
  • Expenses exercise: Using the logical functions and other Excel skills picked up in the day we build an expense claim form from scratch, with transactions of different types, dates and currency.
Day Two
Using the Must-know Functions II

We are often faced with a large amount of data from which we must extract relevant outputs. This session looks at the different ways in which this can be done, identifying the benefits and limitations of each.

  • Data mining in Excel
    • CHOOSE
    • INDEX
    • OFFSET
  • Data mining exercise: Attendees are provided with a spreadsheet containing a large volume of data. Excel skills will be developed that will allow attendees to analyze, rank and disaggregate the data.
  • Working with text and dates in Excel
    • Text strings (dynamic text)
    • TEXT functions
    • RIGHT
    • LEFT
    • LEN
    • EDATE
  • Building diagnostics into your model to monitor and sanity checks outputs
  • Working with circularity in Excel
  • Conditional formatting – uses and dangers
  • Auditing tools
Building Flexible Charts in Excel

A picture paints a thousand words. In the right hands, so can a chart. Quickly creating and updating the chart to make it as flexible as possible is the key.

  • Chart set ups
  • Chart formatting
  • Dynamic labels
  • Building bridge charts
  • Chart exercise: The exercise will quickly build a line chart before editing it to create dynamic labels and titles and then adding a bar chart on the secondary axis
Analyzing the Output
  • Goal Seek
  • Sensitivity analysis in Excel
  • Pivot tables
  • Paste linking to PowerPoint and Word
  • Case study exercise: Attendees are provided with a large data set that requires formatting, analyzing and evaluating. This case study brings together all of the Excel skills used over the course.
Course Reference Guide

The Complete Financial Modeler is included as part of the program (Normal RRP £80).

This 150+ page reference guide, taken from Fitch Learning’s “Complete Investment Banker” manual, is designed to act as a comprehensive and practical Excel and modelling reference guide. It helps users become more proficient at taking an idea or objective and turning it into a robust, flexible model. It is relied upon globally by our clients to support further training after their course. The guide is applicable for professionals at every level, from Intern through to Managing Director. It contains the clearest explanation and application of technical Excel content in a way that makes it accessible to all.

The program also includes our Excel Short Cut that includes quick access to 40 of the most commonly used shortcuts.