Excel for Finance – Masterclass

  Intermediate CPD: 7 hours   1 day

Description

Excel for Finance - Masterclass

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 Range Names 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, PowerBI, 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 Range Names – 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
  • The Get & Transform Utility
  • Forecast Sheets
  • PowerPivot & the PowerBI Tools
  • Data Analysis Expressions (DAX)
  • computer Data retrieval and analysis workshop

Dates and Locations

Date
Date(s): 24 Jan 2020

Location
New York

Category
Modeling and Excel

Other Dates and Locations
Check our course schedule for alternative dates and locations where this course is offered.


  

$635.00


ACF specialises in creating tailored courses for our clients. Fill out the details below to obtain more information on how to run a customised version of this course in-house for your firm.

Fields marked with * are required