Formatting and Efficiency Skills
– Custom number formatting
– Conditional formatting – Basic and Advanced
– Cell styles template – How to create, apply and update
– Excel Quick Access Tool Bar – How to set up
– Keyboard shortcuts for speed and efficiency
– Cell Names and Range Names – Create, edit and delete
– Auditing and Analysis file/model: Auditing tool- F5(special)
Working with Functions
– Basic Math Functions: SUM, AVERAGE, MAX, MIN, SUMIF, COUNTIF, LARGE, SMALL
– Logical Functions: IF and nested IFs, AND, OR
– Date functions: EOMONTH, EDATE, YEARFRAC, DATE, TODAY
– Lookup functions: VLOOKUP, HLOOKUP, LOOKUP
– Text functions: RIGHT, LEFT, LEN, TRIM, PROPER, UPPER, LOWER
– Advanced Data Analytics functions: SUMPRODUCT, INDEX, MATCH, OFFSET, CHOOSE, AVERAGEIF, MEDIANIF, MAXIF, MINIF
– Time Value of Money functions: PV, FV, PMT, NPV, RATE, NPER, IRR, XNPV, XIRR, MIRR
– Statistical functions: CORREL, STDEV, NORM.INV, LARGE, SMALL, MEDIAN, MODE, PERCENTILE
Tools to improve efficiency
– Data Validation: Drop-Down Lists & Customizing spreadsheets and controlling data inputs
– What If Analysis: Data Table: Sensitivity Analysis, Goal Seek and Scenario Manager & Scenario Analysis
– Developer tool: VBA Drop-Down Lists, Check Boxes & Working with large Data Sets: Sorting, Sub-totals, Filtering & Using Excel “Wild-Card” tools for Data Mining
Charting in excel
– Types of charts
– Choosing the right chart for the data
– Dynamic chart titles and labels
– Combo Charts & Interactive charts – Checkboxes and Dropdowns
– Chart Annotations
Pivot Tables
– Uses of Pivot tables
– Adding fields to the report: Filters, Rows, Columns, Values
– Placement of pivot table fields
– Adding layers & rearrangement
– Changing Values field: SUM, MAX, MIN, AVERAGE, etc.
– Inserting calculated fields
– Creating a report filter
– Renaming of your pivot table
– Moving the pivot table Defer layout update (large data sets)
– Clearing all and data refresh
Advanced Financial Analytics
– Simple and Multiple Regression Analysis tools
– Monte Carlo simulation: Predict investment performance
– Bell curve: Analyze investment performance
– Calculate historical and analytical Value at Risk (VaR)
– Hedge fund return analytics: LP perspective
– Private Equity Fund analytics: LP perspective
