Excel for Finance – Masterclass

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

Book Now!

Other Dates and Locations
Search for Excel for Finance – Masterclass in our course schedule for alternative dates and locations where this course is offered.