Excel intensive: financial modelling, data management and reporting 2018 - 2 day workshop

Description

This 2 day 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 you with a deeper knowledge of Excel and the skills to use Excel more effectively in your 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 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

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. Please enrol at least 3 working days prior to the workshop to ensure any dietary and special requirements can be accommodated.

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