Intermediate | CPD: 7 hours | 1 day | Live |
Description
The Excel for Finance Masterclass is a practical one-day class that will leverage participants’ skills to become highly proficient at using Excel’s more powerful features for a wide range of applications in business and finance.
What really sets our program apart is the combination of small classes, personal supervision from an experienced instructor, and plenty of hands-on exercises where participants can immediately put their newly-acquired skills into practice.
By the end of the course, participants will become adept at harnessing advanced Excel capabilities in real-world business applications.
Learning Outcomes
By attending this course, you will:
- Apply a wide range of Excel’s more advanced functions for manipulating dates and text, performing conditional analysis, retrieving data, and handling errors
- Explore Excel’s forecasting capabilities, including the new Excel Forecast Sheets
- See how Named Ranges can be sued to increase the transparency of financial models
- Design your own custom formats for numbers and dates
- Apply the power of conditional formatting
- Understand Excel’s goal seek and solver optimization capabilities
- Create dynamic charts that extend Excel’s in-built charting capabilities
- Perform sensitivity analyses using data tables and scenarios
- Add control elements like drop-downs, buttons, check boxes and spin buttons to a spreadsheet
- Analyse large datasets with pivot tables and pivot charts
- Explore some of the latest Excel techniques like Get & Transform, Power BI, and DAX
Who Should Attend
Anyone who wants to leverage their capabilities in using Excel.
Prerequisites
Practical working knowledge of the basic features of Excel.
Seminar Content
Intermediate / Advanced Excel Functions
- Financial: FV, PV, NPV & XNPV, IRR MIRR & XIRR, PMT, RATE, NPER
- Date and Time: DATE, DATEVALUE, TODAY, WEEKDAY, EDATE, EOMONTH, YEARFRAC
- computer Dates manipulation challenge
- Conditional: COUNTIF(S), SUMIF(s), AVERAGEIF, MAXIFS, MINIFS
- Advanced Lookup: INDEX, MATCH, COLUMN, ROW, INDIRECT, ADDRESS
- computer Data retrieval challenge
- Logical: IF, AND, OR, NOT
- Information: ISBLANK, ISERROR, ISNA, NA
- Text: LEFT, MID, RIGHT, LOWER, UPPER, PROPER, TEXT, VALUE, FIXED, CONCAT
- Forecasting: FORECAST, SLOPE, INTERCEPT, PEARSON, TREND, LINEST
- Forecasting with the new Excel Forecast Sheet
- computer Forecasting workshop
Advanced Excel Techniques
- Using Named Ranges – define, create, apply, paste, manage
- Advanced custom and conditional formatting
- computer Conditional formatting workshop
- Data validation
- Protecting worksheets and workbooks
- Hiding and un-hiding sheets and formulas
- Outlining a spreadsheet
- Grouping of rows and columns
- Arrays and array formulas
- Goal seeking and the solver – optimization techniques
- computer Investment optimization challenge
- Dynamic charts
- computer Creating and using a dynamic chart
- 3D Maps
- Using Excel Data Tables to perform structured sensitivity analyses
- Using Excel Scenarios for different assumptions
- Parsing text data from non-Excel sources
- Recording, editing, and running a macro
- Creating simple “controls”: command and option buttons
- computer Switching scenarios using radio-buttons
Working with Large Data
- Sorting and filtering
- Advanced Filter
- Creating an Excel Table
- Fields and records
- Organizing data
- Filtering and sorting, with complex criteria
- Summarizing and analyzing data
- Creating a PivotTable
- Labels, values, and report filters
- Creating a PivotChart
- computer Pivot table workshop
New Excel Techniques
- Power Query (Get & Transform)
- Forecast Sheets
- Power BI
- Data Analysis Expressions (DAX)
- computer Data retrieval and analysis workshop
When and Where
18 Feb 2025
09:00-17:00
London
Data Science
Other Dates and Locations
Search for Excel for Finance – Masterclass in our course schedule for alternative dates and locations where this course is offered.