Excel intensive: financial modelling, data management and reporting – 2 day workshop 2017

This workshop focuses on Excel tools that can be employed for modelling and report building. These include advanced charting techniques, functions, Tables, PivotTables and the use of dynamic range names. This course is designed to provide participants with a deeper knowledge of Excel, including many of the newer features introduced in 2010 and 2013, providing them with the skills to use Excel more effectively in their work environment.

Key topics:

Day 1 Financial models and data management

  • Develop efficient spreadsheet models
  • Interrogate spreadsheet models using techniques such as what if analysis, goal seek analysis, sensitivity analysis, simulation and optimisation
  • Work with Excel’s auto and advanced filters
  • Use Lookup and Reference functions, such as Index, Match and Offset
  • Use conditional functions, such as COUNTIF, COUNTIFS, SUMIF, and SUMIFS
  • Create and employ Dynamic range names for automatic updating of reports
  • Use Excel Tables

Day 2 Building Excel dashboards and PivotTable reports

  • Use Tables and Pivot Tables
  • Describe the benefits of dashboard reporting
  • Develop charts and use functions suitable for dashboard reports
  • Employ Form Controls and dropdown lists to view different reports
  • Build example dashboards to display key metrics for a business organisation that can be automatically updated

Learning manual provided.

Offered in Excel 2013 and compatible with Excel versions 2007, 2010.

Learning objectives

  • Describe the benefits of properly constructing spreadsheet based financial models
  • Design structured spreadsheet models that clearly identifies inputs, calculations and results
  • Build an efficient model to incorporate the measurement of risk
  • Carry out What If and Scenario analysis, including the use of in-built scenario summaries and scenario pivot tables
  • Develop optimisation models using Solver and have an appreciation for the reports generated by Solver.
  • Choose Lookup and Reference functions, such as Index, Match and Offset
  • Identify conditional functions, such as COUNTIF, COUNTIFS, SUMIF, and SUMIFS
  • Extract summary information from databases using conditional functions
  • Create array formulae to create summary reports
  • Use dynamic range names effectively with conditional functions
  • Use structured referencing to extract information from Excel Tables
  • Create a data model which funnels source data to a Dashboard
  • Create various chart types for use with Excel Dashboards
  • Employ key Excel functions to structure the calculation area of a spreadsheet model
  • Build and use PivotTables supported by dynamic range names
  • Use Sparklines to display trends in data
  • Create Slicers to display different reports instantly
  • Use Timelines to view reports in different time periods
  • Add interactive controls to Dashboards
  • Build Dashboard reports in Excel 2013

Audience

This workshop is designed specifically for finance and accounting professionals who use Excel in their day to day roles.

Please note enrolments close 2 working days prior to the workshop date.

Looking for a customised learning solution for your organisation or team? Contact us today to discuss in-house learning options.

2 days

loading...