Excel for Finance – Masterclass

  Intermediate CPD: 7 hours   1 day

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 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): 21 Mar 2019

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.


  

Bookings are closed for this event.

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