Microsoft Excel specialists

Outline agenda of the 5 papers for the

Certified Professional in Excel for Finance

3 Data extraction and analysis with Microsoft Query, PivotTables and Power BI
Extract data from external sources, manipulate it and produce meaningful reports. Use MS Query and SQL to develop complex data queries and discover advanced PivotTable techniques. With Power Pivot and the Power BI suite of add-ins in XL10/13, generate interactive reports that allow incredible insight for quick, confident decision-making. Delve into your data like never before with the brand-new DAX formula language and perform seriously powerful number-crunching. Learn to implement the next generation of powerful analytical tools.
Data import
  • Import data from text files
  • Retain import settings and allow users to specify parameters
  • Manipulate imported data for downstream analysis
    Direct data extraction
  • Use MS Query to import subsets of external data records
  • Join tables in MS Query
  • Modify SQL to write complex queries
    Advanced PivotTables
  • Produce a profit and loss report with individual month and YTD values
  • Develop a balance sheet with running totals
  • Remove restrictions imposed by calculated items and grouping
  • Create and work with multiple PivotCaches
  • Enhance reports with PivotCharts and XL10+ Slicers
  • Create a common database source for multiple PivotTable workbooks
  • Create a drill-down through multiple layers to base data.
    Power Pivot and the Power BI suite (XL10/13)
  • Create a Power Pivot PivotTable from multiple data sources
  • Define relationships to replace lookup formulas
  • Discover the new DAX formula language
  • Utilise time intelligence functions for financial year calculations
  • Compare time periods with time intelligence Measures
  • Develop an interactive variance report with KPIs, Slicers and more
  • Harness the power of CUBE functions and OLAP cubes
  • Build a dynamic, interactive cash flow report
  • Consolidate and manipulate data with Power Query
    …and much more