Excel for Financial Professionals
Key Learning Outcomes:
- Develop a “toolkit” of Excel tips and techniques
- Sensitise and stress test Excel outputs from models
- Problem solve within an Excel environment
- Analyse large volumes of data
- Rank data and analyse large data volumes
- Data mine large data volumes and analyse
- 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
Get Quicker, Faster
- Setting up your profile
- Building your own quick access toolbar
- Calculation options
- Keyboard shortcuts (to make you 8 times faster)
- 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
- Inputs vs. formulae
- pdf tricks for efficient data extraction into Excel
- Customising 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 Formulas in Excel
- Relative vs. Absolute referencing
- Using names to increase formula efficiency
- Creating cell and range names
- Applying and redefining names
- Naming conventions
Dollarizing and naming exercise: Attendees will be asked to code a revenue matrix using relative and absolute formulae.
- 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
- MAX, MIN, AVERAGE
- SUMIF, COUNTIF
- “Wild-card” SUMIF, COUNTIF
Data mining in Excel
Data mining exercise: Attendees are provided with a spreadsheet containing a large volumes of data. Excel skills will be developed that will allow attendees to analyse, rank and disaggregate the data.
Working with text and dates in Excel
- Text strings (dynamic text)
- TEXT functions
Building diagnostics into your model to monitor and sanity checks outputs
Working with circularity in Excel
Conditional formatting – uses and dangers
Building Flexible Charts in Excel
- Chart set ups
- Chart formatting
- Dynamic labels
- Building bridge charts
Spreadsheet Debugging Skills
- Building diagnostics into models
- Auditing skills
- Watch windows
- Using the camera
- Link elimination
- Circularity issues
- Unnecessary macro creation
- F5 functionality
- Spreadsheet debugging exercise: Attendees are provided with a spreadsheet containing a number of Excel errors and issues. Using their Excel skills they will be asked to fix and eliminate the errors included in the work and present the spreadsheet in a professional format
Analysing the Output
- Sensitivity analysis in Excel
- Techniques to minimise memory usage
- Self centering data tables
Data table exercise: Attendees are provided with a spreadsheet containing a simple calculation with a number of sensitive inputs. They will build a data table that sensitises the output. The attendees will also build in diagnostics that ensure that the data tables are up to date.
- Pivot tables
- Paste linking to Power Point
Case study exercise: Attendees are provided with a large data set that requires formatting, analysing and evaluating. This case study brings together all of the excel skills used over the past two days.
Course Reference Guide
The Complete Financial Modeler is included as part of the programme (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 modeling 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 programme also includes our Excel Short Cut that includes quick access to 40 of the most commonly used shortcuts.
Who should attend
This programme is suitable for anybody needing to rapidly improve their excel skills. The programme would be useful for professionals needing to data mine, analyse 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 programme will be used and applied in our financial modelling programmes. The financial modelling programmes focus on forecasting and valuation within an Excel environment.