ACF Academy
Operations and IT Training Training
Category:
 Operations and IT Training
Targeted Audience:
 Anyone who needs to master Excel
Prerequisites:
 None
CPE Credits:
 7 hours
Course Level:
 Foundation
 
Date  DurationCostVenueRegister
30 Jan 2019  1 day$795.00New York
4 Feb 2019  1 day$795.00New York

Excel Masterclass

 
The principal objectives of this intensive one-day seminar are to:
 
  Share tips and techniques for using Excel efficiently
  Demonstrate “best practice” in designing workbooks and spreadsheets
  Explain the most important functions within Excel
  Provide insight into some of the advanced techniques for using Excel
  Give participants confidence in applying Excel in real-world financial applications
 
Course Outline
   
 Introducing Excel
   
The Excel interface
The main tabs: Home, Insert, Page Layout, Formulas, Data, Review, View
The ribbon
Worksheets and workbooks
Rows and columns
Cells
Ranges
Numbers and text
Simple Excel formulas
Simple formatting options
Some Excel shortcuts
Creating an invoice spreadsheet
   
 Editing a Spreadsheet
   
Organising the ribbon and quick-access toolbar for efficiency
Absolute and relative references
Copy and pasting
Pasting values, formulas, and formats
Using “drag and drop” for moving and copying cells
Filling cells
Merging and unmerging cells
   
 Key Design Issues
   
Organizing the workbook: Data, Assumptions, Results
Freezing rows and columns
Formatting numbers and dates
Using borders, fonts, colors, and shading to enhance visual appearance
Using Range Names for cells and areas
Auto-formatting for tables
Creating and formatting charts
Formatting for printing
 
 Commonly Used Excel Functions
   
Financial: FV, PV, NPV & XNPV, IRR MIRR  & XIRR, PMT, RATE, NPER
Date and Time: DATE, DATEVALUE, TODAY, WEEKDAY, EOMONTH, YEARFRAC
Maths and Statistical: MAX, MIN, SUM, AVERAGE, MEDIAN, RANK, COUNTIF
Using PMT to calculate mortgage repayments
   
Further Excel Functions
   
Lookup, Logical, and Information: ADDRESS, INDIRECT, CHOOSE, COLUMN, ROW, HLOOKUP, VOOKUP, LOOKUP, INDEX, MATCH,
IF, AND, OR, NOT, ISBLANK, ISERROR, ISNA, NA
Text: CHAR, FIXED, LEFT, MID, RIGHT, LOWER, UPPER, PROPER, TEXT, VALUE
Forecasting: FORECAST, SLOPE, INTERCEPT, PEARSON, STEYX, TREND, GROWTH, LINEST, LOGEST
Forecasting workshop
   
 Advanced Excel Techniques
   
Using Range Names
Custom formatting
Conditional formatting
Protecting workbooks and worksheets
Hiding cells and formulas
Grouping rows and columns on a spreadsheet
Arrays and array formulas
Using Goal Seek and the Solver
Recording and running macros
Creating simple “controls”: command and option buttons
Switching scenarios using radio-buttons

 

NB All practical sessions are highlighted like this:
means a Workshop or Simulation
means a Case study
 
Accreditation


 

"Very interesting material – excellent instructor."

– Jason S.