Microsoft Excel specialists

Outline agenda of the 5 papers for the

Certified Professional in Excel for Finance

1 Conventions, formulas and other essentials
Part 1a – Efficient operation
  • Set up Excel optimally
  • Use good technique to operate Excel efficiently
    - For regularly-used commands, use the keyboard
    - Set up a somewhat standardised Quick Access Toolbar
    - Access files and folders efficiently
    - Understand and use the different Excel file types
    - Backups and recovery
    - Navigate quickly
    - Manage multiple windows
    - Manipulate data
  • Find a template
    Part 1b – Formats
  • Create custom number formats
  • Format quickly and consistently with Styles
  • Learn formatting tips
    - Avoid merged cells
    - Avoid hidden rows and columns
    - Make use of Outlines
    Part 1c – Conventions
  • Understand the need for standards
  • Apply conventions for the layout of a sheet
    - Discover the benefits of range names
    - Apply sheet structure and formatting conventions
    Apply conventions to headings
    Minimize borders, include gutters and apply conventions to totals
    Apply conventions to alignment, input cell colour, underline and case
    Document cells
    Apply conventions to print
  • Apply conventions for the layout of sheets within a workbook
    - Structure the sheets of a workbook for clarity
    - Create a Params sheet for settings
    - Document for ease of use and maintenance
    - Add Lists and Welcome sheets if needed
    - Include a Menu sheet when there are many sheets
    - Arrange each section per sheet, name sheets and synchronise
  • Apply conventions to manage files
    Part 2a – Formulas
  • Learn important aspects of formulas
    - Understand calculation precedence, number precision and its issues
    - Be aware of dangers with calculation mode, bug and circular references
    - Be aware of causes of slow calculation
    - Design formulas to be robust
    - Discover array formulas
    - Know how to create robust file-links
    - Understand the meaning of formulas error types
    - Check and trace formulas
    - Discover issues with numbers as text and convert
    - Set the formula warning messages as appropriate
    - Make formulas as clear as possible
    - Understand the pros and cons of custom functions
  • Master conditional logic with IF and Boolean formulas
    - Learn IF, AND, OR, NOT, IFNA, IFERROR and IS functions
    - Shorten formulas with Boolean logic
    - Use MIN and MAX as short alternatives to Ifs
    - Use N to avoid #VALUE!
  • Total
    - Use SUBTOTAL to avoid omissions and double-counting
    - Use INDEX for current month and year-to-date formulas
    - Sum absolute and largest values
    - Calculate with SUMPRODUCT
    - Learn all about SUMIF, SUMIFS, COUNTIFS, DSUM and other D-functions
    - Create a cross-tabulation report
  • Look up and compare
    - Understand VLOOKUP, INDEX, MATCH, the issues and solutions
    - Make lookups robust
    - Create faster lookups
  • Calculate with dates and times
    - Calculate service years & months
    - Accurately calculate the age of an asset in months
    - Calculate time differences and totals
    - Use YEAR, MONTH, DAY, DATE, DATEDIF, NETWORKDAYS and other date functions
  • Manipulate text
    - Concatenate and split
    - Remove spaces and substitute unwanted characters
    - Change case
  • Use statistical formulas
    - Group data with the FREQUENCY function
    - Count unique values
    - Calculate a TREND and determine the goodness of fit
  • Learn accounting formulas
    - Create a formula that pays every third month
    - Cash flow forecasting of sales, debtors, purchases and creditors
    Calculate cash from a constant debtor days across months
    Calculate cash from a debtor days figure that varies by month
    Calculate receipts with a formula that works for any number of debtor days
    Incorporate actuals in a cash flow model
    Calculate a debtor (or creditor) days value
    Calculate cash from sales using a debtors profile
    Calculate cash from sales using SUMPRODUCT & TRANSPOSE
    Create a rolling cash flow forecast
    Create rolling month titles
    - Calculate debtor days
    - Learn depreciation calculations
    - Calculate with loans
    Calculate loan payment and interest
    Convert nominal & effective interest rates
    Part 3a – Tables, filtering, import, export and consolidation
  • Make spreadsheets more reliable by creating Tables and referring to them
    - Include Table references in formulas
    - Source PivotTables, Validation Lists and Charts from Tables
    - Build crosschecks into charts
    - Set the Print Area to self-adjust
    - Apply the ESPTable style
    - Enter data into a Table with a Form
    - Apply advanced filters
  • Make Table formulas reliable by using Table references
    - Duplicate Table formulas correctly
    - Create running totals, row-by-row differences and row index numbers
  • Filter and sort
    - Sort and Filter by colour and icons.
    - Sort into non-alphabetical order (such as month names)
    - Discover Advanced Filter and any criteria you require
    - Create a unique list
    - Remove unwanted records from imported print files
  • Import
    - Import text files    
    Use the Text Import Wizard
    Keep settings for a repeat import
    Solve import problems
    - Extract directly from source
    Download selected records with Query Wizard or MS Query
    Join tables in MS Query
    Append columns in Excel
  • Export
    - Export CSV and fixed-width text files
  • Consolidate
    - SUM through sheets and synchronise
    - Consolidate with Data Consolidate
    - Create a consolidation PivotTable from multiple data ranges
    - Append tables and build a regular PivotTable
    Part 3b – Charts, Conditional Formatting and PivotTables
  • Create Charts 2007-2010
  • Create Charts 2013-2016
    - Create and format a simple Column chart
    - Alter a chart to suit your needs
    - Create Column, Bar, Line and Pie charts
    - Combine chart types
    - Create a Waterfall chart
  • Adopt chart conventions
    - Determine whether a chart, table or both is the best way to communicate
    - Choose the appropriate chart type
    - Generally, be monochromatic, but for many series, use soft colours
    - Remove distractions
  • Apply Conditional Formatting
    - Format cells based on their values
    - Set formula-based criteria
    Format entire rows based on the value of a single cell
    Highlight cells based on the sum of other cells
    Shade alternate rows
    - Apply icon sets
    - Highlight KPIs with Data Bars or traffic light icons
    - Shade duplicates
    - Shade actual months
  • Build PivotTables reports
    - Create and format a PivotTable report
    - Refresh and rearrange
    - Show percentages of column total
    - Display only certain countries and remove total
    - Drill down to underlying data
    - Chart a headcount report
    Part 3c – Collaboration, reconciliation, protection, crosschecks, errors, data validation, testing and troubleshooting
  • Collaborate
    - Compare collaboration approaches
    - Share on a Network server without using Shared workbook
    - Use Shared workbook
    - Master basic Cloud: Sign up, share folder, use Excel Online
    - Master advanced Cloud: Compare methods – Excel Online, Excel2013/16, Download
  • Reconcile
    - Line up for visual comparison
    - Remove duplicates
    - Compare by using formulas
  • Protect
    - Protect some or all of a workbook from change
    - Provide password entry to certain ranges
    - Hide sensitive data
  • Input validation
    - Allow only certain entries
    - Create conditional drop-down list selections
    - Check the validations set-up
  • Add crosschecks
    - Avoid many kinds of errors by inclusion of crosschecks
    - Understand the issues in creating effective crosschecks
    - Apply conditional formatting to make the errors stand out and not be overlooked
    - Create crosschecks areas and a crosschecks sheet for ease of checking
  • Know common errors and how to avoid them
    - 12 common errors and the solutions
    - Test
    - Use patterns to quickly spot errors
    - Reset inputs to zero
    - Clear all data in order to test one aspect and then reinstate the data
  • Troubleshoot
    - Clear deadwood from overly-large files
    - Find the causes of Excel slowing down
    - Deal with Out of memory or not enough resources message
    - Fix problems with Comments, Conditional Formatting, Corrupted Files, Excel crashing
    Part 4a – Macros
  • Create a simple macro for a repetitive task
  • Record and edit a print macro
  • Automate an import application
  • Prompt for input and check for valid entry
  • Loop to delete records
  • Understand objects, properties, and methods
  • Create custom functions
  • Create a custom dialog box
  • Use error-trapping to prevent crashes for anticipated errors
  • Send an email that has an attached spreadsheet
    Part 5a – Applications
  • Import trial balance and report
  • Consolidate business unit actuals from report downloads
  • Categorise imported records on multiple-column criteria and create reports
  • Maintain a fixed assets register
  • Create a weekly cashflow application
  • Build a multi-unit import, translate, adjust and report applications